Réplication PostgreSQL : streaming, hot standby et failover

La réplication streaming de PostgreSQL copie en continu les journaux de transactions (WAL) du primary vers un ou plusieurs standby. Le standby peut servir de hot standby (lectures en temps réel) et prendre le relais en cas de panne via un failover (promote). Ce guide monte un cluster à deux nœuds de bout en bout, de la configuration du primary jusqu’au promote du standby, avec toutes les commandes testées sur PostgreSQL 18.

Ce que vous allez apprendre

• Configurer un primary pour la réplication streaming (wal_level, rôle dédié, slot, pg_hba)
• Initialiser un standby avec pg_basebackup -R et comprendre standby.signal
• Surveiller la réplication : pg_stat_replication, pg_stat_wal_receiver, pg_wal_lsn_diff()
• Utiliser le hot standby pour les lectures et comprendre ses limites
• Exécuter un failover manuel avec pg_promote() et vérifier le changement de timeline
• Distinguer réplication physique et logique pour choisir la bonne approche

Dans quel contexte ?

La réplication streaming répond à des besoins concrets d’administration :

• Vous administrez un PostgreSQL en production et vous voulez éliminer le point de défaillance unique (SPOF)
• Vous avez besoin d’un standby en lecture pour décharger le primary des requêtes de reporting
• Vous préparez une maintenance planifiée (montée de version, migration) avec basculement contrôlé
• Vous devez garantir une reprise rapide (RTO court) en cas de crash du serveur principal
• Vous préparez la certification LFCS ou RHCSA et devez maîtriser la réplication PostgreSQL

Ce guide ne couvre pas…

• La sauvegarde et le PITR (Point-In-Time Recovery) → voir le guide sauvegarde-restauration
• L’orchestration automatique du failover (Patroni, repmgr, pg_auto_failover) → mentionnée en fin de guide
• La réplication logique détaillée → aperçu en fin de guide, guide dédié à venir

pg_basebackup apparaît ici comme outil d’initialisation du standby, pas comme outil de sauvegarde.

Streaming seul = simplification de lab

Ce guide configure le standby en streaming pur (sans archivage WAL continu) pour simplifier l’apprentissage. En production, la documentation PostgreSQL recommande de combiner restore_command (archivage WAL) et streaming : le standby boucle automatiquement entre archive → pg_wal → streaming. L’archive doit rester accessible même quand le primary est en panne. Sans archivage, le primary peut recycler des WAL avant que le standby ne les ait reçus.

Voir le guide sauvegarde-restauration pour configurer l’archivage WAL continu.

Prérequis

• Deux serveurs avec PostgreSQL installé (même version majeure)
• Un réseau permettant la connexion TCP entre les deux nœuds (port 5432)
• Les bases du guide d’installation et de configuration
• Accès superuser (postgres) ou un rôle avec REPLICATION

Lab utilisé dans ce guide :

Rôle	Hostname	IP	OS	PostgreSQL
Primary	pg-primary	192.168.122.61	Debian 12	18.3
Standby	pg-standby	192.168.122.62	Debian 12	18.3

Glissez pour voir

La réplication n’est pas une sauvegarde

Réplication ≠ sauvegarde

La réplication propage tout du primary vers le standby : les INSERT mais aussi les DELETE, les DROP TABLE et les erreurs humaines. Un DROP DATABASE sur le primary est immédiatement répliqué.

Pour se protéger des erreurs humaines et de la corruption, il faut des sauvegardes avec archivage WAL et PITR. Voir le guide sauvegarde-restauration.

Réplication physique vs logique

PostgreSQL propose deux types de réplication. Ce guide se concentre sur la réplication physique (streaming), la plus courante pour la haute disponibilité.

Physique : copie bit à bit des WAL

La réplication physique (streaming) copie les journaux WAL octet par octet. Le standby est une copie exacte du primary : même structure, mêmes bases, mêmes données, même état.

Caractéristiques :

• Copie tout le cluster (toutes les bases, tous les rôles)
• Le standby est en lecture seule (hot standby)
• Très faible latence (quasi temps réel)
• Le standby peut servir de base pour un failover

Logique : réplication sélective par publication/souscription

La réplication logique transmet des changements au niveau des lignes (INSERT, UPDATE, DELETE) pour des tables choisies. Elle utilise le mécanisme PUBLICATION / SUBSCRIPTION introduit en PostgreSQL 10.

