Linux Pratique - Mise en réplication d’une instance PostgreSQL

Reviers, 2 mai 2025

Notre serveur est bien installé. La sauvegarde permet de le restaurer si un problème survenait, mais cela prendra un peu de temps. En répliquant son contenu en temps réel sur un autre serveur, nous pouvons gagner beaucoup de temps pour le rétablissement du service. Nous allons donc voir dans cet article comment mettre en place la réplication.

Depuis l’ajout de la réplication dans PostgreSQL, celle-ci a beaucoup évolué. Deux types de réplication existent, satisfaisant des besoins différents. De nombreux paramètres permettent de personnaliser entièrement la réplication souhaitée. C’est donc un sujet assez complexe si nous souhaitons le maîtriser alors qu’il est paradoxalement assez simple à mettre en place de manière basique.

Types de réplication

Il existe en effet deux types de réplication dans PostgreSQL.

Le premier implanté est la réplication physique. Elle est simple à mettre en place, elle réplique tout sans distinction, et est donc parfaite pour créer un serveur dupliqué sur lequel basculer si le premier serveur tombe. Elle a cependant plusieurs inconvénients. Notamment, il est essentiel que les architectures matérielle et logicielle soient identiques sur chaque serveur du cluster de réplication. Cela veut dire même architecture de processeurs, même système d’exploitation, même librairie C, même version majeure de PostgreSQL, mêmes options de compilation de PostgreSQL.

Le fait que la réplication physique réplique la globalité de l’instance est un avantage dans le cas d’un serveur de PRA, mais cela peut être un gros inconvénient dans d’autres cas. Par exemple, si nous souhaitons ne répliquer que certaines tables pour faire des calculs sur ces tables sur un autre serveur, il serait préférable de pouvoir indiquer de ne répliquer que ces tables pour éviter de perdre du temps (et de l’espace disque et mémoire, et de la bande passante réseau) à répliquer le reste. Ce n’est pas atteignable avec la réplication physique, et c’est donc la raison d’être de la réplication logique. De plus, ce type de réplication permet de répliquer sur des architectures différentes, sur des versions différentes, et le tout de façon partielle si besoin. Il y a donc plein d’intérêts à cet autre type de réplication. Cependant, elle est plus complexe à mettre en place, et à maintenir.

Nous allons donc voir ici la mise en place des deux types de réplication, en commençant par la réplication physique.

Réplication physique

Installation d’un secondaire

Nous avons déjà un serveur en version 16, nous allons l’utiliser comme serveur primaire. Le serveur secondaire est installé sur une deuxième VM. Celle-ci doit être installée tout comme la première VM. Nous vous renvoyons à l’article sur l’installation de PostgreSQL, premier article de cette série. Cependant, il faut installer une version 16 (il n’est pas possible de faire de la réplication physique entre des serveurs de version majeure différente). De plus, il ne faudra pas créer le répertoire de données. En effet, nous allons copier le répertoire du primaire.

Commençons par configurer le secondaire. Nous avons besoin d’un rôle qui s’occupe de la connexion de réplication. Créons-le en exécutant la commande suivante en tant qu’utilisateur système postgres :

createuser --replication --pwprompt replicator

Cette commande demande la saisie d’un mot de passe, en double pour confirmation.

Ceci fait, il faut autoriser auprès de PostgreSQL la connexion provenant du serveur secondaire. Nous en profitons pour autoriser la même chose du serveur actuellement primaire pour qu’en cas de bascule, il n’y ait rien à configurer de plus. Pour ce faire, nous devons ajouter les deux lignes suivantes dans le fichier /srv/data/pg_hba.conf :

host    replication     replicator      192.168.122.9/32        scram-sha-256
host    replication     replicator      192.168.122.150/32      scram-sha-256

Pour information, l’adresse IP de mon serveur primaire est le 192.168.122.9, alors que celle du serveur secondaire est le 192.168.122.150. Les adresses IP sont à remplacer par les adresses IP de vos serveurs.

Il ne reste plus qu’à demander à PostgreSQL de relire sa configuration. Cela se fait avec la commande suivante en tant qu’utilisateur système root :

systemctl reload postgresql-16

Le système doit aussi autoriser les connexions externes sur le port du service PostgreSQL. Donc si vous avez un pare-feu actif, il faut le configurer pour laisser passer ce type de connexion. C’est le cas pour moi sur ma VM dotée du système Rocky Linux 9. Voici la commande pour autoriser les flux nous intéressant :

