How to set up replication in PostgreSQL

Replication gives you a second server that receives all changes from the main database in real time. It is used for redundancy (if the primary fails) and to distribute reads.

How it works (streaming replication)

The primary server sends the transaction log (WAL) to the standby server, which applies the same changes.

1. On the primary

In postgresql.conf:

wal_level = replica
max_wal_senders = 3

Create a dedicated replication role and allow the connection in pg_hba.conf:

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'password';

# pg_hba.conf
host replication replicator STANDBY_IP/32 scram-sha-256

2. On the standby

Stop PostgreSQL, then copy the database from the primary:

pg_basebackup -h PRIMARY_IP -U replicator -D /var/lib/postgresql/data -P -R

The -R option configures the replication connection automatically. Start PostgreSQL on the standby.

3. Verify

On the primary:

SELECT * FROM pg_stat_replication;

For two servers, run the primary and standby on two separate VPS instances, ideally in different locations. Do not forget backups though: replication does not replace a backup.