Caractéristiques :

• Réplique des tables spécifiques, pas le cluster entier
• Le souscripteur est un serveur indépendant (lecture-écriture)
• Permet la réplication entre versions différentes de PostgreSQL
• Utile pour la migration, l’agrégation de données ou le partage sélectif

Quand utiliser quoi

Critère	Physique (streaming)	Logique
Objectif principal	Haute disponibilité, failover	Migration, réplication sélective
Granularité	Cluster entier	Tables choisies
Standby en lecture	Oui (hot standby)	Le souscripteur est en lecture-écriture
Failover	Oui (pg_promote)	Non conçu pour
Versions différentes	Non (même version majeure)	Oui
Latence	Très faible	Faible à modérée
Complexité	Simple	Plus complexe (conflits possibles)

Glissez pour voir

Configurer le primary

La configuration du primary se fait en quatre étapes : paramètres de réplication, rôle dédié, slot de réplication et autorisation réseau.

Paramètres de réplication

Les paramètres essentiels se trouvent dans postgresql.conf. Sur Debian, on utilise ALTER SYSTEM pour les modifier proprement :

-- Activer le niveau WAL nécessaire à la réplication
ALTER SYSTEM SET wal_level = 'replica';

-- Nombre maximum de connexions de réplication simultanées
ALTER SYSTEM SET max_wal_senders = 5;

-- Nombre maximum de slots de réplication
ALTER SYSTEM SET max_replication_slots = 10;

-- Quantité de WAL conservée pour les standby en retard
ALTER SYSTEM SET wal_keep_size = '256MB';

-- Écouter sur toutes les interfaces (pas uniquement localhost)
ALTER SYSTEM SET listen_addresses = '*';

Redémarrage nécessaire

Les paramètres wal_level, max_wal_senders, max_replication_slots et listen_addresses nécessitent un redémarrage de PostgreSQL pour prendre effet :

sudo systemctl restart postgresql

Après redémarrage, vérifiez que les paramètres sont bien appliqués :

SELECT name, setting
FROM pg_settings
WHERE name IN (
    'wal_level', 'max_wal_senders', 'max_replication_slots',
    'wal_keep_size', 'listen_addresses'
);

          name          | setting
------------------------+---------
 listen_addresses       | *
 max_replication_slots  | 10
 max_wal_senders        | 5
 wal_keep_size          | 256
 wal_level              | replica

Créer un rôle REPLICATION dédié

Ne pas utiliser le superuser pour la réplication

Utiliser le rôle postgres (superuser) pour la réplication est une mauvaise pratique. Créez un rôle dédié avec uniquement les privilèges LOGIN et REPLICATION :

CREATE ROLE replicator WITH LOGIN REPLICATION PASSWORD 'motdepasse_fort';

Ce rôle ne peut ni créer de bases, ni lire vos données, ni modifier quoi que ce soit. Il sert exclusivement à la connexion de réplication.

Créer un slot de réplication physique

Un slot de réplication garantit que le primary conserve les WAL nécessaires au standby, même si celui-ci est temporairement déconnecté. Sans slot, un standby déconnecté trop longtemps peut perdre la synchronisation si les WAL ont été recyclés.

SELECT pg_create_physical_replication_slot('standby_slot');

Vérification :

SELECT slot_name, slot_type, active FROM pg_replication_slots;

  slot_name   | slot_type | active
--------------+-----------+--------
 standby_slot | physical  | f

Le slot est inactif (active = f) tant qu’aucun standby ne l’utilise.

Attention aux slots orphelins

Un slot dont le standby ne se reconnecte jamais empêche le recyclage des WAL. L’espace disque du primary va croître indéfiniment. Surveillez les slots inactifs avec pg_replication_slots et supprimez les orphelins :

SELECT pg_drop_replication_slot('standby_slot');

Garde-fous sur les slots en production

Par défaut, un slot conserve les WAL sans limite. Deux paramètres protègent le primary contre l’accumulation incontrôlée :

• max_slot_wal_keep_size — plafonne le volume de WAL retenu par slot (défaut : -1 = illimité). Exemple : '10GB'
• idle_replication_slot_timeout — invalide automatiquement un slot inactif au-delà d’un délai (défaut : 0 = désactivé). Exemple : '60min'

ALTER SYSTEM SET max_slot_wal_keep_size = '10GB';
ALTER SYSTEM SET idle_replication_slot_timeout = '60min';
SELECT pg_reload_conf();

