0

Réplication logique avec PostgreSQL

twitterlinkedinmail

Comme beaucoup d’autres SGBD, PostgreSQL propose sa solution de réplication « WAL streaming » s’appuyant sur le journal de transactions. Cette réplication est dite physique, car le réplica est mis à jour à l’identique du serveur principal en appliquant physiquement dans les blocs des tables les changements enregistrés dans le journal.

Les SGBD proposent souvent une alternative de réplication dite logique s’appuyant sur l’exécution de l’instruction SQL extraite du serveur principal. L’impact physique sur les fichiers de l’exécution du SQL sur le réplica peut ainsi différer. Un avantage d’une telle réplication logique est aussi de pouvoir restreindre les objets ou parties d’objets répliqués (granularité), voire appliquer une transformation avant chargement, et ainsi mettre en application une architecture de type ETL (Extract, Transform, Load).

PostgreSQL propose la réplication logique depuis la version 10.

Principaux aspects :
• La réplication logique s’appuie sur l’identification des enregistrements à répliquer dans les tables ajoutées à la réplication (usuellement la clé primaire).
• Modèle de publication (publish) et souscription (subscribe).
• Les « subscribers » souscrivent à une ou plusieurs « publications » sur un nœud « publisher ».
• Ce mécanisme permet la réplication en cascade avec un degré variable de complexité.
• La réplication logique autorise des applications et usages variés :
• Réplication partielle et ciblée.
• Déclenchement de triggers sur la cible (subscriber) déclenché au fur et à mesure de l’insertion de nouvelles données répliquées.
• Consolidation de N bases de données sources sur un répliquat (BI/DWH).
• Réplication entre serveurs PostgreSQL de versions distinctes ou/et sur des plateformes et systèmes distincts.
• […]

Nous illustrons cela à partir du schéma de données de benchmark « pgbench » sur une instance PostgreSQL 11 qui sera le « master ». pgbench est un outil utile à des fins de « benchmarks » de toutes sortes avec possibilité de créer quatre tables initiales permettant de simuler des jeux de transactions pré-paramétrées sur ces tables :

postgres=# \c benchdb
You are now connected to database "benchdb" as user "postgres".
benchdb=# \dt
List of relations
Schema | Name | Type | Owner
--------+------------------+-------+-------
public | pgbench_accounts | table | bench
public | pgbench_branches | table | bench
public | pgbench_history | table | bench
public | pgbench_tellers | table | bench
(4 rows)

Sur l’instance « master », nous créons un utilisateur « repuser » dédié à la réplication (nécessitant alors l’attribut REPLICATION) avec les droits sur les tables à publier et la base de données associée :

postgres=# CREATE ROLE repuser REPLICATION LOGIN;
CREATE ROLE
postgres=# \password repuser
Enter new password:
Enter it again:

En tant qu’utilisateur BENCH, propriétaire des tables à publier, donner ses droits à REPUSER pour qu’il soit aussi propriétaire des tables:

$ psql --host=localhost --port=5432 --username=bench --dbname=benchdb
benchdb=> grant bench to repuser;
GRANT ROLE

Donner le droit CREATE sur la base BENCHDB à REPUSER pour qu’il y puisse y créer une publication:

benchdb=# grant create on database benchdb to repuser;
GRANT

Vérification des utilisateurs/rôles en présence :

$ psql --host=localhost --port=5432 --username=repuser --dbname=benchdb
benchdb=> \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+--------
bench | | {}
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
repuser | Replication | {bench}

Nous disposons d’une seconde instance qui jouera le rôle de réplica. Celle-ci ne contient aucune des tables présentes dans le master.
Nous y créons un rôle « report » possédant une base de données « benchrpt » (allusion à une éventuelle finalité de « reporting » des données répliquées) :

postgres=# create role report login ;
CREATE ROLE
postgres=# \password report;
Enter new password:
Enter it again:
postgres=# create database benchrpt owner report encoding 'UTF8';
CREATE DATABASE

Le rôle « report » doit recevoir l’attribut « superuser » pour être autorisé à créer des souscriptions. Ainsi connecté en tant que « postgres » :

$ psql
postgres=# alter user report superuser;
postgres=# \du
List of roles
Role name | Attributes | Member of
-----------+------------------------------------------------------------+-------
postgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}
report | Superuser | {}

