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
- Edit the PostgreSQL configuration file to allow replication. Locate the postgresql.conf file, typically found in /etc/postgresql/14/main/ on Linux systems.
- 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
- 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
- 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.
- Ensure that the recovery.conf file is owned by the PostgreSQL user and has the correct permissions.
- 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.