Sans ces garde-fous, un slot orphelin consomme le disque sans limite.

Autoriser la réplication dans pg_hba.conf

Le fichier pg_hba.conf contrôle les connexions autorisées. Ajoutez une ligne pour autoriser le rôle replicator depuis l’IP du standby :

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

Rechargez la configuration :

sudo systemctl reload postgresql

Vérification — les règles actives sont visibles dans pg_hba_file_rules :

SELECT line_number, type, database, user_name, address, netmask, auth_method
FROM pg_hba_file_rules
WHERE 'replication' = ANY(database);

 line_number | type |   database    |  user_name   |    address     |    netmask    |  auth_method
-------------+------+---------------+--------------+----------------+---------------+---------------
         117 | host | {replication} | {replicator} | 192.168.122.62 | 255.255.255.255 | scram-sha-256

Initialiser le standby avec pg_basebackup

Le standby est initialisé en copiant l’intégralité du cluster du primary avec pg_basebackup. Cette commande s’exécute sur le standby.

Préparer le standby

Avant de lancer pg_basebackup, le répertoire de données du standby doit être vide. Si PostgreSQL a été installé et initialisé automatiquement, videz-le :

# Arrêter PostgreSQL sur le standby
sudo systemctl stop postgresql

# Vider le répertoire de données
sudo -u postgres find /var/lib/postgresql/18/main -mindepth 1 -delete

Copier le cluster avec pg_basebackup -R

L’option -R est cruciale : elle génère automatiquement standby.signal et écrit le primary_conninfo dans postgresql.auto.conf.

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

Option	Rôle
-h	Adresse du primary
-U	Rôle de réplication
-D	Répertoire de données cible
-Fp	Format plain (fichiers)
-Xs	Streaming des WAL pendant la copie
-P	Afficher la progression
-R	Générer standby.signal + primary_conninfo

Glissez pour voir

walsenders et pg_basebackup -Xs

Avec l’option -Xs, pg_basebackup consomme deux walsenders en parallèle : un pour la copie de base et un pour le streaming des WAL pendant la copie. Assurez-vous que max_wal_senders est dimensionné en conséquence (les standby connectés + 2 pour le backup).

Ce que -R génère

Deux éléments sont créés automatiquement par l’option -R :

1. Le fichier standby.signal — un fichier vide qui indique à PostgreSQL de démarrer en mode standby :

ls -la /var/lib/postgresql/18/main/standby.signal

-rw------- 1 postgres postgres 0 Apr 13 11:44 standby.signal

2. Le paramètre primary_conninfo dans postgresql.auto.conf — la chaîne de connexion vers le primary :

grep primary_conninfo /var/lib/postgresql/18/main/postgresql.auto.conf

primary_conninfo = 'user=replicator password=motdepasse_fort channel_binding=prefer
 host=192.168.122.61 port=5432 sslmode=prefer sslnegotiation=direct
 sslcompression=0 sslcertmode=allow sslsni=1 ssl_min_protocol_version=TLSv1.2
 gssencmode=prefer krbsrvname=postgres gssdelegation=0
 target_session_attrs=any load_balance_hosts=disable'

Mot de passe en clair dans postgresql.auto.conf

L’option -R de pg_basebackup écrit le mot de passe en clair dans postgresql.auto.conf. En production, préférez un fichier ~/.pgpass sur le standby (propriétaire postgres, permissions 0600) avec replication comme nom de base de données :

192.168.122.61:5432:replication:replicator:motdepasse_fort

Puis supprimez password=… de primary_conninfo dans postgresql.auto.conf.

Configurer le slot sur le standby

Si vous avez créé un slot de réplication sur le primary, indiquez-le au standby pour qu’il l’utilise. Ajoutez dans postgresql.auto.conf ou via ALTER SYSTEM avant le premier démarrage :

echo "primary_slot_name = 'standby_slot'" | \
    sudo -u postgres tee -a /var/lib/postgresql/18/main/postgresql.auto.conf

Depuis PostgreSQL 12 : plus de recovery.conf

Avant PostgreSQL 12, la configuration du standby passait par un fichier recovery.conf séparé. Depuis PostgreSQL 12, ce fichier n’existe plus. Tout passe par les paramètres normaux (primary_conninfo, primary_slot_name) et le fichier standby.signal. Si vous suivez d’anciens tutoriels qui mentionnent recovery.conf, ils ne s’appliquent plus.