firewall-cmd --add-service=postgresql --permanent

Il est à noter que cette commande doit aussi être exécutée sur le secondaire pour que, en cas de bascule, nous n’ayons pas besoin de le faire.

Le peu qui reste sera à faire sur le serveur secondaire.

Commençons par copier les données actuelles du primaire. Pour cela, nous utilisons l’outil pg_basebackup avec un ensemble d’options que nous expliquerons après :

pg_basebackup --host 192.168.122.9 --username replicator --pgdata /srv/data --waldir /srv/wal --checkpoint fast --create-slot --slot lp2_slot --progress --write-recovery-conf

• --host et --username sont des options de connexion indiquant comment se connecter au serveur primaire dont il faut copier les données, notons d’ailleurs l’utilisation de l’utilisateur de réplication créé précédemment et l’adresse IP du serveur primaire qu’il faudra remplacer par la vôtre ;
• --pgdata : indique dans quel répertoire copier les fichiers de données ;
• --waldir : indique le répertoire des journaux de transaction ;
• --checkpoint : à la valeur fast permet de déclencher immédiatement un checkpoint plutôt que d’attendre le prochain checkpoint planifié ;
• --create-slot : déclenche la création d’un slot de réplication dont le nom est précisé par l’option --slot ;
• --progress : affiche une barre de progression pendant la copie ;
• --write-recovery-conf : crée le fichier de configuration (recovery.conf pour les anciennes versions, d’où le nom de l’option, et postgresql.auto.conf pour les versions plus récentes) pour la mise en place du secondaire.

Une fois son exécution terminée, les fichiers de données copiés du primaire se trouvent bien sur le secondaire, avec quelques fichiers supplémentaires. Parmi ces fichiers, il y a le fichier de configuration postgresql.auto.conf, dont voici le contenu :

# Do not edit this file manually!
# It will be overwritten by the ALTER SYSTEM command.
primary_conninfo = 'user=replicator password=replicator channel_binding=prefer host=192.168.122.9 port=5432 sslmode=prefer 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'
primary_slot_name = 'lp2_slot'

Il précise la chaîne de connexion pour que le secondaire puisse se rattacher au primaire et récupérer ensuite les enregistrements des journaux de transactions.

Le fichier standby.signal est aussi créé.

Donc il ne reste plus qu’à démarrer le service PostgreSQL sur le secondaire :

systemctl start postgresql-16

Une fois PostgreSQL démarré, une interrogation sur le statut du service nous donne ceci :

● postgresql-16.service - PostgreSQL 16 database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql-16.service; disabled; preset: disabled)
    Drop-In: /etc/systemd/system/postgresql-16.service.d
             └─override.conf
     Active: active (running) since Sat 2024-06-01 17:00:41 CEST; 6s ago
       Docs: https://www.postgresql.org/docs/16/static/
    Process: 55501 ExecStartPre=/usr/pgsql-16/bin/postgresql-16-check-db-dir ${PGDATA} (code=exited, status=0/SUCCESS)
   Main PID: 55506 (postgres)
      Tasks: 6 (limit: 11116)
     Memory: 80.0M
        CPU: 171ms
     CGroup: /system.slice/postgresql-16.service
             ├─55506 /usr/pgsql-16/bin/postgres -D /srv/data
             ├─55510 "postgres: logger "
             ├─55511 "postgres: checkpointer "
             ├─55512 "postgres: background writer "
             ├─55513 "postgres: startup recovering 000000010000000000000098"
             └─55514 "postgres: walreceiver streaming 0/98430428"

Jun 01 17:00:41 localhost.localdomain systemd[1]: Starting PostgreSQL 16 database server...
Jun 01 17:00:41 localhost.localdomain postgres[55506]: 2024-06-01 17:00:41 CEST [55506]: user=,db=,app=,client= LOG:  redirecting log output to logging collector process
Jun 01 17:00:41 localhost.localdomain postgres[55506]: 2024-06-01 17:00:41 CEST [55506]: user=,db=,app=,client= HINT:  Future log output will appear in directory "log".
Jun 01 17:00:41 localhost.localdomain systemd[1]: Started PostgreSQL 16 database server.

L’apparition du processus walreceiver indique que le secondaire s’est connecté avec succès au primaire et qu’il reçoit ainsi les informations de réplication pour se mettre à jour.