Sur l’instance « master », modifier le niveau de WAL en le passant en « logical » :

# wal_level = replica
wal_level = logical

La modification nécessite un redémarrage :

$ pg_ctl stop
$ pg_ctl start
$ psql
psql (11.2)
Type "help" for help.
postgres=# show wal_level;
wal_level
-----------
logical
(1 row)

Toujours sur le master, s’assurer que l’écoute est faite sur l’adresse IP retenue pour les connexions venant du réplica avec d’éventuelles autres adresses (ceci permettant de retenir l’adresse d’un réseau privé prévu pour la réplication) :

listen_addresses = '172.31.46.46,localhost' # what IP address(es) to listen on;

Autoriser dans « pg_hba.conf » les connexions venant du réplica :

# TYPE DATABASE USER ADDRESS METHOD
…
# for logical replication
host    benchdb         repuser         172.31.33.120/32        md5

• Depuis le réplica, on vérifie que la connexion au master avec “repuser” fonctionne bien :

$ psql --host=172.31.46.46 --port=5432 --username=repuser --dbname=benchdb

Sur le master, connecté en tant que « repuser » sur la base de données « benchdb », créer une PUBLICATION et y ajouter les tables visées en ne publiant que les UPDATE et INSERT.

Les DELETE et TRUNCATE ne seront pas publiés. Ainsi les données de la base de données de reporting sur le réplica consisteront une forme d’entrepôt pour de l’archivage et les requêtes SQL à des fins du BI et du décisionnel :

$ psql --host=localhost --port=5432 --username=repuser --dbname=benchdb
benchdb=> CREATE PUBLICATION pub_bench_data
FOR TABLE pgbench_accounts, pgbench_branches, pgbench_history, pgbench_tellers
WITH (publish = 'insert,update');
CREATE PUBLICATION

Vérification de la publication :

$ psql --host=localhost --port=5432 --username=repuser --dbname=benchdb
benchdb=> \dRp+
Publication pub_bench_data
Owner | All tables | Inserts | Updates | Deletes | Truncates
---------+------------+---------+---------+---------+-----------
repuser | f | t | t | f | f
Tables:
"public.pgbench_accounts"
"public.pgbench_branches"
"public.pgbench_history"
"public.pgbench_tellers"

Sur le replica, connecté en tant que « report » sur la base de données « benchrpt », il faut créer les tables (sans données), qui seront requises au moment de créer la SUBSCRIPTION
Une astuce consiste à exporter le schéma à la source et reproduire les DDL de création des tables:

