Streaming replication PostgreSQL + failover automatique

Symptôme

Objectif : mettre en place une hot standby pour PostgreSQL avec la streaming replication et ajouter Patroni/repmgr pour le failover automatique.

1. Définissez les paramètres WAL et de connexion sur le primaire

Pour la streaming replication, wal_level doit être au moins 'replica'. max_wal_senders doit couvrir le nombre de réplicas + les connexions de sauvegarde, et la suppression prématurée du WAL doit être empêchée avec wal_keep_size ou un slot de réplication. Les modifications nécessitent un redémarrage.

PostgreSQL

ALTER SYSTEM SET wal_level = 'replica';
ALTER SYSTEM SET max_wal_senders = 10;
ALTER SYSTEM SET max_replication_slots = 10;
ALTER SYSTEM SET hot_standby = on;
-- To apply the postgresql.conf change:
-- systemctl restart postgresql

2. Définissez le rôle de réplication et l'accès pg_hba

Créez un rôle doté de l'attribut REPLICATION pour la connexion de la standby. Ajoutez à pg_hba.conf une ligne autorisant l'accès à la base 'replication' depuis l'IP de la standby, puis rechargez avec pg_reload_conf().

PostgreSQL

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'secret';
-- Add to pg_hba.conf:
-- host replication replicator 10.0.0.20/32 scram-sha-256
SELECT pg_reload_conf();

3. Créez un slot de réplication persistant sur le primaire

Un slot de réplication physique empêche le primaire de supprimer le WAL nécessaire même lorsque le standby prend du retard ; la gestion par slot est plus sûre que wal_keep_size. Le nom du slot sera utilisé dans la configuration du standby.

PostgreSQL

SELECT pg_create_physical_replication_slot('standby1_slot');
SELECT slot_name, active, restart_lsn FROM pg_replication_slots;

4. Prenez une sauvegarde de base sur le serveur standby avec pg_basebackup

Avec le répertoire de données du standby vide, prenez une copie complète depuis le primaire. L'option -R écrit automatiquement le fichier standby.signal et primary_conninfo ; -C peut aussi créer le slot. Le nom du slot est passé avec -S.

PostgreSQL

pg_basebackup -h 10.0.0.10 -U replicator -D /var/lib/pgsql/data \
  -R -X stream -S standby1_slot -P -v

5. Vérifiez primary_conninfo et hot_standby, démarrez la standby

Vérifiez primary_conninfo (host, user, slot) dans le postgresql.auto.conf écrit par -R ; la présence du fichier standby.signal détermine le mode standby. Avec hot_standby=on, la standby accepte les requêtes en lecture seule. Démarrez le service.

PostgreSQL

-- Expected in postgresql.auto.conf:
-- primary_conninfo = 'host=10.0.0.10 user=replicator password=secret'
-- primary_slot_name = 'standby1_slot'
-- systemctl start postgresql
SELECT pg_is_in_recovery();  -- should return true on the standby

6. Surveillez le retard de réplication depuis le primaire

Sur le primaire, pg_stat_replication affiche pour chaque standby le LSN envoyé/rejoué et le retard. Les retards write/flush/replay doivent rester faibles ; sync_state vaut 'sync' si un réplica synchrone est utilisé.

PostgreSQL

SELECT client_addr, state, sync_state,
       pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes,
       write_lag, flush_lag, replay_lag
FROM pg_stat_replication;

7. Ajoutez Patroni ou repmgr pour le failover automatique

La streaming replication seule exige une promotion manuelle. Le failover automatique nécessite un gestionnaire de cluster : Patroni (élection du leader sur etcd/Consul + promotion automatique) ou repmgr (surveillance/failover avec repmgrd). Côté application, fournissez un point d'accès unique avec HAProxy/pgBouncer. Si une promotion manuelle est nécessaire : pg_ctl promote. Avant de promouvoir, assurez-vous que l'ancien primaire est réellement arrêté et que les applications ne peuvent pas y écrire (risque de split-brain). Réintégrer l'ancien primaire comme standby du nouveau primaire nécessite pg_rewind ; pour cela, wal_log_hints = on ou les data checksums doivent être activés.

PostgreSQL

-- Manual promote (if there is no cluster manager):
pg_ctl promote -D /var/lib/pgsql/data
-- or via SQL:
SELECT pg_promote();
-- With Patroni: patronictl -c /etc/patroni.yml switchover mycluster