postgresql 9.2.23 主从复制

postgresql 9.2.23 主从复制
select * from pg_stat_replication;
select * from pg_roles;
select * from pg_user;
CREATE ROLE repl login replication encrypted password '123456';

1、主库修改

 

hg_hba.conf:host all all x.x.x.x/32 trust
host replication repl x.x.x.x/32 trust

postgresql.conf修改:
listen_addresses = '*'
max_connections=200
wal_level=hot_standby
archive_mode=on
archive_command = 'cp %p /var/lib/postgresql/data/pg_bak/%f'
max_wal_senders=4
log_timezone = 'Asia/Shanghai'
timezone = 'Asia/Shanghai'

#主库可以加

hot_standby = on

2、拷贝全库到备库:

 pg_basebackup -h localhost -p 5432 -U repl -F p -x -P    -D /tmp/bak0309

scp -r /tmp/bak0309  x.x.x.x:/var/lib/postgresql/data

 

2、recovery.conf修改

 

 

standby_mode = 'on'
primary_conninfo = 'host=主库ip user=repl password=123456'

3、备库修改:

postgresql.conf参数修改其他参数同主库
hot_standby = on

 

4、暂停复制

#暂停复制

select pg_xlog_replay_pause();

#恢复复制
select pg_xlog_replay_resume();

 

#提升为主服务器

 /usr/lib/postgresql/9.2/bin/pg_ctl -D /var/lib/postgresql/data promote