Infrastructure at your Service

The concept of replication slots was introduced in PostgreSQL 9.4 and was created to prevent a primary instance to delete WAL that a replica still needs to apply. That could happen when you have a network interruption or the replica was down for another reason. With replication slots you can prevent that at the downside that your master could fill up your disk if the interruption is too long. This concept of a “physical replication slot” was then advanced so you can also create “logical replication slots” which are used in logical replication which made in into PostgreSQL 10. Now with PostgreSQL 12 being in active development another great feature made it into PostgreSQL core: Copying replication slots.

What might that be good for? Lets assume the following scenario:

  • You want to attach two replicas to your master instance
  • You want both replicas to use a physical replication slot
  • You want to build both replicas from the same basebackup and to start at the same position

What you can do in PostgreSQL is to create base backup that will create a physical replication slot:

[email protected]:/home/postgres/ [PGDEV] psql -X -c "select * from pg_replication_slots" postgres
 slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn 
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------
(0 rows)

[email protected]:/home/postgres/ [PGDEV] mkdir -p /var/tmp/basebackup
[email protected]:/home/postgres/ [PGDEV] pg_basebackup --create-slot --slot myslot --write-recovery-conf -D /var/tmp/basebackup/
[email protected]:/home/postgres/ [PGDEV] psql -X -c "select * from pg_replication_slots" postgres
 slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn 
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------
 myslot    |        | physical  |        |          | f         | f      |            |      |              | 0/2000000   | 
(1 row)

(Please note that there is no more recovery.conf in PostgreSQL 12 so the recovery parameters have been added to postgresql.auto.conf)

The replication slot will not be dropped after pg_basebackup finished and you can use it to attach a new replica. But before doing that: As of PostgreSQL 12 you can copy the slot and then attach a second replica to the copied slot, so both replicas will start at the same position:

[email protected]:/home/postgres/ [PGDEV] psql -X -c "select pg_copy_physical_replication_slot('myslot','myslot2')" postgres
 pg_copy_physical_replication_slot 
-----------------------------------
 (myslot2,)
(1 row)

[email protected]:/home/postgres/ [PGDEV] psql -X -c "select * from pg_replication_slots" postgres
 slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn 
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------
 myslot    |        | physical  |        |          | f         | f      |            |      |              | 0/8000000   | 
 myslot2   |        | physical  |        |          | f         | f      |            |      |              | 0/8000000   | 
(2 rows)

As you can see both replication slots have the same value for “restart_lsn”. This will make it very easy to use the basebackup for the two replicas and start them from the same position:

[email protected]:/home/postgres/ [PGDEV] mkdir -p /var/tmp/replica1
[email protected]:/home/postgres/ [PGDEV] mkdir -p /var/tmp/replica2
[email protected]:/home/postgres/ [PGDEV] cp -pr /var/tmp/basebackup/* /var/tmp/replica1/
[email protected]:/home/postgres/ [PGDEV] cp -pr /var/tmp/basebackup/* /var/tmp/replica2/
[email protected]:/home/postgres/ [PGDEV] sed -i 's/myslot/myslot2/g' /var/tmp/replica2/postgresql.auto.conf 
[email protected]:/home/postgres/ [PGDEV] echo "port=8888" >> /var/tmp/replica1/postgresql.auto.conf 
[email protected]:/home/postgres/ [PGDEV] echo "port=8889" >> /var/tmp/replica2/postgresql.auto.conf 
[email protected]:/home/postgres/ [PGDEV] chmod o-rwx /var/tmp/replica1
[email protected]:/home/postgres/ [PGDEV] chmod o-rwx /var/tmp/replica2

What happened here:

  • Restore the same basebackup to the new replica locations
  • Change the slot to use for the second replica to our copied slot name
  • Change the ports of both replicas because we are running on the same host
  • Fix the permissions so pg_ctl will not complain

That’s it. We can startup both replicas:

[email protected]:/home/postgres/ [PGDEV] pg_ctl -D /var/tmp/replica1/ start
[email protected]:/home/postgres/ [PGDEV] pg_ctl -D /var/tmp/replica2/ start
[email protected]:/home/postgres/ [PGDEV] psql -X -p 8888 -c "select pg_is_in_recovery()" postgres
 pg_is_in_recovery 
-------------------
 t
(1 row)

[email protected]:/home/postgres/ [PGDEV] psql -X -p 8889 -c "select pg_is_in_recovery()" postgres
 pg_is_in_recovery 
-------------------
 t
(1 row)

Quite easy and we can confirm that both replicas are at the same location as previously:

[email protected]:/home/postgres/ [PGDEV] psql -X -c "select * from pg_replication_slots" postgres
 slot_name | plugin | slot_type | datoid | database | temporary | active | active_pid | xmin | catalog_xmin | restart_lsn | confirmed_flush_lsn 
-----------+--------+-----------+--------+----------+-----------+--------+------------+------+--------------+-------------+---------------------
 myslot    |        | physical  |        |          | f         | t      |      15622 |      |              | 0/9000148   | 
 myslot2   |        | physical  |        |          | f         | t      |      15632 |      |              | 0/9000148   | 
(2 rows)

You can also copy logical replication slots, of course. Nice, thanks all involved.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Daniel Westermann
Daniel Westermann

Principal Consultant & Technology Leader Open Infrastructure