La version 10 de PostgreSQL apporte de nombreuses fonctionnalités, dont l’intégration de la réplication logique.

La réplication logique, très répandue, complète les possibilités de PostgreSQL.

La réplication logique, qu’est-ce ?

Dans le domaine des bases de données, on distingue habituellement deux types de réplication : la réplication logique et la réplication physique.

Cette distinction s’applique également avec PostgreSQL.

La réplication physique consiste à répliquer les données d’une instance PostgreSQL au niveau des fichiers de données binaires. Cette technique de réplication ne s’intéresse pas à la nature des données répliquées (leur type ou valeur), mais fait simplement en sorte que l’ensemble d’une instance et des transactions exécutées sur cette instance soient répliqués.

Par opposition, la réplication logique permet de cibler une partie des données, en travaillant au même niveau que les transactions : on connaît donc la nature des données, en particulier, la table de provenance de la donnée.

On peut donc dire que la réplication logique dans PostgreSQL est de plus « haut niveau » que la réplication physique. Elle permet une plus grande finesse dans le choix des données répliquées.

La granularité de la donnée, et le fait de se situer au niveau logique, permettent de répliquer une ou plusieurs tables d’une instance à une autre, y compris dans des versions différentes de PostgreSQL.

En conséquence, il est possible de concevoir des topologies de réplication logique où une instance peut recevoir plusieurs tables distinctes, les requêter, et stocker des résultats localement, ce qui n’est pas possible avec la réplication physique.

Les possibilités avant la v10

La réplication logique est déjà possible avec PostgreSQL depuis plus d’une dizaine d’années, en utilisant des outils externes comme Slony ou Londiste.

Ces outils ajoutent des fonctionnalités dans PostgreSQL, et nécessitent des démons externes supplémentaires, ainsi que des outils d’administration spécifiques. Dans les faits, ces outils impliquent une double écriture des données, en utilisant un « trigger » pour réaliser cette double écriture, ce qui peut s’avérer pénalisant dans certains cas. En réalité, ces outils sont tout à fait stables et performants.

Historique de la réplication logique

Depuis la version 8 de PostgreSQL, il est possible de s’appuyer sur les fichiers WAL pour sauvegarder, restaurer et répliquer les données (warm standby).

La branche 9 ajoute la possibilité pour une instance répliquée de se connecter à l’instance primaire pour récupérer les transactions au fil de l’eau.

La version 9.3 permet de lancer des « Custom Background Workers », soit des démons spécifiques, attachés à l’instance PostgreSQL.

La version 9.4 ajoute la notion de décodage logique : le contenu des journaux de transactions peut être décodé pour retrouver l’information qu’il contient.

À partir de ces nouveautés, il est possible de développer un outil de réplication logique. C’est ce que propose l’extension pglogical : de la réplication dont la granularité est à la table, qui s’appuie sur les journaux de transactions, et qui est pilotée par des démons attachés à l’instance PostgreSQL.

Mais un problème de complexité reste avec cette extension. En effet, les commandes d’administration de la réplication sont des appels de fonctions, ajoutées à PostgreSQL lors de l’installation de cette extension.

La nouveauté de la v10

À partir de la version 10, l’extension pglogical est désormais complètement intégrée à PostgreSQL, et de nouvelles commandes SQL permettent de contrôler facilement ce système de réplication.

La réplication logique en résumé :

• intégrée à PostgreSQL ;
• s’appuie sur les journaux de transactions ;
• permet de répliquer une ou plusieurs tables ;
• utilise des commandes SQL.

Pour le moment, cette fonctionnalité n’est utilisable qu’avec la version 10, mais les futures versions permettront d’utiliser cette réplication entre des versions différentes de PostgreSQL.

Limites

Par rapport à l’extension pglogical dont elle provient, il manque encore à la réplication logique intégrée certaines fonctionnalités :

• réplication des séquences ;
• support de TRUNCATE ;
• réplication du schéma de la table et des DDL (qu’il faut faire manuellement) ;
• filtre des lignes et des colonnes ;
• détection et résolution des conflits.

Ces fonctionnalités seront probablement ajoutées dans les futures versions majeures de PostgreSQL.

Utilisation

La réplication logique intégrée apporte deux nouvelles notions : la publication et la souscription.

La publication se trouve du côté d’où viennent les données, et la souscription est du côté où vont les données.

L’instance PostgreSQL qui porte la souscription se connecte à l’instance qui porte la publication.

Dans l’exemple simple utilisé dans cet article, il n’y a qu’une seule instance pour chacun des deux rôles, même s’il est possible de concevoir des topologies plus complexes.

Configuration

Pour utiliser la réplication logique dans PostgreSQL 10, il faut modifier le niveau d’information des WAL, dans le fichier postgresql.conf

wal_level = logical