$ pg_dump --dbname=benchdb --schema-only
…
-- Name: pgbench_accounts; Type: TABLE; Schema: public; Owner: bench
--
CREATE TABLE public.pgbench_accounts (
aid integer NOT NULL,
bid integer,
abalance integer,
filler character(84)
)
WITH (fillfactor='90’);
…

Sur le replica, connecté en tant que « report » sur la base de données « benchrpt », créer une SUBSCRIPTION avec les paramètres de connexion au master et le nom de la publication :

$ psql --host=localhost --port=5432 --username=report --dbname=benchrpt
benchrpt=> CREATE SUBSCRIPTION sub_bench_data
CONNECTION
'host=172.31.46.46 port=5432 password=repuser_password user=repuser dbname=benchdb'
PUBLICATION pub_bench_data ;
NOTICE: created replication slot "sub_bench_data" on publisher
CREATE SUBSCRIPTION

Nous vérifions que les tables ont ainsi été automatiquement alimentées à partir des « SNAPSHOT » produits implicitement :

benchrpt=# \dt+
List of relations
Schema | Name | Type | Owner | Size | Description
--------+------------------+-------+--------+------------+-----------
public | pgbench_accounts | table | report | 678 MB |
public | pgbench_branches | table | report | 8192 bytes |
public | pgbench_history | table | report | 20 MB |
public | pgbench_tellers | table | report | 72 kB |
(4 rows)
benchrpt=# select count(*) from pgbench_history;
count
--------
400000 --- même nombre de lignes que dans la base source benchdb
(1 row)

Un exemple de mise en application de cette réplication logique consiste à pouvoir créer une vue matérialisée à des fins de requêtes de reporting, sur la base de données réplica et pas sur la base source OLTP :

benchrpt=# CREATE MATERIALIZED VIEW data_history
AS SELECT t.tbalance AS "teller_balance", t.filler AS "teller_filler", b.bbalance as "branche_balance", b.filler as "branche_filler", a.abalance as "account_balance", a.filler as "account_filler", h.delta as "history_balance", h.filler as "history_filler", h.mtime as "history_mtime"
FROM public.pgbench_history h JOIN public.pgbench_accounts a ON (h.aid=a.aid) JOIN public.pgbench_branches b ON (h.bid=b.bid) JOIN public.pgbench_tellers t ON (h.tid=t.tid)
WITH NO DATA;
CREATE MATERIALIZED VIEW
benchrpt=# REFRESH MATERIALIZED VIEW data_history;
REFRESH MATERIALIZED VIEW

Avec l’outil « pgbench », nous insérons 1000 lignes dans pgbench_history avec un mode de fonctionnement vidant la table avant opération :

$ pgbench -h localhost -U bench -t 1000 benchdb
Password:
starting vacuum...end.
...
scaling factor: 100
query mode: simple
number of clients: 1
number of threads: 1
number of transactions per client: 1000
number of transactions actually processed: 1000/1000
latency average = 2.706 ms
tps = 369.616976 (including connections establishing)
tps = 370.292764 (excluding connections establishing)

Puis encore 20000 lignes :

$ pgbench -h localhost -U bench -t 20000 benchdb

Au final, nous n’avons plus que les 20000 lignes nouvellement insérées dans la table source (base de données transactionnelle « benchdb ») :

$ psql --host=localhost --port=5432 --username=repuser --dbname=benchdb
benchdb=> select count(*) from pgbench_history ;
count
-------
20000

Et 421000 lignes (les 400000 lignes initiales +21000 lignes insérées) dans la table cible (base de données entrepôt « benchrpt » ) :

$ psql --host=localhost --port=5432 --username=report --dbname=benchrpt
benchrpt=# select count(*) from pgbench_history ;
count
--------
421000

En conclusion de notre expérimentation, nous constatons que les INSERT sont bien répliqués, au contraire des DELETE qui ne le sont pas…
Ainsi, la réplication permet, entre autres, de mettre en place une base de données archivée sur une instance de replication distincte autorisant des objets (vues matérialisées…) et des requêtes plus couteuses pour du reporting sans impact de l’instance OLTP.

Cet exemple d’application doit être permis par l’intégrité référentielle de votre modèle de données! Ici, le fait de pouvoir répliquer les nouvelles lignes sans supprimer les anciennes implique que d’anciennes valeurs de clés primaires ne soient pas réutilisées pour les nouvelles lignes insérées. 

Enfin, la mise en place de la réplication logique requiert la création de rôles/utilisateurs dédiés avec des privilèges élevés. La vigilance est de mise sur la sécurité et la restriction d’accès aux serveurs PostgreSQL avec ces rôles. 

Des opérations « lourdes » peuvent être planifiées sur la base réplica, tel le rafraichissement de la vue matérialisée :

$ psql --host=localhost --port=5432 --username=report --dbname=benchrpt
benchrpt=# \timing on
Timing is on.
benchrpt=# REFRESH MATERIALIZED VIEW data_history;
REFRESH MATERIALIZED VIEW
Time: 22774.644 ms (00:22.775)

La vue pg_stat_subscription indique la dernière transaction reprise par le processus attaché à la SUBSCRIPTION « pg_stat_subscription » :

$ psql --expanded --host=localhost --port=5432 --username=report --dbname=benchrpt
benchrpt=# select * from pg_stat_subscription ;
-[ RECORD 1 ]---------+------------------------------
subid | 16430
subname | sub_bench_data
pid | 31795
relid |
received_lsn | 1/3A461EC0
last_msg_send_time | 2019-07-25 14:41:31.662798+00
last_msg_receipt_time | 2019-07-25 14:41:31.663983+00
latest_end_lsn | 1/3A461EC0
latest_end_time | 2019-07-25 14:41:31.662798+00

 

Continuez votre lecture sur le blog :

twitterlinkedinmail

Ludovic AUGEREAU

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

Ce site utilise Akismet pour réduire les indésirables. En savoir plus sur comment les données de vos commentaires sont utilisées.