Sur ce document

Formation	Module R56
Titre	Réplication Physique : fondamentaux
Révision	24.12
PDF	https://dali.bo/r56_pdf
EPUB	https://dali.bo/r56_epub
HTML	https://dali.bo/r56_html
Slides	https://dali.bo/r56_slides
TP	https://dali.bo/r56_tp
TP (solutions)	https://dali.bo/r56_solutions

Chers lectrices & lecteurs,

Nos formations PostgreSQL sont issues de nombreuses années d’études, d’expérience de terrain et de passion pour les logiciels libres. Pour Dalibo, l’utilisation de PostgreSQL n’est pas une marque d’opportunisme commercial, mais l’expression d’un engagement de longue date. Le choix de l’Open Source est aussi le choix de l’implication dans la communauté du logiciel.

Au‑delà du contenu technique en lui‑même, notre intention est de transmettre les valeurs qui animent et unissent les développeurs de PostgreSQL depuis toujours : partage, ouverture, transparence, créativité, dynamisme… Le but premier de nos formations est de vous aider à mieux exploiter toute la puissance de PostgreSQL mais nous espérons également qu’elles vous inciteront à devenir un membre actif de la communauté en partageant à votre tour le savoir‑faire que vous aurez acquis avec nous.

Nous mettons un point d’honneur à maintenir nos manuels à jour, avec des informations précises et des exemples détaillés. Toutefois malgré nos efforts et nos multiples relectures, il est probable que ce document contienne des oublis, des coquilles, des imprécisions ou des erreurs. Si vous constatez un souci, n’hésitez pas à le signaler via l’adresse formation@dalibo.com !

À propos de DALIBO

DALIBO est le spécialiste français de PostgreSQL. Nous proposons du support, de la formation et du conseil depuis 2005.

Retrouvez toutes nos formations sur https://dalibo.com/formations

Remerciements

Ce manuel de formation est une aventure collective qui se transmet au sein de notre société depuis des années. Nous remercions chaleureusement ici toutes les personnes qui ont contribué directement ou indirectement à cet ouvrage, notamment :

Alexandre Anriot, Jean‑Paul Argudo, Carole Arnaud, Alexandre Baron, David Bidoc, Sharon Bonan, Franck Boudehen, Arnaud Bruniquel, Pierrick Chovelon, Damien Clochard, Christophe Courtois, Marc Cousin, Gilles Darold, Ronan Dunklau, Vik Fearing, Stefan Fercot, Dimitri Fontaine, Pierre Giraud, Nicolas Gollet, Florent Jardin, Virginie Jourdan, Luc Lamarle, Denis Laxalde, Guillaume Lelarge, Alain Lesage, Benoit Lobréau, Jean‑Louis Louër, Thibaut Madelaine, Adrien Nayrat, Alexandre Pereira, Flavie Perette, Robin Portigliatti, Thomas Reiss, Maël Rimbault, Jehan-Guillaume de Rorthais, Julien Rouhaud, Stéphane Schildknecht, Julien Tachoires, Nicolas Thauvin, Be Hai Tran, Christophe Truffier, Arnaud de Vathaire, Cédric Villemain, Thibaud Walkowiak, Frédéric Yhuel.

Forme de ce manuel

Les versions PDF, EPUB ou HTML de ce document sont structurées autour des slides de nos formations. Le texte suivant chaque slide contient le cours et de nombreux détails qui ne peuvent être données à l’oral.

Licence Creative Commons CC-BY-NC-SA

Cette formation est sous licence CC-BY-NC-SA. Vous êtes libre de la redistribuer et/ou modifier aux conditions suivantes :

Paternité
Pas d’utilisation commerciale
Partage des conditions initiales à l’identique

Vous n’avez pas le droit d’utiliser cette création à des fins commerciales.

Si vous modifiez, transformez ou adaptez cette création, vous n’avez le droit de distribuer la création qui en résulte que sous un contrat identique à celui-ci.

Vous devez citer le nom de l’auteur original de la manière indiquée par l’auteur de l’œuvre ou le titulaire des droits qui vous confère cette autorisation (mais pas d’une manière qui suggérerait qu’ils vous soutiennent ou approuvent votre utilisation de l’œuvre). À chaque réutilisation ou distribution de cette création, vous devez faire apparaître clairement au public les conditions contractuelles de sa mise à disposition. La meilleure manière de les indiquer est un lien vers cette page web. Chacune de ces conditions peut être levée si vous obtenez l’autorisation du titulaire des droits sur cette œuvre. Rien dans ce contrat ne diminue ou ne restreint le droit moral de l’auteur ou des auteurs.

Le texte complet de la licence est disponible sur http://creativecommons.org/licenses/by-nc-sa/2.0/fr/legalcode

Cela inclut les diapositives, les manuels eux-mêmes et les travaux pratiques. Cette formation peut également contenir quelques images et schémas dont la redistribution est soumise à des licences différentes qui sont alors précisées.

Marques déposées

PostgreSQL® Postgres® et le logo Slonik sont des marques déposées par PostgreSQL Community Association of Canada.

Versions de PostgreSQL couvertes

Ce document ne couvre que les versions supportées de PostgreSQL au moment de sa rédaction, soit les versions 13 à 17.

Réplication physique : rappels

PostgreSQL —

Introduction

Patroni repose sur la réplication physique de PostgreSQL
la haute disponibilité implique les équipes techniques
les équipes techniques doivent comprendre la mécanique

Mise en place de la réplication physique
Promotion
Retour à l’état stable

Réplication par streaming

Mise en place de la réplication par streaming

Réplication en flux
Un processus du serveur primaire discute avec un processus du serveur secondaire
- d’où un lag moins important
Asynchrone ou synchrone
En cascade

Le serveur PostgreSQL secondaire lance un processus appelé walreceiver, dont le but est de se connecter au serveur primaire et d’attendre les modifications de la réplication.

Le walreceiver a donc besoin de se connecter sur le serveur PostgreSQL primaire. Ce dernier doit être configuré pour accepter cette connexion. Quand elle est acceptée par le serveur primaire, le serveur PostgreSQL du serveur primaire lance un nouveau processus, appelé walsender. Ce dernier a pour but d’envoyer les données de réplication au serveur secondaire. Les données de réplication sont envoyées suivant l’activité et certains paramètres de configuration.

Cette méthode permet une réplication plus proche du serveur primaire que le log shipping. On peut même configurer un mode synchrone : un client du serveur primaire ne récupère pas la main tant que ses modifications ne sont pas enregistrées sur le serveur primaire et sur le serveur secondaire synchrone. Cela s’effectue à la validation de la transaction, implicite ou lors d’un COMMIT.

Enfin, la réplication en cascade permet à un secondaire de fournir les informations de réplication à un autre secondaire, déchargeant ainsi le serveur primaire d’un certain travail et diminuant aussi la bande passante réseau utilisée par le serveur primaire.

Serveur primaire (1/2) - Configuration

Dans postgresql.conf :

wal_level = replica (ou logical)
max_wal_senders = X
- 1 par client par streaming
- défaut : 10
wal_sender_timeout = 60s

Il faut tout d’abord s’assurer que PostgreSQL enregistre suffisamment d’informations pour que le serveur secondaire puisse rejouer toutes les modifications survenant sur le serveur primaire. Dans certains cas, PostgreSQL peut économiser l’écriture de journaux quand cela ne pose pas de problème pour l’intégrité des données en cas de crash. Par exemple, sur une instance sans archivage ni réplication, il est inutile de tracer la totalité d’une transaction qui commence par créer une table, puis qui la remplit. En cas de crash pendant l’opération, l’opération complète est annulée, la table n’existera plus : PostgreSQL peut donc écrire directement son contenu sur le disque sans journaliser.

Cependant, pour restaurer cette table ou la répliquer, il est nécessaire d’avoir les étapes intermédiaires (le contenu de la table) et il faut donc écrire ces informations supplémentaires dans les journaux.

Le paramètre wal_level fixe le comportement à adopter. Comme son nom l’indique, il permet de préciser le niveau d’informations que l’on souhaite avoir dans les journaux. Il connaît trois valeurs :

Le niveau replica est adapté à l’archivage ou la réplication, en plus de la sécurisation contre les arrêts brutaux. C’est le niveau par défaut. L’optimisation évoquée plus haut n’est pas possible.
Le niveau minimal n’offre que la protection contre les arrêts brutaux, mais ne permet ni réplication ni sauvegarde PITR. Ce niveau ne sert plus guère qu’aux environnements ni archivés, ni répliqués, pour réduire la quantité de journaux générés, comme dans l’optimisation ci-dessus.
Le niveau logical est le plus complet et doit être activé pour l’utilisation du décodage logique, notamment pour utiliser la réplication logique. Il n’est pas nécessaire pour la sauvegarde PITR ou la réplication physique, ni incompatible.

