PostgreSQL : streaming replication

Introduction

La streaming replication PostgreSQL :

• Le primary envoie son WAL (Write-Ahead Log) en continu
• Les replicas appliquent les changements en temps reel
• Replicas accessibles en lecture seule (load balancing reads)
• En cas de panne primary, on promote un replica
• Replication asynchrone (defaut) ou synchrone (zero data loss)

Ce tuto : 1 primary + 1 replica, replication asynchrone.

Prerequis

• 2 VPS Linux Debian / Ubuntu
• PostgreSQL 16 deja installe sur les deux
• Reseau prive entre les VPS (ou VPN)
• IPs : 10.0.0.1 (primary), 10.0.0.2 (replica)

Etape 1 : Preparer le primary

Sur 10.0.0.1, editez /etc/postgresql/16/main/postgresql.conf :

listen_addresses = '*'
wal_level = replica
max_wal_senders = 10
wal_keep_size = 1GB
hot_standby = on

pg_hba.conf :

host    replication     replicator      10.0.0.2/32             scram-sha-256

Restart :

sudo systemctl restart postgresql

Etape 2 : Creer l'utilisateur replication

Sur primary :

sudo -u postgres psql

CREATE ROLE replicator WITH REPLICATION LOGIN ENCRYPTED PASSWORD 'replic-pass-fort';
\q

Etape 3 : Creer un replication slot

SELECT pg_create_physical_replication_slot('replica1_slot');

Le slot garantit que le primary ne supprime pas les WAL avant que le replica les ait recus.

Etape 4 : Preparer le replica

Sur 10.0.0.2, stoppez PG :

sudo systemctl stop postgresql

Videz le datadir :

sudo -u postgres rm -rf /var/lib/postgresql/16/main/*

Etape 5 : Cloner depuis le primary

sudo -u postgres pg_basebackup \
    -h 10.0.0.1 \
    -U replicator \
    -D /var/lib/postgresql/16/main \
    -Fp -Xs -P -R \
    -S replica1_slot

Options :

• -Fp : format plain (fichiers)
• -Xs : stream WAL pendant le backup
• -P : show progress
• -R : ecrit automatiquement standby.signal et la connection info
• -S replica1_slot : utilise le slot cree

pg_basebackup vous demandera le mot de passe replicator.

Etape 6 : Verifier la conf replica

/var/lib/postgresql/16/main/postgresql.auto.conf contient :

primary_conninfo = 'user=replicator passfile=''/var/lib/postgresql/.pgpass'' host=10.0.0.1 port=5432 sslmode=prefer ...'
primary_slot_name = 'replica1_slot'

Verifiez le fichier standby.signal :

sudo ls /var/lib/postgresql/16/main/standby.signal

S'il existe, PG demarrera en mode standby.

Etape 7 : Demarrer le replica

sudo systemctl start postgresql
sudo systemctl status postgresql

Verifiez les logs :

sudo tail -f /var/log/postgresql/postgresql-16-main.log

Cherchez started streaming WAL from primary.

Etape 8 : Verifier la replication

Sur primary :

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

Vous devriez voir state = streaming et sync_state = async.

Sur replica :

SELECT pg_is_in_recovery();

Doit retourner t (true).

SELECT now() - pg_last_xact_replay_timestamp() AS lag;

Affiche le lag (typiquement quelques ms).

Etape 9 : Tester la replication

Sur primary :

CREATE DATABASE test_replic;
\c test_replic
CREATE TABLE foo (id serial, msg text);
INSERT INTO foo (msg) VALUES ('Hello from primary');

Sur replica :

\c test_replic
SELECT * FROM foo;

La donnee est presente. Ecriture interdite :

INSERT INTO foo (msg) VALUES ('Try'); 
-- ERROR: cannot execute INSERT in a read-only transaction

Etape 10 : Replication synchrone (optionnel)

Pour garantir zero data loss, activez le mode synchrone.

Sur primary postgresql.conf :

synchronous_standby_names = 'replica1'
synchronous_commit = on

Sur replica postgresql.auto.conf :

primary_conninfo = '... application_name=replica1 ...'

Reload primary :

SELECT pg_reload_conf();

⚠️ En mode synchrone, chaque COMMIT attend le replica. Si le replica est down, le primary bloque. Utilisez au moins 2 replicas synchrones pour eviter le SPOF.

Etape 11 : Failover manuel

Quand le primary tombe :

Sur le replica, promote-le en primary :

sudo -u postgres pg_ctl promote -D /var/lib/postgresql/16/main

Ou :

SELECT pg_promote();

Le replica devient writeable. Pointez vos apps vers la nouvelle IP.

L'ancien primary, une fois recupere, doit etre reconfigure en replica (utilisez pg_rewind pour eviter de tout recloner).

Etape 12 : Failover automatique avec Patroni

Pour un failover automatique :

• Patroni + etcd / Consul / ZooKeeper : leader election
• PgBouncer ou HAProxy devant pour rediriger les connexions
• pg_auto_failover (Citus) : alternative plus simple

C'est un setup avance, qui meriterait un tuto dedie.

Depannage

Replica ne synchronise pas

sudo tail -f /var/log/postgresql/postgresql-16-main.log

Cherchez des erreurs could not connect to primary. Verifiez :

• Connectivite reseau (firewall, route)
• pg_hba.conf sur primary
• Mot de passe replicator
• Slot existe sur primary

"WAL receiver process exited"

Verifiez wal_keep_size (primary) et max_slot_wal_keep_size. Si trop petit, le replica perd les WAL avant de pouvoir les rattraper.

Lag qui grossit

Le replica n'arrive pas a suivre. Verifiez :

• I/O disque du replica
• CPU du replica
• Reseau (latence, bande passante)

SELECT now() - pg_last_xact_replay_timestamp() AS lag;

"FATAL: could not start WAL streaming"

Verifiez le slot existe sur primary :

SELECT * FROM pg_replication_slots;

Si le slot manque, recreez-le.

Commandes utiles

# Status replication primary
sudo -u postgres psql -c "SELECT * FROM pg_stat_replication;"

# Status replica
sudo -u postgres psql -c "SELECT * FROM pg_stat_wal_receiver;"

# Lag replica
sudo -u postgres psql -c "SELECT now() - pg_last_xact_replay_timestamp() AS lag;"

# Slots
sudo -u postgres psql -c "SELECT * FROM pg_replication_slots;"

# Promote replica
sudo -u postgres pg_ctl promote -D /var/lib/postgresql/16/main

# Re-synchroniser un ancien primary avec pg_rewind
sudo -u postgres pg_rewind \
    --target-pgdata=/var/lib/postgresql/16/main \
    --source-server="host=10.0.0.2 user=replicator password=..."

# Voir le LSN courant
sudo -u postgres psql -c "SELECT pg_current_wal_lsn();"

Conclusion

Avec la streaming replication, vous obtenez :

• Lectures reparties (load balancing)
• Failover en cas de panne
• Backups depuis le replica (sans charger le primary)

Limites :

• DDL (ALTER TABLE) bloque les reads sur le replica le temps de l'apply
• Pas de filtering (tous les changements sont repliques)

Pour aller plus loin :

• Combinez avec Patroni pour le failover auto
• Pour de la replication logique (subset), utilisez logical replication
• Pour multi-master, regardez BDR (commercial) ou Citus

Ressources

• Documentation officielle : https://www.postgresql.org/docs/current/warm-standby.html
• pg_basebackup : https://www.postgresql.org/docs/current/app-pgbasebackup.html
• Patroni : https://github.com/zalando/patroni
• pg_rewind : https://www.postgresql.org/docs/current/app-pgrewind.html