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:

postgres@pgbox:/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)

postgres@pgbox:/home/postgres/ [PGDEV] mkdir -p /var/tmp/basebackup
postgres@pgbox:/home/postgres/ [PGDEV] pg_basebackup --create-slot --slot myslot --write-recovery-conf -D /var/tmp/basebackup/
postgres@pgbox:/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:

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

postgres@pgbox:/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:

postgres@pgbox:/home/postgres/ [PGDEV] mkdir -p /var/tmp/replica1
postgres@pgbox:/home/postgres/ [PGDEV] mkdir -p /var/tmp/replica2
postgres@pgbox:/home/postgres/ [PGDEV] cp -pr /var/tmp/basebackup/* /var/tmp/replica1/
postgres@pgbox:/home/postgres/ [PGDEV] cp -pr /var/tmp/basebackup/* /var/tmp/replica2/
postgres@pgbox:/home/postgres/ [PGDEV] sed -i 's/myslot/myslot2/g' /var/tmp/replica2/postgresql.auto.conf 
postgres@pgbox:/home/postgres/ [PGDEV] echo "port=8888" >> /var/tmp/replica1/postgresql.auto.conf 
postgres@pgbox:/home/postgres/ [PGDEV] echo "port=8889" >> /var/tmp/replica2/postgresql.auto.conf 
postgres@pgbox:/home/postgres/ [PGDEV] chmod o-rwx /var/tmp/replica1
postgres@pgbox:/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:

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

postgres@pgbox:/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:

postgres@pgbox:/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.