HowTo: PostgreSQL streaming replication on Debian

Streaming replication gives you a live copy of the database on a second server — for scaled reads and failover. Primary + standby setup, step by step.

1. Primary config

# pe PRIMARY: /etc/postgresql/16/main/postgresql.conf
wal_level = replica
max_wal_senders = 10
wal_keep_size = 512MB
listen_addresses = '*'

2. Replication user + pg_hba

# user de replicare + regula pg_hba pt standby
sudo -u postgres psql -c "CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'parola';"
# /etc/postgresql/16/main/pg_hba.conf :
host replication replicator 10.0.0.12/32 scram-sha-256
sudo systemctl restart postgresql

3. Base backup on standby

# pe STANDBY: opreste + ia o copie de baza de la primary
sudo systemctl stop postgresql
sudo -u postgres rm -rf /var/lib/postgresql/16/main/*
sudo -u postgres pg_basebackup -h 10.0.0.11 -U replicator \
  -D /var/lib/postgresql/16/main -Fp -Xs -P -R

4. Start standby

# -R a creat deja standby.signal + primary_conninfo
sudo systemctl start postgresql
# verifica pe STANDBY ca e in recovery:
sudo -u postgres psql -c 'SELECT pg_is_in_recovery();'

5. Verify

# pe PRIMARY: vezi standby-ul conectat si lag-ul
sudo -u postgres psql -c 'SELECT client_addr, state, replay_lag FROM pg_stat_replication;'

Conclusion

You now have a live replica with low lag, ready for failover. NOTE: for automatic failover add Patroni or repmgr. We size and configure it for you.