Le serveur primaire accepte un nombre maximum de connexions de réplication : il s’agit du paramètre max_wal_senders. Il faut compter au moins une connexion pour chaque serveur secondaire susceptible de se connecter, ou les outils utilisant le streaming comme pg_basebackup ou pg_receivewal. Il est conseillé de prévoir « large » d’entrée : l’impact mémoire est négligeable, et cela évite d’avoir à redémarrer l’instance primaire à chaque modification. La valeur par défaut de 10 devrait suffire dans la plupart des cas.

Le paramètre wal_sender_timeout permet de couper toute connexion inactive après le délai indiqué par ce paramètre. Par défaut, le délai est d’une minute. Cela permet au serveur primaire de ne pas conserver une connexion coupée ou dont le client a disparu pour une raison ou une autre. Le secondaire retentera par la suite une connexion complète.

Serveur primaire (2/2) - Authentification

Le serveur secondaire doit pouvoir se connecter au serveur primaire
Pseudo-base replication
Utilisateur dédié conseillé avec attributs LOGIN et REPLICATION
Configurer pg_hba.conf :

host replication user_repli 10.2.3.4/32   scram-sha-256

Recharger la configuration

Il est nécessaire après cela de configurer le fichier pg_hba.conf. Dans ce fichier, une ligne (par secondaire) doit indiquer les connexions de réplication. L’idée est d’éviter que tout le monde puisse se connecter pour répliquer l’intégralité des données.

Pour distinguer une ligne de connexion standard et une ligne de connexion de réplication, la colonne indiquant la base de données doit contenir le mot « replication ». Par exemple :

host   replication   user_repli   10.0.0.2/32   scram-sha-256

Dans ce cas, l’utilisateur user_repli pourra entamer une connexion de réplication vers le serveur primaire à condition que la demande de connexion provienne de l’adresse IP 10.0.0.2 et que cette demande de connexion précise le bon mot de passe au format scram-sha-256.

Un utilisateur dédié à la réplication est conseillé pour des raisons de sécurité. On le créera avec les droits suivants :

CREATE ROLE user_repli LOGIN REPLICATION ;

et bien sûr un mot de passe complexe.

Les connexions locales de réplication sont autorisées par défaut sans mot de passe.

Après modification du fichier postgresql.conf et du fichier pg_hba.conf, il est temps de demander à PostgreSQL de recharger sa configuration. L’action reload suffit dans tous les cas, sauf celui où max_wal_senders est modifié (auquel cas il faudra redémarrer PostgreSQL).

Serveur secondaire (1/4) - Copie des données

Copie des données du serveur primaire (à chaud !) :

Copie généralement à chaud donc incohérente !
Le plus simple : pg_basebackup
- simple mais a des limites
Idéal : outil PITR
Possible : rsync, cp…
- ne pas oublier pg_backup_start()/pg_backup_stop() !
- exclure certains répertoires et fichiers
- garantir la disponibilité des journaux de transaction

La première action à réaliser ressemble beaucoup à ce que propose la sauvegarde en ligne des fichiers. Il s’agit de copier le répertoire des données de PostgreSQL ainsi que les tablespaces associés.

Rappelons que généralement cette copie aura lieu à chaud, donc une simple copie directe sera incohérente.

pg_basebackup :

L’outil le plus simple est pg_basebackup. Ses avantages sont sa disponibilité et sa facilité d’utilisation. Il sait ce qu’il n’y a pas besoin de copier et peut inclure les journaux nécessaires pour ne pas avoir à paramétrer l’archivage.

Il peut utiliser la connexion de réplication déjà prévue pour le secondaire, poser des slots temporaires ou le slot définitif.

Pour faciliter la mise en place d’un secondaire, il peut générer les fichiers de configuration à partir des paramètres qui lui ont été fournis (option --write-recovery-conf).

Malgré beaucoup d’améliorations dans les dernières versions, la limite principale de pg_basebackup reste d’exiger un répertoire cible vide : on doit toujours recopier l’intégralité de la base copiée. Cela peut être pénible lors de tests répétés avec une grosse base, ou avec une liaison instable. Toutefois, à partir de PostgreSQL 17, il permet une sauvegarde incrémentale.

Outils PITR :

L’idéal est un outil de restauration PITR permettant la restauration en mode delta, par exemple pgBackRest avec l’option --delta. Ne sont restaurés que les fichiers ayant changé, et le primaire n’est pas chargé par la copie.

rsync :

Un script de copie reste une option possible. Il est possible de le faire manuellement, tout comme pour une sauvegarde PITR.

Une copie manuelle implique que les journaux sont archivés par ailleurs.

Rappelons les trois étapes essentielles :

le pg_backup_start() ;
la copie des fichiers : généralement avec rsync --whole-file, ou tout moyen permettant une copie fiable et rapide ;
le pg_backup_stop().

On exclura les fichiers inutiles lors de la copie qui pourraient gêner un redémarrage, notamment les fichiers postmaster.pid, postmaster.opts, pg_internal.init, les répertoires pg_wal, pg_replslot, pg_dynshmem, pg_notify, pg_serial, pg_snapshots, pg_stat_tmp, pg_subtrans, pgslq_tmp*. La liste complète figure dans la documentation officielle.

Serveur secondaire (2/4) - Fichiers de configuration

postgresql.conf & postgresql.auto.conf
- paramètres
standby.signal (dans PGDATA)
- vide

Au choix, les paramètres sont à ajouter dans postgresql.conf, dans un fichier appelé par ce dernier avec une clause d’inclusion, ou dans postgresql.auto.conf (forcément dans le répertoire de données pour ce dernier, et qui surcharge les fichiers précédents). Cela dépend des habitudes, de la méthode d’industrialisation…

S’il y a des paramètres propres au primaire dans la configuration d’un secondaire, ils seront ignorés, et vice-versa. Dans les cas simples, le postgresql.conf peut donc être le même.

Puis il faut créer un fichier vide nommé standby.signal dans le répertoire PGDATA, qui indique à PostgreSQL que le serveur doit rester en recovery permanent.

Au cas où vous rencontreriez un vieux serveur en version antérieure à la 12 : jusqu’en version 11, on activait le mode standby non dans la configuration, mais en créant un fichier texte recovery.conf dans le PGDATA de l’instance, et en y plaçant le paramètre standby_mode à on. Les autres paramètres sont les mêmes. Toute modification impliquait un redémarrage.

Serveur secondaire (2/4) - Paramètres

primary_conninfo (streaming) :

primary_conninfo = 'user=user_repli host=prod port=5434
 application_name=standby '

Optionnel :
- primary_slot_name
- restore_command
- wal_receiver_timeout

PostgreSQL doit aussi savoir comment se connecter au serveur primaire. C’est le paramètre primary_conninfo qui le lui dit. Il s’agit d’un DSN standard où il est possible de spécifier l’adresse IP de l’hôte ou son alias, le numéro de port, le nom de l’utilisateur, etc. Il est aussi possible de spécifier le mot de passe, mais c’est risqué en terme de sécurité. En effet, PostgreSQL ne vérifie pas si ce fichier est lisible par quelqu’un d’autre que lui. Il est donc préférable de placer le mot de passe dans le fichier .pgpass, généralement dans ~postgres/ sur le secondaire, fichier qui n’est utilisé que s’il n’est lisible que par son propriétaire. Par exemple :

primary_conninfo = 'user=postgres host=prod passfile=/var/lib/postgresql/.pgpass'

Toutes les options de la libpq sont accessibles. Par exemple, cette chaîne de connexion a été générée pour un nouveau secondaire par pg_basebackup -R :

primary_conninfo = 'host=prod user=postgres passfile=''/var/lib/postgresql/.pgpass'' channel_binding=prefer port=5436 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

S’y trouvent beaucoup de paramétrage par défaut dépendant de méthodes d’authentification, ou pour le SSL.

Parmi les autres paramètres optionnels de primary_conninfo, il est conseillé d’ajouter application_name, par exemple avec le nom du serveur. Cela facilite la supervision. C’est même nécessaire pour paramétrer une réplication synchrone.

primary_conninfo = 'user=postgres host=prod passfile=/var/lib/postgresql/.pgpass  application_name=secondaire2 '

Si application_name n’est pas fourni, le cluster_name du secondaire sera utilisé, mais il est rarement correctement configuré (par défaut, il vaut 16/main sur Debian/Ubuntu, et n’est pas configuré sur Red Hat/Rocky Linux).

De manière optionnelle, nous verrons que l’on peut définir aussi deux paramètres :

primary_slot_name, pour sécuriser la réplication avec un slot de réplication ;
restore_command, pour sécuriser la réplication avec un accès à la sauvegarde PITR.

