Personal understanding of postgresql replication slots

os: centos 7.4
postgresql: 9.6.9

replication slots was introduced from postgresql 9.4. It mainly provides an automatic method to ensure that the master computer will not remove all the backup computers before they receive the WAL segments, and the master computer will not remove the rows that may cause recovery conflicts, even if the backup computer is disconnected.

In an environment where replication slots are not enabled, if you encounter an error: requested wal segment XXXX has already been removed, the solution is to either open the archive in advance or redo the slave. In addition, you need to set the wal_keep_segments on the master to a larger value.

Note that stream replication does not turn on replication slots by default and needs to be configured manually.

Create a copy slot

Each copy slot has a name that can contain lowercase letters, numbers, and underscore characters.

master sets several parameters

max_replication_slots = 10
wal_level = replica perhaps logical

After restarting postgresql, create 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)

To configure slave to use this slot, the primary slot name should be configured in recovery.conf of the standby machine, as follows:

$ 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'

View replication

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

Delete copy slot

slave cannot delete replication slots when using the primary slot name parameter

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

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

Tags: PostgreSQL CentOS Database

Posted on Fri, 31 Jan 2020 12:34:44 -0800 by donkeychoker