How to set up PostgreSQL streaming replication with replication slots on Debian 10

How to set up 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 under development since 1996. Postgres provides different ways to archive and replicate data, one of which is streaming replication. In this mode, the master (master) instance processes the master active database and performs operations. The secondary (slave) instance replicates all changes from the primary database and keeps the same copy of the active database. Secondary servers can also accept read-only queries. If the primary server fails, the secondary server can exit standby mode and run as a new primary server (this is called failover).

PostgreSQL replication usually relies on write-ahead logging (WAL), the process of recording data before writing it to disk. These WAL records are then copied as files to a second node (file-based log shipping) or streamed directly between nodes (stream copy). In most cases, the latter reduces the latency of the standby node receiving changes on the primary node.

The problem with streaming replication without file-based log shipping is that if the primary server discards them prematurely, the secondary server may lose some WAL records. Many configuration parameters can reduce this risk, but often introduce unnecessary storage costs. The solution is to duplicate the slots, a feature provided by Postgres that ensures that the primary server discards them only after the standby node receives the WAL records.

We will set up streaming replication using the replication slot on both Debian 10 nodes.

Claim

  • Two identical Debian 10 instances.
  • The root accesses two instances.
  • The $ EDITOR environment variable should be set on both instances.

Step 1: Install PostgreSQL

Update and restart both nodes:

apt update
apt upgrade -y
reboot

Install Postgres on both nodes and ensure that PostgreSQL is enabled and running:

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

Note: When updating PostgreSQL, according to its documentation, it is safer to update the standby database first.

Step 2: Initial setup

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

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

Find the following line:

#listen_addresses = 'localhost'

change to:

listen_addresses = 'node_ip_address,127.0.0.1'

If two nodes share the same local network, although Postgres is not accessible via the Internet, you can use a private address for node_ip_address. Otherwise, use a public address.

Save your changes and restart both instances:

systemctl restart [email protected]

Step 3: Master configuration

This step applies only to the master / master server.

Open Postgres terminal:

sudo -u postgres psql

The standby node will use the user to connect to the primary node. Create it:

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

Then create a copy slot and exit:

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

For simplicity, both replication roles and slots are referred to as “replicators”, although they need not be the same.

Next, create an entry in pg_hba.conf to allow replicator users to connect from the standby server to the primary server. Open it:

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

Add the following line to the end:

host	replication	replicator	standby_ip_address/32		md5

Restart the master instance:

systemctl restart [email protected]

Step 4: Basic 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]

Back up the old data directory:

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

Use the following command to clone the data directory of the master server to the slave server:

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 when you created the replication role on the master server. After 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: Alternate configuration

This step is only relevant for secondary / slave servers.

Enable hot standby mode in postgresql.conf:

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

Find and uncomment the following lines:

#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 server:

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

Set the name of the replication slot you created on the master server:

primary_slot_name = 'replicator'

Set the path of the 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 begin normal operation as the master 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 any new transactions should be copied.

testing

Test replication

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

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

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

sudo -u postgres psql -c "l"

You should see that the replica database is 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)

Test failover

Note: As shown here, testing failover will require resetting the standby server after the failover.

Since Postgres is in standby mode, you should not be able to perform any write operations 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 a failover, create a trigger file specified in recovery.conf

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

Wait a few seconds, and then try to perform a write operation. E.g:

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

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

To return 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 now returns to standby mode. You may need to retest replication at this point.

Put together

Delete all unnecessary databases on the master node, for example:

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

And delete the old data directory on the standby node:

rm /var/lib/postgresql/11/main.bak -r
rm /var/lib/postgresql/11/main.2.bak -r

Source

Sidebar