Le paramètre wal_receiver_timeout sur le secondaire est le symétrique de wal_sender_timeout sur le primaire. Il indique au bout de combien de temps couper une connexion inactive. Le secondaire retentera la connexion plus tard.

Serveur secondaire (3/4) - Démarrage

Démarrer PostgreSQL
Suivre dans les traces que tout va bien

Processus

Sur le primaire :

walsender ... streaming 0/3BD48728

Sur le secondaire :

walreceiver streaming 0/3BD48728

Sur le primaire, un processus walsender apparaît pour chaque secondaire connecté. Son nom de processus est mis à jour en permanence avec l’emplacement dans le flux de journaux de transactions :

 postgres: 16/secondaire1: walsender postgres [local] streaming 15/6A6EF408
 postgres: 16/secondaire2: walsender postgres [local] streaming 15/6A6EF408

Symétriquement, sur chaque secondaire, un process walreceiver apparaît.

 postgres: 16/secondaire2: walreceiver streaming 0/DD73C218

Promotion

Attention au split-brain !
Vérification avant promotion
Promotion : méthode et déroulement
Retour à l’état stable

Attention au split-brain !

Si un serveur secondaire devient le nouveau primaire
- s’assurer que l’ancien primaire ne reçoit plus d’écriture
Éviter que les deux instances soient ouvertes aux écritures
- confusion et perte de données !

Vérification avant promotion

Primaire :

# systemctl stop postgresql-14

$ pg_controldata -D /var/lib/pgsql/14/data/ \
| grep -E '(Database cluster state)|(REDO location)'
Database cluster state:               shut down
Latest checkpoint's REDO location:    0/3BD487D0

Secondaire :

$ psql -c 'CHECKPOINT;'
$ pg_controldata -D /var/lib/pgsql/14/data/ \
| grep -E '(Database cluster state)|(REDO location)'
Database cluster state:               in archive recovery
Latest checkpoint's REDO location:    0/3BD487D0

Avant une bascule, il est capital de vérifier que toutes les modifications envoyées par le primaire sont arrivées sur le secondaire. Si le primaire a été arrêté proprement, ce sera le cas. Après un CHECKPOINT sur le secondaire, on y retrouvera le même emplacement dans les journaux de transaction.

Ce contrôle doit être systématique avant une bascule. Même si toutes les écritures applicatives sont stoppées sur le primaire, quelques opérations de maintenance peuvent en effet écrire dans les journaux et provoquer un écart entre les deux serveurs (divergence). Il n’y aura alors pas de perte de données mais cela pourrait gêner la transformation de l’ancien primaire en secondaire, par exemple. En revanche, même avec un arrêt propre du primaire, il peut y avoir perte de données s’il y a un lag important entre primaire et secondaire : même si le rejeu va toujours jusqu’au bout avant le changement de timeline, les WAL qui n’ont pas pu être récupérés avant la déconnexion, ou après la récupération des archives (si le log shipping est en place) sont perdus pour le nouveau primaire.

Noter que pg_controldata n’est pas dans les chemins par défaut des distributions. La fonction SQL pg_control_checkpoint() affiche les même informations, mais n’est bien sûr pas accessible sur un primaire arrêté.

Promotion du standby : méthode

Shell :
- pg_ctl promote
SQL :
- fonction pg_promote()

Promotion du standby : déroulement

Une promotion déclenche :

déconnexion de la streaming replication (bascule programmée)
rejeu des dernières transactions en attente d’application
récupération et rejeu de toutes les archives disponibles
choix d’une nouvelle timeline du journal de transaction
suppression du fichier standby.signal
nouvelle timeline et fichier .history
ouverture aux écritures

La promotion se déroule en bonne partie comme un recovery après restauration PITR.

Une fois l’instance promue, elle finit de rejouer les derniers journaux de transaction en provenance du serveur principal en sa possession, puis se déconnecte de celui-ci (si l’on est encore connecté en streaming). Après la déconnexion, si une restore_command est configurée, toutes les archives disponibles sont récupérées et rejouées (en général, il n’y a pas d’archive contenant des WAL plus récents que le dernier récupéré en streaming ; mais des écritures lourdes et/ou un réseau trop lent peuvent entraîner un retard du streaming).

Le dernier journal reçu de l’ancien primaire est souvent incomplet. Il est renommé avec le suffixe .partial et archivé. Cela évite un conflit de nom éventuel avec le même fichier issu de l’ancien serveur, qui a pu aussi être archivé, à un point éventuellement postérieur à la divergence.

Ensuite, l’instance choisit une nouvelle timeline pour son journal de transactions. Rappelons que la timeline est le premier numéro dans le nom du segment (fichier WAL) ; par exemple une timeline 5 pour un fichier nommé 000000050000003200000031). Le nouveau primaire choisit généralement le numéro suivant celui du primaire (à moins que les archives ne contiennent d’autres timelines de numéro supérieur, s’il y a eu plusieurs restaurations et retours en arrière, et il choisit alors le numéro suivant la dernière).

Le choix d’une nouvelle timeline permet à PostgreSQL de rendre les journaux de transactions de ce nouveau serveur en écriture incompatibles avec son ancien serveur principal. De plus, des journaux de nom différent permet l’archivage depuis ce primaire sans perturber l’ancien s’il existe encore. Il n’y a plus de fichier en commun même si l’espace d’archivage est partagé.

Les timelines ne changent pas que lors des promotions, mais aussi lors des restaurations PITR. En général, on désire que les secondaires (parfois en cascade) suivent. Heureusement, ceci est le paramétrage par défaut depuis la version 12 :

recovery_target_timeline = latest

Un secondaire suit donc par défaut les évolutions de timeline de son primaire, tant que celui-ci n’effectue pas de retour en arrière.

L’instance crée un fichier d’historique dans pg_wal/, par exemple 00000006.history pour la nouvelle timeline 6. C’est un petit fichier texte qui contient les différentes timelines ayant mené à la nouvelle. Ce fichier est immédiatement archivé s’il y a archivage.

Enfin, l’instance autorise les connexions en lecture et en écriture.

Opérations après promotion du standby

VACUUM ANALYZE conseillé
- calcul d’informations nécessaires pour autovacuum

Retour à l’état stable

Si un standby a été momentanément indisponible, reconnexion directe possible si :
- journaux nécessaires encore présents sur primaire (slot, wal_keep_size/wal_keep_segments)
- journaux nécessaires présents en archives (restore_command)
Sinon
- « décrochage »
- reconstruction nécessaire

Si un serveur secondaire est momentanément indisponible mais revient en ligne sans perte de données (réseau coupé, problème OS…), alors il a de bonnes chances de se « raccrocher » à son serveur primaire. Il faut bien sûr que l’ensemble des journaux de transaction depuis son arrêt soit accessible à ce serveur, sans exception.

En cas de réplication par streaming : le primaire ne doit pas avoir recyclé les journaux après ses checkpoints. Il les aura conservés s’il y a un slot de réplication actif dédié à ce secondaire, ou si on a monté wal_keep_size (ou wal_keep_segments jusque PostgreSQL 12 compris) assez haut par rapport à l’activité en écriture sur le primaire. Les journaux seront alors toujours disponibles sur le principal et le secondaire rattrapera son retard par streaming. Si le primaire n’a plus les journaux, il affichera une erreur, et le secondaire tentera de se rabattre sur le log shipping, s’il est aussi configuré.

En cas de réplication par log shipping, il faut que la restore_command fonctionne, que le stock des journaux remonte assez loin dans le temps (jusqu’au moment où le secondaire a perdu contact), et qu’aucun journal ne manque ou ne soit corrompu. Sinon le secondaire se bloquera au dernier journal chargé. En cas d’échec, ou si le dernier journal disponible vient d’être rejoué, le secondaire basculera sur le streaming, s’il est configuré.

Si le secondaire ne peut rattraper le flux des journaux du primaire, il doit être reconstruit par l’une des méthodes précédentes.

Retour à l’état stable, suite

Synchronisation automatique une fois la connexion rétablie
Mais reconstruction obligatoire :
- si le serveur secondaire était plus avancé que le serveur promu (« divergence »)
Reconstruire les serveurs secondaires à partir du nouveau principal :
- rsync, restauration PITR, plutôt que pg_basebackup
- pg_rewind
Reconstruction : manuelle !
Tablespaces !

Un secondaire qui a bien « accroché » son primaire se synchronise automatiquement avec lui, que ce soit par streaming ou log shipping. C’est notamment le cas si l’on vient de le construire depuis une sauvegarde ou avec pg_basebackup, et que l’archivage ou le streaming alimentent correctement le secondaire. Cependant, il y a des cas où un secondaire ne peut être simplement raccroché à un primaire, notamment si le secondaire se croit plus avancé que le primaire dans le flux des journaux.

