Multi Node Deployment of PostgreSQL

Introduction

Deploying PostgreSQL on multiple nodes increases availability, fault tolerance, and scalability. This tutorial will guide you through the process of setting up PostgreSQL in a multi-node configuration, covering installation, configuration, replication, and basic usage.

1. Prerequisites

Before you begin, ensure that you have the following:

Multiple servers or virtual machines with a supported operating system (e.g., Linux, Windows, macOS).
Root or administrative access to the servers.
An internet connection for downloading PostgreSQL packages.
SSH access between nodes for secure communication.

2. Installing PostgreSQL

Installation steps vary based on the operating system. Below are the instructions for popular operating systems.

2.1. Linux (Ubuntu/Debian)

Example Code:

sudo apt update
sudo apt install postgresql postgresql-contrib

2.2. Windows

Download the PostgreSQL installer from the official website and follow the installation wizard.

2.3. macOS

Example Code:

brew install postgresql

3. Configuring Primary Node

After installing PostgreSQL, some initial configuration is necessary to set up the primary node.

3.1. Editing PostgreSQL Configuration

Example Code:

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

# Adjust the following parameters
listen_addresses = '*'
wal_level = replica
max_wal_senders = 10
wal_keep_segments = 64

3.2. Setting Up Replication User

Example Code:

sudo -i -u postgres
psql
CREATE USER replica REPLICATION LOGIN ENCRYPTED PASSWORD 'yourpassword';
\q
exit

3.3. Configuring pg_hba.conf

Example Code:

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

# Add the following lines to allow replication
host replication replica your_replica_ip/32 md5

3.4. Restarting PostgreSQL

Example Code:

sudo systemctl restart postgresql

4. Configuring Replica Node

The replica node must be configured to follow the primary node.

4.1. Setting Up Base Backup

Example Code:

sudo systemctl stop postgresql
sudo -i -u postgres
pg_basebackup -h primary_ip -D /var/lib/postgresql/12/main -U replica -v -P --wal-method=stream
exit

4.2. Creating recovery.conf

Example Code:

sudo -i -u postgres
nano /var/lib/postgresql/12/main/recovery.conf

# Add the following lines
standby_mode = 'on'
primary_conninfo = 'host=primary_ip port=5432 user=replica password=yourpassword'
trigger_file = '/tmp/promote_to_primary'

4.3. Restarting Replica Node

Example Code:

sudo systemctl start postgresql

5. Monitoring and Maintenance

Regular monitoring and maintenance ensure the smooth operation of your multi-node PostgreSQL deployment.

5.1. Checking Replication Status

Example Code:

sudo -i -u postgres
psql
SELECT * FROM pg_stat_replication;

5.2. Handling Failover

If the primary node fails, you can promote a replica node to be the new primary:

Example Code:

sudo -i -u postgres
touch /tmp/promote_to_primary

5.3. Reconfiguring After Failover

After promoting a replica to primary, reconfigure other replicas to follow the new primary.

Conclusion

Deploying PostgreSQL on multiple nodes significantly enhances your database system's availability and scalability. By following the steps outlined in this tutorial, you can set up, configure, and maintain a multi-node PostgreSQL deployment effectively. This setup is ideal for production environments where high availability and fault tolerance are critical.