PostgreSQL Streaming Replication on NetBSD 10

Primary: 192.168.1.10   Standby: 192.168.1.11

Primary – /var/lib/postgres/data/postgresql.conf

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

/var/lib/postgres/data/pg_hba.conf:

host replication replicator 192.168.1.11/32 scram-sha-256

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

systemctl restart postgresql

Standby – clone from primary

systemctl stop postgresql
rm -rf /var/lib/pgsql/data/*
sudo -u postgres pg_basebackup -h 192.168.1.10 -U replicator \
    -D /var/lib/pgsql/data -P -R --wal-method=stream
systemctl start postgresql

Monitor (on primary)

SELECT client_addr, state, sent_lsn, replay_lsn
FROM pg_stat_replication;

Replication lag (on standby)

SELECT now() - pg_last_xact_replay_timestamp() AS lag;