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.

4 Comments

  • zoovid says:

    “(Please note that there is no more recovery.conf in PostgreSQL 12 so the recovery parameters have been added to postgresql.auto.conf)”
    Nope, postgresql.auto.conf must stay untouched, the recovery params are now in postgresql.conf

    • Daniel Westermann says:

      Nope:
      [email protected]:/home/postgres/ [pgdev] pg_basebackup –create-slot –slot myslot –write-recovery-conf -D /var/tmp/basebackup/
      [email protected]:/home/postgres/ [pgdev] cat /var/tmp/basebackup/postgresql.auto.conf
      # Do not edit this file manually!
      # It will be overwritten by the ALTER SYSTEM command.
      logging_collector = ‘on’
      log_truncate_on_rotation = ‘on’
      log_filename = ‘postgresql-%a.log’
      log_line_prefix = ‘%m – %l – %p – %h – %[email protected]%d ‘
      log_directory = ‘pg_log’
      primary_conninfo = ‘user=postgres passfile=”/home/postgres/.pgpass” channel_binding=prefer port=5432 sslmode=prefer sslcompression=0 gssencmode=disable krbsrvname=postgres target_session_attrs=any’
      primary_slot_name = ‘myslot’

  • zoovid says:

    btw do you know if it’s possible to change the slot internal identifier? I’m pulling my hair of a recovery based on pd_dumpall and a new initdb with the same slot name on the master, thanks.

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