Le cas typique est un ancien primaire que l’on veut transformer en secondaire d’un ancien secondaire promu. Si la bascule s’était faite proprement, et que l’ancien primaire avait pu envoyer tous ses journaux avant de s’arrêter ou d’être arrêté, il n’y a pas de problème. Si le primaire a été arrêté violemment, sans pouvoir transmettre tous ses journaux, l’ancien secondaire n’a rejoué que ce qu’il a reçu, puis a ouvert en écriture sa propre timeline depuis un point moins avancé que là où le primaire était finalement arrivé avant d’être arrêté. Les deux serveurs ont donc « divergé », même pendant très peu de temps. Les journaux non envoyés au nouveau primaire doivent être considérés comme perdus. Quand l’ancien primaire revient en ligne, parfois très longtemps après, il voit que sa timeline est plus avancée que la version qu’en a gardée le nouveau primaire. Il ne sait donc pas comment appliquer les journaux qu’il reçoit du nouveau primaire.

La principale solution, et la plus simple, reste alors la reconstruction du secondaire à raccrocher.

L’utilisation de pg_basebackup est possible mais déconseillée si la volumétrie est importante : cet outil impose une copie de l’ensemble des données du serveur principal, et ce peut être long.

La durée de reconstruction des secondaires peut être optimisée en utilisant des outils de synchronisation de fichiers pour réduire le volume des données à transférer. Les outils de restauration PITR offrent souvent une restauration en mode delta (notamment l’option --delta de pgBackRest) et c’est ce qui est généralement à privilégier. Dans un script de sauvegarde PITR, rsync --whole-file reste une bonne option.

Le fait de disposer de l’ensemble des fichiers de configuration sur tous les nœuds permet de gagner un temps précieux lors des phases de reconstruction, qui peuvent également être scriptées.

Par contre, les opérations de reconstructions se doivent d’être lancées manuellement pour éviter tout risque de corruption de données dues à des opérations automatiques externes, comme lors de l’utilisation de solutions de haute disponibilité.

Enfin, on rappelle qu’il ne faut pas oublier de prendre en compte les tablespaces lors de la reconstruction.

Une alternative à la reconstruction est l’utilisation de l’outil pg_rewind pour « rembobiner » l’ancien primaire, si tous les journaux nécessaires sont disponibles.

Conclusion

La réplication physique native est très robuste
Patroni peut automatiser :
- la mise en réplication
- la promotion
- le raccrochage d’un ancien primaire

Installation de PostgreSQL depuis les paquets communautaires

L’installation est détaillée ici pour Rocky Linux 8 et 9 (similaire à Red Hat et à d’autres variantes comem Oracle Linux et Fedora), et Debian/Ubuntu.

Elle ne dure que quelques minutes.

Sur Rocky Linux 8 ou 9

ATTENTION : Red Hat, CentOS, Rocky Linux fournissent souvent par défaut des versions de PostgreSQL qui ne sont plus supportées. Ne jamais installer les packages postgresql, postgresql-client et postgresql-server ! L’utilisation des dépôts du PGDG est fortement conseillée.

Installation du dépôt communautaire :

Les dépôts de la communauté sont sur https://yum.postgresql.org/. Les commandes qui suivent sont inspirées de celles générées par l’assistant sur https://www.postgresql.org/download/linux/redhat/, en précisant :

la version majeure de PostgreSQL (ici la 17) ;
la distribution (ici Rocky Linux 8) ;
l’architecture (ici x86_64, la plus courante).

Les commandes sont à lancer sous root :

# dnf install -y https://download.postgresql.org/pub/repos/yum/reporpms\
/EL-8-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# dnf -qy module disable postgresql

Installation de PostgreSQL 17 (client, serveur, librairies, extensions) :

# dnf install -y postgresql17-server postgresql17-contrib

Les outils clients et les librairies nécessaires seront automatiquement installés.

Une fonctionnalité avancée optionnelle, le JIT (Just In Time compilation), nécessite un paquet séparé.

# dnf install postgresql17-llvmjit

Création d’une première instance :

Il est conseillé de déclarer PG_SETUP_INITDB_OPTIONS, notamment pour mettre en place les sommes de contrôle et forcer les traces en anglais :

# export PGSETUP_INITDB_OPTIONS='--data-checksums --lc-messages=C'
# /usr/pgsql-17/bin/postgresql-17-setup initdb
# cat /var/lib/pgsql/17/initdb.log

Ce dernier fichier permet de vérifier que tout s’est bien passé et doit finir par :

Success. You can now start the database server using:

    /usr/pgsql-17/bin/pg_ctl -D /var/lib/pgsql/17/data/ -l logfile start

Chemins :

Objet	Chemin
Binaires	`/usr/pgsql-17/bin`
Répertoire de l’utilisateur postgres	`/var/lib/pgsql`
`PGDATA` par défaut	`/var/lib/pgsql/17/data`
Fichiers de configuration	dans `PGDATA/`
Traces	dans `PGDATA/log`

Configuration :

Modifier postgresql.conf est facultatif pour un premier lancement.

Commandes d’administration habituelles :

Démarrage, arrêt, statut, rechargement à chaud de la configuration, redémarrage :

# systemctl start postgresql-17
# systemctl stop postgresql-17
# systemctl status postgresql-17
# systemctl reload postgresql-17
# systemctl restart postgresql-17

Test rapide de bon fonctionnement et connexion à psql :

# systemctl --all |grep postgres
# sudo -iu postgres psql

Démarrage de l’instance au lancement du système d’exploitation :

# systemctl enable postgresql-17

Ouverture du firewall pour le port 5432 :

Voir si le firewall est actif :

# systemctl status firewalld

Si c’est le cas, autoriser un accès extérieur :

# firewall-cmd --zone=public --add-port=5432/tcp --permanent
# firewall-cmd --reload
# firewall-cmd --list-all

(Rappelons que listen_addresses doit être également modifié dans postgresql.conf.)

Création d’autres instances :

Si des instances de versions majeures différentes doivent être installées, il faut d’abord installer les binaires pour chacune (adapter le numéro dans dnf install …) et appeler le script d’installation de chaque version. l’instance par défaut de chaque version vivra dans un sous-répertoire numéroté de /var/lib/pgsql automatiquement créé à l’installation. Il faudra juste modifier les ports dans les postgresql.conf pour que les instances puissent tourner simultanément.

Si plusieurs instances d’une même version majeure (forcément de la même version mineure) doivent cohabiter sur le même serveur, il faut les installer dans des PGDATA différents.

Ne pas utiliser de tiret dans le nom d’une instance (problèmes potentiels avec systemd).
Respecter les normes et conventions de l’OS : placer les instances dans un nouveau sous-répertoire de /var/lib/pgsqsl/17/ (ou l’équivalent pour d’autres versions majeures).

Pour créer une seconde instance, nommée par exemple infocentre :

Création du fichier service de la deuxième instance :

# cp /lib/systemd/system/postgresql-17.service \
        /etc/systemd/system/postgresql-17-infocentre.service

Modification de ce dernier fichier avec le nouveau chemin :

Environment=PGDATA=/var/lib/pgsql/17/infocentre

Option 1 : création d’une nouvelle instance vierge :

# export PGSETUP_INITDB_OPTIONS='--data-checksums --lc-messages=C'
# /usr/pgsql-17/bin/postgresql-17-setup initdb postgresql-17-infocentre

Option 2 : restauration d’une sauvegarde : la procédure dépend de votre outil.
Adaptation de /var/lib/pgsql/17/infocentre/postgresql.conf (port surtout).
Commandes de maintenance de cette instance :

# systemctl [start|stop|reload|status] postgresql-17-infocentre
# systemctl [enable|disable] postgresql-17-infocentre

Ouvrir le nouveau port dans le firewall au besoin.

Sur Debian / Ubuntu

Sauf précision, tout est à effectuer en tant qu’utilisateur root.

Référence : https://apt.postgresql.org/

Installation du dépôt communautaire :

L’installation des dépôts du PGDG est prévue dans le paquet Debian :

# apt update
# apt install -y  gnupg2  postgresql-common 
# /usr/share/postgresql-common/pgdg/apt.postgresql.org.sh

Ce dernier ordre créera le fichier du dépôt /etc/apt/sources.list.d/pgdg.list adapté à la distribution en place.

Installation de PostgreSQL 17 :

La méthode la plus propre consiste à modifier la configuration par défaut avant l’installation :

Dans /etc/postgresql-common/createcluster.conf, paramétrer au moins les sommes de contrôle et les traces en anglais :

initdb_options = '--data-checksums --lc-messages=C'

Puis installer les paquets serveur et clients et leurs dépendances :

# apt install postgresql-17 postgresql-client-17

La première instance est automatiquement créée, démarrée et déclarée comme service à lancer au démarrage du système. Elle porte un nom (par défaut main).

