How to Set Up PostgreSQL 15 and Cascading Replication Involving Three Servers

4 min readMar 6, 2023

PostgreSQL is a powerful open-source database management system that offers various features to facilitate data management. One of the features that make PostgreSQL stand out is cascading replication. Cascading replication is a feature that enables the replication of data from one PostgreSQL database to another PostgreSQL database, which, in turn, replicates the data to another database, and so on. This means that you can have several replicas of your primary database, and each of them can replicate the data to another replica database. This feature helps to distribute data across different geographical locations and ensure that all your databases have the latest data. It is particularly useful if you intend on replacing the primary with the first replica, if for example you are retiring the primary server.

In this article, I will show you how to set up PostgreSQL 15 and cascading replication involving three servers. You could, of course, rinse and repeat for additional replicas if that strikes your fancy.

Prerequisites

Before you can set up cascading replication in PostgreSQL 15, you need to have the following prerequisites:

Three servers running PostgreSQL 15
The PostgreSQL instances on each server must be up and running
Replication user account and password

Step 1: Enable streaming replication on the primary server

To enable streaming replication on the primary server, you need to modify the postgresql.conf and pg_hba.conf files.

First, open the postgresql.conf file and make the following changes:

# Uncomment and modify the following settings:
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 10

These settings enable WAL (Write Ahead Log) archiving and allow replication to occur. max_wal_senders determines the maximum number of replication connections that can be made to the primary server, and wal_keep_segments determines the number of WAL segments that the server will keep before deleting them.

Next, open the pg_hba.conf file and add the following line to allow the replication user to connect:

host replication replication_user_ip_address/32 md5

Replace replication_user_ip_address with the IP address of the replication user.

Finally, restart the primary server for the changes to take effect.

Step 2: Create the first replica server

To create the first replica server, you need to first take a base backup of the primary server. This backup will be used to initialize the replica server.

To take a base backup, run the following command on the first replica server:

pg_basebackup -h primary_server_ip_address -U replication -P -R -X stream -D /path/to/replica/data/directory

Replace primary_server_ip_address with the IP address of the primary server and /path/to/replica/data/directory with the path to the data directory on the first replica server.

Once the backup is complete, create a recovery.conf file in the first replica data directory with the following contents:

standby_mode = on
primary_conninfo = 'host=primary_server_ip_address port=5432 user=replication password=password'

Replace primary_server_ip_address with the IP address of the primary server and password with the password for the replication user.

Step 3: Create the second replica server

To create the second replica server, you need to first take a base backup of the first replica server. This backup will be used to initialize the second replica server.

To take a base backup, run the following command on the second replica server:

pg_basebackup -h first_replica_server_ip_address -U replication -P -R -X stream -D /path/to/second_replica/data/directory

Replace `first_replica_server_ip_address` with the IP address of the first replica server and `/path/to/second_replica/data/directory` with the path to the data directory on the second replica server.

Once the backup is complete, create a `recovery.conf` file in the second replica data directory with the following contents:```conf
standby_mode = on
primary_conninfo = 'host=first_replica_server_ip_address port=5432 user=replication password=password'

Replace first_replica_server_ip_address with the IP address of the first replica server and password with the password for the replication user.

Step 4: Configure cascading replication

To configure cascading replication, you need to modify the recovery.conf file on the second replica server to point to the first replica server instead of the primary server.

standby_mode = on
primary_conninfo = 'host=first_replica_server_ip_address port=5432 user=replication password=password'

Replace first_replica_server_ip_address with the IP address of the first replica server and password with the password for the replication user.

Repeat Step 4 to add more replica servers.

Conclusion

In this article, I have shown you how to set up PostgreSQL 15 and cascading replication involving three servers. This setup ensures that your data is replicated across multiple servers, which increases availability and reduces the risk of data loss. As always, make sure to test your replication setup thoroughly and monitor it regularly to ensure that everything is working as expected.

I Love Coffee! https://ko-fi.com/canutethegreat