PostgreSQL Streaming Replication on OpenBSD 7.5

This guide sets up a primary/standby streaming replication pair.

Prerequisites

Two servers running PostgreSQL on OpenBSD 7.5:

Edit /var/lib/postgres/data/postgresql.conf on the primary:

wal_level = replica
max_wal_senders = 5
wal_keep_size = 512MB
listen_addresses = '*'

Edit /var/lib/postgres/data/pg_hba.conf on the primary:

host replication replicator 192.168.1.11/32 scram-sha-256

Create the replication user:

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'ReplPass123!';

Reload/restart the primary:

systemctl restart postgresql

Stop PostgreSQL on the standby and wipe the data directory:

systemctl stop postgresql
rm -rf /var/lib/pgsql/data/*   # adjust path for OpenBSD 7.5

Use pg_basebackup to clone the primary:

sudo -u postgres pg_basebackup -h 192.168.1.10 -U replicator \
    -D /var/lib/pgsql/data -P -R --wal-method=stream

The -R flag creates standby.signal and populates postgresql.auto.conf.

systemctl start postgresql

On the primary:

SELECT client_addr, state, sent_lsn, write_lsn, flush_lsn, replay_lsn
FROM pg_stat_replication;

On the standby:

SELECT now() - pg_last_xact_replay_timestamp() AS replication_lag;