HowTo: replicare PostgreSQL (streaming) pe Debian

Replicarea streaming iti da o copie live a bazei pe un al doilea server — pentru citiri scalate si failover. Configurare primary + standby, pas cu pas.

1. Config primary

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

2. User replicare + 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 pe 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. Pornire 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. Verificare

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

Concluzie

Ai o replica live cu lag mic, gata de failover. ATENTIE: pt failover automat adauga Patroni sau repmgr. O dimensionam si o configuram pentru tine.