pg配置流同步

配置流同步：

1.两台机器上设置hosts文件

[root@bogon ~]# vim /etc/hosts

主库IP master

备库IP slave

2.主库中创建同步账户

[postgres@bogon ~]$ psql

postgres=# CREATE USER repuser replication LOGIN CONNECTION LIMIT 3 ENCRYPTED PASSWORD 'repuser';

CREATE ROLE

3.主库中配置pg_hba.confsu

在master的pg_hba.conf最后一行增加如下:

host all all 0.0.0.0/0 trust

host replication repuser slave md5

 

4.主库中配置postgresql.conf

max_wal_senders = 2

wal_level = replica

archive_mode = on

archive_command = 'cd ./'

hot_standby = on

wal_keep_segments = 64

full_page_writes = on

wal_log_hints = on

1.  

   master上配置访问slave参数

   [postgres@bogon ~]$ vi ~/.pgpass

   slave:5432:postgres:repuser:repuser

    

6.重启或roload主库：

/usr/pgsql-9.6/bin/pg_ctl restart -m fast -Dvar/lib/pgsql/9.6/data ----重启数据库

7.安装备库

8.初始化后，删除/var/lib/pgsql/9.6/data目录

9.从主库初始化备库：

pg_basebackup -D $PGDATA -Fp -Xs -v -P -h master -p 5432 -U repuser

10.配置recovery.conf文件

cp share/recovery.conf.sample /var/lib/pgsql/9.6/data/

进入：/var/lib/pgsql/9.6/data/

mv recovery.conf.sample recovery.conf

修改文件：vi recovery.conf

加入如下配置：

recovery_target_timeline = 'latest'

standby_mode = on

primary_conninfo = 'host=master port=5432 user=repuser password=repuser'

trigger_file = '/var/lib/pgsql/9.6/data/trigger_file'

 

11.启动备库

/usr/pgsql-9.6/bin/pg_ctl -Dvar/lib/pgsql/9.6/data-l logfile start

重启

/usr/pgsql-9.6/bin/pg_ctl restart -m fast -D /var/lib/pgsql/9.6/data/

启动

/usr/pgsql-9.6/bin/pg_ctl -Dvar/lib/pgsql/9.6/data-l logfile start

关闭

/usr/pgsql-9.6/bin/pg_ctl stop -m fast -D /var/lib/pgsql/9.6/data/