D’autres paramètres sont utiles, avec des valeurs suffisantes pour démarrer, mais qui doivent être augmentées en fonction du nombre d’instances connectées :

max_wal_sender = 10
max_worker_processes = 8
max_logical_replication_workers = 4
max_sync_workers_per_subscription = 2

Ces modifications nécessitent un redémarrage de l’instance.

Le fichier pg_hba.conf doit aussi être adapté afin de permettre les connexions depuis les répliquas.

Même si ces modifications ne sont nécessaires que du côté de la publication, il est possible d’utiliser des configurations identiques du côté des souscriptions.

Côté « publication »

Dans l’instance où se trouvent les tables que l’on souhaite répliquer, il suffit de lancer la commande suivante :

CREATE PUBLICATION emp_repli
FOR TABLE employees.departments, employees.dept_emp, employees.dept_manager,
         employees.employees, employees.salaries, employees.titles ;

La publication est alors créée pour les tables indiquées.

Côté « souscription »

La réplication ne démarre réellement que lorsqu’une souscription s’abonne à une publication existante. Avant cela, il faut avoir créé les tables, avec une commande de ce type :

pg_dump -h server01 -s -C -t employees.departments -t employees.dept_emp \
     -t employees.dept_manager -t employees.employees -t employees.salaries \
     -t employees.titles -d employees | psql -d employees

La commande précédente extrait les commandes SQL de création des tables depuis la base de données où est la publication, et passe ces commandes dans la base de données de l’instance où est créée la souscription.

Il reste à lancer la souscription :

CREATE SUBSCRIPTION sub_emp_repli CONNECTION 'host=server01 dbname=employees'
   PUBLICATION emp_repli ;

Les données des tables sont alors copiées et les modifications de données sont répliquées.

Une connexion étant ouverte entre les deux instances PostgreSQL, on peut constater l’existence d’un « worker » spécifique dans chacune des instances. Côté publication, on peut voir un walsender, et côté souscription, un logical replication worker.

Supervision

Côté publication, la requête suivante permet d’identifier les tables publiées :

employees=# SELECT pubname, pr.prrelid::regclass
FROM pg_catalog.pg_publication p
JOIN pg_catalog.pg_publication_rel pr ON p.oid = pr.prpubid;
  pubname  |   prrelid
-----------+--------------
 emp_repli | departments
 emp_repli | dept_emp
 emp_repli | dept_manager
 emp_repli | employees
 emp_repli | salaries
 emp_repli | titles
(6 rows)

La vue pg_stat_replication permet également de suivre l’activité des souscriptions connectées.

Côté souscription, la requête suivante permet de lister les réplications en cours :

employees=# select * from pg_subscription;
-[ RECORD 1 ]---+---------------------------
subdbid         | 16384
subname         | sub_emp_repli
subowner        | 10
subenabled      | t
subconninfo     | host=server01 dbname=employees
subslotname     | sub_emp_repli
subsynccommit   | off
subpublications | {emp_repli}

C’est la vue pg_stat_subscription qui permet de suivre l’avancement de la réplication :

employees=# select * from pg_stat_subscription ;
-[ RECORD 1 ]---------+------------------------------
subid                 | 16463
subname               | sub_emp_repli
pid                   | 10114
relid                 |
received_lsn          | 4/BD38B6C8
last_msg_send_time    | 2017-12-21 18:34:15.24688+01
last_msg_receipt_time | 2017-12-21 18:34:15.246919+01
latest_end_lsn        | 4/BD38B6C8
latest_end_time       | 2017-12-21 18:34:15.24688+01

Modification des réplications

Une fois la réplication démarrée, il est possible de modifier les publications et les souscriptions.

On peut, par exemple, ajouter ou enlever une table d’une publication. On peut modifier une souscription en la suspendant, ou en forçant un rafraîchissement des données, par exemple.

Les ordres ALTER PUBLICATION et ALTER SUBSCRIPTION sont alors utilisés.

La commande suivante permet de copier à nouveau les données depuis la publication :

ALTER SUBSCRIPTION sub_emp_repli REFRESH PUBLICATION ;

Et après la v10 ?

Cette fonctionnalité simplifie grandement l’administration de vos données, en permettant la création de topologies logiques jusqu’ici dépendantes d’outils externes parfois délicats à installer et à administrer.

Cependant, certaines fonctionnalités sont absentes dans la version 10, mais déjà présentes dans l’extension pglogical.

Au-delà de cet aspect, l’extension pglogical est un choix incontournable pour les utilisateurs qui utilisent des versions plus anciennes de PostgreSQL, ou qui ont besoin des fonctionnalités citées.

On peut cependant s’attendre à voir de nouvelles fonctionnalités utiles arriver dans les futures versions majeures de PostgreSQL, comme la réplication des séquences ou des DDL.

Un outil de plus pour le DBA PostgreSQL !