Replication slots in Postgresql

There are several reasons to upgrade to PostgreSQL 9.4, with JSONB getting the most buzz. But there are also some lesser known features that are quite useful.

PostgreSQL 9.4

One of these new features is the concept of replication slots. This should appeal to those of us running streaming replication between a master and one or several hot standbys (or just archiving standbys).

Before 9.4, in the event of a standby going offline, a master kept set a number of WAL files until the standby comes back online. So, in order to know how many WAL files need to be kept around, the wal_keep_segments setting needed to be set just right.

Unfortunately this setting is sometimes tricky to get right, and the end result is that one has to set up a hot standby from scratch again.

Now, with 9.4 and onwards, the wal_keep_segments is no longer needed as one can use replication slots instead. The behavior is similar, but a replication slot ensures that all the WAL files are being kept forever on behalf of the standby.

So instead of relying on a single wal_keep_segments setting for all standbys, one can now be certain that no standby will be left alone.

On the master set the max_replication_slots setting in postgresql.conf:

max_replication_slots = 1

Restart the master so the changes can take effect. Then run the following query on the master:

select * from pg_create_physical_replication_slot('standby_replication_slot');

This effectively creates the slot.

On the slave, you’ll have to add this to the recovery.conf file:

primary_slot_name = 'standby_replication_slot'

For more information, check the official documentation on http://www.postgresql.org/docs/9.4/static/warm-standby.html#STREAMING-REPLICATION-SLOTS

Note that in order to drop a replication slot, one needs to select from pg_drop_replication_slot, similar to the example above with pg_crete_physical_replication_slot.

If you remove a standby, and forget to drop the slot, it means WAL archives will accumulate until the master runs out of disk space!