Elle est immédiatement accessible par l’utilisateur système postgres.

Chemins :

Objet	Chemin
Binaires	`/usr/lib/postgresql/17/bin/`
Répertoire de l’utilisateur postgres	`/var/lib/postgresql`
PGDATA de l’instance par défaut	`/var/lib/postgresql/17/main`
Fichiers de configuration	dans `/etc/postgresql/17/main/`
Traces	dans `/var/log/postgresql/`

Configuration

Modifier postgresql.conf est facultatif pour un premier essai.

Démarrage/arrêt de l’instance, rechargement de configuration :

Debian fournit ses propres outils, qui demandent en paramètre la version et le nom de l’instance :

# pg_ctlcluster 17 main [start|stop|reload|status|restart]

Démarrage de l’instance avec le serveur :

C’est en place par défaut, et modifiable dans /etc/postgresql/17/main/start.conf.

Ouverture du firewall :

Debian et Ubuntu n’installent pas de firewall par défaut.

Statut des instances du serveur :

# pg_lsclusters

Test rapide de bon fonctionnement et connexion à psql :

# systemctl --all |grep postgres
# sudo -iu postgres psql

Destruction d’une instance :

# pg_dropcluster 17 main

Création d’autres instances :

Ce qui suit est valable pour remplacer l’instance par défaut par une autre, par exemple pour mettre les checksums en place :

optionnellement, /etc/postgresql-common/createcluster.conf permet de mettre en place tout d’entrée les checksums, les messages en anglais, le format des traces ou un emplacement séparé pour les journaux :

initdb_options = '--data-checksums --lc-messages=C'
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
waldir = '/var/lib/postgresql/wal/%v/%c/pg_wal'

créer une instance :

# pg_createcluster 17 infocentre

Il est également possible de préciser certains paramètres du fichier postgresql.conf, voire les chemins des fichiers (il est conseillé de conserver les chemins par défaut) :

# pg_createcluster 17 infocentre \
  --port=12345 \
  --datadir=/PGDATA/17/infocentre \
  --pgoption shared_buffers='8GB' --pgoption work_mem='50MB' \
  --  --data-checksums --waldir=/ssd/postgresql/17/infocentre/journaux

adapter au besoin /etc/postgresql/17/infocentre/postgresql.conf ;
démarrage :

# pg_ctlcluster 17 infocentre start

Accès à l’instance depuis le serveur même (toutes distributions)

Par défaut, l’instance n’est accessible que par l’utilisateur système postgres, qui n’a pas de mot de passe. Un détour par sudo est nécessaire :

$ sudo -iu postgres psql
psql (17.0)
Type "help" for help.
postgres=#

Ce qui suit permet la connexion directement depuis un utilisateur du système :

Pour des tests (pas en production !), il suffit de passer à trust le type de la connexion en local dans le pg_hba.conf :

local   all             postgres                               trust

La connexion en tant qu’utilisateur postgres (ou tout autre) n’est alors plus sécurisée :

dalibo:~$ psql -U postgres
psql (17.0)
Type "help" for help.
postgres=#

Une authentification par mot de passe est plus sécurisée :

dans pg_hba.conf, paramétrer une authentification par mot de passe pour les accès depuis localhost (déjà en place sous Debian) :

# IPv4 local connections:
host    all             all             127.0.0.1/32            scram-sha-256
# IPv6 local connections:
host    all             all             ::1/128                 scram-sha-256

(Ne pas oublier de recharger la configuration en cas de modification.)

ajouter un mot de passe à l’utilisateur postgres de l’instance :

dalibo:~$ sudo -iu postgres psql
psql (17.0)
Type "help" for help.
postgres=# \password
Enter new password for user "postgres":
Enter it again:
postgres=# quit

dalibo:~$ psql -h localhost -U postgres
Password for user postgres:
psql (17.0)
Type "help" for help.
postgres=#

Pour se connecter sans taper le mot de passe à une instance, un fichier .pgpass dans le répertoire personnel doit contenir les informations sur cette connexion :

localhost:5432:*:postgres:motdepassetrèslong

Ce fichier doit être protégé des autres utilisateurs :

$ chmod 600 ~/.pgpass

Pour n’avoir à taper que psql, on peut définir ces variables d’environnement dans la session voire dans ~/.bashrc :

export PGUSER=postgres
export PGDATABASE=postgres
export PGHOST=localhost

Rappels :

en cas de problème, consulter les traces (dans /var/lib/pgsql/17/data/log ou /var/log/postgresql/) ;
toute modification de pg_hba.conf ou postgresql.conf impliquant de recharger la configuration peut être réalisée par une de ces trois méthodes en fonction du système :

root:~# systemctl reload postgresql-17

root:~# pg_ctlcluster 17 main reload

postgres:~$ psql -c 'SELECT pg_reload_conf()'

Travaux pratiques

La version en ligne des solutions de ces TP est disponible sur https://dali.bo/r56_solutions.

Ce TP utilise plusieurs VM : p1 et p2.

Les commandes systemctl doivent être exécutées avec l’utilisateur root ou (de préférence) avec la commande sudo, depuis un utilisateur qui dispose des droits nécessaire. Par défaut, ce n’est pas le cas pour l’utilisateur postgres. Dans les machines virtuelles, l’utilisateur admin dispose de ces droits.

Il est possible de changer ce comportement, en exécutant la commande sudo visudo et en ajoutant la ligne postgres apres root :

root     ALL=(ALL:ALL)
postgres ALL=(ALL) NOPASSWD: ALL

L’utilisateur postgres sera sudoer au prochain démarrage de session. Attention à la syntaxe et à l’éventuel message d’erreur de visudo, une erreur dans le fichier peut empêcher tout accès à la VM.

Sur Rocky Linux 8 ou 9

Réplication asynchrone en flux avec un seul secondaire

Promotion de l’instance secondaire

Retour à la normale

Sur Debian 12

Réplication asynchrone en flux avec un seul secondaire

Promotion de l’instance secondaire

Retour à la normale

Travaux pratiques (solutions)

Sur Rocky Linux 8 ou 9

Réplication asynchrone en flux avec un seul secondaire

Créer l’instance principale dans /var/lib/pgsql/16/main sur le serveur p1.

# export PGSETUP_INITDB_OPTIONS='--data-checksums'
# /usr/pgsql-16/bin/postgresql-16-setup initdb
Initializing database ... OK
# systemctl start postgresql-16.service

Mettre en place la configuration de la réplication par streaming.
L’utilisateur dédié sera nommé repli.

Au sein du fichier /var/lib/pgsql/16/data/pg_hba.conf, ajouter l’entrée ci-dessous pour que l’utilisateur repli (avec l’attribut REPLICATION) ait accès en réplication à l’instance :

# Allow replication
host    replication     repli           10.0.0.22/32            scram-sha-256

Les entrées configurée par défaut qui permettent de faire de la réplication en local peuvent être supprimées :

# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

Créer le rôle repli, qui sera dédié à la réplication, en lui affectant le mot de passe confidentiel :

$ createuser --no-superuser --no-createrole --no-createdb --replication -P repli
Enter password for new role:
Enter it again:

Configurer ensuite le fichier .pgpass de l’utilisateur système postgres :

$ echo '*:*:*:repli:confidentiel' >> ~/.pgpass
$ chmod 600 ~/.pgpass

Il faut adapter la configuration pour que PostgreSQL écoute sur l’interface réseau qui sera utilisé pour la réplication (listen_addresses = '*'). Afin d’éviter que la mise en réplication n’échoue car les WAL ont été recyclés par PostgreSQL, il faut dire à PostgreSQL de conserver des WAL (wal_keep_size = '256MB'). Cette modification est facultative mais elle permet d’avoir plus de confort dans les manipulations. Un effet similaire peut être obtenu en utilisant des slots de réplication. La mise à jour de la configuration doit être réalisée dans /var/lib/pgsql/16/data/postgresql.conf.

$ psql -c "\dconfig+ (listen_addresses|wal_keep_size)"

Comme vous pouvez le voir dans la colonne context, listen_addresses nécessite un redémarrage.

# systemctl restart postgresql-16.service  # redémarage
# systemctl status postgresql-16.service   # contrôle

Créer la première instance secondaire sur le serveur p2, par copie à chaud du répertoire de données avec pg_basebackup.
Penser à copier les fichiers de configuration

Créer un squelette d’instance sur le serveur p2 :

# /usr/pgsql-16/bin/postgresql-16-setup initdb

Supprimer le répertoire de données :

$ rm -Rf /var/lib/pgsql/16/data

Créer le fichier ~/.pgpass :

$ echo '*:*:*:repli:confidentiel' >> ~/.pgpass
$ chmod 600 ~/.pgpass

Utiliser pg_basebackup pour créer l’instance secondaire :

