Introduction

Multi-master replication in PostgreSQL allows for high availability and redundancy by enabling multiple database servers to accept write operations simultaneously. This guide will walk you through the steps to set up multi-master PostgreSQL replication on Ubuntu 22.04, which is especially effective when utilizing a Windows VPS UK for your database management.

Prerequisites

• Two or more Ubuntu 22.04 servers with root access
• Basic knowledge of PostgreSQL and Linux commands
• PostgreSQL installed on all nodes

Step 1: Install PostgreSQL

On each server, install PostgreSQL using the following commands:

sudo apt update
sudo apt install postgresql postgresql-contrib -y

Step 2: Configure PostgreSQL for Replication

Open the PostgreSQL configuration file on each server:

sudo nano /etc/postgresql/14/main/postgresql.conf

Make the following changes:

listen_addresses = '*'
wal_level = logical
max_wal_senders = 5
max_replication_slots = 5

Save the file and exit.

Step 3: Update pg_hba.conf

Edit the pg_hba.conf file to allow replication connections:

sudo nano /etc/postgresql/14/main/pg_hba.conf

Add the following lines, replacing node_ip with the IP address of the other server:

host    replication     all             node_ip/32            md5

Repeat this step for each server to allow connections from each other.

Step 4: Create Replication User

Log into PostgreSQL on one of the servers and create a replication user:

sudo -u postgres psql

CREATE ROLE replicator WITH REPLICATION PASSWORD 'your_password' LOGIN;

Exit the PostgreSQL prompt:

EXIT;

Step 5: Set Up Logical Replication

On each node, run the following commands to create a publication and subscription:

sudo -u postgres psql
CREATE PUBLICATION my_pub FOR ALL TABLES;
CREATE SUBSCRIPTION my_sub CONNECTION 'host=node_ip dbname=your_db user=replicator password=your_password' PUBLICATION my_pub;

Repeat this for each server, adjusting the connection string as necessary.

Step 6: Start PostgreSQL Service

Start the PostgreSQL service on each node:

sudo systemctl restart postgresql

Step 7: Verify Replication Setup

Log into PostgreSQL and check the replication status:

sudo -u postgres psql
SELECT * FROM pg_stat_replication;

This should show the status of connected replication clients.

Step 8: Configure Firewall

Ensure that your firewall allows traffic on PostgreSQL's default port (5432):

sudo ufw allow 5432/tcp

Step 9: Conclusion

You have successfully set up multi-master PostgreSQL replication on Ubuntu 22.04, providing a robust solution for high availability. This configuration can be particularly beneficial when deployed on a Windows VPS. For additional options, explore various VPS UK Windows solutions, including Windows Virtual Private Server Hosting and Windows VPS Hosting UK for optimal performance.