postgresql replication slots 的一些个人理解

os: centos 7.4
postgresql: 9.6.9

replication slots 是从postgresql 9.4 引入的，主要是提供了一种自动化的方法来确保主控机在所有的后备机收到 WAL 段之前不会移除它们，并且主控机也不会移除可能导致恢复冲突的行，即使后备机断开也是如此。

在没有启用 replication slots 的环境中，如果碰到 ERROR: requested WAL segment xxxx has already been removed 的错误，解决办法是要么提前开启了归档，要么重做slave，另外还需要在master上设置 wal_keep_segments 为更大的值。

需要注意的是流复制并不会默认开启 replication slots, 需要手工配置。

创建复制槽

每个复制槽都有一个名字，名字可以包含小写字母、数字和下划线字符。

master设置几个参数

max_replication_slots = 10

hot_standby = on
wal_level = replica 或者 logical
hot_standby_feedback = on
max_wal_senders = 10

重启postgresql后，创建 replication slots

postgres=# SELECT * FROM pg_create_physical_replication_slot('pg96_102');
postgres=# SELECT * FROM pg_create_physical_replication_slot('pg96_103');

postgres=# select * from pg_replication_slots;
 slot_name | plugin | slot_type | datoid | database | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn 
-----------+--------+-----------+--------+----------+--------+------------+------+--------------+-------------+---------------------
 pg96_102  |        | physical  |        |          | t      |       1675 |      |              | 0/8000140   | 
 pg96_103  |        | physical  |        |          | t      |       1787 |      |              | 0/8000140   | 
(2 rows)

要配置slave使用这个槽，在后备机的recovery.conf中应该配置 primary_slot_name，如下：

$ vi $PGDATA/recovery.conf

primary_slot_name = 'pg96_101'
standby_mode = 'on'
recovery_target_timeline = 'latest'
primary_conninfo = 'user=replicator password=1qaz2wsx host=192.168.56.101 port=5432 application_name=pg96_103'
trigger_file = '/tmp/postgresql.trigger.5432'

查看复制情况

postgres=# select * from pg_stat_replication;
-[ RECORD 1 ]----+------------------------------
pid              | 1675
usesysid         | 16384
usename          | replicator
application_name | pg96_102
client_addr      | 192.168.56.102
client_hostname  | 
client_port      | 35474
backend_start    | 2018-07-25 17:43:44.652145+08
backend_xmin     | 
state            | streaming
sent_location    | 0/8000060
write_location   | 0/8000060
flush_location   | 0/8000060
replay_location  | 0/8000060
sync_priority    | 1
sync_state       | sync
-[ RECORD 2 ]----+------------------------------
pid              | 1787
usesysid         | 16384
usename          | replicator
application_name | pg96_103
client_addr      | 192.168.56.103
client_hostname  | 
client_port      | 49790
backend_start    | 2018-07-25 17:47:07.663301+08
backend_xmin     | 
state            | streaming
sent_location    | 0/8000060
write_location   | 0/8000060
flush_location   | 0/8000060
replay_location  | 0/8000060
sync_priority    | 1
sync_state       | potential

删除复制槽

slave在使用 primary_slot_name 参数时是无法删除 replication slots

postgres=# SELECT * FROM pg_drop_replication_slot('pg96_102');
postgres=# SELECT * FROM pg_drop_replication_slot('pg96_103');

参考：
http://postgres.cn/docs/10/warm-standby.html#STREAMING-REPLICATION-SLOTS
http://postgres.cn/docs/10/view-pg-replication-slots.html