C’est le bon moment pour tester que des écritures sur le primaire apparaissent pratiquement immédiatement sur le secondaire et que des écritures sur le secondaire se soldent par une erreur.

Basculer

Il faut distinguer deux types de bascules : le failover (le primaire est mort, nous transformons un secondaire en primaire pour que les écritures puissent se faire), et le switchover (nous basculons le rôle du primaire vers un secondaire, et l’ancien primaire devient secondaire… généralement effectué quand on veut stopper l’ancien primaire, par exemple le temps de mettre à jour le système d’exploitation).

PostgreSQL ne fournit qu’un moyen rapide de transformer un secondaire en serveur autonome, disponible en écriture. Cependant, ce moyen n’agit pas sur le primaire qui était en cours. Si celui-ci est toujours disponible, il peut très bien accepter des écritures, et nous aboutissons ainsi malheureusement à une situation de Split Brain.

Comment donc gérer les deux bascules complètes ?

Le cas le plus simple est le failover. Ce type de bascule est réalisé quand le primaire tombe. Il convient dans un premier temps de vérifier que le primaire est bien tombé et arrêté, et que nous ne pourrons pas le remettre en route rapidement. S’il est possible de le remettre en route rapidement, il est préférable de le faire pour éviter la bascule. Si, par contre, la bascule est inévitable, cela se fait en appelant la fonction pg_promote() sur le secondaire qui doit passer en primaire. Le plus simple revient à exécuter la requête suivante sur le secondaire :

SELECT pg_promote();

Une fois exécutée, toutes les connexions en cours sur le secondaire basculent automatiquement de lecture seule à lecture/écriture, et les nouvelles connexions se font directement en lecture/écriture.

Il vous faut maintenant rediriger les connexions qui allaient vers l’ancien primaire pour qu’elles aillent vers le nouveau primaire. Cela se fait généralement via une adresse IP virtuelle qu’il faut reconfigurer.

Attention à l’ancien primaire, si vous réussissez à le redémarrer, il acceptera toujours les écritures et ne se rendra pas compte que l’ancien secondaire est devenu primaire. Il faudra donc, avant tout, le reconstruire en tant que secondaire du nouveau primaire.

Le cas d’un switchover est un peu plus complexe, mais revient essentiellement au même. Un switchover est généralement une opération maîtrisée. En réalisant les opérations méthodiquement, nous pourrons nous éviter de reconstruire l’ancien primaire en secondaire (le problème de cette reconstruction vient surtout de l’envoi des données, qui est d’autant plus long que l’instance est volumineuse).

Voici les différentes étapes à suivre avec s1 serveur primaire à basculer en secondaire et s2 serveur secondaire à basculer en primaire) :

1. arrêter le serveur s1 ;
2. s’assurer que le serveur s2 est au même niveau dans les journaux de transactions que s1 ;
3. promouvoir s2 ;
4. créer le slot de réplication sur s2 ;
5. configurer s1 pour se connecter à s2 (pour cela, il faut modifier les fichiers postgresql.auto.conf et pg_hba.conf, et créer le fichier standby.signal) ;
6. démarrer s1 ;
7. vérifier que tout va bien.

L’étape problématique est l’étape 2. Un bon moyen de savoir où nous nous trouvons dans les journaux d’une instance PostgreSQL est d’utiliser l’outil pg_controldata ainsi :

$ /usr/pgsql-16/bin/pg_controldata -D /srv/data | grep -i "redo location"
Latest checkpoint's REDO location:    0/97000028

Si la valeur du « Latest checkpoint’s REDO location » du secondaire est supérieure ou égale à celle de l’ancien primaire, alors tout va bien. Sinon il faut attendre que le secondaire rattrape son retard, en ayant pris soin de couper les connexions vers le primaire pour éviter de nouvelles écritures.

Basculer automatiquement

Pour l’instant, nous n’avons parlé que d’une bascule manuelle. Il est très fortement conseillé de ne procéder qu’à des bascules manuelles. Cependant, dans certains cas, il est possible d’installer un système de bascule automatique.

