How to setup PostgreSQL Streaming Replication with Replication Slots on Debian 10

PostgreSQL is a powerful and feature-rich relational database management system (RDBMS). It is free and open-source, and has been in development since 1996. Postgres offers different ways of archiving and replicating data, one of which is streaming replication. In this mode, a primary (master) instance handles the main active database and executes operations. The secondary (slave) instance copies all changes from the primary, maintaining an identical copy of the active database. The secondary server can also accept read-only queries. If the primary fails, the secondary server can exit standby mode and operate as the new master (this is called failover).

PostgreSQL replication usually relies on write-ahead logging (WAL), the process of logging data changes before writing them to disk. These WAL records are then either copied to a second node as files (file-based log shipping), or directly streamed between nodes (streaming replication). In most cases, the latter reduces the delay for changes on the master node to be received by the standby node.

The problem with using streaming replication without file-based log shipping is that the secondary server may miss some WAL records if the primary discards them too soon. A number of configuration parameters can reduce this risk but often come with an unnecessary storage cost. The solution is replication slots, a feature provided by Postgres that ensures the primary server only discards WAL records after they have been received by the standby node.

We will be setting up streaming replication with replication slots on two Debian 10 nodes.

Requirements

  • Two identical Debian 10 instances.
  • Root access to both instances.
  • The $EDITOR environment variable should be set on both instances.

Step 1: Installing PostgreSQL

Update and reboot both nodes:

apt update
apt upgrade -y
reboot

Install Postgres on both nodes and make sure PostgreSQL is enabled and running:

apt install -y postgresql
systemctl enable --now [email protected]

NOTE: When updating PostgreSQL, updating the standby first is the safer option according to their documentation.

Step 2: Initial Configuration

By default, PostgreSQL only listens on the loopback interface and is not externally accessible. Change the listen address on both nodes by editing postgresql.conf:

$EDITOR /etc/postgresql/11/main/postgresql.conf

Find the following line:

#listen_addresses = 'localhost'

Change it to:

listen_addresses = 'node_ip_address,127.0.0.1'

If both nodes share the same local network, you can use private addresses for node_ip_address, though Postgres won't be internet accessible. Otherwise, use public addresses.

Save the change then restart both instances:

systemctl restart [email protected]

Step 3: Master Configuration

This step only pertains to the primary/master server.

Open the Postgres terminal:

sudo -u postgres psql

The standby node will be using a user to connect to the master. Create it:

postgres=# CREATE ROLE replicator LOGIN REPLICATION ENCRYPTED PASSWORD 'replicator_password';

Then create a replication slot and exit:

postgres=# SELECT * FROM pg_create_physical_replication_slot('replicator');
postgres=# \q

For the sake of simplicity, the replication role and slot are both named "replicator", though they do not have to be identical.

Next, create an entry in pg_hba.conf to allow the replicator user to connect from standby to master. Open it:

$EDITOR /etc/postgresql/11/main/pg_hba.conf

Append the following line to the end:

host	replication	replicator	standby_ip_address/32		md5

Restart the master instance:

systemctl restart [email protected]

Step 4: Base backup

The commands in this step should be executed on the secondary/slave server.

First, stop Postgres on the secondary node:

systemctl stop [email protected]

Backup the old data directory:

mv /var/lib/postgresql/11/main/ /var/lib/postgresql/11/main.bak

Use the following command to clone the master's data directory to the slave:

pg_basebackup -h master_ip_address -U replicator -D /var/lib/postgresql/11/main/ -P --password --slot replicator

You will be prompted for a password. Enter the password you chose for the replicator role during its creation on the master. Once the transfer is complete, grant ownership of the data directory to the postgres user:

chown -R postgres:postgres /var/lib/postgresql/11/main

Step 5: Standby Configuration

This step only pertains to the secondary/slave server.

Enable hot standby mode in postgresql.conf:

$EDITOR /etc/postgresql/11/main/postgresql.conf

Find and uncomment the following line:

#hot_standby = on

Create the file recovery.conf in the Postgres data directory:

$EDITOR /var/lib/postgresql/11/main/recovery.conf

Enable standby mode:

standby_mode = 'on'

Set the replication connection parameters using the credentials created on the master:

primary_conninfo = 'host=master_ip_address port=5432 user=replicator password=replicator_password'

Set the name of the replication slot you've created on the master:

primary_slot_name = 'replicator'

Set the path to a failover trigger file:

trigger_file = '/var/lib/postgresql/11/main/failover.trigger'

If the trigger_file parameter is set, Postgres will exit standby mode and start normal operation as a primary server when this trigger file is created. This parameter is not required.

After creating recovery.conf, grant ownership to the postgres user:

chown postgres:postgres /var/lib/postgresql/11/main/recovery.conf

You can now start Postgres:

systemctl start [email protected]

It is now in standby mode and should be replicating any new transaction.

Testing

Testing Replication

To test replication, perform any write action on the master. For example, create a new database on the master:

sudo -u postgres psql -c "CREATE DATABASE replitest"

Wait a few seconds then list the databases on the slave:

sudo -u postgres psql -c "\l"

You should see that the replitest database was indeed replicated by the standby server:

List of databases
   Name    |  Owner   | Encoding |   Collate   |    Ctype    |   Access privileges
-----------+----------+----------+-------------+-------------+-----------------------
 postgres  | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 replitest | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 |
 template0 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.UTF-8 | en_US.UTF-8 | =c/postgres          +
           |          |          |             |             | postgres=CTc/postgres
(4 rows)

Testing Failover

NOTE: Testing failover as shown here will require resetting the standby server after failover.

Since Postgres is in standby mode, you should not be able to perform any write operation on the secondary node before failover. For example, execute the following command:

sudo -u postgres psql -c "CREATE DATABASE test"

The command should fail:

ERROR:  cannot execute CREATE DATABASE in a read-only transaction

To signal failover, create the trigger file specified in recovery.conf

touch /var/lib/postgresql/11/main/failover.trigger

Wait a few seconds, then try performing a write operation. For example:

sudo -u postgres psql -c "CREATE DATABASE test2"

Since Postgres is no longer operating as a standby, the operation will succeed. Postgres will also rename your recovery.conf file to recovery.done, and will delete the trigger file.

To go back to standby, stop Postgres on the (former) secondary node:

systemctl stop [email protected]

Reset the data directory:

mv /var/lib/postgresql/11/main/ /var/lib/postgresql/11/main.2.bak
pg_basebackup -h master_ip_address -U replicator -D /var/lib/postgresql/11/main/ -P --password --slot replicator
chown -R postgres:postgres /var/lib/postgresql/11/main

And recreate recovery.conf:

cp /var/lib/postgresql/11/main.2.bak/recovery.done /var/lib/postgresql/11/main/recovery.conf

Finally, restart Postgres:

systemctl start [email protected]

The secondary instance is now back to standby mode. You may want to re-test replication at this point.

Finishing up

Remove any unnecessary databases on the master node, for example:

sudo -u postgres psql
postgres=# DROP DATABASE replitest;

And delete the old data directories on your standby node:

rm /var/lib/postgresql/11/main.bak -r
rm /var/lib/postgresql/11/main.2.bak -r
Share this page:

Suggested articles

0 Comment(s)

Add comment