{"id":9432,"date":"2022-10-28T09:58:08","date_gmt":"2022-10-28T08:58:08","guid":{"rendered":"https:\/\/blog.capdata.fr\/?p=9432"},"modified":"2023-03-29T15:55:40","modified_gmt":"2023-03-29T14:55:40","slug":"postgresql-15-parmi-les-nouveautes-lire-dans-les-wal-avec-du-sql","status":"publish","type":"post","link":"https:\/\/blog.capdata.fr\/index.php\/postgresql-15-parmi-les-nouveautes-lire-dans-les-wal-avec-du-sql\/","title":{"rendered":"PostgreSQL 15 : parmi les nouveaut\u00e9s, lire dans les WAL avec PSQL"},"content":{"rendered":"<a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-twitter nolightbox\" data-provider=\"twitter\" target=\"_blank\" rel=\"nofollow\" title=\"Share on Twitter\" href=\"https:\/\/twitter.com\/intent\/tweet?url=https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F9432&#038;text=Article%20sur%20le%20blog%20de%20la%20Capdata%20Tech%20Team%20%3A%20\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px;margin-right:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"twitter\" title=\"Share on Twitter\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/twitter.png\" \/><\/a><a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-linkedin nolightbox\" data-provider=\"linkedin\" target=\"_blank\" rel=\"nofollow\" title=\"Share on Linkedin\" href=\"https:\/\/www.linkedin.com\/shareArticle?mini=true&#038;url=https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F9432&#038;title=PostgreSQL%2015%20%3A%20parmi%20les%20nouveaut%C3%A9s%2C%20lire%20dans%20les%20WAL%20avec%20PSQL\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px;margin-right:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"linkedin\" title=\"Share on Linkedin\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/linkedin.png\" \/><\/a><a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-mail nolightbox\" data-provider=\"mail\" rel=\"nofollow\" title=\"Share by email\" href=\"mailto:?subject=PostgreSQL%2015%20%3A%20parmi%20les%20nouveaut%C3%A9s%2C%20lire%20dans%20les%20WAL%20avec%20PSQL&#038;body=Article%20sur%20le%20blog%20de%20la%20Capdata%20Tech%20Team%20%3A%20:%20https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F9432\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"mail\" title=\"Share by email\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/mail.png\" \/><\/a><p>lo<\/p>\n<p>ca y&#8217;est la nouvelle version de PostgreSQL est maintenant parmi nous, et ce depuis le 13 octobre 2022.<br \/>\nPour ceux et celles qui avaient d\u00e9j\u00e0 essay\u00e9 la version beta auparavant, vous avez pu qualifier certaines nouveaut\u00e9s, avec cette liste non exhaustive :<\/p>\n<ul>\n<li>Nouvelles m\u00e9thodes de compression avec Zstandard compression et LZ4.\n<ul>\n<li>Les algorithmes Zstandard et LZ4 peuvent \u00eatre utilis\u00e9s pour la g\u00e9n\u00e9ration des WALs, mais aussi avec Pg_basebackup. Ces algorithmes sont bien plus performants qu&#8217;un &#8220;gzip&#8221; classique.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<ul>\n<li>Possibilit\u00e9 de prendre en charge ICU collation comme collation par d\u00e9faut pour l&#8217;instance ou la base directement.\n<ul>\n<li>ICU est une alternative \u00e0 &#8220;libc&#8221; , le fonctionnement des tris et la comparaison des caract\u00e8res sont g\u00e9r\u00e9s de fa\u00e7on diff\u00e9rente.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<ul>\n<li>Meilleures performances lors d&#8217;op\u00e9rations de tris en m\u00e9moire ou sur disque, notamment lors de l&#8217;utilisation de fonctions telles : <strong>row_number(), rank(), dense_rank() and count().<\/strong><\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<ul>\n<li>Il est possible d&#8217;utiliser des expressions r\u00e9guli\u00e8res pour travailler sur des chaines de caract\u00e8res avec des fonctions comme <strong>regexp_count(), regexp_instr() regexp_like() et regexp_substr().<\/strong><\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<ul>\n<li>Si vous utilisez la r\u00e9plication logique, il vous sera possible \u00e0 pr\u00e9sent, avec la version 15 de PostgreSQL, de filtrer vos publications avec un sous ensemble.\n<ul>\n<li>Utiliser une clause WHERE directement dans la cr\u00e9ation de votre publication.<br \/>\nExemple :Cr\u00e9ation de notre publication avec condition, sur l&#8217;instance principale:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">(postgres@[local]:5434) [manu] create publication publication1 for table public.test where (nom = 'manu');\r\nCREATE PUBLICATION\r\n<\/pre>\n<p>Cr\u00e9ation de notre suscription sur l&#8217;instance secours :<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">(postgres@[local]:5435) [manu] create subscription subscription1 connection 'port=5434' publication publication1\r\nNOTICE: created replication slot &quot;subscription1&quot; on publisher\r\nCREATE SUBSCRIPTION\r\n<\/pre>\n<\/li>\n<\/ul>\n<\/li>\n<li>La commande MERGE est enfin compatible sous PostgreSQL 15 !\n<ul>\n<li>Cette commande permet de faciliter grandement les ordres SQL conditionnelles. Le MERGE s&#8217;utilise avec un &#8220;WHEN MATCHED&#8221; sur la premi\u00e8re action de la table et un &#8220;WHEN NOT MATCHED&#8221; sur seconde action sur la table cible.<br \/>\nUn petit exemple, extrait du site <a href=\"https:\/\/www.postgresql.org\/docs\/current\/sql-merge.html\">postgresql.org<\/a> pour mieux comprendre :<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\"> MERGE INTO customer_account ca\r\nUSING recent_transactions t\r\nON t.customer_id = ca.customer_id\r\nWHEN MATCHED THEN\r\n  UPDATE SET balance = balance + transaction_value\r\nWHEN NOT MATCHED THEN\r\n  INSERT (customer_id, balance)\r\n  VALUES (t.customer_id, t.transaction_value); <\/pre>\n<\/li>\n<\/ul>\n<\/li>\n<li>Et surtout, lire les enregistrements directement dans les WAL gr\u00e2ce \u00e0 &#8220;pg_wallinspect&#8221;. Ce\u00a0 que nous allons voir plus en d\u00e9tail ci dessous.<\/li>\n<\/ul>\n<p>&nbsp;<\/p>\n<h2>PG_WALINSPECT<\/h2>\n<p>&nbsp;<\/p>\n<p>Il s&#8217;agit d&#8217;une extension, propos\u00e9e dans les contribs de la version 15 de PostgreSQL, qui va faciliter la lecture des diff\u00e9rentes transactions au sein d&#8217;une instance de base de donn\u00e9es en allant directement interroger les informations dans les WALs.<\/p>\n<p>Les \u00e9diteurs comme Oracle et SQL Server permettent deja depuis un certain temps de faire ces op\u00e9rations. On pense \u00e0 Logminer pour Oracle, et l&#8217;utilisation du package DBMS_LOGMNR et des vues &#8220;v$logmnr_****&#8221;. Ou encore de la fonction &#8220;fn_dump_dblog()&#8221; pour SQL Server, et la je vous invite \u00e0 vous rem\u00e9morer cet article de notre blog qui permet de rechercher <a href=\"https:\/\/blog.capdata.fr\/index.php\/suppression-accidentelle-de-ligne-comment-retrouver-le-coupable\/\">le coupable d&#8217;un malencontreux DELETE<\/a> !<\/p>\n<p>Avec PostgreSQL 15, c&#8217;est une extension qui doit \u00eatre ajout\u00e9e pour g\u00e9rer cette op\u00e9ration. Nous pouvions d\u00e9j\u00e0 utiliser &#8220;<strong>pg_waldump<\/strong>&#8221; pour aller chercher des informations directement dans les WALs. Mais ici, avec l&#8217;extension &#8220;pg_walinspect&#8221;, nous pouvons faire cela avec du code SQL sous l&#8217;outil psql.<\/p>\n<pre class=\"brush: bash; title: ; notranslate\" title=\"\">[postgres~]$ vi $PGDATA\/postgresql.conf\r\n....\r\nshared_preload_libraries = 'pg_walinspect' # (change requires restart)\r\n...\r\n[postgres ~]$ pg_ctl -D $PGDATA restart\r\n<\/pre>\n<p>&nbsp;<\/p>\n<p>Par la suite, on cr\u00e9e cette extension dans PostgreSQL, avec l&#8217;outil &#8220;psql&#8217;<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">(postgres@[local]:5434) [postgres] create extension pg_walinspect; \r\nCREATE EXTENSION \r\nTime: 21.598 ms <\/pre>\n<p>&nbsp;<\/p>\n<p>Voici les fonctions fournies par cette extension, et qui nous permettront de chercher les diff\u00e9rents enregistrements dans les WALs<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">(postgres@[local]:5434) [postgres] \\dx+ pg_walinspect\r\nObjects in extension &quot;pg_walinspect&quot;\r\nObject description\r\n-----------------------------------------------------------\r\nfunction pg_get_wal_record_info(pg_lsn)\r\nfunction pg_get_wal_records_info(pg_lsn,pg_lsn)\r\nfunction pg_get_wal_records_info_till_end_of_wal(pg_lsn)\r\nfunction pg_get_wal_stats(pg_lsn,pg_lsn,boolean)\r\nfunction pg_get_wal_stats_till_end_of_wal(pg_lsn,boolean)\r\n(5 rows)<\/pre>\n<p>&nbsp;<\/p>\n<h3>Exemple d&#8217;utilisation<\/h3>\n<p>&nbsp;<\/p>\n<p>Nous allons cr\u00e9er une table &#8220;employ\u00e9s&#8221;, dans laquelle nous ins\u00e9rons des donn\u00e9es.<br \/>\nAvant cela, rep\u00e9rez \u00e0 quel valeur &#8220;lsn&#8221; vous \u00eates dans votre WAL courant.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">(postgres@[local]:5434) [manu] SELECT pg_current_wal_lsn();\r\npg_current_wal_lsn\r\n--------------------\r\n0\/1C51F80\r\n(1 row)<\/pre>\n<p>&nbsp;<\/p>\n<p>Puis<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">(postgres@[local]:5434) [manu] create table employes (nom varchar(20), societe varchar(20), entree date);\r\nCREATE TABLE\r\nTime: 18.370 ms\r\n(postgres@[local]:5434) [manu] insert into employes values ('Emmanuel','Capdata','12\/01\/2010');\r\nINSERT 0 1\r\nTime: 3.764 ms\r\n(postgres@[local]:5434) [manu] insert into employes values ('Olivier','Osmozium','05\/23\/2015');\r\nINSERT 0 1\r\nTime: 1.410 ms\r\n(postgres@[local]:5434) [manu] insert into employes values ('Jerome','Oracle','06\/10\/2002');\r\nINSERT 0 1\r\nTime: 1.453 ms\r\n(postgres@[local]:5434) [manu] select * from employes;\r\nnom | societe | entree\r\n----------+----------+------------\r\nEmmanuel | Capdata | 2010-12-01\r\nOlivier | Osmozium | 2015-05-23\r\nJerome | Oracle | 2002-06-10\r\n(3 rows)<\/pre>\n<p>&nbsp;<\/p>\n<p>Puis une mise \u00e0 jour dans celle ci<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">(postgres@[local]:5434) [manu] update employes set entree='08\/10\/2002' where nom='Jerome';\r\nUPDATE 1\r\nTime: 11.179 ms\r\n(postgres@[local]:5434) [manu] select * from employes;\r\nnom | societe | entree\r\n----------+----------+------------\r\nEmmanuel | Capdata | 2010-12-01\r\nOlivier | Osmozium | 2015-05-23\r\nJerome | Oracle | 2002-08-10\r\n(3 rows)<\/pre>\n<p>&nbsp;<\/p>\n<p>et un DELETE<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">(postgres@[local]:5434) [manu] delete from employes where nom='Olivier';\r\nDELETE 1\r\nTime: 1.376 ms\r\n(postgres@[local]:5434) [manu] select * from employes;\r\nnom | societe | entree\r\n----------+---------+------------\r\nEmmanuel | Capdata | 2010-12-01\r\nJerome | Oracle | 2002-08-10\r\n(2 rows)<\/pre>\n<p>&nbsp;<\/p>\n<p>Mais alors, si je veux retrouver \u00e0 quel moment a \u00e9t\u00e9 fait le DELETE ? Et surtout si je veux retrouver mes donn\u00e9es de ma table, via une restauration PITR !<\/p>\n<p>Pour cela, rep\u00e9rez \u00e0 nouveau, la velur &#8220;lsn&#8221; courante dans le WAL, apr\u00e8s les op\u00e9rations:<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">(postgres@[local]:5434) [postgres] SELECT pg_current_wal_lsn();\r\npg_current_wal_lsn\r\n--------------------\r\n0\/1C6B8C8\r\n(1 row)<\/pre>\n<p>&nbsp;<\/p>\n<p>Utilisons \u00e0 pr\u00e9sent la fonction &#8220;<span style=\"color: #993366;\">pg_get_wal_records_info<\/span>&#8221; pour aller chercher, directement dans les WALs, les ordres enregistr\u00e9s. Il faudra passer en param\u00e8tres le 1er lsn trouv\u00e9, avant les ordres SQL ex\u00e9cut\u00e9s pour notre table &#8220;employ\u00e9s&#8221;, puis en second param\u00e8tre, le lsn post op\u00e9rations.<\/p>\n<p>Ce qui donne les informations suivantes :<\/p>\n<p>&nbsp;<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">(postgres@[local]:5434) [postgres] select start_lsn, end_lsn, prev_lsn, xid, resource_manager, record_type, record_length,description from pg_get_wal_records_info('0\/1C51F80','0\/1C6B8C8');\r\nstart_lsn | end_lsn | prev_lsn | xid | resource_manager | record_type | record_length | description\r\n-----------+-----------+-----------+-----+------------------+-------------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------\r\n0\/1C51F80 | 0\/1C535E0 | 0\/1C51D68 | 0 | XLOG | FPI_FOR_HINT | 5697 |\r\n0\/1C535E0 | 0\/1C551A0 | 0\/1C51F80 | 0 | XLOG | FPI_FOR_HINT | 7077 |\r\n0\/1C551A0 | 0\/1C551D0 | 0\/1C535E0 | 0 | Storage | CREATE | 42 | base\/16388\/16407\r\n0\/1C551D0 | 0\/1C552A8 | 0\/1C551A0 | 750 | Heap | INSERT | 211 | off 14 flags 0x00\r\n0\/1C552A8 | 0\/1C566A8 | 0\/1C551D0 | 750 | Btree | INSERT_LEAF | 5093 | off 250\r\n0\/1C566A8 | 0\/1C566F0 | 0\/1C552A8 | 750 | Btree | INSERT_LEAF | 72 | off 127\r\n0\/1C566F0 | 0\/1C56748 | 0\/1C566A8 | 750 | Heap2 | MULTI_INSERT | 85 | 1 tuples flags 0x02\r\n0\/1C56748 | 0\/1C58480 | 0\/1C566F0 | 750 | Btree | INSERT_LEAF | 7453 | off 159\r\n0\/1C58480 | 0\/1C59B80 | 0\/1C56748 | 750 | Btree | INSERT_LEAF | 5885 | off 136\r\n0\/1C59B80 | 0\/1C5BB90 | 0\/1C58480 | 750 | XLOG | FPI_FOR_HINT | 8177 |\r\n0\/1C5BB90 | 0\/1C5BC68 | 0\/1C59B80 | 750 | Heap | INSERT | 211 | off 15 flags 0x00\r\n0\/1C5BC68 | 0\/1C5BCA8 | 0\/1C5BB90 | 750 | Btree | INSERT_LEAF | 64 | off 250\r\n0\/1C5BCA8 | 0\/1C5BCF0 | 0\/1C5BC68 | 750 | Btree | INSERT_LEAF | 72 | off 45\r\n0\/1C5BCF0 | 0\/1C5BD48 | 0\/1C5BCA8 | 750 | Heap2 | MULTI_INSERT | 85 | 1 tuples flags 0x02\r\n0\/1C5BD48 | 0\/1C5BD90 | 0\/1C5BCF0 | 750 | Btree | INSERT_LEAF | 72 | off 159\r\n0\/1C5BD90 | 0\/1C5D3A0 | 0\/1C5BD48 | 750 | Btree | INSERT_LEAF | 5621 | off 108\r\n0\/1C5D3A0 | 0\/1C5D3D8 | 0\/1C5BD90 | 0 | Standby | RUNNING_XACTS | 54 | nextXid 751 latestCompletedXid 749 oldestRunningXid 750; 1 xacts: 750\r\n0\/1C5D3D8 | 0\/1C5E868 | 0\/1C5D3A0 | 750 | Heap | INSERT | 5238 | off 1 flags 0x01\r\n0\/1C5E868 | 0\/1C5F1F0 | 0\/1C5D3D8 | 750 | Btree | INSERT_LEAF | 2433 | off 117\r\n0\/1C5F1F0 | 0\/1C5F238 | 0\/1C5E868 | 750 | Btree | INSERT_LEAF | 72 | off 29\r\n0\/1C5F238 | 0\/1C60BA0 | 0\/1C5F1F0 | 750 | Btree | INSERT_LEAF | 6477 | off 277\r\n0\/1C60BA0 | 0\/1C60D58 | 0\/1C5F238 | 750 | Heap2 | MULTI_INSERT | 440 | 3 tuples flags 0x02\r\n0\/1C60D58 | 0\/1C62628 | 0\/1C60BA0 | 750 | Btree | INSERT_LEAF | 6321 | off 173\r\n0\/1C62628 | 0\/1C630A0 | 0\/1C60D58 | 750 | Btree | INSERT_LEAF | 2673 | off 129\r\n0\/1C630A0 | 0\/1C630E8 | 0\/1C62628 | 750 | Btree | INSERT_LEAF | 72 | off 174\r\n0\/1C630E8 | 0\/1C63128 | 0\/1C630A0 | 750 | Btree | INSERT_LEAF | 64 | off 130\r\n0\/1C63128 | 0\/1C63170 | 0\/1C630E8 | 750 | Btree | INSERT_LEAF | 72 | off 173\r\n0\/1C63170 | 0\/1C631B0 | 0\/1C63128 | 750 | Btree | INSERT_LEAF | 64 | off 131\r\n0\/1C631B0 | 0\/1C63368 | 0\/1C63170 | 750 | Heap2 | MULTI_INSERT | 440 | 3 tuples flags 0x00\r\n0\/1C63368 | 0\/1C653B8 | 0\/1C631B0 | 750 | XLOG | FPI_FOR_HINT | 8241 |\r\n0\/1C653B8 | 0\/1C67408 | 0\/1C63368 | 750 | XLOG | FPI_FOR_HINT | 8241 |\r\n0\/1C67408 | 0\/1C69458 | 0\/1C653B8 | 750 | XLOG | FPI_FOR_HINT | 8241 |\r\n0\/1C69458 | 0\/1C69610 | 0\/1C67408 | 750 | Heap2 | MULTI_INSERT+INIT | 434 | 3 tuples flags 0x02\r\n0\/1C69610 | 0\/1C69658 | 0\/1C69458 | 750 | Btree | INSERT_LEAF | 72 | off 173\r\n0\/1C69658 | 0\/1C69698 | 0\/1C69610 | 750 | Btree | INSERT_LEAF | 64 | off 129\r\n0\/1C69698 | 0\/1C696E0 | 0\/1C69658 | 750 | Btree | INSERT_LEAF | 72 | off 177\r\n0\/1C696E0 | 0\/1C69720 | 0\/1C69698 | 750 | Btree | INSERT_LEAF | 64 | off 129\r\n0\/1C69720 | 0\/1C69768 | 0\/1C696E0 | 750 | Btree | INSERT_LEAF | 72 | off 173\r\n0\/1C69768 | 0\/1C697A8 | 0\/1C69720 | 750 | Btree | INSERT_LEAF | 64 | off 129\r\n0\/1C697A8 | 0\/1C697F0 | 0\/1C69768 | 750 | Btree | INSERT_LEAF | 72 | off 178\r\n0\/1C697F0 | 0\/1C69830 | 0\/1C697A8 | 750 | Btree | INSERT_LEAF | 64 | off 129\r\n0\/1C69830 | 0\/1C69878 | 0\/1C697F0 | 750 | Btree | INSERT_LEAF | 72 | off 173\r\n0\/1C69878 | 0\/1C698B8 | 0\/1C69830 | 750 | Btree | INSERT_LEAF | 64 | off 129\r\n0\/1C698B8 | 0\/1C69900 | 0\/1C69878 | 750 | Btree | INSERT_LEAF | 72 | off 179\r\n0\/1C69900 | 0\/1C69940 | 0\/1C698B8 | 750 | Btree | INSERT_LEAF | 64 | off 129\r\n0\/1C69940 | 0\/1C69998 | 0\/1C69900 | 750 | Heap2 | MULTI_INSERT | 85 | 1 tuples flags 0x02\r\n0\/1C69998 | 0\/1C6B390 | 0\/1C69940 | 750 | Btree | INSERT_LEAF | 6617 | off 113\r\n0\/1C6B390 | 0\/1C6B3D8 | 0\/1C69998 | 750 | Btree | INSERT_LEAF | 72 | off 139\r\n0\/1C6B3D8 | 0\/1C6B418 | 0\/1C6B390 | 750 | Heap2 | PRUNE | 60 | latestRemovedXid 749 nredirected 0 ndead 3\r\n0\/1C6B418 | 0\/1C6B448 | 0\/1C6B3D8 | 750 | Standby | LOCK | 42 | xid 750 db 16388 rel 16407\r\n0\/1C6B448 | 0\/1C6B620 | 0\/1C6B418 | 750 | Transaction | COMMIT | 469 | 2022-10-26 10:05:38.29952+00; inval msgs: catcache 80 catcache 79 catcache 80 catcache 79 catcache 55 catcache 54 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 catcache 7 catcache 6 snapshot 2608 relcache 16407\r\n0\/1C6B620 | 0\/1C6B670 | 0\/1C6B448 | 751 | Heap | INSERT+INIT | 79 | off 1 flags 0x00\r\n0\/1C6B670 | 0\/1C6B698 | 0\/1C6B620 | 751 | Transaction | COMMIT | 34 | 2022-10-26 10:05:42.821037+00\r\n0\/1C6B698 | 0\/1C6B6E8 | 0\/1C6B670 | 752 | Heap | INSERT | 79 | off 2 flags 0x00\r\n0\/1C6B6E8 | 0\/1C6B710 | 0\/1C6B698 | 752 | Transaction | COMMIT | 34 | 2022-10-26 10:05:47.73708+00\r\n0\/1C6B710 | 0\/1C6B748 | 0\/1C6B6E8 | 0 | Standby | RUNNING_XACTS | 50 | nextXid 753 latestCompletedXid 752 oldestRunningXid 753\r\n0\/1C6B748 | 0\/1C6B798 | 0\/1C6B710 | 753 | Heap | INSERT | 75 | off 3 flags 0x00\r\n0\/1C6B798 | 0\/1C6B7C0 | 0\/1C6B748 | 753 | Transaction | COMMIT | 34 | 2022-10-26 10:05:53.606115+00\r\n0\/1C6B7C0 | 0\/1C6B808 | 0\/1C6B798 | 754 | Heap | HOT_UPDATE | 71 | off 3 xmax 754 flags 0x60 ; new off 4 xmax 0\r\n0\/1C6B808 | 0\/1C6B830 | 0\/1C6B7C0 | 754 | Transaction | COMMIT | 34 | 2022-10-26 10:06:08.786415+00\r\n0\/1C6B830 | 0\/1C6B868 | 0\/1C6B808 | 0 | Standby | RUNNING_XACTS | 50 | nextXid 755 latestCompletedXid 754 oldestRunningXid 755\r\n0\/1C6B868 | 0\/1C6B8A0 | 0\/1C6B830 | 755 | Heap | DELETE | 54 | off 2 flags 0x00 KEYS_UPDATED\r\n0\/1C6B8A0 | 0\/1C6B8C8 | 0\/1C6B868 | 755 | Transaction | COMMIT | 34 | 2022-10-26 10:06:17.172234+00\r\n(63 rows)\r\n\r\nTime: 0.775 ms<\/pre>\n<p>Nous voyons, un \u00e0 un, les valeurs &#8220;lsn&#8221; s&#8217;incr\u00e9menter au fur et \u00e0 mesure des transactions trait\u00e9es sur l&#8217;instance.<\/p>\n<p>On voit que notre ordre &#8220;DELETE&#8221; apparait au lsn 0\/1C6B868. Le xid de notre transaction devient le 755.<br \/>\nL&#8217;ordre est directement trait\u00e9 dans la table heap.<\/p>\n<p>&nbsp;<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">(postgres@[local]:5434) [postgres] \\x\r\nExpanded display is on.\r\n(postgres@[local]:5434) [postgres] select * from pg_get_wal_records_info('0\/1C51F80','0\/1C6B8C8') where record_type='DELETE';\r\n-[ RECORD 1 ]----+------------------------------------------------\r\nstart_lsn | 0\/1C6B868\r\nend_lsn | 0\/1C6B8A0\r\nprev_lsn | 0\/1C6B830\r\nxid | 755\r\nresource_manager | Heap\r\nrecord_type | DELETE\r\nrecord_length | 54\r\nmain_data_length | 8\r\nfpi_length | 0\r\ndescription | off 2 flags 0x00 KEYS_UPDATED\r\nblock_ref | blkref #0: rel 1663\/16388\/16407 fork main blk 0\r\n\r\nTime: 0.610 ms<\/pre>\n<p>&nbsp;<\/p>\n<p>Cela veut donc dire que si vous souhaitez revenir exactement avant le DELETE, il faudra donc choisir, dans la liste, de revenir avant le lsn 0\/1C6B868.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">0\/1C6B830 | 0\/1C6B868 | 0\/1C6B808 | 0 | Standby | RUNNING_XACTS | 50 | nextXid 755 latestCompletedXid 754 oldestRunningXid 755\r\n0\/1C6B868 | 0\/1C6B8A0 | 0\/1C6B830 | 755 | Heap | DELETE | 54 | off 2 flags 0x00 KEYS_UPDATED<\/pre>\n<p>Dans le cas d&#8217;une restauration PITR, il sera indispensable de pr\u00e9ciser les valeurs suivantes dans le fichier &#8220;postgrsql.conf&#8221; ou &#8220;postgresql.auto.conf&#8221; :<\/p>\n<p>&nbsp;<\/p>\n<pre class=\"brush: bash; title: ; notranslate\" title=\"\">recovery_target_lsn = '0\/1C6B868'\r\nrecovery_target_inclusive = false<\/pre>\n<p>&nbsp;<\/p>\n<p>Attention, tout de m\u00eame, gardez \u00e0 l&#8217;esprit, que les fonctions utilis\u00e9es par l&#8217;extension &#8220;<span style=\"color: #993366;\">pg_walinspect<\/span>&#8221; permettent de retrouver le type de transaction g\u00e9n\u00e9r\u00e9e \u00e0 une valeur &#8220;lsn&#8221; pr\u00e9cise, elles permettent \u00e9galement d&#8217;avoir certaines statistiques sur le poids des enregistrements en base, et la taille de cet enregistrement.<br \/>\nConsultez la fonction &#8220;<span style=\"color: #993366;\">pg_get_wal_stats<\/span>&#8221; pour avoir pas mal d&#8217;information \u00e0 ce sujet.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">(postgres@[local]:5434) [postgres] &amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt; select * from pg_get_wal_stats('0\/1C51F80','0\/1C6B8C8',true) where count&amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;amp;gt;0;\r\nresource_manager\/record_type | count | count_percentage | record_size | record_size_percentage | fpi_size | fpi_size_percentage | combined_size | combined_size_percentage\r\n------------------------------+-------+--------------------+-------------+------------------------+----------+---------------------+---------------+--------------------------\r\nXLOG\/FPI_FOR_HINT | 6 | 9.523809523809524 | 294 | 5.2285256980259645 | 45380 | 45.996351104804376 | 45674 | 43.798126252601094\r\nTransaction\/COMMIT | 6 | 9.523809523809524 | 639 | 11.364040547750312 | 0 | 0 | 639 | 0.61275567446276\r\nStorage\/CREATE | 1 | 1.5873015873015872 | 42 | 0.7469322425751378 | 0 | 0 | 42 | 0.04027502085670723\r\nStandby\/LOCK | 1 | 1.5873015873015872 | 42 | 0.7469322425751378 | 0 | 0 | 42 | 0.04027502085670723\r\nStandby\/RUNNING_XACTS | 3 | 4.761904761904762 | 154 | 2.7387515561088387 | 0 | 0 | 154 | 0.1476750764745932\r\nHeap2\/PRUNE | 1 | 1.5873015873015872 | 60 | 1.0670460608216255 | 0 | 0 | 60 | 0.05753574408101033\r\nHeap2\/MULTI_INSERT | 5 | 7.936507936507937 | 1135 | 20.184954650542416 | 0 | 0 | 1135 | 1.088384492199112\r\nHeap2\/MULTI_INSERT+INIT | 1 | 1.5873015873015872 | 434 | 7.718299839943091 | 0 | 0 | 434 | 0.41617521551930803\r\nHeap\/INSERT | 5 | 7.936507936507937 | 630 | 11.203983638627067 | 5184 | 5.254409081694709 | 5814 | 5.575213601449901\r\nHeap\/DELETE | 1 | 1.5873015873015872 | 54 | 0.960341454739463 | 0 | 0 | 54 | 0.051782169672909294\r\nHeap\/HOT_UPDATE | 1 | 1.5873015873015872 | 71 | 1.2626711719722568 | 0 | 0 | 71 | 0.06808396382919556\r\nHeap\/INSERT+INIT | 1 | 1.5873015873015872 | 79 | 1.4049439800818069 | 0 | 0 | 79 | 0.07575539637333026\r\nBtree\/INSERT_LEAF | 31 | 49.20634920634921 | 1989 | 35.37257691623689 | 48096 | 48.749239813500914 | 50085 | 48.027962371623374\r\n(13 rows)<\/pre>\n<p>Notre ordre DELETE n&#8217;a \u00e9t\u00e9 ex\u00e9cut\u00e9 qu&#8217;une seule fois, et ne prend que 54 octets dans le WAL.<br \/>\nEn revanche, vous n&#8217;aurez pas d&#8217;informations sur le nom de l&#8217;objet modifi\u00e9, ni la requ\u00eate compl\u00e8te avec le pr\u00e9dicat enregistr\u00e9e.<\/p>\n<p>Il faudra donc faire une recherche assez minutieuse et pr\u00e9cise afin de filtrer la bonne transaction dans la cadre d&#8217;une instance hautement transactionnelle.<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>\ud83d\ude42<\/p>\n<p>&nbsp;<\/p>\n<a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-twitter nolightbox\" data-provider=\"twitter\" target=\"_blank\" rel=\"nofollow\" title=\"Share on Twitter\" href=\"https:\/\/twitter.com\/intent\/tweet?url=https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F9432&#038;text=Article%20sur%20le%20blog%20de%20la%20Capdata%20Tech%20Team%20%3A%20\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px;margin-right:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"twitter\" title=\"Share on Twitter\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/twitter.png\" \/><\/a><a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-linkedin nolightbox\" data-provider=\"linkedin\" target=\"_blank\" rel=\"nofollow\" title=\"Share on Linkedin\" href=\"https:\/\/www.linkedin.com\/shareArticle?mini=true&#038;url=https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F9432&#038;title=PostgreSQL%2015%20%3A%20parmi%20les%20nouveaut%C3%A9s%2C%20lire%20dans%20les%20WAL%20avec%20PSQL\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px;margin-right:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"linkedin\" title=\"Share on Linkedin\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/linkedin.png\" \/><\/a><a class=\"synved-social-button synved-social-button-share synved-social-size-24 synved-social-resolution-single synved-social-provider-mail nolightbox\" data-provider=\"mail\" rel=\"nofollow\" title=\"Share by email\" href=\"mailto:?subject=PostgreSQL%2015%20%3A%20parmi%20les%20nouveaut%C3%A9s%2C%20lire%20dans%20les%20WAL%20avec%20PSQL&#038;body=Article%20sur%20le%20blog%20de%20la%20Capdata%20Tech%20Team%20%3A%20:%20https%3A%2F%2Fblog.capdata.fr%2Findex.php%2Fwp-json%2Fwp%2Fv2%2Fposts%2F9432\" style=\"font-size: 0px;width:24px;height:24px;margin:0;margin-bottom:5px\"><img loading=\"lazy\" decoding=\"async\" alt=\"mail\" title=\"Share by email\" class=\"synved-share-image synved-social-image synved-social-image-share\" width=\"24\" height=\"24\" style=\"display: inline;width:24px;height:24px;margin: 0;padding: 0;border: none;box-shadow: none\" src=\"https:\/\/blog.capdata.fr\/wp-content\/plugins\/social-media-feather\/synved-social\/image\/social\/regular\/48x48\/mail.png\" \/><\/a>","protected":false},"excerpt":{"rendered":"<p>lo ca y&#8217;est la nouvelle version de PostgreSQL est maintenant parmi nous, et ce depuis le 13 octobre 2022. Pour ceux et celles qui avaient d\u00e9j\u00e0 essay\u00e9 la version beta auparavant, vous avez pu qualifier certaines nouveaut\u00e9s, avec cette liste&hellip; <a href=\"https:\/\/blog.capdata.fr\/index.php\/postgresql-15-parmi-les-nouveautes-lire-dans-les-wal-avec-du-sql\/\" class=\"more-link\">Continuer la lecture <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":32,"featured_media":9452,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[266],"tags":[447],"class_list":["post-9432","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-postgresql","tag-postgresql15"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v20.8 - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>PostgreSQL 15 : parmi les nouveaut\u00e9s, lire dans les WAL avec PSQL - 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\/postgresql-15-parmi-les-nouveautes-lire-dans-les-wal-avec-du-sql\/\" \/>\n<meta property=\"og:locale\" content=\"fr_FR\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"PostgreSQL 15 : parmi les nouveaut\u00e9s, lire dans les WAL avec PSQL - Capdata TECH BLOG\" \/>\n<meta property=\"og:description\" content=\"lo ca y&#8217;est la nouvelle version de PostgreSQL est maintenant parmi nous, et ce depuis le 13 octobre 2022. Pour ceux et celles qui avaient d\u00e9j\u00e0 essay\u00e9 la version beta auparavant, vous avez pu qualifier certaines nouveaut\u00e9s, avec cette liste&hellip; Continuer la lecture &rarr;\" \/>\n<meta property=\"og:url\" content=\"https:\/\/blog.capdata.fr\/index.php\/postgresql-15-parmi-les-nouveautes-lire-dans-les-wal-avec-du-sql\/\" \/>\n<meta property=\"og:site_name\" content=\"Capdata TECH BLOG\" \/>\n<meta property=\"article:published_time\" content=\"2022-10-28T08:58:08+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2023-03-29T14:55:40+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/10\/1-boule-de-billard-n15.png\" \/>\n\t<meta property=\"og:image:width\" content=\"350\" \/>\n\t<meta property=\"og:image:height\" content=\"350\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Emmanuel RAMI\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"\u00c9crit par\" \/>\n\t<meta name=\"twitter:data1\" content=\"Emmanuel RAMI\" \/>\n\t<meta name=\"twitter:label2\" content=\"Dur\u00e9e de lecture estim\u00e9e\" \/>\n\t<meta name=\"twitter:data2\" content=\"14 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\/postgresql-15-parmi-les-nouveautes-lire-dans-les-wal-avec-du-sql\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/postgresql-15-parmi-les-nouveautes-lire-dans-les-wal-avec-du-sql\/\"},\"author\":{\"name\":\"Emmanuel RAMI\",\"@id\":\"https:\/\/blog.capdata.fr\/#\/schema\/person\/797b9b6698fa35f7ce3e9a70a8b102ae\"},\"headline\":\"PostgreSQL 15 : parmi les nouveaut\u00e9s, lire dans les WAL avec PSQL\",\"datePublished\":\"2022-10-28T08:58:08+00:00\",\"dateModified\":\"2023-03-29T14:55:40+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/postgresql-15-parmi-les-nouveautes-lire-dans-les-wal-avec-du-sql\/\"},\"wordCount\":2612,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/blog.capdata.fr\/#organization\"},\"keywords\":[\"postgresql15\"],\"articleSection\":[\"PostgreSQL\"],\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/postgresql-15-parmi-les-nouveautes-lire-dans-les-wal-avec-du-sql\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/postgresql-15-parmi-les-nouveautes-lire-dans-les-wal-avec-du-sql\/\",\"url\":\"https:\/\/blog.capdata.fr\/index.php\/postgresql-15-parmi-les-nouveautes-lire-dans-les-wal-avec-du-sql\/\",\"name\":\"PostgreSQL 15 : parmi les nouveaut\u00e9s, lire dans les WAL avec PSQL - Capdata TECH BLOG\",\"isPartOf\":{\"@id\":\"https:\/\/blog.capdata.fr\/#website\"},\"datePublished\":\"2022-10-28T08:58:08+00:00\",\"dateModified\":\"2023-03-29T14:55:40+00:00\",\"breadcrumb\":{\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/postgresql-15-parmi-les-nouveautes-lire-dans-les-wal-avec-du-sql\/#breadcrumb\"},\"inLanguage\":\"fr-FR\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/blog.capdata.fr\/index.php\/postgresql-15-parmi-les-nouveautes-lire-dans-les-wal-avec-du-sql\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/blog.capdata.fr\/index.php\/postgresql-15-parmi-les-nouveautes-lire-dans-les-wal-avec-du-sql\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/blog.capdata.fr\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"PostgreSQL 15 : parmi les nouveaut\u00e9s, lire dans les WAL avec PSQL\"}]},{\"@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\/797b9b6698fa35f7ce3e9a70a8b102ae\",\"name\":\"Emmanuel RAMI\",\"sameAs\":[\"https:\/\/blog.capdata.fr\"],\"url\":\"https:\/\/blog.capdata.fr\/index.php\/author\/erami\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"PostgreSQL 15 : parmi les nouveaut\u00e9s, lire dans les WAL avec PSQL - 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\/postgresql-15-parmi-les-nouveautes-lire-dans-les-wal-avec-du-sql\/","og_locale":"fr_FR","og_type":"article","og_title":"PostgreSQL 15 : parmi les nouveaut\u00e9s, lire dans les WAL avec PSQL - Capdata TECH BLOG","og_description":"lo ca y&#8217;est la nouvelle version de PostgreSQL est maintenant parmi nous, et ce depuis le 13 octobre 2022. Pour ceux et celles qui avaient d\u00e9j\u00e0 essay\u00e9 la version beta auparavant, vous avez pu qualifier certaines nouveaut\u00e9s, avec cette liste&hellip; Continuer la lecture &rarr;","og_url":"https:\/\/blog.capdata.fr\/index.php\/postgresql-15-parmi-les-nouveautes-lire-dans-les-wal-avec-du-sql\/","og_site_name":"Capdata TECH BLOG","article_published_time":"2022-10-28T08:58:08+00:00","article_modified_time":"2023-03-29T14:55:40+00:00","og_image":[{"width":350,"height":350,"url":"https:\/\/blog.capdata.fr\/wp-content\/uploads\/2022\/10\/1-boule-de-billard-n15.png","type":"image\/png"}],"author":"Emmanuel RAMI","twitter_card":"summary_large_image","twitter_misc":{"\u00c9crit par":"Emmanuel RAMI","Dur\u00e9e de lecture estim\u00e9e":"14 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/blog.capdata.fr\/index.php\/postgresql-15-parmi-les-nouveautes-lire-dans-les-wal-avec-du-sql\/#article","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/index.php\/postgresql-15-parmi-les-nouveautes-lire-dans-les-wal-avec-du-sql\/"},"author":{"name":"Emmanuel RAMI","@id":"https:\/\/blog.capdata.fr\/#\/schema\/person\/797b9b6698fa35f7ce3e9a70a8b102ae"},"headline":"PostgreSQL 15 : parmi les nouveaut\u00e9s, lire dans les WAL avec PSQL","datePublished":"2022-10-28T08:58:08+00:00","dateModified":"2023-03-29T14:55:40+00:00","mainEntityOfPage":{"@id":"https:\/\/blog.capdata.fr\/index.php\/postgresql-15-parmi-les-nouveautes-lire-dans-les-wal-avec-du-sql\/"},"wordCount":2612,"commentCount":0,"publisher":{"@id":"https:\/\/blog.capdata.fr\/#organization"},"keywords":["postgresql15"],"articleSection":["PostgreSQL"],"inLanguage":"fr-FR","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/blog.capdata.fr\/index.php\/postgresql-15-parmi-les-nouveautes-lire-dans-les-wal-avec-du-sql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/blog.capdata.fr\/index.php\/postgresql-15-parmi-les-nouveautes-lire-dans-les-wal-avec-du-sql\/","url":"https:\/\/blog.capdata.fr\/index.php\/postgresql-15-parmi-les-nouveautes-lire-dans-les-wal-avec-du-sql\/","name":"PostgreSQL 15 : parmi les nouveaut\u00e9s, lire dans les WAL avec PSQL - Capdata TECH BLOG","isPartOf":{"@id":"https:\/\/blog.capdata.fr\/#website"},"datePublished":"2022-10-28T08:58:08+00:00","dateModified":"2023-03-29T14:55:40+00:00","breadcrumb":{"@id":"https:\/\/blog.capdata.fr\/index.php\/postgresql-15-parmi-les-nouveautes-lire-dans-les-wal-avec-du-sql\/#breadcrumb"},"inLanguage":"fr-FR","potentialAction":[{"@type":"ReadAction","target":["https:\/\/blog.capdata.fr\/index.php\/postgresql-15-parmi-les-nouveautes-lire-dans-les-wal-avec-du-sql\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/blog.capdata.fr\/index.php\/postgresql-15-parmi-les-nouveautes-lire-dans-les-wal-avec-du-sql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/blog.capdata.fr\/"},{"@type":"ListItem","position":2,"name":"PostgreSQL 15 : parmi les nouveaut\u00e9s, lire dans les WAL avec PSQL"}]},{"@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\/797b9b6698fa35f7ce3e9a70a8b102ae","name":"Emmanuel RAMI","sameAs":["https:\/\/blog.capdata.fr"],"url":"https:\/\/blog.capdata.fr\/index.php\/author\/erami\/"}]}},"_links":{"self":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/9432","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\/32"}],"replies":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/comments?post=9432"}],"version-history":[{"count":43,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/9432\/revisions"}],"predecessor-version":[{"id":9963,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/posts\/9432\/revisions\/9963"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media\/9452"}],"wp:attachment":[{"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/media?parent=9432"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/categories?post=9432"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/blog.capdata.fr\/index.php\/wp-json\/wp\/v2\/tags?post=9432"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}