Un peu de contexte
PostgreSQL est un système de gestion de base de données relationnelle et objet. Le projet était initialement nommé POSTGRES en référence à la base de données Ingres. Il a démarré en 1985. En 1995, les premières fonctionnalités SQL ont été ajoutées. Le projet sera renommé PostgreSQL l’année suivante.
PostgreSQL est un logiciel open source avec sa propre licence PostgreSQL dont le code est hébergé sur le site de PostgreSQL. La dernière version majeure publiée est la version 16. Les versions 12, 13, 14 et 15 sont encore maintenues.
La communauté francophone est très active et compte parmi ses participants l’association PostgreSQLFr. Cette association assure notamment la coordination de la traduction de la documentation et l’hébergement d’un forum qui permet d’échanger à propos de PostgreSQL. Elle fait aussi partie des organisateurs de PGDay France, le rendez-vous annuel de la communauté PostgreSQL.
Chez Worteks nous utilisons PostgreSQL comme base de données de plusieurs applications, en particulier NextCloud et LemonLDAP::NG. Ces applications étant souvent déployées en haute-disponibilité, nous sommes en veille constante sur les possibilités techniques de fonctionnement en cluster de cette base de données.
Dans ce billet de blog, nous allons montrer comment mettre en place un cluster bi-directionnel natif dans PostgreSQL 16. Nous allons utiliser la réplication logique pour créer une configuration maître-maître. L’objectif est d’avoir deux serveurs capables d’accepter des commandes d’écriture.
La version PostgreSQL 16 est sortie le 14 septembre 2023 et sera maintenue jusqu’au 9 octobre 2028. Elle contient de nombreuses améliorations, en particulier concernant la réplication logique qui nous intéresse ici.
La documentation PostgreSQL définit la réplication logique comme “[…] une méthode permettant de répliquer des données au niveau objet ainsi que les modifications apportées à ces objets, ceci basé sur leur identité de réplication (habituellement la clé primaire)”. Elle se différencie de la réplication physique car elle ne copie pas “octet par octet” les données du serveur mais peut juste copier une table ou même une colonne.
La réplication logique par défaut nécessite un serveur éditeur (publisher) et un serveur abonné (subscriber). L’abonné récupère les données publiées par l’éditeur et construit ainsi ses données. La réplication logique est disponible en natif depuis PostgreSQL 10.
Les améliorations citées ci-dessus permettent aujourd’hui de configurer la réplication logique des serveurs dans les deux sens et ainsi d’obtenir deux serveurs maîtres.
Mise en place du cluster PostgreSQL
À la fin de ce tutoriel, nous aurons deux serveurs en cluster avec PostgreSQL installé ainsi qu’un serveur applicatif qui sert à interroger le cluster. Cette troisième machine représentera l’application qui interagit avec la base de données (LemonLDAP, Nextcloud, la station spatiale internationale, etc…). Les adresses IP sont les suivants:
- Serveur PostgreSQL 1 - premier maître :
192.168.2.24; - Serveur PostgreSQL 2 - deuxième maître :
192.168.2.65; - Serveur applicatif:
192.168.2.54
L’installation se fera dans des machines Rocky Linux 8 nouvelles. Toutefois, il suffit d’adapter les commandes pour une distribution de la famille Debian.
L’utilisateur par défaut sera le super-utilisateur root. Lorsqu’il faudra changer d’utilisateur, les instructions l’expliciteront.
Nous allons installer et activer la version 16 de PostgreSQL sur chacun des serveurs :
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
dnf install postgresql16-server -y
/usr/pgsql-16/bin/postgresql-16-setup initdb
systemctl enable postgresql-16
systemctl start postgresql-16
La première commande ajoute les dépôts PostgreSQL. Ces dépôts donnent accès aux versions plus récents de PostgreSQL. La deuxième désactive les modules par défaut de la distribution. Les quatre dernières commandes installent et configurent PostgreSQL. PostgreSQL est activé par défaut, c’est-à-dire qu’il sera actif dès le démarrage du système.
Il faut éditer le fichier de configuration pour permettre l’accès à distance et la réplication logique.
vim /var/lib/pgsql/16/data/postgresql.conf
#--------------------------------------------#
listen_addresses = '*'
wal_level = logical
max_worker_processes = 10
Les valeurs de max_worker_processes, max_replication_slots, max_wal_senders sont à adapter selon le contexte (nombre de cores, utilisation du serveur dédié à PostgreSQL, etc…). Pour plus de détails, il faudra consulter la documentation PostgreSQL .
Il faut aussi éditer le fichier d’accès pour autoriser les connexions distantes :
vim /var/lib/pgsql/16/data/pg_hba.conf
#--------------------------------------------#
# [....]
host database_test pgreplication 192.168.2.24/32 scram-sha-256
host database_test pgreplication 192.168.2.44/32 scram-sha-256
host database_test user_distant 192.168.2.54/24 scram-sha-256
Puis, redémarrer PostgreSQL pour prendre en compte les changements :
systemctl restart postgresql-16.service
Il ne faut pas oublier d’ouvrir le pare-feu et l’écoute sur les ports :
firewall-cmd --permanent --add-service=postgresql
firewall-cmd --reload
Ensuite, il faut configurer les utilisateurs locaux de PostgreSQL. Pour accéder à PostgreSQL en local, nous utilisons la commande suivante :
su - postgres -c psql
Pour ce tutoriel, nous allons créer la base de données database_test et un utilisateur pgreplication pour la réplication. Nous créerons également un utilisateur user_distant pour générer les données fictives et vérifier la réplication.
-- Pour des questions de sécurité
ALTER USER postgres WITH ENCRYPTED PASSWORD 'postgres1';
-- L’utilisateur qui permettra de faire la réplication
CREATE ROLE pgreplication LOGIN REPLICATION PASSWORD 'pgrepuser1';
GRANT ALL ON ALL TABLES IN SCHEMA public TO pgreplication;
-- L'utilisateur pour user_distant
CREATE USER user_distant WITH ENCRYPTED PASSWORD 'user1';
CREATE DATABASE database_test OWNER user_distant;
GRANT ALL PRIVILEGES ON DATABASE database_test TO user_distant;
GRANT ALL PRIVILEGES ON DATABASE database_test TO pgreplication;
\q
Nous allons maintenant créer des données sur le serveur PostgreSQL 1. Nous commencerons par la table test et quelques valeurs. Il est important d’utiliser une clé primaire (PRIMARY KEY) par table. C’est un point à ne pas oublier si ce tutoriel est appliqué à des base de données déjà existantes. La méthode de la réplication logique ne marchera pas sur des tables sans clé primaire :
\c database_test
-- Créer la table.
CREATE TABLE table_test (
id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
title varchar(40) NOT NULL,
name varchar(40) NOT NULL);
-- Peupler la table.
INSERT INTO table_test VALUES
('001', 'El ultimo lector', 'Toscana'),
('002', 'Le Comte de Monte-Cristo', 'Dumas'),
('003', 'Ulysse', 'Joyce');
-- Création de la publication de la table
CREATE PUBLICATION publication_test1 FOR ALL TABLES;
Pour le serveur PostgreSQL 2, nous allons d’abord l’abonner au serveur PostgreSQL 1. Une fois cette abonnement validé, nous ferons l’abonnement dans le sens inverse pour avoir la configuration bi-directionnelle.
Les pré-requis qui permettent de s’abonner à une table ou base de données sont les schémas. Ici nous utilisons une base vide, la marche à suivre pour copier les schémas d’une base de données déjà existante est expliquée dans la section Extra. Dans notre cas, nous créons la table dans le deuxième serveur puis nous nous y abonnons.
\c database_test
-- Peupler la table.
CREATE TABLE table_test (
id integer PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
title varchar(40) NOT NULL,
name varchar(40) NOT NULL);
-- Premier abonné
CREATE SUBSCRIPTION subscription_au_serveur1
CONNECTION 'host=192.168.2.24 port=5432 user=pgreplication dbname=database_test password=pgrepuser1'
PUBLICATION publication_test1
WITH (copy_data=true, origin=none);
-- Subscription de l'abonné
CREATE PUBLICATION publication_test2 FOR ALL TABLES;
L’abonné se connecte à la base de données distante avec l’utilisateur pgreplication. Ensuite, il écrit les modifications via le propriétaire de la subscription. Le propriétaire est par défaut l’utilisateur qui a créé la subscription. Il faut s’assurer que cet utilisateur ait des droits suffisants, notamment le privilège pg_create_subscription. Par défaut, il s’agit de l’utilisateur postgres.
Le secret de la réplication logique bi-directionnelle est dans l’avant dernière ligne : WITH (copy_data=true, origin=none);. Cette ligne contient les paramètres de la subscription.
L’option copy_data=true défini si le serveur importera les données existantes dans l’éditeur lors de la première connexion. Pour ce premier abonnement, nous souhaitons peupler la table. Toutefois, lors de l’abonnement du serveur PostgreSQL 1 vers PostgreSQL 2, nous le désactiverons.
Finalement, le paramètre origin=none indique à l’éditeur de ne pousser que les changements qui n’ont pas d’origine.
Le valeur par défaut est any, dans ce cas l’éditeur pousse tous les changements, quelque soit l’origine. Dans le cas d’une réplication bi-directionnelle cette option produit une boucle nommée transactional loopback. En d’autres termes, les modifications poussées par l’éditeur seront répercutés vers lui, créant ainsi une boucle infinie et donc une erreur.
La réplication peut être vérifiée en cherchant les entrées créées dans le PostgreSQL 1 sur le serveur PostgreSQL 2.
SELECT * FROM table_test;
id | title | name
----+--------------------------+---------
1 | El ultimo lector | Toscana
2 | Le Comte de Monte-Cristo | Dumas
3 | Ulysse | Joyce
(3 rows)
Pour finir, nous nous abonnons depuis le serveur PostgreSQL 1 au serveur PostgreSQL 2 :
\c database_test
-- Deuxième abonné
CREATE SUBSCRIPTION subscription_au_serveur2
CONNECTION 'host=192.168.2.44 port=5432 user=pgreplication dbname=database_test password=pgrepuser1'
PUBLICATION publication_test2
WITH (copy_data=false, origin=none);
Vérification de la réplication entre les deux serveurs
La vérification la plus simple et directe est de suivre les différentes modifications dans les tables, par exemple via un SELECT * FROM table_test;. Les logs de PostgreSQL permettent aussi de savoir si la réplication existe.
Il existe des étapes intermédiaires qui permettent de déboguer en cas de problèmes. Les étapes de la réplication logique sont :
- L’envoi des
WALde l’éditeur - La réception côté abonné puis leur application
Pour vérifier au niveau de l’éditeur, le process walsender doit exister:
ps aux | grep walsender
Il est aussi possible de vérifier la réplication depuis psql:
su - postgres -c psql
\x
SELECT * FROM pg_stat_replication;
Pour l’abonné, la vérification de la réception se fait via les process du système d’exploitation soit via psql :
ps aux | grep 'logical replication'
su - postgres -c psql
\x
SELECT * FROM pg_stat_subscription;
Limites de la méthode
La réplication logique bi-directionnelle est une méthode très intéressante et assez utile. Toutefois, cela n’est pas toujours la méthode adéquate ou optimale.
Il est important de savoir que la méthode à besoin d’une supervision ou au minimum d’un checkup régulier. En effet, lorsqu’un des abonnés rencontre une erreur, la réplication s’arrête. Dans ce cas, il faudra regarder les logs. Ces conflits sont abordés dans la documentation sous la section 31.5.Conflits. Par exemple, lorsque la clé primaire est dupliquée en local et à distance :
024-04-24 12:14:59.993 CEST [4973] ERROR: duplicate key value violates unique constraint "table_test_pkey"
2024-04-24 12:14:59.993 CEST [4973] DETAIL: Key (id)=(1) already exists.
2024-04-24 12:14:59.993 CEST [4973] CONTEXT: processing remote data for replication origin "pg_16399" during message type "INSERT" for replication target relation "public.table_test" in transaction 816, finished at 0/1A09DB0
2024-04-24 12:14:59.994 CEST [2731] LOG: background worker "logical replication worker" (PID 4973) exited with exit code 1
Cela peut se produire régulièrement si la clé primaire est incrémentale, par exemple si les deux serveurs sont sollicités en même temps.
Il faut donc envisager les erreurs et les prévenir. Il faut aussi pouvoir les repérer quand elles arrivent et les corriger avant que ce ne soit trop tard.
Conclusion
PostgreSQL continue de s’améliorer. Par exemple, en dix ans, la latence à été divisée par deux !. Toutefois, de grands pouvoirs impliquent de grandes complexités ! PostgreSQL n’est pas le logiciel le plus simple à prendre en main lors de sa première utilisation. Mais grâce à la liberté des outils Open Source nous pouvons partager nos connaissances, comme ce tutoriel, pour que vous puissiez créer votre cluster le plus facilement possible. Chez Worteks nous utilisons PostgreSQL et nous souhaitons que vous puissiez l’utiliser aussi.
N’oubliez pas de consulter la documentation qui est très complète en français !
Merci pour votre lecture.
Extra
Pour ce tutoriel, nous avons pris le cas le plus simple : une architecture pensée dès le début en mode cluster maître-maître. En effet, il est toujours plus simple de partir de zéro plutôt que de s’adapter à l’existant. Malheureusement, ce n’est pas toujours possible.
Dans le cas où la base de données existe déjà mais pas en mode cluster, la deuxième base de données doit être initialisée avec le schéma de la première base. Pour ce faire, nous devons dumper la base et l’ajouter dans le deuxième serveur. Voici un exemple d’application :
pg_dumpall --schema-only --dbname 'host=localhost dbname=database_test user=user_distant' > db_structures.out
scp db_structures.out rocky@192.168.2.65:
Ensuite, nous devons importer ce fichier dans la deuxième base de données :
mv [path/to/file]/db_structures.out /var/lib/pgsql/
chown postgres:postgres /var/lib/pgsql/db_structures.out
su - postgres -c 'psql < db_structures.out 2> /tmp/errors.log'
# Verifier s'il y a eu des erreurs lors de la création du schema
cat /tmp/errors.log
# Seulement l'erreur suivant devrait apparaître
ERROR: role "postgres" already exists
