Configuration de la Replication Streaming PostgreSQL

La replication streaming de PostgreSQL fournit un mecanisme robuste pour maintenir des copies en temps reel de votre base de donnees sur plusieurs serveurs. Que vous ayez besoin de haute disponibilite, de recuperation apres sinistre ou de mise a l’echelle des lectures, la replication streaming offre une coherence au niveau des octets avec un lag minimal. Ce guide vous accompagne dans la mise en place d’une architecture primaire-standby depuis zero, la configuration de l’archivage WAL, la surveillance de la sante de la replication et la planification des strategies de failover.

Prerequis

• Deux serveurs Linux (Ubuntu 22.04+ ou RHEL 9+) avec connectivite reseau
• PostgreSQL 16 ou ulterieur installe sur les deux serveurs
• Espace disque suffisant pour les fichiers WAL et les sauvegardes de base
• Acces root ou sudo sur les deux machines
• Port 5432 ouvert entre les serveurs primaire et standby
• Familiarite de base avec les fichiers de configuration PostgreSQL

Comprendre la Replication PostgreSQL

PostgreSQL prend en charge deux modes principaux de replication : la replication streaming et la replication logique. La replication streaming opere au niveau des octets WAL (Write-Ahead Log), envoyant un flux continu d’enregistrements WAL du primaire vers les standbys. Cela cree une copie binaire exacte de l’ensemble du cluster.

Le processus de replication suit ce flux :

1. Le primaire ecrit les modifications dans les segments WAL
2. Le processus WAL sender transmet les enregistrements aux standbys connectes
3. Le WAL receiver du standby ecrit les enregistrements dans le WAL local
4. Le processus startup du standby rejoue les enregistrements WAL pour mettre a jour les fichiers de donnees

La replication streaming peut fonctionner en mode asynchrone (par defaut) ou synchrone. Le mode asynchrone offre de meilleures performances avec un lag minimal, tandis que le synchrone garantit zero perte de donnees au prix d’une latence en ecriture.

Configuration du Serveur Primaire

Editez le fichier postgresql.conf du serveur primaire pour activer la replication :

# /etc/postgresql/16/main/postgresql.conf

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

Le parametre wal_level = replica assure que le WAL contient suffisamment d’informations pour la replication. max_wal_senders controle le nombre de connexions de streaming simultanees autorisees. wal_keep_size empeche le primaire de recycler les segments WAL avant que le standby ne les ait consommes.

Ensuite, configurez pg_hba.conf pour permettre au standby de se connecter pour la replication :

# /etc/postgresql/16/main/pg_hba.conf

# TYPE  DATABASE        USER            ADDRESS              METHOD
host    replication     replicator      10.0.1.20/32         scram-sha-256

Remplacez 10.0.1.20 par l’adresse IP de votre serveur standby.

Creez un utilisateur dedie a la replication :

CREATE ROLE replicator WITH REPLICATION LOGIN PASSWORD 'your_secure_password';

Redemarrez PostgreSQL pour appliquer les modifications de configuration :

sudo systemctl restart postgresql

Mise en Place du Serveur Standby

Sur le serveur standby, arretez PostgreSQL et nettoyez le repertoire de donnees existant :