Des outils permettent de mettre en place ce système : des outils généralistes comme KeepAlive ou pacemaker (avec l’excellent agent PAF, PostgreSQL Automatic Failover, http://clusterlabs.github.io/PAF/), et des outils spécialisés comme patroni (https://github.com/zalando/patroni) ou pg_auto_failover (https://github.com/hapostgres/pg_auto_failover).

Néanmoins, tous ces outils ont une faille : il est très difficile de s’assurer qu’ils ne feront pas de bascules inutiles. Et une bascule inutile, c’est une interruption de travail pour toutes les personnes connectées, c’est du travail en plus pour les administrateurs, c’est un risque important pour la continuité du service et la protection des données.

En conclusion, si vous le pouvez, évitez de mettre en place de la bascule automatique. Et si vous en avez absolument besoin, vous avez les outils ci-dessus. Mais ne venez pas vous plaindre, vous avez été prévenu :)

Réplication logique

Un peu de vocabulaire

La réplication logique s’appuie sur un concept de publication et de souscription. Nous créons une publication qui précise les tables à répliquer sur un serveur, et nous créons une souscription à cette publication sur un autre serveur.

Une publication gère une ou plusieurs tables, et une ou plusieurs opérations (INSERT, UPDATE, DELETE, TRUNCATE). Il est possible de modifier la liste des tables. Il est même possible de répliquer uniquement certaines colonnes ou certaines lignes d’une table. Les options sont suffisamment nombreuses pour s’y perdre.

La réplication logique ne gère que les données. Les requêtes DDL ne sont pas répliquées, à l’exception de TRUNCATE.

Mise en place des serveurs

Cette fois, nous avons une configuration à réaliser sur le serveur déjà installé. Nous devons changer la valeur du paramètre wal_level pour le configurer à logical. Ceci augmentera la quantité d’informations enregistrées dans les journaux de transaction. Pour que ce changement soit pris en compte, il faut redémarrer le serveur.

Il nous reste à préparer un deuxième serveur qui recevra la réplication. Cet autre serveur est installé sur une deuxième VM. Celle-ci doit être installée tout comme la première VM. Nous vous renvoyons là encore à l’article sur l’installation de PostgreSQL, qu’il faudra suivre dans sa globalité.

Création d’une publication

La création d’une publication se fait avec l’ordre CREATE PUBLICATION. La clause FOR ALL TABLES permet de publier toutes les tables, alors que les clauses FOR TABLES IN SCHEMA et FOR TABLE permettent de publier respectivement toutes les tables du schéma indiqué et une ou plusieurs tables strictement indiquées. Lorsque le nom d’une table est indiqué, il est possible de préciser les colonnes à répliquer. Il est aussi possible dans ce cas d’ajouter une clause WHERE pour ne répliquer que les lignes respectant l’expression du WHERE.

Prenons une nouvelle table factures créée ainsi (sachant que nous avons toujours les tables de la base b1) :

CREATE TABLE factures (id integer PRIMARY KEY, libelle text, montant numeric, paye boolean DEFAULT false);

Nous pouvons créer une publication avec toutes les tables de la base ainsi :

CREATE PUBLICATION pub1 FOR ALL TABLES;

Nous pouvons aussi créer une publication avec uniquement les factures payées :

CREATE PUBLICATION pub2 FOR TABLE factures WHERE paye IS TRUE;

Cette nouvelle publication peut se voir ajouter ou retirer des tables après coup avec un ordre ALTER PUBLICATION.

Création d’une souscription

Pour qu’une publication ait un intérêt, il faut que quelqu’un y souscrive. Nous allons donc créer une souscription sur un autre serveur. Peu importe la version du serveur PostgreSQL, peu importe le système d’exploitation sur lequel il est installé. Par contre, il faut que ce serveur ait accès au serveur de la publication. Il est donc nécessaire de modifier le fichier pg_hba.conf du serveur « publieur » pour accepter les connexions du serveur abonné.

host  b1  replicator  192.168.122.150/32  scram-sha-256

Nous ne précisons pas d’autorisation de connexion dans l’autre sens tout simplement parce qu’une bascule n’a pas de sens dans ce type de réplication.

Contrairement à la modification apportée à ce fichier pour la réplication physique, la colonne du nom de base ne contient pas le mot-clé replication, mais le nom de la base contenant la publication.

Après avoir rechargé la configuration du serveur « publieur », nous pouvons créer la souscription sur l’autre serveur. Pour cela, il faut utiliser l’ordre CREATE SUBSCRIPTION avec les deux clauses suivantes :

• CONNECTION pour préciser la chaîne de connexion vers le serveur « publieur » ;
• PUBLICATION pour indiquer le nom de la publication à laquelle souscrire.

