{"id":8022,"date":"2020-01-23T10:42:08","date_gmt":"2020-01-23T09:42:08","guid":{"rendered":"https:\/\/blog.capdata.fr\/?p=8022"},"modified":"2020-01-23T10:46:02","modified_gmt":"2020-01-23T09:46:02","slug":"replication-logique-avec-postgresql","status":"publish","type":"post","link":"https:\/\/blog.capdata.fr\/index.php\/replication-logique-avec-postgresql\/","title":{"rendered":"R\u00e9plication logique avec PostgreSQL"},"content":{"rendered":"\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>

Comme beaucoup d\u2019autres SGBD, PostgreSQL propose sa solution de r\u00e9plication \u00ab WAL streaming<\/a> \u00bb s\u2019appuyant sur le journal de transactions. Cette r\u00e9plication est dite physique, car le r\u00e9plica est mis \u00e0 jour \u00e0 l\u2019identique du serveur principal en appliquant physiquement dans les blocs des tables les changements enregistr\u00e9s dans le journal.<\/p>\n

Les SGBD proposent souvent une alternative de r\u00e9plication dite logique s\u2019appuyant sur l\u2019ex\u00e9cution de l\u2019instruction SQL extraite du serveur principal. L\u2019impact physique sur les fichiers de l\u2019ex\u00e9cution du SQL sur le r\u00e9plica peut ainsi diff\u00e9rer. Un avantage d\u2019une telle r\u00e9plication logique est aussi de pouvoir restreindre les objets ou parties d\u2019objets r\u00e9pliqu\u00e9s (granularit\u00e9), voire appliquer une transformation avant chargement, et ainsi mettre en application une architecture de type ETL (Extract, Transform, Load).<\/p>\n

PostgreSQL propose la r\u00e9plication logique depuis la version 10.<\/strong><\/p>\n

Principaux aspects :<\/strong>
\n\u2022 La r\u00e9plication logique s\u2019appuie sur l\u2019identification des enregistrements \u00e0 r\u00e9pliquer dans les tables ajout\u00e9es \u00e0 la r\u00e9plication (usuellement la cl\u00e9 primaire).
\n\u2022 Mod\u00e8le de publication (publish) et souscription (subscribe).
\n\u2022 Les \u00ab subscribers \u00bb souscrivent \u00e0 une ou plusieurs \u00ab publications \u00bb sur un n\u0153ud \u00ab publisher \u00bb.
\n\u2022 Ce m\u00e9canisme permet la r\u00e9plication en cascade avec un degr\u00e9 variable de complexit\u00e9.
\n\u2022 La r\u00e9plication logique autorise des applications et usages vari\u00e9s :
\n\u2022 R\u00e9plication partielle et cibl\u00e9e.
\n\u2022 D\u00e9clenchement de triggers sur la cible (subscriber) d\u00e9clench\u00e9 au fur et \u00e0 mesure de l\u2019insertion de nouvelles donn\u00e9es r\u00e9pliqu\u00e9es.
\n\u2022 Consolidation de N bases de donn\u00e9es sources sur un r\u00e9pliquat (BI\/DWH).
\n\u2022 R\u00e9plication entre serveurs PostgreSQL de versions distinctes ou\/et sur des plateformes et syst\u00e8mes distincts.
\n\u2022 [\u2026]<\/p>\n

\"\"<\/p>\n

Nous illustrons cela \u00e0 partir du sch\u00e9ma de donn\u00e9es de benchmark \u00ab pgbench \u00bb sur une instance PostgreSQL 11 qui sera le \u00ab master \u00bb. pgbench est un outil utile \u00e0 des fins de \u00ab benchmarks \u00bb de toutes sortes avec possibilit\u00e9 de cr\u00e9er quatre tables initiales permettant de simuler des jeux de transactions pr\u00e9-param\u00e9tr\u00e9es sur ces tables :<\/p>\n

postgres=# \\c benchdb\r\nYou are now connected to database "benchdb" as user "postgres".\r\nbenchdb=# \\dt\r\nList of relations\r\nSchema | Name | Type | Owner\r\n--------+------------------+-------+-------\r\npublic | pgbench_accounts | table | bench\r\npublic | pgbench_branches | table | bench\r\npublic | pgbench_history | table | bench\r\npublic | pgbench_tellers | table | bench\r\n(4 rows)<\/pre>\n
Sur l\u2019instance \u00ab master \u00bb, nous cr\u00e9ons un utilisateur \u00ab repuser \u00bb d\u00e9di\u00e9 \u00e0 la r\u00e9plication (n\u00e9cessitant alors l\u2019attribut REPLICATION) avec les droits sur les tables \u00e0 publier et la base de donn\u00e9es associ\u00e9e :<\/p>\n
postgres=# CREATE ROLE repuser REPLICATION LOGIN;\r\nCREATE ROLE\r\npostgres=# \\password repuser\r\nEnter new password:\r\nEnter it again:<\/pre>\n
En tant qu\u2019utilisateur BENCH, propri\u00e9taire des tables \u00e0 publier, donner ses droits \u00e0 REPUSER pour qu\u2019il soit aussi propri\u00e9taire des tables:<\/p>\n
$ psql --host=localhost --port=5432 --username=bench --dbname=benchdb\r\nbenchdb=> grant bench to repuser;\r\nGRANT ROLE<\/pre>\n
Donner le droit CREATE sur la base BENCHDB \u00e0 REPUSER pour qu\u2019il y puisse y cr\u00e9er une publication:<\/p>\n
benchdb=# grant create on database benchdb to repuser;\r\nGRANT<\/pre>\n
V\u00e9rification des utilisateurs\/r\u00f4les en pr\u00e9sence :<\/p>\n
$ psql --host=localhost --port=5432 --username=repuser --dbname=benchdb\r\nbenchdb=> \\du\r\nList of roles\r\nRole name | Attributes | Member of\r\n-----------+------------------------------------------------------------+--------\r\nbench | | {}\r\npostgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}\r\nrepuser | Replication | {bench}<\/pre>\n
Nous disposons d\u2019une seconde instance qui jouera le r\u00f4le de r\u00e9plica. Celle-ci ne contient aucune des tables pr\u00e9sentes dans le master.
\nNous y cr\u00e9ons un r\u00f4le \u00ab report \u00bb poss\u00e9dant une base de donn\u00e9es \u00ab benchrpt \u00bb (allusion \u00e0 une \u00e9ventuelle finalit\u00e9 de \u00ab reporting \u00bb des donn\u00e9es r\u00e9pliqu\u00e9es) :<\/p>\n
postgres=# create role report login ;\r\nCREATE ROLE\r\npostgres=# \\password report;\r\nEnter new password:\r\nEnter it again:\r\npostgres=# create database benchrpt owner report encoding 'UTF8';\r\nCREATE DATABASE<\/pre>\n
Le r\u00f4le \u00ab report \u00bb<\/span> doit recevoir l\u2019attribut \u00ab superuser \u00bb pour \u00eatre autoris\u00e9 \u00e0 cr\u00e9er des souscriptions. Ainsi connect\u00e9 en tant que \u00ab postgres \u00bb :<\/p>\n
$ psql\r\npostgres=# alter user report superuser;\r\npostgres=# \\du\r\nList of roles\r\nRole name | Attributes | Member of\r\n-----------+------------------------------------------------------------+-------\r\npostgres | Superuser, Create role, Create DB, Replication, Bypass RLS | {}\r\nreport | Superuser | {}<\/pre>\n
Sur l\u2019instance \u00ab master \u00bb, modifier le niveau de WAL en le passant en \u00ab logical \u00bb :<\/p>\n
# wal_level = replica\r\nwal_level = logical<\/pre>\n
La modification n\u00e9cessite un red\u00e9marrage :<\/p>\n
$ pg_ctl stop\r\n$ pg_ctl start\r\n$ psql\r\npsql (11.2)\r\nType "help" for help.\r\npostgres=# show wal_level;\r\nwal_level\r\n-----------\r\nlogical\r\n(1 row)<\/pre>\n
Toujours sur le master, s\u2019assurer que l\u2019\u00e9coute est faite sur l\u2019adresse IP retenue pour les connexions venant du r\u00e9plica avec d\u2019\u00e9ventuelles autres adresses (ceci permettant de retenir l\u2019adresse d\u2019un r\u00e9seau priv\u00e9 pr\u00e9vu pour la r\u00e9plication) :<\/p>\n
listen_addresses = '172.31.46.46,localhost' # what IP address(es) to listen on;<\/pre>\n
Autoriser dans \u00ab pg_hba.conf \u00bb les connexions venant du r\u00e9plica :<\/p>\n
# TYPE DATABASE USER ADDRESS METHOD\r\n\u2026\r\n# for logical replication\r\nhost    benchdb         repuser         172.31.33.120\/32        md5\r\n<\/pre>\n
\u2022 Depuis le r\u00e9plica, on v\u00e9rifie que la connexion au master avec \u201crepuser\u201d fonctionne bien :<\/p>\n
$ psql --host=172.31.46.46 --port=5432 --username=repuser --dbname=benchdb<\/pre>\n
Sur le master, connect\u00e9 en tant que \u00ab repuser \u00bb sur la base de donn\u00e9es \u00ab benchdb \u00bb, cr\u00e9er une PUBLICATION et y ajouter les tables vis\u00e9es en ne publiant que les UPDATE et INSERT.<\/p>\n
Les DELETE et TRUNCATE ne seront pas publi\u00e9s. Ainsi les donn\u00e9es de la base de donn\u00e9es de reporting sur le r\u00e9plica consisteront une forme d\u2019entrep\u00f4t pour de l\u2019archivage et les requ\u00eates SQL \u00e0 des fins du BI et du d\u00e9cisionnel :<\/p>\n
$ psql --host=localhost --port=5432 --username=repuser --dbname=benchdb\r\nbenchdb=> CREATE PUBLICATION pub_bench_data\r\nFOR TABLE pgbench_accounts, pgbench_branches, pgbench_history, pgbench_tellers\r\nWITH (publish = 'insert,update');\r\nCREATE PUBLICATION<\/pre>\n
V\u00e9rification de la publication :<\/p>\n
$ psql --host=localhost --port=5432 --username=repuser --dbname=benchdb\r\nbenchdb=> \\dRp+\r\nPublication pub_bench_data\r\nOwner | All tables | Inserts | Updates | Deletes | Truncates\r\n---------+------------+---------+---------+---------+-----------\r\nrepuser | f | t | t | f | f\r\nTables:\r\n"public.pgbench_accounts"\r\n"public.pgbench_branches"\r\n"public.pgbench_history"\r\n"public.pgbench_tellers"<\/pre>\n
Sur le replica, connect\u00e9 en tant que \u00ab report \u00bb sur la base de donn\u00e9es \u00ab benchrpt \u00bb, il faut cr\u00e9er les tables (sans donn\u00e9es), qui seront requises au moment de cr\u00e9er la SUBSCRIPTION
\nUne astuce consiste \u00e0 exporter le sch\u00e9ma \u00e0 la source et reproduire les DDL de cr\u00e9ation des tables:<\/p>\n
$ pg_dump --dbname=benchdb --schema-only\r\n\u2026\r\n-- Name: pgbench_accounts; Type: TABLE; Schema: public; Owner: bench\r\n--\r\nCREATE TABLE public.pgbench_accounts (\r\naid integer NOT NULL,\r\nbid integer,\r\nabalance integer,\r\nfiller character(84)\r\n)\r\nWITH (fillfactor='90\u2019);\r\n\u2026<\/pre>\n
Sur le replica, connect\u00e9 en tant que \u00ab report \u00bb sur la base de donn\u00e9es \u00ab benchrpt \u00bb, cr\u00e9er une SUBSCRIPTION avec les param\u00e8tres de connexion au master et le nom de la publication :<\/p>\n
$ psql --host=localhost --port=5432 --username=report --dbname=benchrpt\r\nbenchrpt=> CREATE SUBSCRIPTION sub_bench_data\r\nCONNECTION\r\n'host=172.31.46.46 port=5432 password=repuser_password user=repuser dbname=benchdb'\r\nPUBLICATION pub_bench_data ;\r\nNOTICE: created replication slot "sub_bench_data" on publisher\r\nCREATE SUBSCRIPTION<\/pre>\n
Nous v\u00e9rifions que les tables ont ainsi \u00e9t\u00e9 automatiquement aliment\u00e9es \u00e0 partir des \u00ab SNAPSHOT \u00bb produits implicitement :<\/p>\n
benchrpt=# \\dt+\r\nList of relations\r\nSchema | Name | Type | Owner | Size | Description\r\n--------+------------------+-------+--------+------------+-----------\r\npublic | pgbench_accounts | table | report | 678 MB |\r\npublic | pgbench_branches | table | report | 8192 bytes |\r\npublic | pgbench_history | table | report | 20 MB |\r\npublic | pgbench_tellers | table | report | 72 kB |\r\n(4 rows)\r\nbenchrpt=# select count(*) from pgbench_history;\r\ncount\r\n--------\r\n400000 --- m\u00eame nombre de lignes que dans la base source benchdb\r\n(1 row)<\/pre>\n
Un exemple de mise en application de cette r\u00e9plication logique consiste \u00e0 pouvoir cr\u00e9er une vue mat\u00e9rialis\u00e9e \u00e0 des fins de requ\u00eates de reporting, sur la base de donn\u00e9es r\u00e9plica et pas sur la base source OLTP :<\/p>\n
benchrpt=# CREATE MATERIALIZED VIEW data_history\r\nAS 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"\r\nFROM 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)\r\nWITH NO DATA;\r\nCREATE MATERIALIZED VIEW\r\nbenchrpt=# REFRESH MATERIALIZED VIEW data_history;\r\nREFRESH MATERIALIZED VIEW<\/pre>\n
Avec l\u2019outil \u00ab pgbench \u00bb, nous ins\u00e9rons 1000 lignes dans pgbench_history avec un mode de fonctionnement vidant la table avant op\u00e9ration :<\/p>\n
$ pgbench -h localhost -U bench -t 1000 benchdb\r\nPassword:\r\nstarting vacuum...end.\r\n...\r\nscaling factor: 100\r\nquery mode: simple\r\nnumber of clients: 1\r\nnumber of threads: 1\r\nnumber of transactions per client: 1000\r\nnumber of transactions actually processed: 1000\/1000\r\nlatency average = 2.706 ms\r\ntps = 369.616976 (including connections establishing)\r\ntps = 370.292764 (excluding connections establishing)<\/pre>\n
Puis encore 20000 lignes :<\/p>\n
$ pgbench -h localhost -U bench -t 20000 benchdb<\/pre>\n
Au final, nous n\u2019avons plus que les 20000 lignes nouvellement ins\u00e9r\u00e9es dans la table source (base de donn\u00e9es transactionnelle \u00ab benchdb \u00bb) :<\/p>\n
$ psql --host=localhost --port=5432 --username=repuser --dbname=benchdb\r\nbenchdb=> select count(*) from pgbench_history ;\r\ncount\r\n-------\r\n20000<\/pre>\n
Et 421000 lignes (les 400000 lignes initiales +21000 lignes ins\u00e9r\u00e9es) dans la table cible (base de donn\u00e9es entrep\u00f4t \u00ab benchrpt \u00bb ) :<\/p>\n
$ psql --host=localhost --port=5432 --username=report --dbname=benchrpt\r\nbenchrpt=# select count(*) from pgbench_history ;\r\ncount\r\n--------\r\n421000<\/pre>\n
En conclusion de notre exp\u00e9rimentation, nous constatons que les INSERT sont bien r\u00e9pliqu\u00e9s, au contraire des DELETE qui ne le sont pas\u2026 <\/strong>
\nAinsi, la r\u00e9plication permet, entre autres, de mettre en place une base de donn\u00e9es archiv\u00e9e sur une instance de replication distincte autorisant des objets (vues mat\u00e9rialis\u00e9es\u2026) et des requ\u00eates plus couteuses pour du reporting sans impact de l\u2019instance OLTP.<\/strong><\/p>\n
Cet exemple d’application doit \u00eatre permis par l’int\u00e9grit\u00e9 r\u00e9f\u00e9rentielle de votre mod\u00e8le de donn\u00e9es! Ici, le fait de pouvoir r\u00e9pliquer les nouvelles lignes sans supprimer les anciennes implique que d’anciennes valeurs de cl\u00e9s primaires ne soient pas r\u00e9utilis\u00e9es pour les nouvelles lignes ins\u00e9r\u00e9es.\u00a0<\/strong><\/p>\n
Enfin, la mise en place de la r\u00e9plication logique requiert la cr\u00e9ation de r\u00f4les\/utilisateurs d\u00e9di\u00e9s avec des privil\u00e8ges \u00e9lev\u00e9s. La vigilance est de mise sur la s\u00e9curit\u00e9 et la restriction d’acc\u00e8s aux serveurs PostgreSQL avec ces r\u00f4les.\u00a0<\/b><\/p>\n
Des op\u00e9rations \u00ab lourdes \u00bb peuvent \u00eatre planifi\u00e9es sur la base r\u00e9plica, tel le rafraichissement de la vue mat\u00e9rialis\u00e9e :<\/p>\n
$ psql --host=localhost --port=5432 --username=report --dbname=benchrpt\r\nbenchrpt=# \\timing on\r\nTiming is on.\r\nbenchrpt=# REFRESH MATERIALIZED VIEW data_history;\r\nREFRESH MATERIALIZED VIEW\r\nTime: 22774.644 ms (00:22.775)<\/pre>\n
La vue pg_stat_subscription indique la derni\u00e8re transaction reprise par le processus attach\u00e9 \u00e0 la SUBSCRIPTION \u00ab pg_stat_subscription \u00bb :<\/p>\n
$ psql --expanded --host=localhost --port=5432 --username=report --dbname=benchrpt\r\nbenchrpt=# select * from pg_stat_subscription ;\r\n-[ RECORD 1 ]---------+------------------------------\r\nsubid | 16430\r\nsubname | sub_bench_data\r\npid | 31795\r\nrelid |\r\nreceived_lsn | 1\/3A461EC0\r\nlast_msg_send_time | 2019-07-25 14:41:31.662798+00\r\nlast_msg_receipt_time | 2019-07-25 14:41:31.663983+00\r\nlatest_end_lsn | 1\/3A461EC0\r\nlatest_end_time | 2019-07-25 14:41:31.662798+00<\/pre>\n
 <\/p>\n\"twitter\"<\/a>\"linkedin\"<\/a>\"mail\"<\/a>","protected":false},"excerpt":{"rendered":"
Comme beaucoup d\u2019autres SGBD, PostgreSQL propose sa solution de r\u00e9plication \u00ab WAL streaming \u00bb s\u2019appuyant sur le journal de transactions. Cette r\u00e9plication est dite physique, car le r\u00e9plica est mis \u00e0 jour \u00e0 l\u2019identique du serveur principal en appliquant physiquement… Continuer la lecture →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":8027,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[266],"tags":[336],"class_list":["post-8022","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgresql","tag-replication-logique"],"yoast_head":"\nR\u00e9plication logique avec PostgreSQL - Capdata TECH BLOG<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/blog.capdata.fr\/index.php\/replication-logique-avec-postgresql\/\" \/>\n<meta property=\"og:locale\" content=\"fr_FR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"R\u00e9plication logique avec PostgreSQL - Capdata TECH BLOG\" \/>\n<meta property=\"og:description\" content=\"Comme beaucoup d\u2019autres SGBD, PostgreSQL propose sa solution de r\u00e9plication \u00ab WAL streaming \u00bb s\u2019appuyant sur le journal de transactions. Cette r\u00e9plication est dite physique, car le r\u00e9plica est mis \u00e0 jour \u00e0 l\u2019identique du serveur principal en appliquant physiquement… Continuer la lecture →\" \/>\n<meta property=\"og:url\" content=\"https:\/\/blog.capdata.fr\/index.php\/replication-logique-avec-postgresql\/\" \/>\n<meta property=\"og:site_name\" content=\"Capdata TECH BLOG\" \/>\n<meta property=\"article:published_time\" content=\"2020-01-23T09:42:08+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2020-01-23T09:46:02+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2019\/09\/PG_logical_rep.png\" \/>\n\t<meta property=\"og:image:width\" content=\"1425\" \/>\n\t<meta property=\"og:image:height\" content=\"763\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Capdata team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"\u00c9crit par\" \/>\n\t<meta name=\"twitter:data1\" content=\"Capdata team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Dur\u00e9e de lecture estim\u00e9e\" \/>\n\t<meta name=\"twitter:data2\" content=\"10 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/replication-logique-avec-postgresql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/replication-logique-avec-postgresql\/\"},\"author\":{\"name\":\"Capdata team\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/bfd9395c8ba4fa125792a543377035e9\"},\"headline\":\"R\u00e9plication logique avec PostgreSQL\",\"datePublished\":\"2020-01-23T09:42:08+00:00\",\"dateModified\":\"2020-01-23T09:46:02+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/replication-logique-avec-postgresql\/\"},\"wordCount\":1912,\"commentCount\":1,\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"keywords\":[\"r\u00e9plication logique\"],\"articleSection\":[\"PostgreSQL\"],\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/replication-logique-avec-postgresql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/replication-logique-avec-postgresql\/\",\"url\":\"https:\/\/blog.capdata.fr\/index.php\/replication-logique-avec-postgresql\/\",\"name\":\"R\u00e9plication logique avec PostgreSQL - Capdata TECH BLOG\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/#website\"},\"datePublished\":\"2020-01-23T09:42:08+00:00\",\"dateModified\":\"2020-01-23T09:46:02+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/replication-logique-avec-postgresql\/#breadcrumb\"},\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/replication-logique-avec-postgresql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/replication-logique-avec-postgresql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/blog.capdata.fr\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"R\u00e9plication logique avec PostgreSQL\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/blog.capdata.fr\/#website\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"name\":\"Capdata TECH BLOG\",\"description\":\"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting\",\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/blog.capdata.fr\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"fr-FR\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/blog.capdata.fr\/#organization\",\"name\":\"Capdata TECH BLOG\",\"url\":\"https:\/\/blog.capdata.fr\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"fr-FR\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"contentUrl\":\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp\",\"width\":800,\"height\":254,\"caption\":\"Capdata TECH BLOG\"},\"image\":{\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/bfd9395c8ba4fa125792a543377035e9\",\"name\":\"Capdata team\",\"sameAs\":[\"https:\/\/www.capdata.fr\"],\"url\":\"https:\/\/blog.capdata.fr\/index.php\/author\/admin\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"R\u00e9plication logique avec PostgreSQL - Capdata TECH BLOG","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/blog.capdata.fr\/index.php\/replication-logique-avec-postgresql\/","og_locale":"fr_FR","og_type":"article","og_title":"R\u00e9plication logique avec PostgreSQL - Capdata TECH BLOG","og_description":"Comme beaucoup d\u2019autres SGBD, PostgreSQL propose sa solution de r\u00e9plication \u00ab WAL streaming \u00bb s\u2019appuyant sur le journal de transactions. Cette r\u00e9plication est dite physique, car le r\u00e9plica est mis \u00e0 jour \u00e0 l\u2019identique du serveur principal en appliquant physiquement… Continuer la lecture →","og_url":"https:\/\/blog.capdata.fr\/index.php\/replication-logique-avec-postgresql\/","og_site_name":"Capdata TECH BLOG","article_published_time":"2020-01-23T09:42:08+00:00","article_modified_time":"2020-01-23T09:46:02+00:00","og_image":[{"width":1425,"height":763,"url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2019\/09\/PG_logical_rep.png","type":"image\/png"}],"author":"Capdata team","twitter_card":"summary_large_image","twitter_misc":{"\u00c9crit par":"Capdata team","Dur\u00e9e de lecture estim\u00e9e":"10 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/blog.capdata.fr\/index.php\/replication-logique-avec-postgresql\/#article","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/index.php\/replication-logique-avec-postgresql\/"},"author":{"name":"Capdata team","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/bfd9395c8ba4fa125792a543377035e9"},"headline":"R\u00e9plication logique avec PostgreSQL","datePublished":"2020-01-23T09:42:08+00:00","dateModified":"2020-01-23T09:46:02+00:00","mainEntityOfPage":{"@id":"https:\/\/blog.capdata.fr\/index.php\/replication-logique-avec-postgresql\/"},"wordCount":1912,"commentCount":1,"publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"keywords":["r\u00e9plication logique"],"articleSection":["PostgreSQL"],"inLanguage":"fr-FR","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/blog.capdata.fr\/index.php\/replication-logique-avec-postgresql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/blog.capdata.fr\/index.php\/replication-logique-avec-postgresql\/","url":"https:\/\/blog.capdata.fr\/index.php\/replication-logique-avec-postgresql\/","name":"R\u00e9plication logique avec PostgreSQL - Capdata TECH BLOG","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/#website"},"datePublished":"2020-01-23T09:42:08+00:00","dateModified":"2020-01-23T09:46:02+00:00","breadcrumb":{"@id":"https:\/\/blog.capdata.fr\/index.php\/replication-logique-avec-postgresql\/#breadcrumb"},"inLanguage":"fr-FR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/blog.capdata.fr\/index.php\/replication-logique-avec-postgresql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/blog.capdata.fr\/index.php\/replication-logique-avec-postgresql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/blog.capdata.fr\/"},{"@type":"ListItem","position":2,"name":"R\u00e9plication logique avec PostgreSQL"}]},{"@type":"WebSite","@id":"https:\/\/blog.capdata.fr\/#website","url":"https:\/\/blog.capdata.fr\/","name":"Capdata TECH BLOG","description":"Le blog technique sur les bases de donn\u00e9es de CAP DATA Consulting","publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/blog.capdata.fr\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"fr-FR"},{"@type":"Organization","@id":"https:\/\/blog.capdata.fr\/#organization","name":"Capdata TECH BLOG","url":"https:\/\/blog.capdata.fr\/","logo":{"@type":"ImageObject","inLanguage":"fr-FR","@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/","url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","contentUrl":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2023\/01\/logo_capdata.webp","width":800,"height":254,"caption":"Capdata TECH BLOG"},"image":{"@id":"https:\/\/blog.capdata.fr\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.linkedin.com\/company\/cap-data-consulting\/mycompany\/"]},{"@type":"Person","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/bfd9395c8ba4fa125792a543377035e9","name":"Capdata team","sameAs":["https:\/\/www.capdata.fr"],"url":"https:\/\/blog.capdata.fr\/index.php\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/8022","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/comments?post=8022"}],"version-history":[{"count":7,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/8022\/revisions"}],"predecessor-version":[{"id":8268,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/8022\/revisions\/8268"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media\/8027"}],"wp:attachment":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media?parent=8022"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/categories?post=8022"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/tags?post=8022"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}