$ pg_basebackup --pgdata /var/lib/pgsql/16/data \
                --progress \
                --write-recovery-conf \
                --checkpoint fast \
                --host 10.0.0.21 \
                --username repli

23172/23172 kB (100%), 1/1 tablespace

L’option -R ou --write-recovery-conf de pg_basebackup a préparé la configuration de la mise en réplication en créant le fichier standby.signal ainsi qu’en configurant primary_conninfo dans le fichier postgresql.auto.conf (dans les versions antérieures à la 11, il renseignerait recovery.conf) :

$ cat /var/lib/pgsql/16/data/postgresql.auto.conf

primary_conninfo = 'user=repli passfile=''/var/lib/pgsql/.pgpass''
                    channel_binding=prefer host=10.0.0.21 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'

$ file /var/lib/pgsql/16/data/standby.signal

/var/lib/pgsql/16/data/standby.signal: empty

Les fichiers pg_hba.conf et du postgresql.conf ont été répliqué avec le reste de l’instance présente sur p1. Il faut cependant adapter la configuration du pg_hba.conf pourqu’elle autorise les connexions de réplication depuis p1 :

$ grep -E "host.*replication" /etc/pgsql/16/data/pg_hba.conf

host    replication     repli           10.0.0.21/32            scram-sha-256

Démarrer l’instance sur p2 et s’assurer que la réplication fonctionne bien avec ps.
Tenter de se connecter au serveur secondaire.
Créer quelques tables pour vérifier que les écritures se propagent du primaire au secondaire.

Il ne reste désormais plus qu’à démarrer l’instance secondaire :

# systemctl start  postgresql-16.service   # redémarrage
# systemctl status  postgresql-16.service  # contrôle

La commande ps suivante permet de voir que les deux serveurs sont lancés :

$ ps -o pid,cmd fx

Voici le résultat de la commande sur le seveur p2 :

  PID CMD
12895 /usr/pgsql-16/bin/postgres -D /var/lib/pgsql/16/data/
12896  \_ postgres: logger
12897  \_ postgres: checkpointer
12898  \_ postgres: background writer
12899  \_ postgres: startup recovering 000000010000000000000003
12900  \_ postgres: walreceiver streaming 0/3000148

La même commande exécutée sur le serveur p1 :

  PID CMD
12443 /usr/pgsql-16/bin/postgres -D /var/lib/pgsql/16/data/
12444  \_ postgres: logger
12445  \_ postgres: checkpointer
12446  \_ postgres: background writer
12448  \_ postgres: walwriter
12449  \_ postgres: autovacuum launcher
12450  \_ postgres: logical replication launcher
12940  \_ postgres: walsender repli 10.0.0.22(50520) streaming 0/3000148

Nous avons bien les deux processus de réplication en flux wal sender et wal receiver.

Créons quelques données sur le principal et assurons-nous qu’elles soient transmises au secondaire :

$ createdb b1

$ psql b1

psql (16.1)
Type "help" for help.

b1=# CREATE TABLE t1(id integer);

CREATE TABLE

b1=# INSERT INTO t1 SELECT generate_series(1, 1000000);

INSERT 0 1000000

En exécutant la commande suivante sur les serveurs p1 et p2, on constate que le flux a été transmis :

$ ps -o pid,cmd fx | egrep "(startup|walsender|walreceiver)"

Sur p1 :

12940  \_ postgres: walsender repli 10.0.0.22(50520) streaming 0/71AFD20

Sur p2 :

12899  \_ postgres: startup recovering 000000010000000000000007
12900  \_ postgres: walreceiver streaming 0/71AFD20

Essayons de nous connecter au secondaire et d’exécuter quelques requêtes :

$ psql b1

psql (16.1)
Type "help" for help.

b1=# SELECT COUNT(*) FROM t1;

  count
---------
 1000000

b1=# CREATE TABLE t2(id integer);

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

On peut se connecter, lire des données, mais pas écrire.

Le comportement est visible dans le log de l’instance secondaire dans le fichier pointé par /var/lib/pgsql/16/data/current_logfiles :

$ cat /var/lib/pgsql/16/data/current_logfiles

stderr log/postgresql-Thu.log

$ cat /var/lib/pgsql/16/data/current_logfiles
stderr log/postgresql-Thu.log

... LOG:  database system is ready to accept read only connections

PostgreSQL indique bien qu’il accepte des connexions en lecture seule.

Promotion de l’instance secondaire

En respectant les étapes de vérification de l’état des instances, effectuer une promotion contrôlée de l’instance secondaire.

Sur p1, arrêt de l’instance primaire et vérification de son état :

# systemctl stop postgresql-16.service

$ /usr/pgsql-16/bin/pg_controldata -D /var/lib/pgsql/16/data/ \
| grep -E '(cluster)|(REDO)'

Database cluster state:               shut down
Latest checkpoint's REDO location:    0/71AFE08
Latest checkpoint's REDO WAL file:    000000010000000000000007

Vérification de l’instance secondaire sur p2 :

$ psql -c 'CHECKPOINT;'

$ /usr/pgsql-16/bin/pg_controldata -D /var/lib/pgsql/16/data/ \
| grep -E '(cluster)|(REDO)'

Database cluster state:               in archive recovery
Latest checkpoint's REDO location:    0/71AFE08
Latest checkpoint's REDO WAL file:    000000010000000000000007

L’instance principale est bien arrêtée, l’instance secondaire est bien en archive recovery et les deux sont bien synchronisées.

Promotion de l’instance secondaire :

$ /usr/pgsql-16/bin/pg_ctl -D /var/lib/pgsql/16/data promote

waiting for server to promote.... done
server promoted

Tenter de se connecter au serveur secondaire fraîchement promu.
Les écritures y sont-elles possibles ?

Connectons-nous à ce nouveau primaire et tentons d’y insérer des données :

$ psql b1
psql (16.1)
Type "help" for help.

b1=# CREATE TABLE t2(id integer);

CREATE TABLE

b1=# INSERT INTO t2 SELECT generate_series(1, 1000000);

INSERT 0 1000000

Les écritures sont désormais bien possible sur cette instance.

Retour à la normale

Reconstruire l’instance initiale sur p1 comme nouvelle instance secondaire en repartant d’une copie complète de p2 en utilisant pg_basebackup.

Afin de rétablir la situation, nous pouvons réintégrer l’ancienne instance primaire en tant que nouveau secondaire (sur p1). Pour ce faire, nous devons re-synchroniser les données. Utilisons pg_basebackup comme précédemment après avoir mis de côté les fichiers de l’ancien primaire :

$ mv /var/lib/pgsql/16/data /var/lib/pgsql/16/data.old

$ pg_basebackup --pgdata /var/lib/pgsql/16/data \
                --progress \
                --write-recovery-conf \
                --checkpoint fast \
                --host 10.0.0.22 \
                --username repli

101603/101603 kB (100%), 1/1 tablespace

Créer le fichier standby.signal s’il n’existe pas déjà. Contrôler postgresql.auto.conf (qui contient potentiellement deux lignes primary_conninfo !) et adapter le port :

$ touch /var/lib/pgsql/16/data/standby.signal

$ cat /var/lib/pgsql/16/data/postgresql.auto.conf

primary_conninfo = 'user=repli passfile=''/var/lib/pgsql/.pgpass'' host=10.0.0.22 port=5433 sslmode=prefer sslcompression=0 gssencmode=prefer krbsrvname=postgres target_session_attrs=any'

Démarrer cette nouvelle instance.

# systemctl start postgresql-16.service   # démarrage
# systemctl status postgresql-16.service  # contrôle

Vérifier que les processus adéquats sont bien présents, et que les données précédemment insérées dans les tables créées plus haut sont bien présentes dans l’instance reconstruite.

Les processus adéquats sont bien présents :

$ ps -o pid,cmd fx | egrep "(startup|walsender|walreceiver)"

Sur p1 :

13666  \_ postgres: startup recovering 00000002000000000000000C
13667  \_ postgres: walreceiver streaming 0/C000060

Sur p2 :

13676  \_ postgres: walsender repli 10.0.0.21(46610) streaming 0/C000060

$ psql b1

psql (16.1)
Type "help" for help.

En nous connectant à la nouvelle instance secondaire (p2), vérifions que les données précédemment insérées dans la table t2 sont bien présentes :

b1=# SELECT COUNT(*) FROM t2;

  count
---------
 1000000

Inverser à nouveau les rôles des deux instances afin que p2 redevienne l’instance secondaire, cette fois-ci effectuer la remise en service de l’ancienne instance primaire sans reconstruction.

Afin que l’instance p1 redevienne primaire et celle sur p2 secondaire, on peut ré-appliquer la procédure de promotion vue précédemment dans l’autre sens.

Arrêt de l’instance primaire (p2) et vérification de son état :

# systemctl stop postgresql-16.service

