PostgreSQL Streaming Physical Replication With Slots

PostgreSQL streaming physical replication with slots simplifies setup and maintenance procedures. Usually, you should estimate disk usage for the Write Ahead Log (WAL) and provide appropriate limitation to the number of segments and setup of the WAL archive procedure. In this article, you will see how to use replication with slots and understand what problems it could solve.

Introduction

PostgreSQL physical replication is based on WAL. Th Write Ahead Log contains all database changes, saved in 16MB segment files. Normally postgres tries to keep segments between checkpoints. So with default settings, just 1GB of WAL segment files is available.

Replication requires all WAL files created after backup and up until the current time. Previously, it was necessary to keep a huge archive directory (usually mounted by NFS to all slave servers). The slots feature introduced in 9.4 allows Postgres to track the latest segment downloaded by a slave server. Now, PostgreSQL can keep all segments on disk, even without archiving, if a slave is seriously behind its master due to downtime or networking issues. The drawback: the disk space could be consumed infinitely in the case of configuration error. Before continuing, if you need a better understanding of physical replication and streaming replication, I recommend you read “Streaming Replication with PostgreSQL“.

Create a sandbox with two PostgreSQL servers

To setup replication, you need at least two PostgreSQL servers. I’m using pgcli (pgc) to setup both servers on the same host. It’s easy to install on Linux, Windows, and OS X, and provides the ability to download and run any version of PostgreSQL on your staging server or even on your laptop.

First of all you should allow the replication user to connect:

If you are running master and slave on different servers, please replace 127.0.0.1 with the slave’s address.

Next pgc creates a shell environment file with PATH and all the other variables required for PostgreSQL:

Allow connections from the remote host, and create a replication user and slot on master:

To apply system variables changes and hba.conf, restart the Postgres server:

Test table

Create a table with lots of padding on the master:

Filling WAL with random data

To see the benefits of slots, we should fill the WAL with some data by running transactions. Repeat the update statement below to generate a huge amount of WAL data:

Checking the current WAL size

You can check total size for all WAL segments from the shell or from psql:

Check maximum WAL size without slots activated

Before replication configuration, we can fill the WAL with random data and find that after 1.1G, the data/pg10/pg_wal directory size does not increase regardless of the number of update queries.

Backup master from the slave server

Next, let’s make a backup for our slot1:

Unfortunately pg_basebackup hangs with: initiating base backup, waiting for checkpoint to complete.
We can wait for the next checkpoint, or force the checkpoint on the master. Checkpoint happens every checkpoint_timeout seconds, and is set to five minutes by default.

Forcing checkpoint on master:

The backup continues on the slave side:

The backup copies settings from the master, including its TCP port value. I’m running both master and slave on the same host, so I should change the port in the slave .conf file:

Now we can return to the master and run some queries:

By running these queries, the WAL size is now 1.4G, and it’s bigger than 1.1G! Repeat this update query three times and the WAL grows to 2.8GB:

Certainly, the WAL could grow infinitely until whole disk space is consumed.
How do we find out the reason for this?

We have one slot behind the master of 2.38GB.

Let’s repeat the update and check again. The gap has increased:

Wait, though: we have already used slot1 for backup! Let’s start the slave:

Replication started without any additional change to recovery.conf:

pg_basebackup -R option instructs backup to write to the recovery.conf file with all required options, including primary_slot_name.

WAL size, all slots connected

The gap reduced several seconds after the slave started:

And a few minutes later:

Slave server maintenance

Let’s simulate slave server maintenance with ./pgc stop pg10 executed on the slave. We’ll push some data onto the master again (execute the UPDATE query 4 times).

Now, “slot1” is again 2.36GB behind.

Removing unused slots

By now, you might realize that a problematic slot is not in use. In such cases, you can drop it to allow retention for segments:

Finally the disk space is released:

Important system variables

• archive_mode is not required for streaming replication with slots.
• wal_level – is replica by default
• max_wal_senders – set to 10 by default, a minimum of three for one slave, plus two for each additional slave
• wal_keep_segments – 32 by default, not important because PostgreSQL will keep all segments required by slot
• archive_command – not important for streaming replication with slots
• listen_addresses – the only option that it’s necessary to change, to allow remote slaves to connect
• hot_standby – set to on by default, important to enable reads on slave
• max_replication_slots – 10 by default https://www.postgresql.org/docs/10/static/runtime-config-replication.html

Summary

• Physical replication setup is really easy with slots. By default in pg10, all settings are already prepared for replication setup.
• Be careful with orphaned slots. PostgreSQL will not remove WAL segments for inactive slots with initialized restart_lsn.
• Check pg_replication_slots restart_lsn value and compare it with current redo_lsn.
• Avoid long downtime for slave servers with slots configured.
• Please use meaningful names for slots, as that will simplify debug.

Percona and PostgreSQL work better together. Try Percona Distribution for PostgreSQL today.

References

• https://www.openscg.com/bigsql/package-manager/
• https://www.openscg.com/bigsql/docs/pgcli/pgcli/
• https://www.postgresql.org/docs/current/static/wal-configuration.html
• https://paquier.xyz/postgresql-2/postgres-9-4-feature-highlight-replication-phydical-slots/
• https://www.postgresql.org/docs/current/static/protocol-replication.html
• https://www.postgresql.org/docs/9.4/static/functions-admin.html

Unlock the secrets to cost-efficient database management – Read our eBook now and start saving!