postgres--流复制
配置
master 192.168.2.21 5432
slave 192.168.2.22 5432
目录 /var/lib/pgsql/10/data/
配置主库
配置用户
psql
create role rep login replication encrypted password '1qaz2wsx';
配置访问权限 pg_hba.conf
在/var/lib/pgsql/10/data/pg_hba.conf增加
host replication rep 192.168.2.22/32 md5
配置同步文件
cd /var/lib/pgsql/10/data/
vi recovery.done
recovery_target_timeline = 'latest'
standby_mode = on
primary_conninfo = 'host=192.168.2.21 port=5432 user=rep password=1qaz2wsx'
# recovery_min_apply_delay = 0 #延迟多少分钟应用，用户可以配置延迟的备库，例如给一点误操作的缓冲时间。在备库不会这么早被应用。
启动主机
配置从库
创建目录
安装从机之后，不要初始化和启动数据库
mkdir /var/lib/pgsql/10/data/
chown postgres:postgres /var/lib/pgsql/10/data/
chmod 700 /var/lib/pgsql/10/data/
同步主库数据
su - postgres
pg_basebackup -D /var/lib/pgsql/10/data/ -F p -X stream -h 192.168.2.21 -p 5432 -U rep
修改配置文件
根据从机配置适当修改/var/lib/pgsql/10/data/postgresql.conf
配置recovery.conf
cd /var/lib/pgsql/10/data/
mv recovery.done recovery.conf
启动从库
service postgresql-10 restart
流复制节点的状态监控
主库
su - postgres
psql
postgres=# \x
Expanded display is on.
postgres=# select pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), sent_lsn)) as sent_delay,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), write_lsn)) as write_delay,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), flush_lsn)) as flush_delay,
pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn)) as replay_delay,
*
from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
sent_delay | 0 bytes
write_delay | 0 bytes
flush_delay | 0 bytes
replay_delay | 0 bytes
pid | 25628
usesysid | 16399
usename | rep
application_name | walreceiver
client_addr | 192.168.2.22
client_hostname |
client_port | 59674
backend_start | 2018-01-03 17:57:00.690789+08
backend_xmin |
state | streaming
sent_lsn | 0/F787EA30
write_lsn | 0/F787EA30
flush_lsn | 0/F787EA30
replay_lsn | 0/F787EA30
write_lag | 00:00:00.000259
flush_lag | 00:00:00.013562
replay_lag | 00:00:00.013563
sync_priority | 0
sync_state | async
从库
进入数据库
su - postgres
psql
查看当前WAL应用是否暂停
postgres=# select pg_is_wal_replay_paused();
-[ RECORD 1 ]-----------+--
pg_is_wal_replay_paused | f
f表示没有暂停，t表示暂停
查看WAL接收LSN点
postgres=# select pg_last_wal_receive_lsn();
-[ RECORD 1 ]-----------+-----------
pg_last_wal_receive_lsn | 0/FFA88FB8
查看WAL应用LSN点
postgres=# select pg_last_wal_replay_lsn();
-[ RECORD 1 ]----------+---------
pg_last_wal_replay_lsn | 1/1EC488
查看wal receiver的统计信息
postgres=# \x
Expanded display is on.
postgres=# select * from pg_stat_get_wal_receiver();
-[ RECORD 1 ]---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid | 11648
status | streaming
receive_start_lsn | 0/3A000000
receive_start_tli | 1
received_lsn | 1/2DE1E28
received_tli | 1
last_msg_send_time | 2018-01-04 17:20:38.645492+08
last_msg_receipt_time | 2018-01-04 17:20:38.640939+08
latest_end_lsn | 1/2DE1E28
latest_end_time | 2018-01-04 17:20:38.645492+08
slot_name |
conninfo | user=rep password=******** dbname=replication host=192.168.2.21 port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any
暂停WAL的应用
select pg_wal_replay_pause();
-[ RECORD 1 ]-------+-
pg_wal_replay_pause |
postgres=# select pg_is_wal_replay_paused();
-[ RECORD 1 ]-----------+--
pg_is_wal_replay_paused | t
postgres=# select * from pg_stat_get_wal_receiver();
-[ RECORD 1 ]---------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
pid | 11648
status | streaming
receive_start_lsn | 0/3A000000
receive_start_tli | 1
received_lsn | 1/71D3E88
received_tli | 1
last_msg_send_time | 2018-01-04 17:23:52.302411+08
last_msg_receipt_time | 2018-01-04 17:23:52.297611+08
latest_end_lsn | 1/71D3E88
latest_end_time | 2018-01-04 17:23:52.302411+08
slot_name |
conninfo | user=rep password=******** dbname=replication host=192.168.2.21 port=5432 fallback_application_name=walreceiver sslmode=prefer sslcompression=1 krbsrvname=postgres target_session_attrs=any
恢复WAL的应用
postgres=# select pg_wal_replay_resume();
-[ RECORD 1 ]--------+-
pg_wal_replay_resume |
postgres=# select pg_is_wal_replay_paused();
-[ RECORD 1 ]-----------+--
pg_is_wal_replay_paused | f
注意事项
防止主库wal文件被删除，从库没有接收到
方法有：使用slot、配置足够大的wal keep、wal归档。
slot
配置方法
主 postgresql.conf
max_replication_slots = 10
备 recovery.conf
primary_slot_name = ''
当备库挂了或者不再使用，而忘记删除对应的SLOT时，可能导致主库WAL无限膨胀。
wal keep
配置方法
主 postgresql.conf
wal_keep_segments = 1024
会导致主库的WAL预留足够的个数，占用一定空间（walkeepsegments*16M = 16G）。
wal归档
配置主库的归档，同时备库需要能获取到已归档的WAL文件。
配置方式
主 postgresql.conf
archive_mode = on
archive_command = '......'
备 recovery.conf
restore_command = '....'
同步账号密码的安全性
由于recovery.conf是明文，需要保护好recovery.conf文件的访问权限
或者限制只允许特定的从机访问