sudo systemctl stop postgresql
sudo rm -rf /var/lib/postgresql/16/main/*

Executez pg_basebackup pour cloner les donnees du primaire :

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

Les options ont des roles specifiques :

• -Fp : Format de sortie en clair
• -Xs : Transmet le WAL pendant la sauvegarde pour eviter les lacunes
• -P : Affiche la progression
• -R : Cree automatiquement standby.signal et ecrit primary_conninfo dans postgresql.auto.conf

Si vous preferez une configuration manuelle, creez le signal standby et les parametres de connexion vous-meme :

touch /var/lib/postgresql/16/main/standby.signal

Ajoutez dans postgresql.conf sur le standby :

primary_conninfo = 'host=10.0.1.10 port=5432 user=replicator password=your_secure_password application_name=standby1'
hot_standby = on

Demarrez le serveur standby :

sudo systemctl start postgresql

Archivage WAL

L’archivage WAL fournit un filet de securite supplementaire en copiant les segments WAL termines vers un emplacement d’archivage. Cela permet la recuperation a un point dans le temps (PITR) et protege contre les scenarios ou le standby prend trop de retard.

Configurez l’archivage sur le primaire :

# /etc/postgresql/16/main/postgresql.conf

archive_mode = on
archive_command = 'cp %p /var/lib/postgresql/wal_archive/%f'

Creez le repertoire d’archivage :

sudo mkdir -p /var/lib/postgresql/wal_archive
sudo chown postgres:postgres /var/lib/postgresql/wal_archive

Pour les environnements de production, utilisez rsync ou une solution de stockage cloud au lieu du cp local :

archive_command = 'rsync -a %p backup-server:/wal_archive/%f'

Configurez le standby pour utiliser les WAL archives comme solution de repli avec restore_command :

# Dans le postgresql.conf du standby
restore_command = 'cp /mnt/wal_archive/%f %p'

Cela permet au standby de recuperer les segments WAL depuis l’archive lorsque le streaming prend du retard.

Surveillance du Lag de Replication

Surveiller le lag de replication est essentiel pour s’assurer que votre standby reste a jour. Sur le serveur primaire, interrogez pg_stat_replication :

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

Sur le serveur standby, verifiez le statut du recepteur et calculez le lag :

SELECT
  status,
  received_lsn,
  latest_end_lsn,
  last_msg_send_time,
  last_msg_receipt_time
FROM pg_stat_wal_receiver;

-- Estimation du lag basee sur le temps
SELECT
  now() - pg_last_xact_replay_timestamp() AS replication_delay;

Configurez une alerte lorsque le lag depasse votre seuil :

#!/bin/bash
LAG_BYTES=$(psql -h primary -U monitor -t -c \
  "SELECT pg_wal_lsn_diff(sent_lsn, replay_lsn) FROM pg_stat_replication WHERE application_name='standby1';")

if [ "$LAG_BYTES" -gt 104857600 ]; then
  echo "ALERTE: Le lag de replication depasse 100Mo ($LAG_BYTES octets)" | mail -s "PG Replication Alert" admin@example.com
fi

Strategies de Failover

Lorsque le primaire tombe en panne, vous avez besoin d’un plan de failover clair. PostgreSQL fournit plusieurs methodes de promotion :

Promotion manuelle :

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

# Avec SQL (PostgreSQL 12+)
SELECT pg_promote(wait := true, wait_seconds := 60);

Promotion par fichier trigger :

Configurez promote_trigger_file dans le postgresql.conf du standby :

promote_trigger_file = '/tmp/postgresql.trigger'

Creez le fichier pour declencher la promotion :

touch /tmp/postgresql.trigger

Failover automatise avec Patroni :

Pour les environnements de production, utilisez un outil comme Patroni avec etcd :

# /etc/patroni/patroni.yml (extrait)
scope: pg-cluster
namespace: /db/
name: node1

restapi:
  listen: 0.0.0.0:8008

etcd:
  hosts: 10.0.1.100:2379

bootstrap:
  dcs:
    ttl: 30
    loop_wait: 10
    maximum_lag_on_failover: 1048576

postgresql:
  listen: 0.0.0.0:5432
  data_dir: /var/lib/postgresql/16/main
  authentication:
    replication:
      username: replicator
      password: your_secure_password

Apres le failover, l’ancien primaire doit etre reintroduit comme standby en utilisant pg_rewind :

sudo -u postgres pg_rewind \
  --target-pgdata=/var/lib/postgresql/16/main \
  --source-server="host=10.0.1.20 port=5432 user=postgres"

Comparaison : Streaming vs Replication Logique

Caracteristique	Replication Streaming	Replication Logique
Niveau de replication	Niveau octets (WAL)	Niveau ligne (changements decodes)
Portee du cluster	Cluster entier	Par table ou par base de donnees
Support multi-versions	Meme version majeure requise	Versions majeures differentes supportees
Ecriture sur le standby	Lecture seule (hot standby)	Lecture-ecriture sur le subscriber
Replication DDL	Automatique	Manuelle (non repliquee)
Surcout de performance	Tres faible	Modere (cout de decodage)
Complexite de configuration	Simple	Moderee
Cas d’utilisation	HA et reprise apres sinistre	Replication selective et migrations

Choisissez la replication streaming pour la haute disponibilite complete du cluster. Choisissez la replication logique lorsque vous avez besoin de replication selective de tables, de mises a niveau entre versions ou de subscribers en ecriture.

Scenario Reel

Vous gerez une base de donnees de production e-commerce traitant 5 000 transactions par seconde. L’entreprise exige moins de 30 secondes d’indisponibilite lors de toute defaillance. Voici comment architecturer la solution :

Le serveur primaire (10.0.1.10) gere toutes les ecritures. Le standby synchrone (10.0.1.20) dans le meme centre de donnees assure zero perte de donnees. Le standby asynchrone (10.0.2.10) dans un centre de donnees distant fournit la reprise apres sinistre.

Configurez la replication synchrone sur le primaire :

synchronous_standby_names = 'FIRST 1 (standby_dc1, standby_dc2)'
synchronous_commit = on

Dirigez les requetes de lecture vers les standbys avec PgBouncer ou HAProxy :

# /etc/haproxy/haproxy.cfg (extrait)
listen pg-read
    bind *:5433
    mode tcp
    balance roundrobin
    option pgsql-check user haproxy
    server standby1 10.0.1.20:5432 check
    server standby2 10.0.2.10:5432 check

Cette architecture offre zero perte de donnees pour les defaillances locales et une perte minimale pour les evenements au niveau du centre de donnees, tout en dechargeant le trafic de lecture vers les standbys.

Pieges et Cas Particuliers

• Suppression des segments WAL : Si le standby se deconnecte plus longtemps que ne le permet wal_keep_size, il ne peut pas rattraper son retard via streaming. Configurez toujours l’archivage WAL comme filet de securite ou utilisez les slots de replication : SELECT pg_create_physical_replication_slot('standby1_slot');
• Slots de replication et utilisation disque : Les slots de replication inutilises empechent le nettoyage du WAL, remplissant potentiellement le disque. Surveillez pg_replication_slots et supprimez les slots inactifs
• Transactions volumineuses : Une seule transaction massive (import en masse) genere d’enormes volumes de WAL qui peuvent submerger le standby ou le reseau. Divisez les grandes operations en lots
• Latence du commit synchrone : Activer synchronous_commit = on avec des standbys synchrones ajoute le temps aller-retour reseau a chaque commit. Testez le debit d’ecriture avant d’activer
• Divergence de timeline : Apres avoir promu un standby, l’ancien primaire est sur un timeline different. Vous devez utiliser pg_rewind ou le reconstruire entierement avant de le reintroduire comme standby
• Mot de passe dans primary_conninfo : Stockez les identifiants dans .pgpass plutot qu’en clair dans les fichiers de configuration

Resolution de Problemes

Le standby ne se connecte pas :

# Verifier les logs du standby
sudo tail -f /var/log/postgresql/postgresql-16-main.log

# Verifier les connexions de replication sur le primaire
sudo -u postgres psql -c "SELECT * FROM pg_stat_replication;"

# Tester la connectivite depuis le standby
psql -h 10.0.1.10 -U replicator -d postgres -c "IDENTIFY_SYSTEM;"

Le lag de replication augmente :

-- Verifier si le standby rejoue
SELECT pg_is_in_recovery(), pg_last_wal_replay_lsn();

-- Verifier les requetes longues bloquant le rejeu
SELECT pid, query, state, wait_event FROM pg_stat_activity
WHERE state != 'idle' AND backend_type = 'client backend';

L’archive WAL se remplit :

# Verifier le statut de l'archivage
sudo -u postgres psql -c "SELECT * FROM pg_stat_archiver;"

# Nettoyer manuellement les anciens fichiers (garder au moins 1 jour)
find /var/lib/postgresql/wal_archive -mtime +1 -delete

Resume

• La replication streaming PostgreSQL cree des copies binaires en temps reel de l’ensemble de votre cluster de base de donnees
• Configurez wal_level = replica, max_wal_senders et un utilisateur de replication sur le primaire
• Utilisez pg_basebackup avec l’option -R pour initialiser les standbys avec configuration automatique
• L’archivage WAL fournit un filet de securite pour les standbys qui prennent du retard sur le streaming
• Surveillez le lag de replication via pg_stat_replication et pg_stat_wal_receiver
• Utilisez Patroni ou des outils similaires pour le failover automatise en production
• Les slots de replication empechent la suppression du WAL mais necessitent une surveillance pour eviter l’epuisement du disque
• Testez toujours votre procedure de failover avant d’en avoir besoin en urgence

Articles Connexes

• Understanding PostgreSQL indexing strategies
• Database backup and recovery best practices
• High availability architectures for databases
• Linux server hardening for database hosts
• Monitoring infrastructure with Prometheus and Grafana