Démarrer le standby

sudo systemctl start postgresql

Vérifiez immédiatement que le standby est bien en mode recovery :

SELECT pg_is_in_recovery();

 pg_is_in_recovery
-------------------
 t

t (true) confirme que PostgreSQL fonctionne en mode standby.

Vérifier la réplication

Une fois le standby démarré, il faut vérifier que la réplication streaming est active et fonctionnelle. Les deux vues clés sont pg_stat_replication (côté primary) et pg_stat_wal_receiver (côté standby).

Sur le primary : pg_stat_replication

Cette vue montre les connexions de réplication actives :

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

 pid  |   state   | sync_state | sent_lsn  | write_lsn | flush_lsn | replay_lsn
------+-----------+------------+-----------+-----------+-----------+------------
 4536 | streaming | async      | 0/30067F0 | 0/30067F0 | 0/30067F0 | 0/30067F0

Colonne	Signification
state	streaming = réplication active en temps réel
sync_state	async = réplication asynchrone (par défaut)
sent_lsn	Dernière position WAL envoyée au standby
write_lsn	Dernière position écrite en mémoire par le standby
flush_lsn	Dernière position écrite sur disque par le standby
replay_lsn	Dernière position rejouée par le standby

Glissez pour voir

Quand les quatre colonnes LSN sont identiques, le standby est parfaitement synchronisé.

Sur le standby : pg_stat_wal_receiver

Cette vue montre la connexion de réplication active côté standby :

SELECT status, sender_host, sender_port,
       slot_name, flushed_lsn
FROM pg_stat_wal_receiver;

  status   |  sender_host   | sender_port | slot_name | flushed_lsn
-----------+----------------+-------------+-----------+-------------
 streaming | 192.168.122.61 |        5432 |           | 0/30067F0

Le status = streaming confirme que le standby reçoit les WAL en continu.

Calculer le lag avec pg_wal_lsn_diff()

Pour mesurer l’écart entre le primary et le standby en octets :

-- Sur le primary
SELECT
    client_addr,
    pg_wal_lsn_diff(sent_lsn, replay_lsn) AS replay_lag_bytes
FROM pg_stat_replication;

  client_addr    | replay_lag_bytes
-----------------+------------------
 192.168.122.62  |                0

Un lag de 0 octets signifie que le standby a rejoué tout ce que le primary lui a envoyé. En production sous charge, un leger lag (quelques Ko) est normal.

Surveillance continue du lag

Le lag de réplication est un indicateur critique. Intégrez-le dans votre monitoring (Prometheus, Zabbix, etc.). PostgreSQL expose aussi les colonnes write_lag, flush_lag et replay_lag (en intervalle de temps) dans pg_stat_replication pour une granularité plus fine.

Tester la réplication en temps réel

Le test le plus parlant : écrire sur le primary, lire sur le standby.

Sur le primary — insérez une ligne :

INSERT INTO app.servers (hostname, ip_address, os, role, env, cpu_cores, ram_gb)
VALUES ('srv-repl-test', '10.0.0.42', 'Debian 12', 'test', 'dev', 2, 4);

Sur le standby — vérifiez immédiatement :

SELECT hostname, ip_address FROM app.servers
WHERE hostname = 'srv-repl-test';

   hostname    | ip_address
---------------+------------
 srv-repl-test | 10.0.0.42

La ligne est visible quasi instantanément. Le standby reçoit et rejoue les WAL en continu.

Hot standby : lectures sur le réplica

Le hot standby permet d’exécuter des requêtes en lecture sur un standby pendant qu’il continue de recevoir et rejouer les WAL. Le paramètre hot_standby contrôle cette fonctionnalité — il est à on par défaut depuis PostgreSQL 10 et n’a d’effet que sur un serveur en recovery/standby. Aucune configuration n’est nécessaire sur le primary.

Ce qu’on peut faire

Le hot standby accepte les opérations en lecture seule :

-- Requêtes SELECT
SELECT * FROM app.servers WHERE env = 'prod';

-- Vues statistiques
SELECT * FROM pg_stat_user_tables;

-- Plans d'exécution
EXPLAIN ANALYZE SELECT * FROM app.servers WHERE hostname LIKE 'srv%';

C’est idéal pour :

