🚀 Master the Flow: Setting Up PostgreSQL 15 Streaming Replication

In the world of robust data management, high availability and read scalability are non-negotiables. PostgreSQL’s built-in streaming replication is the powerhouse feature that helps you achieve this. By setting up a Master-Slave (Primary-Standby) configuration, you ensure that every transaction on your primary server is streamed in real-time to one or more standby servers.

This guide walks you through setting up a fast, reliable streaming replication link between a PostgreSQL 15 Primary (Master) and Standby (Slave) server.

🛠️ Prerequisites

Before we start, you’ll need two separate servers with PostgreSQL 15 installed:

Primary Server: The server where all writes occur.

IP Example: 192.168.1.101

Standby Server: The read-only replica that receives changes from the primary.

IP Example: 192.168.1.102

You’ll also need administrative access (sudo or the postgres user) on both machines.

1. Primary Server Configuration

A. Create a Replication User

Connect to your primary server’s PostgreSQL instance (e.g., using psql) and create a dedicated user for replication. This user needs the REPLICATION privilege.

CREATE USER replicator WITH REPLICATION ENCRYPTED PASSWORD 'a_strong_password';

B. Configure postgresql.conf

Edit the main configuration file, typically located in your data directory (e.g., /etc/postgresql/15/main/postgresql.conf).

C. Configure pg_hba.conf

Edit the host-based authentication file (pg_hba.conf) to allow the standby server to connect as a replication user.

Add a line like this, replacing the IP address with your standby’s IP:

# TYPE    DATABASE     USER       ADDRESS           METHOD
host    replication    replicator 192.168.1.102/32    scram-sha-256

D. Restart the Primary Server

You must restart the PostgreSQL service for these changes to take effect:

sudo systemctl restart postgresql

2. Standby Server Initialization

The standby server needs an initial copy of the primary’s data directory. The pg_basebackup utility is the simplest way to do this.

A. Stop PostgreSQL on Standby

First, ensure the standby’s PostgreSQL service is stopped to clear the data directory for the backup.

sudo systemctl stop postgresql

B. Clear and Backup Data Directory

It’s critical that the standby’s data directory is empty before taking a base backup. Make sure you back up any existing data first!

Empty the data directory (e.g., /var/lib/postgresql/15/main/).

sudo rm -rf /var/lib/postgresql/15/main/*

Take the base backup from the primary using the replicator user.

# Run this command on the Standby Server, replacing the IP/paths
sudo -u postgres pg_basebackup -h 192.168.1.101 -U replicator -D /var/lib/postgresql/15/main -Fp -Xs -R -P

Option	Description
-h	Primary server hostname/IP.
-U	Replication user (replicator).
-D	Target data directory on the standby.
-Fp	Plain file format (default).
-Xs stream	Enables streaming WAL during backup.
-R	Crucial: Automatically creates the necessary standby.signal and postgresql.auto.conf files.
-P	Reports progress.

The -R flag in modern PostgreSQL (12+) automatically sets up the standby configuration by creating a standby.signal file and a primary_conninfo entry in postgresql.auto.conf inside the data directory.

C. Start the Standby Server

Now, start the PostgreSQL service on the standby. It will automatically detect the standby.signal file and begin connecting to the primary for replication.

sudo systemctl start postgresql

3. Verification

To confirm replication is working, you can check both the primary and standby servers.

On the Primary Server

Check the pg_stat_replication view to see the standby connection.

SQL

psql -c "SELECT usename, client_addr, state, sync_state FROM pg_stat_replication;"

You should see a row with usename as replicator, client_addr as the standby’s IP, and a state of streaming.

On the Standby Server

1. Check the Standby Status: Connect to the standby and check if it’s currently recovering (which means it’s receiving WAL).

SQL

psql -c "SELECT pg_is_in_recovery();"

The result should be t (true).

2. Test Read-Only Status: Attempt a write operation on the standby—it should fail, confirming it’s a read-only replica.

SQL

psql -c "CREATE TABLE test_rep (id int);

— ERROR: cannot execute CREATE TABLE in a read-only transaction

3. Test Data Replication: Create a table or insert data on the Primary, then run a SELECT query on the Standby to confirm the new data exists. If the data is present, your streaming replication is live!