Creating High Availability with PostgreSQL 14 Stream Replication: A Step-by-Step Guide

Setting up PostgreSQL 14 Stream Replication for High Availability involves creating a primary database server and one or more standby servers that replicate data from the primary. This step-by-step guide will walk you through the installation and configuration process. 

Step 1: Install PostgreSQL 14 on Primary Server

First, ensure that you have PostgreSQL 14 installed on your primary server. You can do this using a package manager or by compiling from source. For example, on Ubuntu, you can use: 

sudo apt-get update
sudo apt-get install postgresql-14

Step 2: Configure Primary Server

  1. Edit the PostgreSQL configuration file to allow replication. Locate the postgresql.conf file, typically found in /etc/postgresql/14/main/ on Linux systems.
  2. Add or modify the following lines:

listen_addresses = ‘*’ # Listen on all network interfaces
wal_level = replica # Set the Write-Ahead Log level to replica
max_wal_senders = 3 # Number of replication connections allowed
wal_keep_segments = 64 # Number of WAL files to keep for replication

3. Create a replication user and grant the necessary permissions:

CREATE USER replicator REPLICATION LOGIN CONNECTION LIMIT 3 ENCRYPTED PASSWORD ‘your_password’;

4. Update the pg_hba.conf file to allow replication connections. Add the following line to allow replication from the standby server(s):

host replication replicator standby_ip/32 md5

5. Restart PostgreSQL to apply the changes:

sudo systemctl restart postgresql

Step 3: Create a Base Backup on Primary

  1. On the primary server, create a base backup using the pg_basebackup utility. Replace standby_username and standby_password with the credentials for the replication user you created earlier:

pg_basebackup -h primary_ip -U replicator -D /var/lib/postgresql/14/main -P –wal-method=stream

Step 4: Install PostgreSQL 14 on Standby Server

Install PostgreSQL 14 on the standby server using the same method as in Step 1.

Step 5: Configure Standby Server

  1. On the standby server, create a recovery configuration file named recovery.conf in the PostgreSQL data directory (e.g., /var/lib/postgresql/14/main). Add the following lines:

standby_mode = ‘on’
primary_conninfo = ‘host=primary_ip port=5432 user=replicator password=standby_password’
trigger_file = ‘/path/to/trigger/file’

Replace primary_ip, replicator, standby_password, and /path/to/trigger/file with appropriate values.

  1. Ensure that the recovery.conf file is owned by the PostgreSQL user and has the correct permissions.
  2. Start the PostgreSQL service on the standby server:

sudo systemctl start postgresql

Step 6: Monitor Replication

You can monitor the replication status using the following SQL command on the standby server:

SELECT * FROM pg_stat_replication;

Step 7: Failover (Optional)

In the event of a primary server failure, you can promote a standby server to become the new primary. This requires manual intervention and may involve updating DNS records or connection settings in your applications.