• Le reporting et les requêtes analytiques longues
• Les lectures distribuées (connecter les applications en lecture au standby)
• Le debug et l’analyse de performances sans impacter le primary

Ce qu’on ne peut pas faire

Toute opération en écriture est refusée :

-- INSERT → erreur
INSERT INTO app.servers (hostname) VALUES ('test');

ERROR:  cannot execute INSERT in a read-only transaction

-- DDL → erreur
CREATE TABLE app.test (id serial);

ERROR:  cannot execute CREATE TABLE in a read-only transaction

C’est un comportement normal et attendu : le standby est une copie en lecture seule du primary.

Conflits hot standby

Parfois, le rejeu des WAL sur le standby entre en conflit avec des requêtes de lecture longues. Par exemple, si le primary fait un VACUUM qui supprime des lignes mortes, le standby doit rejouer cette opération — mais une requête SELECT est peut-être en train de lire ces lignes.

Le paramètre max_standby_streaming_delay contrôle combien de temps le standby attend avant d’annuler la requête en conflit :

SHOW max_standby_streaming_delay;

 max_standby_streaming_delay
-----------------------------
 30s

Par défaut, le standby attend 30 secondes avant d’annuler la requête conflictuelles. Augmentez cette valeur si vos requêtes de reporting sont longues :

ALTER SYSTEM SET max_standby_streaming_delay = '120s';
SELECT pg_reload_conf();

Compromis performance vs latence

Augmenter max_standby_streaming_delay permet aux requêtes longues de terminer, mais retarde le rejeu des WAL. Le standby prend du retard pendant la durée du conflit. C’est un compromis entre la fraîcheur des données et la stabilité des requêtes de lecture.

Failover manuel

Le failover consiste à promouvoir le standby en nouveau primary quand l’ancien primary est indisponible (panne, maintenance, migration).

Le failover est irréversible

Une fois promu, le standby devient un serveur autonome sur une nouvelle timeline. L’ancien primary ne peut plus le suivre. La seule façon de rétablir la réplication est de reconstruire l’ancien primary comme nouveau standby (avec pg_basebackup ou pg_rewind).

Vérifier l’état avant le promote

Avant de promouvoir, assurez-vous que le standby est bien synchronisé :

-- Sur le standby : vérifier qu'on est bien en recovery
SELECT pg_is_in_recovery();
-- → t

-- Vérifier la timeline actuelle
SELECT timeline_id FROM pg_control_checkpoint();
-- → 1

-- Vérifier que standby.signal existe
-- ls -la /var/lib/postgresql/18/main/standby.signal

Promouvoir avec pg_promote()

La promotion se fait en une seule commande SQL, exécutée sur le standby :

SELECT pg_promote();

 pg_promote
------------
 t

Alternatives à pg_promote()

Vous pouvez aussi promouvoir via la ligne de commande :

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

Ou en créant le fichier trigger (si promote_trigger_file est configuré). pg_promote() (SQL) est la méthode la plus simple depuis PostgreSQL 12.

Vérifier le promote

Après le promote, plusieurs changements sont immédiatement observables sur l’ex-standby :

1. Plus en recovery :

SELECT pg_is_in_recovery();

 pg_is_in_recovery
-------------------
 f

f (false) — le serveur n’est plus un standby, il accepte les écritures.

2. Nouvelle timeline :

SELECT timeline_id FROM pg_control_checkpoint();

 timeline_id
-------------
           2

La timeline passe de 1 à 2. Chaque promote crée une nouvelle timeline pour éviter toute confusion avec les WAL de l’ancienne.

3. standby.signal supprimé :

ls /var/lib/postgresql/18/main/standby.signal

ls: cannot access '/var/lib/postgresql/18/main/standby.signal': No such file or directory

PostgreSQL a automatiquement supprimé le fichier.

4. pg_stat_wal_receiver vide :

SELECT * FROM pg_stat_wal_receiver;

(0 rows)

Le standby ne reçoit plus de WAL — il est maintenant un primary autonome.

5. Les écritures fonctionnent :

INSERT INTO app.servers (hostname, ip_address, os, role, env, cpu_cores, ram_gb)
VALUES ('srv-after-promote', '10.0.0.99', 'Debian 12', 'test', 'dev', 2, 4);

INSERT 0 1

L’INSERT passe, le DDL aussi. Le serveur est pleinement opérationnel en lecture-écriture.

