How to Set Up PostgreSQL 15 and Cascading Replication Involving Three Servers
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