PostgreSQL流复制

异步流复制hot standby
0、环境说明：
1、主库配置
首先需要在主库配置接受流复制的连接，修改pg_hba.conf文件

# TYPE  DATABASE        USER            ADDRESS                 METHOD
  host  replication     all             192.168.7.177/24        trust

修改postgresql.conf，由于要使用流复制，所以max_wal_senders要修改成一个大于0的数。
listen_addresses = ‘*’
max_wal_senders = 5
wal_level = hot_standby

2、备库配置
2、1在备库生成基础备份
[email protected]> pg_basebackup -h 192.168.17.190 -U osdba -F p -P -R -D /home/itm_pg/bk_data/ -l backup190117
WARNING: skipping special file “./.s.PGSQL.1921”
WARNING: skipping special file “./.s.PGSQL.1921”
1580781/1580781 kB (100%), 1/1 tablespace
这里需要注意，要使用一个空的目录，这里新建了一个目录，否则会报错：
pg_basebackup: directory “/home/itm_pg/pgdata” exists but is not empty

2.2、修改参数文件
在/home/itm_pg/bk_data/目录下修改recovery.conf文件,由于pg_basebackup使用了-R参数，所以生成了recovery.conf文件，否则需要手动去share目录中拷贝
standby_mode = ‘on’
primary_conninfo = ‘user=osdba password=osdba host=192.168.17.190 port=1921 sslmode=disable sslcompression=1 target_session_attrs=any’

还要修改postgresql.conf文件
hot_standby=on

2.3、启动数据库
这里需要注意要指定bk_data目录启动数据库。
[email protected]> pg_ctl start -D /home/itm_pg/bk_data
[email protected]> ps -ef|grep postgres
itm_pg 13910 1 0 16:11 pts/1 00:00:00 /home/itm_pg/pgsql10.3/bin/postgres -D /home/itm_pg/pg_bak
itm_pg 13911 13910 0 16:11 ? 00:00:00 postgres: logger process
itm_pg 13912 13910 0 16:11 ? 00:00:00 postgres: startup process recovering 000000010000000000000051
itm_pg 13913 13910 0 16:11 ? 00:00:00 postgres: checkpointer process
itm_pg 13914 13910 0 16:11 ? 00:00:00 postgres: writer process
itm_pg 13915 13910 0 16:11 ? 00:00:00 postgres: stats collector process
itm_pg 13916 13910 0 16:11 ? 00:00:00 postgres: wal receiver process streaming 0/51000140
itm_pg 13918 13852 0 16:11 pts/1 00:00:00 grep postgres

postgres: wal receiver process，说明是备库。至此，异步standby就搭建完成了！

同步流复制standby
0、环境说明：

1、主库配置
同样需要在主库配置接受流复制的连接，修改pg_hba.conf文件，添加另一个备库的信息

# TYPE  DATABASE        USER            ADDRESS                 METHOD
  host  replication     all             192.168.7.166/24        trust
在postgresql.conf文件添加
listen_addresses = '*'
max_wal_senders = 5
wal_level = hot_standby
synchronous_standby_names = 'standby01,standby02'

2、备库配置
在两个备库执行同样操作
备份数据：
[email protected]> pg_basebackup -h 192.168.17.190 -U osdba -F p -P -R -D /home/itm_pg/pgdata/bk_data/ -l backup190117
WARNING: skipping special file “./.s.PGSQL.1921”
WARNING: skipping special file “./.s.PGSQL.1921”
1580790/1580790 kB (100%), 1/1 tablespace
修改recovery.conf文件：
standby_mode = ‘on’
primary_conninfo = ‘application_name=standby01 user=osdba passfile=’’/home/digoal/.pgpass’’ host=192.168.17.190 port=1921 sslmode=disable sslcompression=1 target_session_attrs=any’
然后启动数据库即可，接着在另一台备库执行同样的操作。
主库查询：

postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 25494
usesysid         | 16420
usename          | osdba
application_name | standby02
client_addr      | 192.168.7.166
client_hostname  | 
client_port      | 45526
backend_start    | 2019-01-16 10:29:01.391783+08
backend_xmin     | 
state            | streaming
sent_lsn         | 0/55000220
write_lsn        | 0/55000220
flush_lsn        | 0/55000220
replay_lsn       | 0/55000220
write_lag        | 00:00:00.00067
flush_lag        | 00:00:00.00067
replay_lag       | 00:00:00.00067
sync_priority    | 2
sync_state       | potential
-[ RECORD 2 ]----+------------------------------
pid              | 25492
usesysid         | 16420
usename          | osdba
application_name | standby01
client_addr      | 192.168.7.177
client_hostname  | 
client_port      | 55266
backend_start    | 2019-01-16 10:29:00.600386+08
backend_xmin     | 
state            | streaming
sent_lsn         | 0/55000220
write_lsn        | 0/55000220
flush_lsn        | 0/55000220
replay_lsn       | 0/55000220
write_lag        | 00:00:00.000536
flush_lag        | 00:00:00.000536
replay_lag       | 00:00:00.000536
sync_priority    | 1
sync_state       | sync

至此，完成同步流复制standby数据库的搭建！