Que faire de l’ancien primary ?

Après le promote, l’ancien primary est toujours sur la timeline 1 et ne sait pas que le standby a été promu. Il y a trois options :

Option	Quand l’utiliser
pg_basebackup complet	L’ancien primary a beaucoup divergé ou est corrompu
pg_rewind	L’ancien primary a peu divergé (recommandé)
Reconstruire entièrement	Changement de version ou de configuration

Glissez pour voir

pg_rewind est l’option la plus efficace quand l’ancien primary n’a accepté que peu d’écritures après la déconnexion du standby :

# Arrêter l'ancien primary
sudo systemctl stop postgresql

# Resynchroniser avec le nouveau primary
sudo -u postgres pg_rewind \
    --target-pgdata=/var/lib/postgresql/18/main \
    --source-server="host=192.168.122.62 user=postgres dbname=postgres"

Prérequis pg_rewind

pg_rewind exige que le serveur cible ait été initialisé avec les data checksums activés (c’est le défaut depuis PostgreSQL 18) ou que wal_log_hints = on soit configuré. Le paramètre full_page_writes doit aussi être à on (c’est le défaut). Si aucune de ces conditions n’est remplie, pg_rewind échouera.

Ensuite, créez un standby.signal et configurez primary_conninfo pour que l’ancien primary devienne le nouveau standby et pointe vers le nouveau primary.

Réplication synchrone

Encadré avancé

Par défaut, la réplication streaming est asynchrone : le primary n’attend pas la confirmation du standby avant de valider un COMMIT. C’est le mode le plus performant mais il peut perdre quelques transactions en cas de crash du primary.

La réplication synchrone garantit qu’un COMMIT n’est validé côté client que lorsque le standby a confirmé avoir écrit (ou rejoué) les WAL correspondants. C’est le mode le plus sûr pour le RPO (zéro perte de données).

Activer la réplication synchrone

-- Sur le primary
-- synchronous_commit = on est déjà la valeur par défaut
-- C'est synchronous_standby_names qui active le mode synchrone
ALTER SYSTEM SET synchronous_standby_names = 'FIRST 1 (standby1)';
SELECT pg_reload_conf();

Paramètre	Rôle
synchronous_commit = on	Déjà la valeur par défaut — le COMMIT attend la confirmation du standby
synchronous_standby_names	L’activateur réel : quels standby sont synchrones (par application_name)

Glissez pour voir

D'où vient le nom du standby ?

Le nom utilisé dans synchronous_standby_names est le application_name configuré dans le primary_conninfo du standby. Par défaut, il vaut cluster_name (s’il est défini) ou walreceiver. En production, nommez vos standby explicitement en ajoutant application_name=standby1 dans primary_conninfo — évitez le joker * qui rendrait synchrone tout standby qui se connecte, y compris un serveur inattendu.

Après configuration, pg_stat_replication affiche sync au lieu de async :

SELECT application_name, sync_state FROM pg_stat_replication;

 application_name | sync_state
------------------+------------
 walreceiver      | sync

Compromis de la réplication synchrone

Avantage	Inconvénient
RPO = 0 (zéro perte de données)	Latence accrue sur chaque COMMIT
Garantie de cohérence	Si le standby tombe, le primary bloque les COMMIT
Confiance pour les données critiques	Nécessite un réseau fiable et rapide

Glissez pour voir

Standby down = primary bloqué

En mode synchrone, si le standby devient indisponible, le primary bloque sur chaque COMMIT en attendant la confirmation. C’est pourquoi la réplication synchrone est souvent utilisée avec au moins deux standby et la clause FIRST 1 (standby1, standby2) pour basculer automatiquement.

Réplication logique : aperçu

La réplication logique est un sujet à part entière. Voici un aperçu rapide du mécanisme pour comprendre quand l’envisager.

Principe

Le primary crée une publication sur des tables choisies. Un autre serveur PostgreSQL crée une souscription pour recevoir les changements :

-- Sur le primary (publisher)
CREATE PUBLICATION ma_publication FOR TABLE app.servers, app.deployments;

-- Sur le souscripteur (un serveur indépendant)
CREATE SUBSCRIPTION ma_souscription
    CONNECTION 'host=192.168.122.61 dbname=labdb user=replicator password=motdepasse_fort'
    PUBLICATION ma_publication;

Quand utiliser la réplication logique