$ /usr/pgsql-16/bin/pg_controldata -D /var/lib/pgsql/16/data/ \
| grep -E '(cluster)|(REDO)'

Database cluster state:               shut down
Latest checkpoint's REDO location:    0/C000148
Latest checkpoint's REDO WAL file:    00000002000000000000000C

Vérification de l’instance secondaire p1 :

$ psql -c 'CHECKPOINT;'
$ /usr/pgsql-16/bin/pg_controldata -D /var/lib/pgsql/16/data/ \
| grep -E '(cluster)|(REDO)'

Database cluster state:               in archive recovery
Latest checkpoint's REDO location:    0/C000148
Latest checkpoint's REDO WAL file:    00000002000000000000000C

L’instance principale est bien arrêtée, l’instance secondaire est bien en archive recovery et les deux sont bien synchronisées.

Promotion de l’instance secondaire :

$ /usr/pgsql-16/bin/pg_ctl -D /var/lib/pgsql/16/data promote

waiting for server to promote.... done
server promoted

La configuration du fichier pg_hba.conf doit être adaptée pour autoriser les connexions depuis 10.0.0.22 et la configuration rechargée.

$ grep "^host.*replication" /var/lib/pgsql/16/data/pg_hba.conf

host    replication     repli           10.0.0.22/32            scram-sha-256

$ /usr/pgsql-16/bin/pg_ctl reload -D /var/lib/pgsql/16/data/

server signaled

Afin que p2 redevienne l’instance secondaire, créer le fichier standby.signal, démarrer le service et vérifier que les processus adéquats sont bien présents :

$ touch /var/lib/pgsql/16/data/standby.signal

# systemctl start postgresql-16.service

$  ps -o pid,cmd fx | egrep "(startup|walsender|walreceiver)"

Sur p1 :

14254  \_ postgres: walsender repli 10.0.0.22(53776) streaming 0/C0002D8

Sur p2 :

13996  \_ postgres: startup recovering 00000003000000000000000C
14172  \_ postgres: walreceiver streaming 0/C0002D8

Sur Debian 12

Cette solution se base sur un système Debian 12, installé à minima depuis les paquets du PGDG, et en anglais.

Le prompt # indique une commande à exécuter avec l’utilisateur root. Le prompt $ est utilisé pour les commandes de l’utilisateur postgres.

Au préalable, nettoyer les instances précédemment créées sur le serveur.

# pg_dropcluster --stop 16 main

Réplication asynchrone en flux avec un seul secondaire

Créer l’instance principale en utilisant pg_createcluster sur le serveur p1.

# pg_createcluster 16 main
Creating new PostgreSQL cluster 16/main ...
...
Ver Cluster Port Status Owner    Data directory              Log file
16  main    5432 down   postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.lo

Le répertoire des données se trouvera sous /var/lib/postgresql/16/main.

Démarrer l’instance, soit avec :

# pg_ctlcluster start 16 main

soit explicitement via systemd :

# systemctl start postgresql@16-main

Vérifier la configuration de la réplication par streaming.
L’utilisateur dédié sera nommé repli.

Au sein du fichier /etc/postgresql/16/main/pg_hba.conf, ajouter l’entrée ci-dessous pour que l’utilisateur repli (avec l’attribut REPLICATION) ait accès en réplication à l’instance :

# Allow replication
host    replication     repli           10.0.0.22/32            scram-sha-256

Les entrées configurée par défaut qui permettent de faire de la réplication en local peuvent être supprimées :

# Allow replication connections from localhost, by a user with the
# replication privilege.
local   replication     all                                     peer
host    replication     all             127.0.0.1/32            scram-sha-256
host    replication     all             ::1/128                 scram-sha-256

Créer le rôle repli, qui sera dédié à la réplication, en lui affectant le mot de passe confidentiel :

$ createuser --no-superuser --no-createrole --no-createdb --replication -P repli
Enter password for new role:
Enter it again:

Configurer ensuite le fichier .pgpass de l’utilisateur système postgres :

$ echo '*:*:*:repli:confidentiel' >> ~/.pgpass
$ chmod 600 ~/.pgpass

Il faut adapter la configuration pour que PostgreSQL écoute sur l’interface réseau qui sera utilisé pour la réplication (listen_addresses = '*'). Afin d’éviter que la mise en réplication n’échoue car les WAL ont été recyclés par PostgreSQL, il faut dire à PostgreSQL de conserver des WAL (wal_keep_size = '256MB'). Cette modification est facultative mais elle permet d’avoir plus de confort dans les manipulations. Un effet similaire peut être obtenu en utilisant des slots de réplication. La mise à jour de la configuration doit être réalisée dans /etc/postgresql/16/main.

$ psql -c "\dconfig+ (listen_addresses|wal_keep_size)"

Comme vous pouvez le voir dans la colonne context, listen_addresses nécessite un redémarrage.

# systemctl restart postgresql@16-main  # redémarage
# systemctl status postgresql@16-main   # contrôle

Créer la première instance secondaire sur le serveur p2, par copie à chaud du répertoire de données avec pg_basebackup.
Penser à copier les fichiers de configuration

Nous allons réutiliser le squelette de l’instance main pour créer l’instance secondaire.

Arrêter l’instance main :

# systemctl stop postgresql@16-main

$ pg_lsclusters 16 main

Ver Cluster Port Status Owner    Data directory              Log file
16  main    5432 down   postgres /var/lib/postgresql/16/main /var/log/postgresql/postgresql-16-main.log

Supprimer le répertoire de données :

$ rm -Rf /var/lib/postgresql/16/main

Créer le fichier ~/.pgpass :

$ echo '*:*:*:repli:confidentiel' >> ~/.pgpass
$ chmod 600 ~/.pgpass

Utiliser pg_basebackup pour créer l’instance secondaire :

$ pg_basebackup --pgdata /var/lib/postgresql/16/main \
                --progress \
                --write-recovery-conf \
                --checkpoint fast \
                --host 10.0.0.21 \
                --username repli

23110/23110 kB (100%), 1/1 tablespace

L’option -R ou --write-recovery-conf de pg_basebackup a préparé la configuration de la mise en réplication en créant le fichier standby.signal ainsi qu’en configurant primary_conninfo dans le fichier postgresql.auto.conf (dans les versions antérieures à la 11, il renseignerait recovery.conf) :

$ cat /var/lib/postgresql/16/main/postgresql.auto.conf

primary_conninfo = 'user=repli passfile=''/var/lib/postgresql/.pgpass''
                    channel_binding=prefer host=10.0.0.21 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'

$ file /var/lib/postgresql/16/main/standby.signal

/var/lib/postgresql/16/main/standby.signal: empty

Il faut répercuter les modifications du pg_hba.conf et du postgresql.conf en les adaptant si nécessaire sur la nouvelle instance :

$ grep -E "host.*replication" /etc/postgresql/16/main/pg_hba.conf

host    replication     repli           10.0.0.21/32            scram-sha-256

$ grep -E "^(listen_addresses|wal_keep_size)" /etc/postgresql/16/main/postgresql.conf

listen_addresses = '*'
wal_keep_size = '256MB'

Démarrer l’instance sur p2 et s’assurer que la réplication fonctionne bien avec ps.
Tenter de se connecter au serveur secondaire.
Créer quelques tables pour vérifier que les écritures se propagent du primaire au secondaire.

Il ne reste désormais plus qu’à démarrer l’instance secondaire :

# systemctl start  postgresql@16-main   # redémarrage
# systemctl status  postgresql@16-main  # contrôle

La commande ps suivante permet de voir que les deux serveurs sont lancés :

$ ps -o pid,cmd fx

Voici le résultat de la commande sur le seveur p2 :

 PID CMD
4247 /usr/lib/postgresql/16/bin/postgres -D /var/lib/postgresql/16/main -c config_file=/etc/postgresql/16/main/postgresql.conf
4248  \_ postgres: 16/main: checkpointer
4249  \_ postgres: 16/main: background writer
4250  \_ postgres: 16/main: startup recovering 000000010000000000000003
4251  \_ postgres: 16/main: walreceiver streaming 0/3000148

La même commande exécutée sur le serveur p1 :

 PID CMD
4084 /usr/lib/postgresql/16/bin/postgres -D /var/lib/postgresql/16/main -c config_file=/etc/postgresql/16/main/postgresql.conf
4085  \_ postgres: 16/main: checkpointer
4086  \_ postgres: 16/main: background writer
4088  \_ postgres: 16/main: walwriter
4089  \_ postgres: 16/main: autovacuum launcher
4090  \_ postgres: 16/main: logical replication launcher
4252  \_ postgres: 16/main: walsender repli 10.0.0.22(48570) streaming 0/3000148

Nous avons bien les deux processus de réplication en flux wal sender et wal receiver.

