Step-by-step PostgreSQL 15 multi-master Replication for High Availability

Step-by-step PostgreSQL 15 multi-master Replication for High Availability

Here is a general step-by-step guide for setting up multi-master replication in PostgreSQL 15 for high availability:

Step 1: Prepare the master nodes: Make sure that the master nodes are running the same version of PostgreSQL and that they are properly configured.

Step 2: Create replication users: Create replication users on both master nodes with the necessary permissions for replication.

Step 3: Configure replication: On each master node, add the following settings to the postgresql.conf file:

wal_level = replica
max_wal_senders = <number of replicas>

Step 4: Create a replication slot: On each master node, create a replication slot using the pg_create_physical_replication_slot function:

SELECT pg_create_physical_replication_slot('<slot_name>');

Step 5: Start the replication: On each master node, start the replication using the pg_basebackup command and the replication slot name:

pg_basebackup -D /path/to/data_directory -R -S <slot_name> -U <replication_user> -h <master_host> -p <master_port>

Step 6: Configure the recovery.conf file: On each replica, configure the recovery.conf file with the necessary settings, including the master host, port, and replication user:

standby_mode = on
primary_conninfo = 'host=<master_host> port=<master_port> user=<replication_user>'

Step 7: Start the replica: Start the replica and make sure that it is connected to the master node and is replicating the data.

Step 8: Monitor the replication: Use the pg_stat_replication view to monitor the replication status and ensure that it is working correctly.

Step 9: Repeat steps 4-8 on the second master node

Step 10: Configure the load balancer: Configure the load balancer to distribute requests across the master nodes.

It’s important to note that this is a general guide, and that the exact steps may vary depending on the specific requirements of your system. Also, it’s important to test the replication in a test environment before applying it to a production environment, and to keep a backup of the data and the configuration files in case of a disaster recovery scenario.


About Shiv Iyer 444 Articles
Open Source Database Systems Engineer with a deep understanding of Optimizer Internals, Performance Engineering, Scalability and Data SRE. Shiv currently is the Founder, Investor, Board Member and CEO of multiple Database Systems Infrastructure Operations companies in the Transaction Processing Computing and ColumnStores ecosystem. He is also a frequent speaker in open source software conferences globally.