• Migration de version : répliquer d’un PostgreSQL 16 vers un 18
• Réplication sélective : ne transmettre que certaines tables
• Agrégation : collecter des données de plusieurs sources
• Pas pour le failover : le souscripteur n’est pas un standby (pas de timeline, pas de promote)

Outils d’orchestration automatique

Au-delà du failover manuel

Le failover manuel fonctionne, mais il n’est pas adapté à la production où le temps de réaction doit être de quelques secondes. Trois outils principaux automatisent le failover PostgreSQL :

Outil	Description
Patroni	Le plus utilisé. Utilise un DCS (etcd, Consul, ZooKeeper) pour le consensus. Gère promote, fencing et reconfiguration automatique.
repmgr	Extension PostgreSQL mature. Détection de panne et promote automatique avec repmgrd.
pg_auto_failover	Développé par Citus/Microsoft. Architecture simple avec un nœud “monitor” qui coordonne le failover.

Glissez pour voir

Ces outils gèrent aussi la reconstruction automatique de l’ancien primary comme nouveau standby après un failover.

Dépannage

Le standby ne se connecte pas

1. Vérifier la connectivité réseau :

   # Depuis le standby
   pg_isready -h 192.168.122.61 -p 5432

2. Vérifier pg_hba.conf sur le primary — le rôle de réplication doit être autorisé depuis l’IP du standby :

   SELECT user_name, address, auth_method
   FROM pg_hba_file_rules
   WHERE 'replication' = ANY(database);

3. Vérifier le mot de passe — testez la connexion manuellement :

   psql "host=192.168.122.61 user=replicator dbname=postgres replication=database" -c "IDENTIFY_SYSTEM;"

4. Vérifier les logs du standby :

   sudo tail -20 /var/log/postgresql/postgresql-18-main.log

Le lag augmente en continu

Cause possible	Solution
Charge d’écriture très élevée sur le primary	Augmenter wal_keep_size, vérifier les IOPS disque du standby
Requêtes longues sur le standby (conflits)	Augmenter max_standby_streaming_delay ou déporter les requêtes
Réseau saturé	Vérifier la bande passante entre primary et standby
Slot de réplication non utilisé	Vérifier pg_replication_slots — supprimer les slots orphelins

Glissez pour voir

Le promote échoue

• Vérifiez que pg_is_in_recovery() retourne t avant le promote
• Vérifiez que l’utilisateur a les droits (superuser ou rôle avec pg_promote)
• Consultez les logs : sudo tail -30 /var/log/postgresql/postgresql-18-main.log

Slot orphelin qui remplit le disque

-- Identifier les slots inactifs
SELECT slot_name, active, pg_size_pretty(
    pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)
) AS retained_wal
FROM pg_replication_slots
WHERE NOT active;

-- Supprimer un slot orphelin
SELECT pg_drop_replication_slot('standby_slot');

À retenir

1. La réplication streaming copie les WAL en continu du primary vers le standby — en production, combinez-la avec l’archivage WAL (restore_command) pour couvrir les déconnexions prolongées
2. La réplication n’est pas une sauvegarde : un DROP TABLE est immédiatement répliqué
3. pg_basebackup -R initialise le standby et génère automatiquement standby.signal + primary_conninfo
4. Depuis PostgreSQL 12, recovery.conf n’existe plus — tout passe par les paramètres normaux
5. Les slots de réplication empêchent le recyclage prématuré des WAL — configurez max_slot_wal_keep_size et idle_replication_slot_timeout pour éviter un disque plein
6. Le hot standby accepte les lectures mais refuse toutes les écritures (INSERT, UPDATE, DELETE, DDL)
7. pg_promote() transforme le standby en primary autonome sur une nouvelle timeline — c’est irréversible
8. La réplication synchrone s’active via synchronous_standby_names — elle garantit zéro perte de données mais bloque si le standby tombe
9. En production, utilisez un outil d’orchestration automatique (Patroni, repmgr) pour le failover

Prochaines étapes

Sauvegarde et restauration pg_dump, pg_basebackup, archivage WAL et PITR — la vraie protection contre les erreurs humaines

Monitoring et maintenance Superviser l'activité, les index, le bloat et automatiser le VACUUM

Sécurisation Rôles, pg_hba.conf, SSL/TLS, chiffrement et audit

Configuration Paramètres mémoire, connexions, WAL et tuning

×

📖 Voir la documentation →