Créons quelques données sur le principal et assurons-nous qu’elles soient transmises au secondaire :

$ createdb b1

$ psql b1

psql (16.1)
Type "help" for help.

b1=# CREATE TABLE t1(id integer);

CREATE TABLE

b1=# INSERT INTO t1 SELECT generate_series(1, 1000000);

INSERT 0 1000000

En exécutant la commande suivante sur les serveurs p1 et p2, on constate que le flux a été transmis :

$ ps -o pid,cmd fx | egrep "(startup|walsender|walreceiver)"

Sur p1 :

4252  \_ postgres: 16/main: walsender repli 10.0.0.22(48570) streaming 0/71A9E50

Sur p2 :

4250  \_ postgres: 16/main: startup recovering 000000010000000000000007
4251  \_ postgres: 16/main: walreceiver streaming 0/71A9E50

Essayons de nous connecter au secondaire et d’exécuter quelques requêtes :

$ psql b1

psql (16.1)
Type "help" for help.

b1=# SELECT COUNT(*) FROM t1;

  count
---------
 1000000

b1=# CREATE TABLE t2(id integer);

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

On peut se connecter, lire des données, mais pas écrire.

Le comportement est visible dans le log de l’instance secondaire dans le fichier /var/log/postgresql/postgresql-16-main.log :

... LOG:  database system is ready to accept read only connections

PostgreSQL indique bien qu’il accepte des connexions en lecture seule.

Promotion de l’instance secondaire

En respectant les étapes de vérification de l’état des instances, effectuer une promotion contrôlée de l’instance secondaire.

Arrêt de l’instance primaire et vérification de son état sur p1 :

# systemctl stop postgresql@16-main

$ /usr/lib/postgresql/16/bin/pg_controldata -D /var/lib/postgresql/16/main \
| grep -E '(cluster)|(REDO)'

Database cluster state:               shut down
Latest checkpoint's REDO location:    0/71A9F38
Latest checkpoint's REDO WAL file:    000000010000000000000007

Vérification de l’instance secondaire sur p2 :

$ psql -c 'CHECKPOINT'

$ /usr/lib/postgresql/16/bin/pg_controldata -D /var/lib/postgresql/16/main \
| grep -E '(cluster)|(REDO)'

Database cluster state:               in archive recovery
Latest checkpoint's REDO location:    0/71A9F38
Latest checkpoint's REDO WAL file:    000000010000000000000007

L’instance principale est bien arrêtée, l’instance secondaire est bien en archive recovery et les deux sont bien synchronisées.

Promotion de l’instance secondaire sur p2 :

$ psql -c 'SELECT pg_promote()'

 pg_promote
------------
 t
(1 row)

Tenter de se connecter au serveur secondaire fraîchement promu.
Les écritures y sont-elles possibles ?

Connectons-nous à ce nouveau primaire et tentons d’y insérer des données :

$ psql b1
psql (16.1)
Type "help" for help.

b1=# CREATE TABLE t2(id integer);

CREATE TABLE

b1=# INSERT INTO t2 SELECT generate_series(1, 1000000);

INSERT 0 1000000

Les écritures sont désormais bien possible sur cette instance.

Retour à la normale

Reconstruire l’instance initiale sur p1 comme nouvelle instance secondaire en repartant d’une copie complète de p2 en utilisant pg_basebackup.

Afin de rétablir la situation, nous pouvons réintégrer l’ancienne instance primaire en tant que nouveau secondaire (sur p1). Pour ce faire, nous devons re-synchroniser les données. Utilisons pg_basebackup comme précédemment après avoir mis de côté les fichiers de l’ancien primaire :

$ mv /var/lib/postgresql/16/main /var/lib/postgresql/16/main.old

$ pg_basebackup --pgdata /var/lib/postgresql/16/main \
                --progress \
                --write-recovery-conf \
                --checkpoint fast \
                --host 10.0.0.22 \
                --username repli

101537/101537 kB (100%), 1/1 tablespace

Vérifier la présence du fichier standby.signal. Contrôler postgresql.auto.conf (qui contient potentiellement deux lignes primary_conninfo !). Le fichiers de configuration de l’instance n’ayant quant à eux pas été modifiés, il n’est pas nécessaire de remodifier la configuration.

$ file /var/lib/postgresql/16/main/standby.signal

$ cat /var/lib/postgresql/16/main/postgresql.auto.conf

primary_conninfo = 'user=repli passfile=''/var/lib/postgresql/.pgpass''
                    channel_binding=prefer host=10.0.0.22 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'

Démarrer cette nouvelle instance.

Démarrer le service :

# systemctl start postgresql@16-main     # Démarrage
# systemctl status postgresql@16-main    # Contrôle

Vérifier que les processus adéquats sont bien présents, et que les données précédemment insérées dans les tables créées plus haut sont bien présentes dans l’instance reconstruite.

Les processus adéquats sont bien présents :

$ ps -o pid,cmd fx | egrep "(startup|walsender|walreceiver)"

Sur p1 (secondaire) :

27488  \_ postgres: 16/main: startup recovering 00000002000000000000000C
27489  \_ postgres: 16/main: walreceiver streaming 0/C000060

Sur p2 (primaire) :

19319  \_ postgres: 16/main: walsender repli 10.0.0.21(45332) streaming 0/C000060

En nous connectant à la nouvelle instance secondaire (p2), vérifions que les données précédemment insérées dans la table t2 sont bien présentes :

$ psql b1

psql (16.1)
Type "help" for help.

b1=# SELECT COUNT(*) FROM t2;

  count
---------
 1000000

Inverser à nouveau les rôles des deux instances afin que p2 redevienne l’instance secondaire, cette fois-ci effectuer la remise en service de l’ancienne instance primaire sans reconstruction.

Afin que l’instance p1 redevienne primaire et celle sur p2 secondaire, on peut ré-appliquer la procédure de promotion vue précédemment dans l’autre sens.

Arrêt de l’instance primaire (p2) et vérification de son état :

# systemctl stop postgresql@16-main

$ /usr/lib/postgresql/16/bin/pg_controldata -D /var/lib/postgresql/16/main/ \
| grep -E '(cluster)|(REDO)'

Database cluster state:               shut down
Latest checkpoint's REDO location:    0/C000148
Latest checkpoint's REDO WAL file:    00000002000000000000000C

Vérification de l’instance secondaire sur p1 :

$ psql -c 'CHECKPOINT;'
$ /usr/lib/postgresql/16/bin/pg_controldata -D /var/lib/postgresql/16/main/ \
| grep -E '(cluster)|(REDO)'

Database cluster state:               in archive recovery
Latest checkpoint's REDO location:    0/C000148
Latest checkpoint's REDO WAL file:    00000002000000000000000C

L’instance principale est bien arrêtée, l’instance secondaire est bien en archive recovery et les deux sont bien synchronisées.

Promotion de l’instance secondaire :

$ psql -c 'SELECT pg_promote()'

 pg_promote 
------------
 t
(1 row)

Afin que p2 redevienne l’instance secondaire, créer le fichier standby.signal, démarrer le service et vérifier que les processus adéquats sont bien présents :

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

#  systemctl start postgresql@16-main

$  ps -o pid,cmd fx | egrep "(startup|walsender|walreceiver)"

Sur p1 :

27562  \_ postgres: 16/main: walsender repli 10.0.0.22(34562) streaming 0/C0002D8

Sur p2 :

19371  \_ postgres: 16/main: startup recovering 00000003000000000000000C
19373  \_ postgres: 16/main: walreceiver streaming 0/C0002D8

Réplication Physique : fondamentaux

Sur ce document

Licence Creative Commons CC-BY-NC-SA

Marques déposées

Versions de PostgreSQL couvertes

Réplication physique : rappels

Introduction

Au menu

Réplication par streaming

Mise en place de la réplication par streaming

Serveur primaire (1/2) - Configuration

Serveur primaire (2/2) - Authentification

Serveur secondaire (1/4) - Copie des données

Serveur secondaire (2/4) - Fichiers de configuration

Serveur secondaire (2/4) - Paramètres

Serveur secondaire (3/4) - Démarrage

Processus

Promotion

Au menu

Attention au split-brain !

Vérification avant promotion

Promotion du standby : méthode

Promotion du standby : déroulement

Opérations après promotion du standby

Retour à l’état stable

Retour à l’état stable, suite

Conclusion

Installation de PostgreSQL depuis les paquets communautaires

Travaux pratiques

Sur Rocky Linux 8 ou 9

Réplication asynchrone en flux avec un seul secondaire

Promotion de l’instance secondaire

Retour à la normale

Sur Debian 12

Réplication asynchrone en flux avec un seul secondaire

Promotion de l’instance secondaire

Retour à la normale

Travaux pratiques (solutions)

Sur Rocky Linux 8 ou 9

Sur Debian 12