postgresql流复制配置

流复制搭建

一、配置环境：

二、postgresql安装（略）

主库完全安装。备库安装只需要到makeinstall即可不需要initdb。

三、主库配置（在192.168.128.128操作配置）

1、创建复制角色

postgres=#createuserrepreplicationloginconnectionlimit100encryptedpassword'rep123';

CREATEROLE

2、配置pg_hba.conf

hostreplicationrep192.168.128.129/32md5

hostallall192.168.128.1/32trust

3、配置postgresql.conf

wal_level=hot_standby

archive_mode=on

archive_command='/bin/date'

wal_keep_segments=256

max_wal_senders=32

max_standby_archive_delay=300s

max_standby_streaming_delay=300s

wal_receiver_status_interval=10s

hot_standby_feedback=on

hot_standby=on

/app/postgreSQL/bin/pg_ctlrestart

/app/postgreSQL/bin/pg_ctlreload-D/app/postgreSQL/data

四、备库配置（在192.168.128.129）上进行操作

1、创建目录

创建于主库相同的目录并授权如data目录以及后来创建的表空间目录

2、创建密码文件

[postgres@db2~]$vi.pgpass

192.168.128.128:5432:replication:rep:rep123

[postgres@db2~]$chmod0600.pgpass

3、pg_basebackup

[postgres@db2~]$/app/postgreSQL/bin/pg_basebackup-Fp-D/app/postgreSQL/data-h192.168.128.128-p5432-Urep

[postgres@db2data]$cp/app/postgreSQL/share/postgresql/recovery.conf.samplerecovery.conf

4、修改recovery.conf配置

standby_mode=on

primary_conninfo='host=192.168.128.128port=5432user=rep'

trigger_file='/app/postgreSQL/data/postgresql.trigger.5432'

五、启动服务

/app/postgreSQL/bin/pg_ctlstart-D/app/postgreSQL/data

1、主库进程

[postgres@db1~]$ps-ef|greppostgres

postgres1117413893004:13?00:00:00postgres:walsenderprocessrep192.168.128.129(49028)streaming0/30001D0

postgres1118713984004:16pts/100:00:00ps-ef

postgres1118813984004:16pts/100:00:00greppostgres

postgres138931003:46pts/100:00:00/app/postgreSQL/bin/postgres

postgres1389513893003:46?00:00:00postgres:writerprocess

postgres1389613893003:46?00:00:00postgres:walwriterprocess

postgres1389713893003:46?00:00:00postgres:autovacuumlauncherprocess

postgres1389813893003:46?00:00:00postgres:archiverprocesslastwas000000010000000000000002.00000020.backup

postgres1389913893003:46?00:00:00postgres:statscollectorprocess

root1392113890002:10pts/100:00:00su-postgres

postgres1392213921002:10pts/100:00:00-bash

root1398313951002:11pts/100:00:00su-postgres

postgres1398413983002:11pts/100:00:00-bash

2、备库进程

[postgres@db2~]$ps-ef|greppostgres

postgres108551004:10pts/100:00:00/app/postgreSQL/bin/postgres-D/app/postgreSQL/data

postgres1085610855004:10?00:00:00postgres:startupprocessrecovering000000010000000000000003

postgres1085710855004:10?00:00:01postgres:walreceiverprocessstreaming0/3000260

postgres1085810855004:10?00:00:00postgres:writerprocess

postgres1085910855004:10?00:00:00postgres:statscollectorprocess

postgres1086913863004:15pts/100:00:00ps-ef

postgres1087013863004:15pts/100:00:00greppostgres

root138623671002:14pts/100:00:00su-postgres

postgres1386313862002:14pts/100:00:00-bash

六、测试

1、主库建表插入数据

[postgres@db1~]$/app/postgreSQL/bin/psql

psql(9.1.7)

Type"help"forhelp.

^

postgres=#createtabletest(idinteger);

CREATETABLE

^

postgres=#insertintotestvalues(1);

INSERT01

postgres=#select*fromtest;

id

----

1

(1row)

2、备库查看数据是否传输。

[postgres@db2~]$/app/postgreSQL/bin/psql

psql(9.1.7)

Type"help"forhelp.

postgres=#select*fromtest;

id

----

1

(1row)

postgres=#

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!