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 :
- Migration PostgreSQL via SLONY-I ou comment réduire le temps de coupure (Capdata team) [PostgreSQL]
- PostgreSQL 13 : présentation (Emmanuel RAMI) [PostgreSQL]
- “Pruning” de partitions sous PostgreSQL ou comment bien élaguer ! (Capdata team) [PostgreSQL]
- Migrer d’un cluster Galera MariaDB 10.3 vers MariaDB 10.5 avec la réplication logique (David Baffaleuf) [ContainerMySQLNon classé]
- PostgreSQL : planifier une tâche avec pg_cron (Emmanuel RAMI) [Non classéPostgreSQL]
Bonjour,
Article intéressant mais je voudrais savoir si je créé des tables temporaires dans le serveur 1, comment je fais pour les répliqués dans le serveur 2 sans que la réplication du serveur 2 plante ?