postgresql流复制配置简记

博客文章除注明转载外，均为原创。转载请注明出处。
本文链接地址：http://blog.chinaunix.net/uid-31396856-id-5756912.html



1.在主库增加同步的用户名与密码 
postgres=# CREATE ROLE replica login replication encrypted password 'replica';
CREATE ROLE
postgres=# 
postgres=# SELECT rolname from pg_roles;
      rolname      
-------------------
 pg_signal_backend
 postgres
 replica
(3 rows)


postgres=# SELECT usename from pg_user;
 usename  
----------
 postgres
 replica
(2 rows)

2.在主库pg01上进行配置
2.1
vi /data/pgsql/pgdata/pg_hba.conf  
增加以下：  
host    replication     replica        192.168.0.0/24           md5  

2.2编辑postgresql.conf
vi /data/pgsql/pgdata/postgresql.conf
参考配置
wal_level = hot_standby
fsync = on
synchronous_commit = on    #同步复制必须设置为on
wal_sync_method = fdatasync
max_wal_senders = 4        #配置的是两个slave节点，最好设置大一点，方便以后添加slave节点，pg_basebackup使用stream方式需要占用2个槽
wal_keep_segments = 100

3.配置从库
3.1
mkdir -p /data/pgsql/pgdata/
chown -R postgres:postgres /data/pgsql/pgdata


3.2 获取主库备份

pg_basebackup -h 192.168.0.31 -U replica -F p -P -x -R -D /data/pgsql/pgdata/ -l backup20161123


#my used as follows
pg_basebackup -X s -h 192.168.0.31 -U replica -D /data/pgsql/pgdata/


以前采用办法
1 tart the backup (On Master )
select pg_start_backup('base backup for log streaming');

2 COPY 数据文件 
tar czvf pgdata.tar.gz pgdata --exclude=pg_root/pg_xlog
  
3 将数据文件COPY到standby 主机并解压
scp pgdata.tar.gz pgb:/database

4 数据COPY完后,结束备份 Stop the backup (On Master)
select pg_stop_backup(), current_timestamp;


3.3配置从库参数

hot_standby = on
max_standby_archive_delay = 30s
max_standby_streaming_delay = 30s



34配置
[postgres@pgsql02 pgdata]$ cat recovery.conf 
standby_mode = 'on'
primary_conninfo = 'host=192.168.0.31 port=5432 user=replica password=replica application_name=pgsql02'
restore_command = ''
archive_cleanup_command = ''
trigger_file = '/tmp/postgresql.trigger.5432'


3.5确认
主库上
postgres=# select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;  
 pid  |   state   | client_addr  | sync_priority | sync_state 
------+-----------+--------------+---------------+------------
 1771 | streaming | 192.168.0.32 |             0 | async

postgres=#  select pid,state,client_addr,sync_priority,sync_state from pg_stat_replication;
 pid  |   state   | client_addr  | sync_priority | sync_state 
------+-----------+--------------+---------------+------------
 1771 | streaming | 192.168.0.32 |             0 | async

--The End