PostgreSQL High Availability: Primary-Replica Configuration

Checking the Machines

Start by checking the hostnames and IP addresses of both machines. On VM1, run:

cat /etc/hosts

On VM2, run the same:

cat /etc/hosts

The output shows VM1 at 10.42.2.42 and VM2 at 10.42.2.154. These addresses are used for the subnet mask in pg_hba.conf and the hostname in the physical backup command.

Configuring Listen Addresses

PostgreSQL by default listens only on localhost. Change the listen address to accept connections from other machines. Edit the configuration:

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

Find the listen_addresses line and set it to *:

listen_addresses = '*'

This tells PostgreSQL to bind to all network interfaces. Restart PostgreSQL:

sudo systemctl restart postgresql

Verify the change:

sudo -u postgres psql -c "SHOW listen_addresses;"

The output should show *.

Verifying Replication Settings

Still in postgresql.conf, check three settings for replication. Search for wal_level – it must be replica. Search for max_wal_senders – it should be at least 10. Search for max_replication_slots – it should also be at least 10. These are already set correctly by default.

Configuring Host-Based Authentication

Edit the pg_hba.conf file:

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

Scroll down and add two lines. First, allow all databases and all users from the subnet with password authentication:

host    all             all             10.42.0.0/16            md5

Second, allow replication connections for the replicator user from the same subnet:

host    replication     replicator      10.42.0.0/16            md5

Save and exit. Restart PostgreSQL:

sudo systemctl restart postgresql

Creating the Replication User

Create a dedicated role for replication:

sudo -u postgres psql -c "CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'replicator123';"

The REPLICATION privilege is separate from normal database privileges – a regular role cannot open a replication stream.

Preparing the Replica Server

On VM2, stop PostgreSQL:

sudo systemctl stop postgresql

Check the PostgreSQL version:

ls /etc/postgresql/

It should be 16. Remove the default data directory:

sudo rm -rf /var/lib/postgresql/16/main

Taking a Base Backup

On VM2, run pg_basebackup to copy the primary's data:

sudo -u postgres pg_basebackup \
  --host=vm1 \
  --username=replicator \
  --pgdata=/var/lib/postgresql/16/main \
  -P -X stream -R

• -P shows progress.
• -X stream streams WAL during the backup so no changes are missed.
• -R configures the replica to start as a standby and writes the primary_conninfo and standby.signal files.

Enter the password replicator123 when prompted.

Starting the Replica

Start PostgreSQL on VM2:

sudo systemctl start postgresql

PostgreSQL reads the standby.signal file, connects to VM1 using primary_conninfo, and begins streaming WAL.

Verifying Replication

On VM1 (primary), check the replication status:

SELECT application_name, state, sync_state FROM pg_stat_replication;

The output should show streaming as the state.

On VM2 (replica), verify the connection:

SELECT status, sender_host FROM pg_stat_wal_receiver;

The status should be streaming and the sender host should be vm1.

Testing Data Propagation

On VM1, create a database and table:

CREATE DATABASE shopdb;
\c shopdb
CREATE TABLE products (id SERIAL PRIMARY KEY, name TEXT);
INSERT INTO products (name) VALUES ('shoes'), ('shirt'), ('hat');
SELECT * FROM products;

On VM2, connect to the same database and query:

sudo -u postgres psql -d shopdb -c "SELECT * FROM products;"

The data appears on the replica. Attempting to write on the replica fails:

INSERT INTO products (name) VALUES ('fail');

This returns an error: cannot execute INSERT in a read-only transaction.

Performing a Manual Failover

On VM1, stop PostgreSQL:

sudo systemctl stop postgresql
sudo systemctl status postgresql

On VM2, promote the replica to primary:

sudo pg_ctlcluster 16 main promote

Now the replica becomes writable. Insert data:

INSERT INTO products (name) VALUES ('shootfail');
SELECT * FROM products;

The new row appears. The replica has taken over as the primary.