Building a High-Availability Database: PostgreSQL Primary-Replica Replication

The Architecture of Redundancy

A single database instance is a critical point of failure. If the underlying storage corrupts or the kernel panics, your entire application goes offline. Asynchronous streaming replication solves this by continuously shipping Write-Ahead Logs (WAL) from a primary node to one or more standby nodes. To implement this architecture, you must provision at least two isolated KVM VPS instances at CLOUD HIVE DC. Ensure both servers are heavily protected by a strict firewall, as detailed in our Securing Your Server guide.

Configuring the Primary Node

Log into your primary server. You must instruct PostgreSQL to listen on its public or private IP and set the logging level to support replication. Open the main configuration file and adjust the parameters:

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

Find and modify these specific lines to enable WAL streaming. Adjust the version number in the path depending on your specific PostgreSQL installation:

listen_addresses = 'YOUR_PRIMARY_IP, localhost'
wal_level = replica
max_wal_senders = 10
max_replication_slots = 10

Next, you must authorize the replica server to connect and stream the logs. Open the client authentication file:

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

Append this rule to the end of the file, replacing the placeholder with the IP address of your replica node, and restart the service to apply the changes:

host replication replicator YOUR_REPLICA_IP/32 scram-sha-256
sudo systemctl restart postgresql

Bootstrapping the Replica Node

On the secondary server, the PostgreSQL service must be stopped, and its default data directory must be completely erased to make room for the primary node snapshot. Execute these commands with absolute caution:

sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/14/main/*

Now, use the built-in backup utility to clone the primary database directly over the network. The utility will prompt you for the replicator user password. Once the transfer completes, start the service:

sudo -u postgres pg_basebackup -h YOUR_PRIMARY_IP -D /var/lib/postgresql/14/main -U replicator -v -P -X stream
sudo systemctl start postgresql

Verifying the Streaming State

To confirm that the replica is actively receiving and applying the Write-Ahead Logs, return to your primary node and query the internal replication statistics view:

sudo -u postgres psql -c "SELECT client_addr, state, sync_state FROM pg_stat_replication;"

If the state returns as streaming, your high-availability cluster is successfully synchronized. Your data is now redundant, secure, and ready to handle intensive read workloads on the enterprise infrastructure of CLOUD HIVE DC.