Une clause optionnelle, WITH, permet de configurer encore plus finement la souscription.

Voici un exemple de création d’une souscription pour une publication du serveur 192.168.122.9 :

CREATE SUBSCRIPTION sub1
  CONNECTION 'host=192.168.122.9 dbname=b1 username=replicator'
  PUBLICATION sub1;

À la création d’une souscription, le contenu de chaque table comprise dans la publication est synchronisé sur l’autre serveur. Après synchronisation, l’autre serveur rejoue toutes les modifications qui ont eu lieu entre le début et la fin de la synchronisation. Cela peut donc ajouter de la charge au niveau CPU, mémoire et disque.

Attention, avant de créer la souscription, il faut que les tables existent dans la base de destination. Le plus simple est d’utiliser pg_dump et son option -s pour cela. Cela nous donne pour la publication pub1 qui gère toutes les tables de la base b1 :

pg_dump -h 192.168.122.9 -U replicator -s b1 | psql -h 192.168.122.150 -U replicator b1

Et voilà, l’autre serveur est prêt.

Et la réplication multimaître ?

La réplication native de PostgreSQL ne propose qu’un serveur en écriture et un ou plusieurs serveurs en lecture seule. Cela permet d’avoir un serveur PRA en cas de perte du primaire, cela permet aussi de répartir la charge en lecture. Cependant, la charge en écriture est toujours supportée par un seul serveur.

La réplication logique native permet d’aller un peu plus loin, en utilisant intelligemment des publications sur les deux serveurs, mais cela ne peut concerner que des tables différentes. De plus, le système est complexe à mettre en place et il est donc aisé de faire des erreurs.

Il existe des versions dérivées de PostgreSQL, souvent propriétaires et payantes, proposant du multimaître. Je n’ai pas spécialement d’opinions au niveau technique de ces solutions, ne les ayant pas testées. Par contre, j’ai une opinion sur les logiciels propriétaires et elle est fortement négative. De plus, force est de constater que les clients que je rencontre au fil de mes missions n’utilisent pas ces solutions.

Le problème principal de ces solutions revient à la gestion des conflits. Que se passe-t-il si une écriture différente survient sur la même ligne sur deux serveurs ? Par exemple, si j’ai ces deux requêtes, chacune sur un serveur différent :

UPDATE utilisateurs SET nom='Lelarge', [...], ville='Lille' WHERE id=2;

et

UPDATE utilisateurs SET nom='Lelarge', [...], ville='Caen' WHERE id=2;

Les deux requêtes vont réussir localement, mais quand le serveur 1 va envoyer le résultat de la requête au serveur 2, celui-ci va s’apercevoir que la ligne a changé entre temps. Que doit-il faire ? Appliquer la version du serveur 1 et ainsi perdre ses propres modifications ? Conserver sa version et se retrouver avec deux serveurs ayant des données différentes ? Comment le dire à l’autre serveur et aux deux utilisateurs qui ont exécuté ces requêtes et récupéré un retour positif ?

La solution basique revient à dire que pour chaque écriture sur un serveur, ce serveur doit prévenir les autres serveurs qu’il va écrire sur une ligne spécifique pour que ces autres serveurs puissent empêcher toute écriture sur cette même ligne. Très clairement, cela va ralentir tout le système, ce qui est dommage vu que nous cherchions à gagner en performance en répartissant les écritures. Nous sommes assurés d’avoir fragilisé l’architecture avec un plus grand nombre de serveurs, sans garantie que les performances seront meilleures. L’intérêt du multimaître est donc contestable, et il serait bien de prouver par des tests qu’il améliore les performances dans votre cas particulier avant de déployer un tel système en production.

Conclusion

PostgreSQL gère plusieurs types de réplication ainsi que de nombreuses options pour couvrir tous les cas possibles. La seule fonctionnalité manquante est le multimaître, mais dans la majorité des cas, elle n’est pas nécessaire. Cet article n’a fait qu’effleurer la question de la réplication. Toutes les possibilités offertes par PostgreSQL sont couvertes dans son manuel.

Depuis quelques années, Guillaume Lelarge publie des articles dans le magazine « Linux Pratique » édité par les Éditions Diamond. Avec leur accord, il reprend ici une série destinée à guider l’installation, la maintenance et l’utilisation de PostgreSQL.