![Page 1: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/1.jpg)
Réplication Logique
pgDay.fr Lille 2016
Cédric Villemain [email protected]
31 mai 2016
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 1 / 29
![Page 2: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/2.jpg)
Cédric Villemain
Expertise & DéveloppementFormation
Support & Remote DBA
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 2 / 29
![Page 3: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/3.jpg)
2ndQuadrant
Sponsor Platine de PostgreSQL
• 9.1, Synchronous Replication
• 9.2, Refactoring checkpoint group commit
• 9.3, Event Trigger, Refactoring
• 9.4, Replication Slot / Réplication Logique
• 9.5, Replication Origin / Commit Timestamp
• 9.6, Logical WAL messaging
• NEXT, Timeline Switch / Integrated Failover
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 3 / 29
![Page 5: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/5.jpg)
Opérations Logiques
BEGIN;
INSERT INTO country VALUES (1, 'france') ;
INSERT INTO towns VALUES (default, 1 , 'lille');
COMMIT;
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 5 / 29
![Page 6: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/6.jpg)
Après le COMMIT: analyse des opérations
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 6 / 29
![Page 7: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/7.jpg)
Opérations Physiques
fopen(1, ...)
fseek(1, ...)
fwrite(1, ...)
fopen(2, ...)
fseek(2, ...)
fwrite(2, ...)
...
fsync()
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 7 / 29
![Page 8: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/8.jpg)
Après le COMMIT: empreintes des modi�cations
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 8 / 29
![Page 10: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/10.jpg)
Evènements
• INSERT
• UPDATE
• DELETE
• TRUNCATE
• CREATE TABLE
• ...
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 10 / 29
![Page 11: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/11.jpg)
Evènements
• INSERT
• UPDATE
• DELETE
• TRUNCATE
• CREATE TABLE
• ...
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 10 / 29
![Page 12: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/12.jpg)
Evènements
• INSERT
• UPDATE
• DELETE
• TRUNCATE
• CREATE TABLE
• ...
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 10 / 29
![Page 13: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/13.jpg)
Evènements
• INSERT
• UPDATE
• DELETE
• TRUNCATE
• CREATE TABLE
• ...
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 10 / 29
![Page 14: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/14.jpg)
Evènements
• INSERT
• UPDATE
• DELETE
• TRUNCATE
• CREATE TABLE
• ...
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 10 / 29
![Page 15: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/15.jpg)
Evènements
• INSERT
• UPDATE
• DELETE
• TRUNCATE
• CREATE TABLE
• ...
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 10 / 29
![Page 16: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/16.jpg)
Evènements
• Trigger: Déclencheur
• Logical Slot: Déclencheur / Accumulateur / Communication
• LISTEN / NOTIFY: Communication
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 11 / 29
![Page 17: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/17.jpg)
Evènements
• Trigger: Déclencheur
• Logical Slot: Déclencheur / Accumulateur / Communication
• LISTEN / NOTIFY: Communication
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 11 / 29
![Page 18: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/18.jpg)
Evènements
• Trigger: Déclencheur
• Logical Slot: Déclencheur / Accumulateur / Communication
• LISTEN / NOTIFY: Communication
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 11 / 29
![Page 20: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/20.jpg)
Associer certains évènementsà certaines actions
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 13 / 29
![Page 21: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/21.jpg)
Trigger
ON INSERT DO ....
...
ROLLBACK;
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 14 / 29
![Page 22: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/22.jpg)
traitement super�u
...et prend du temps �utilisateur�
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 15 / 29
![Page 23: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/23.jpg)
LISTEN / NOTIFY
# ... ON INSERT DO ....
# NOTIFY channel_1, 'id:1,country:france'
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 16 / 29
![Page 25: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/25.jpg)
Logical Slot
• ON COMMIT only
• Pas de perte
• Commit Timestamp
• Origine
• ...
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 18 / 29
![Page 26: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/26.jpg)
Logical Slot
• ON COMMIT only
• Pas de perte
• Commit Timestamp
• Origine
• ...
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 18 / 29
![Page 27: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/27.jpg)
Logical Slot
• ON COMMIT only
• Pas de perte
• Commit Timestamp
• Origine
• ...
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 18 / 29
![Page 28: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/28.jpg)
Logical Slot
• ON COMMIT only
• Pas de perte
• Commit Timestamp
• Origine
• ...
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 18 / 29
![Page 29: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/29.jpg)
Logical Slot
• ON COMMIT only
• Pas de perte
• Commit Timestamp
• Origine
• ...
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 18 / 29
![Page 31: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/31.jpg)
Réplication
• Slony / Skytools / Londiste
• pgLogical
• Bi-Directionnal Replication
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 20 / 29
![Page 32: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/32.jpg)
Réplication
• Slony / Skytools / Londiste
• pgLogical
• Bi-Directionnal Replication
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 20 / 29
![Page 33: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/33.jpg)
Réplication
• Slony / Skytools / Londiste
• pgLogical
• Bi-Directionnal Replication
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 20 / 29
![Page 34: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/34.jpg)
pgLogical
• Evènement -> Logical Decoder(Callback) -> Logical Slot
• Logical Slot <�> Logical Consummer -> ?
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 21 / 29
![Page 35: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/35.jpg)
pgLogical
• Evènement -> Logical Decoder(Callback) -> Logical Slot
• Logical Slot <�> Logical Consummer -> ?
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 21 / 29
![Page 37: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/37.jpg)
Réplication
• Réplication partielle
• Réplication croisée et bi-directionnelle (�multi-maitre�)
• Schémas di�érents
• Aggrégation, centralisation, séparation, ...
• Mise à jour PostgreSQL
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 23 / 29
![Page 38: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/38.jpg)
Extract Load Transform
• pas ETL mais ELT
• En continu
• pgLogical -> Extract Load
• Triggers on Apply -> Transform
• Alimentation d'un système tiers (solR par exemple)
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 24 / 29
![Page 39: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/39.jpg)
• Partitionnement Horizontal
• Répartition de charge
• Audit / Historisation (merci commit timestamp!)
• Archivage de données
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 25 / 29
![Page 40: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/40.jpg)
• Cycle de vie des données
• Trigger asynchrone
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 26 / 29
![Page 41: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/41.jpg)
Future
• Timeline Following
• Integrated Failover
• Row Control
• Réplication en Cascade
• 10 : Réplication Logique intégrée
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 27 / 29
![Page 42: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/42.jpg)
Ressources
• Logical Decoding:https://www.postgresql.org/docs/current/static/logicaldecoding.html
• pglogical: http://2ndquadrant.com/en/resources/pglogical/
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 28 / 29
![Page 43: Réplication Logique - pgDay.fr Lille 2016 · 9.1 , Synchronous Replication 9.2 , Refactoring checkpoint group commit 9.3 , Event rigger,T Refactoring 9.4 , Replication Slot / Réplication](https://reader033.vdocuments.us/reader033/viewer/2022060320/5f0ce9d17e708231d437c060/html5/thumbnails/43.jpg)
Questions ?
C'est à vous!
Cédric Villemain [email protected] Réplication Logique 31 mai 2016 29 / 29