High-Availability PostgreSQL Streaming Replication on Linux

Architecture Overview

PostgreSQL streaming replication is a highly available architecture that ensures data consistency and durability across multiple nodes. This setup involves a primary node (also known as the master) and one or more standby nodes (also known as slaves). The primary node accepts write operations, while the standby nodes replicate the data in real-time.

Prerequisites

• PostgreSQL 10 or later
• Linux distribution (e.g., Ubuntu, CentOS)
• Network connectivity between nodes

Implementation & Commands

Step 1: Configure the Primary Node

Create a new PostgreSQL cluster on the primary node:

sudo apt-get update
sudo apt-get install postgresql-13

Create a new database and user for replication:

CREATE DATABASE mydb;
CREATE ROLE myuser WITH REPLICATION;
ALTER ROLE myuser WITH PASSWORD 'mypassword';

Step 2: Configure the Standby Node

Create a new PostgreSQL cluster on the standby node:

sudo apt-get update
sudo apt-get install postgresql-13

Create a new database and user for replication:

CREATE DATABASE mydb;
CREATE ROLE myuser WITH REPLICATION;
ALTER ROLE myuser WITH PASSWORD 'mypassword';

Step 3: Configure Streaming Replication

Edit the postgresql.conf file on the primary node to enable streaming replication:

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

Add the following configuration:

wal_level = hot_standby
max_wal_senders = 5
wal_sender_timeout = 60

Restart the PostgreSQL service:

sudo service postgresql restart

On the standby node, edit the postgresql.conf file to enable hot standby:

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

Add the following configuration:

wal_level = hot_standby
max_wal_senders = 5
wal_sender_timeout = 60

Restart the PostgreSQL service:

sudo service postgresql restart

Step 4: Configure Replication

On the primary node, create a new file for the standby node’s connection:

sudo nano /etc/postgresql/13/main/recovery.conf

Add the following configuration:

standby_mode = 'on'
primary_conninfo = 'host=localhost port=5432 user=myuser password=mypassword'

On the standby node, start the PostgreSQL service with the recovery file:

sudo service postgresql start

Production Edge Cases & Safeguards

• Ensure network connectivity between nodes is stable and reliable.
• Regularly check the PostgreSQL logs for any replication issues.
• Use pg_stat_replication to monitor replication performance.

Conclusion

High-availability PostgreSQL streaming replication on Linux provides a robust and scalable architecture for disaster recovery and data durability. By following these steps, you can ensure your PostgreSQL cluster is highly available and ready for production use cases.

Additional Resources

• PostgreSQL documentation: https://www.postgresql.org/docs/
• PostgreSQL community forum: https://www.postgresql.org/community/