PostgreSQL propose un système de réplication native. Répliquer ses données permet de synchroniser plusieurs sources de données, d’améliorer la résilience de ses systèmes et d’augmenter la tolérance aux pannes.
Dans cet article, nous verrons comment mettre en place une telle réplication depuis une instance PostgreSQL on-premise vers une instance Azure PostgreSQL Flexible Server.
Motivations
Une solution éditeur repose sur une base de données PostgreSQL on-premise. Cette base est sensible et fragile, supportant mal la charge de lecture. L’objectif est donc d’ouvrir le service à un plus grand nombre de consommateurs sans impacter l’application actuelle.
Nous avons ici fait le choix de créer un réplica accessible en lecture, et de bénéficier de la scalabilité et de la robustesse proposée par la solution managée d’Azure. Le faible effort d’administration est aussi pris en considération.
Réplication logique PostgreSQL
Fonctionnement
La réplication des données native PostgreSQL repose sur la réplication des WAL. Sigle anglais signifiant “Write-Ahead Logging”. Le principe de base est que toute modification en base doive être loggée avant d’être poussée en base. Cela permet une garantie forte de l’intégrité des données. Ici, en propageant ces même WAL à d’autres nœuds PostgreSQL, les données seront de facto répliquées.
Concrètement, le nœud maître va créer un certain nombre de publications qui peuvent couvrir toute ou partie d’un schéma. Les nœuds esclaves vont quant à eux créer des souscriptions qui vont se connecter au maître afin de tirer les WAL en continu. Une même publication peut être souscrite autant de fois que nécessaire, un esclave peut souscrire à autant de publication qu’il le souhaite, et un nœud peut à la fois être esclave et maître.
Attention, il est bon de rappeler que le schéma n’est pas répliqué. Tout changement sur ce dernier devra être répliqué manuellement.
Schéma de test
Le schéma n’étant pas répliqué, nous allons ici le définir avec un exemple tout simple. Il faudra le pousser sur le nœud maître et le nœud esclave.
CREATE TABLE ma_table (
id int PRIMARY KEY,
name varchar(31) NOT NULL
);
Mettre en place la réplication
Mettons en place la réplication de nos données. Nous commencerons par le maître, puis configurerons le nœud esclave.
Côté maître des données
Configuration initiale
Avant tout, il vous faudra configurer votre service PostgreSQL de la bonne façon. Il suffit d’adapter les fichiers de configuration du maître afin de permettre la réplication. Par défaut, les fichiers de configuration se trouve dans /etc/postgresql/<NUMÉRO DE VERSION MAJEURE>/main/. La version 16 est utilisée ci-dessous.
# /etc/postgresql/16/main/ pg_hba.conf -- Accès à la DB à distance.
# ...
host all all 1.2.3.4/32 password
host replication all 1.2.3.4/32 passwordIci, la plage fermée 1.2.3.4/32 permet de cibler une adresse IP unique. Il devra s’agir de celle du service managé Azure. En cas de doute, vous pouvez toujours débugger la connectivité en replaçant la plage par 0.0.0.0/0, temporairement.
# /etc/postgresql/16/main/ postgresql.conf -- Configuration système
# ...
listen_addresses = '*'
wal_level = logical
max_replication_slots = 100
max_wal_senders = 100
# ...Le WAL level affecté à “logical” permet de conserver les logs logiques, permettant ainsi la réplication. Le nombre de slots de réplication est à changer en fonction de ce qui est attendu sur votre charge. En fonction de votre politique de sécurité et de votre configuration réseau, la propriété listen_addresses peut être amenée à changer. Il faudra cependant bien veiller à ce que l’esclave puisse accéder au maître.
Setup côté maître
Une fois le service redémarré afin de prendre en compte les changements de configuration, nous allons pouvoir mettre en place la réplication.
-- Création de la publication ici, pour toutes les tables de notre database.
CREATE PUBLICATION pub_test FOR ALL TABLES;
Il nous faudra créer un utilisateur qui aura accès en lecture aux tables concernées par la réplication. Ce sera avec cet utilisateur ci que les nœuds esclaves accéderont aux données.
-- Création d'un user "remoteuser" avec un mot de passe basique.
CREATE USER remoteuser WITH PASSWORD "postgres";
ALTER USER remoteuser REPLICATION;
-- Ne pas oublier de donner accès aux tables à cet user
GRANT SELECT ON ALL TABLES IN SCHEMA public TO remoteuser;
Désormais, nous pouvons insérer quelques données dans la table de test :
INSERT INTO ma_table VALUES (1, 'michel'), (2, 'fred');
Un mot sur le réseau
En fonction de votre réseau, il est possible que vous deviez autoriser la connectivité entrante sur le port de PostgreSQL (5432 par défaut). Vérifiez que le port est bien en écoute.
sudo netstat -tunlp
# Vous devriez alors avoir :
#> tcp 0 0 0.0.0.0:5432 0.0.0.0:* LISTEN 693/postgres
#> tcp6 0 0 :::5432 :::* LISTEN 693/postgres
En cas de soucis, essayez de netcat le port depuis votre machine locale.
Si vous êtes sur un réseau Azure (avec une VM par exemple), n’oubliez-pas de vérifier les NSG de notre virtual-network.
Côté nœud esclave
Nous passons maintenant à la partie Azure.
Si la solution managée a un moyen très simple de devenir esclave d’une autre instance Azure PostgreSQL Flexible serveur, ça n’est pas aussi simple pour un réplica depuis du on-premise.
Configuration initiale
Une fois votre flexible-serveur créé et en ligne, vous devrez encore adapter quelques paramètres :
| Propriété | Valeur | Commentaire |
|---|---|---|
| wal_level | LOGICAL | Permet d’ingérer les WAL |
| max_parallel_workers | 12 | Nombre maximum de workers |
| max_logical_replication_workers | 10 | Nombre de workers affectés à la réplication. La proportion exacte à utilisée n’est pas très claire |
Pour la réplication, le nombre de workers, bien que très peu documenté, est l’élément clé. En cas de problème avec la réplication (et comme vu plus tard) n’hésitez-pas à jouer avec ce paramétrage.
Création de la souscription de réplication Postgresql
Une fois le service redémarré, vous pouvez ouvrir une session SQL et créer une souscription. Ici, nous partons du principe que vous êtes dans une database du même homonyme à celle du maître.
N’oubliez pas de créer le schéma, sans quoi la commande ne pourra pas aboutir.
-- Ici, le nom de la database est "test-db".
-- De plus, l'IP du service on-premise est ici "5.6.7.8". La résolution DNS est évidemment possible.
CREATE SUBSCRIPTION sub_test
CONNECTION 'postgresql://remoteuser:postgres@5.6.7.8:5432/test-db?application_name=sub_test'
PUBLICATION pub_test;
À présent, les données sont synchronisées du maître vers l’esclave.
Vérification avec la table test
Sur l’esclave, on remarque que la table de test a bien été remplie lors de la création de la souscription.
SELECT * FROM ma_table;
-- 1, michel
-- 2, fred
Désormais, si vous effectuez un INSERT ou un DELETE sur la table, le changement sera propagé sur le nœud Azure.
Débugger la réplication
Si, cependant, la table est toujours vide et que vous n’avez pas eu de message d’erreur : voici mon retour d’expérience.
Problèmes communs
Voici déjà quelques erreurs faciles à rencontrer :
- Lors de la création de la souscription : schéma invalide.
- Le schéma doit être répliqué sur la base de données ciblée.
- Lors de la création de la souscription : connexion impossible
- Vérifiez s’il est possible de se connecter à la cible à l’aide du connection-string fourni dans la souscription.
- Impossible de supprimer la souscription alors qu’elle existe
- Vous êtes probablement sur la mauvaise base.
Problèmes discrets
Maître ou esclave, il va falloir fouiller les logs applicatifs pour retrouver l’origine du problème.
Côté maître
Si, lors d’un insert ou d’une nouvelle souscription, les logs indiquent des problèmes de permission, alors il est probable que l’utilisateur donnant l’accès aux tables n’ait soit pas le rôle REPLICATION, ou alors n’ait pas les permissions suffisantes pour accéder aux tables de la publication. Dans ce dernier cas, PostgreSQL ne remontera pas d’erreur lors de la création de cette dernière.
Pour vérifier quels sont les slots de réplication actifs, voici une commande utile :
SELECT slot_name, datoid, database, active, active_pid, restart_lsn, confirmed_flush_lsn, wal_status, two_phase, conflicting
FROM pg_replication_slots;
Côté esclave
Pour remonter les logs, il faudra dans un premier temps créer un log-analytics workspace dans lequel pousser les journaux de PostgreSQL.
Dans mon cas, j’ai pu tomber sur ce problème avec les valeurs par défaut :
Comme vous pouvez le voir, les erreurs sont assez explicites une fois trouvées. Il a fallu augmenter le nombre de workers, comme indiqué précédemment.
Voici une commande dans le support d’Azure vous permettant de vérifier l’état de vos slots de réplication.
select b.subname,c.subenabled, c.subslotname, c.subpublications, srrelid::regclass, case srsubstate when 'i' then 'initialize' when 'd' then 'data is being copied' when 's' then 'synchronized' when 'r' then 'ready (normal replication)' end as state, received_lsn, latest_end_lsn from pg_subscription_rel a, pg_stat_subscription b, pg_subscription c where a.srsubid = b.subid and b.subname = c.subname;
-- Avant le fix des workers, la réplication était coincé dans l'étape d'initialisation
-- # subname | subenabled | subslotname | subpublications | srrelid | state | received_lsn | latest_end_lsn
-- # ---------+------------+-------------+-----------------+---------+------------+--------------+----------------
-- # sub1 | t | sub1 | {pub1} | t1 | initialize | 0/19A6BF8 | 0/19A6BF8