2、异步流复制简单配置示例
主节点配置
1、初始化数据集簇
./initdb ../data-primary -U postgres
2、启动服务，创建用户
ALTER USER postgres WITH PASSWORD ‘123456‘;
CREATE ROLE repl WITH PASSWORD ‘123456‘ REPLICATION LOGIN;
3、配置 postgresql.conf
listen_addresses = ‘*‘
archive_mode = on
archive_command = ‘cp "%p" "/opt/PG-12/archivedir"‘
max_wal_senders = 10
max_replication_slots = 10
wal_level = replica
wal_keep_segments = 512
4、配置 pg_hba.conf
host all all 0.0.0.0/24 trust
host replication all 0.0.0.0/24 trust
5、重启服务
standby01备节点配置
1、基础备份
./pg_basebackup -h 127.0.0.1 -p 5432 -U repl -W -Fp -Xs -Pv -R -D ../data-standby01
data-standby01路径下：会生成：standby.signal 文件
data-standby01路径下： postgresql.auto.conf 文件会添加连接信息
[sun@localhost data-standby01]$ cat postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = ‘user=repl password=123456 host=127.0.0.1 port=5432 sslmode=disable sslcompression=0 gssencmode=disable target_session_attrs=any‘
2、启动备库（同一服务器实验，需要先修改 postgresql.conf 的端口）
standby02备节点配置
1、基础备份
./pg_basebackup -h 127.0.0.1 -p 5432 -U repl -W -Fp -Xs -Pv -R -D ../data-standby02
data-standby02路径下：会生成：standby.signal 文件
data-standby02路径下： postgresql.auto.conf 文件会添加连接信息
[sun@localhost data-standby02]$ cat postgresql.auto.conf
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = ‘user=repl password=123456 host=127.0.0.1 port=5432 sslmode=disable sslcompression=0 gssencmode=disable target_session_attrs=any‘
2、启动备库（同一服务器实验，需要先修改 postgresql.conf 的端口）
查看
postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_replication ;
-[ RECORD 1 ]----+------------------------------
pid | 24416
usesysid | 16384
usename | repl
application_name | walreceiver
client_addr | 127.0.0.1
client_hostname |
client_port | 55580
backend_start | 2019-10-28 18:12:28.701572+08
backend_xmin |
state | streaming
sent_lsn | 0/5000060
write_lsn | 0/5000060
flush_lsn | 0/5000060
replay_lsn | 0/5000060
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
reply_time | 2019-10-28 18:14:50.806858+08
-[ RECORD 2 ]----+------------------------------
pid | 24433
usesysid | 16384
usename | repl
application_name | walreceiver
client_addr | 127.0.0.1
client_hostname |
client_port | 55582
backend_start | 2019-10-28 18:12:33.452452+08
backend_xmin |
state | streaming
sent_lsn | 0/5000060
write_lsn | 0/5000060
flush_lsn | 0/5000060
replay_lsn | 0/5000060
write_lag |
flush_lag |
replay_lag |
sync_priority | 0
sync_state | async
reply_time | 2019-10-28 18:14:53.639337+08
3、同步流复制示例
在异步流复制的基础上：
主库 postgresql.conf 添加配置
synchronous_commit = on
synchronous_standby_names = ‘standby01,standby02‘
备库 postgresql.auto.conf 修改配置
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = ‘application_name=standby01 user=repl password=123456 host=127.0.0.1 port=5432 sslmode=disable sslcompression=0 gssencmode=disable target_session_attrs=any‘
# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = ‘application_name=standby02 user=repl password=123456 host=127.0.0.1 port=5432 sslmode=disable sslcompression=0 gssencmode=disable target_session_attrs=any‘
重启主、备服务，查看状态
postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_replication ;
-[ RECORD 1 ]----+------------------------------
pid | 24880
usesysid | 16384
usename | repl
application_name | standby02
client_addr | 127.0.0.1
client_hostname |
client_port | 55598
backend_start | 2019-10-28 18:25:09.462092+08
backend_xmin |
state | streaming
sent_lsn | 0/60000D8
write_lsn | 0/60000D8
flush_lsn | 0/60000D8
replay_lsn | 0/60000D8
write_lag |
flush_lag |
replay_lag |
sync_priority | 2
sync_state | potential
reply_time | 2019-10-28 18:25:29.613489+08
-[ RECORD 2 ]----+------------------------------
pid | 24858
usesysid | 16384
usename | repl
application_name | standby01
client_addr | 127.0.0.1
client_hostname |
client_port | 55596
backend_start | 2019-10-28 18:25:06.144197+08
backend_xmin |
state | streaming
sent_lsn | 0/60000D8
write_lsn | 0/60000D8
flush_lsn | 0/60000D8
replay_lsn | 0/60000D8
write_lag |
flush_lag |
replay_lag |
sync_priority | 1
sync_state | sync
reply_time | 2019-10-28 18:25:26.282327+08