PostgreSQL搭建Standby复制

计划搭建一主一从的pg数据库集群

环境配置

主机名	IP地址	角色	数据目录
master	192.168.56.15	主库	/data
master1	192.168.56.16	standby	/data

主库上的配置

• 配置pg_hba.conf

host    replication     postgres        192.168.56.0/24         trust

• 修改postgresql.conf

max_wal_senders = 5
wal_level = hot_standby

指定同步复制的standby的名字
synchronous_standby_names = 'standby01

备库上的配置

在/data 数据目录里面创建文件

vi  recovery.conf
standby_mode = 'on'
recovery_target_timeline = 'latest'
primary_conninfo = 'application_name=standby01 user=postgres host=192.168.56.15 port=5432 password=postgres sslmode=disable sslcompression=1'

启动数据库及备库

• 将两个主备依次启动

• 从主库上进入数据库中可以查看到数据主备的状态

postgres=# select * from pg_stat_replication;
 pid  | usesysid | usename  | application_name |  client_addr  | client_hostname | client_port |         backend_start         | backend_xmin |   state   |  
sent_lsn  | write_lsn  | flush_lsn  | replay_lsn | write_lag | flush_lag | replay_lag | sync_priority | sync_state 
------+----------+----------+------------------+---------------+-----------------+-------------+-------------------------------+--------------+-----------+--
----------+------------+------------+------------+-----------+-----------+------------+---------------+------------
 1859 |       10 | postgres | standby01        | 192.168.56.16 |                 |        7998 | 2018-07-04 16:11:33.549227+08 |              | streaming | 5
/DBA4F268 | 5/DBA4F268 | 5/DBA4F268 | 5/DBA4F268 |           |           |            |             1 | sync
(1 row)