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
