0

PostgreSQL 15 : parmi les nouveautés, lire dans les WAL avec PSQL

Posted on by Emmanuel RAMI
twitterlinkedinmail

lo

ca y’est la nouvelle version de PostgreSQL est maintenant parmi nous, et ce depuis le 13 octobre 2022.
Pour ceux et celles qui avaient déjà essayé la version beta auparavant, vous avez pu qualifier certaines nouveautés, avec cette liste non exhaustive :

  • Nouvelles méthodes de compression avec Zstandard compression et LZ4.
    • Les algorithmes Zstandard et LZ4 peuvent être utilisés pour la génération des WALs, mais aussi avec Pg_basebackup. Ces algorithmes sont bien plus performants qu’un “gzip” classique.
  • Possibilité de prendre en charge ICU collation comme collation par défaut pour l’instance ou la base directement.
    • ICU est une alternative à “libc” , le fonctionnement des tris et la comparaison des caractères sont gérés de façon différente.
  • Meilleures performances lors d’opérations de tris en mémoire ou sur disque, notamment lors de l’utilisation de fonctions telles : row_number(), rank(), dense_rank() and count().

 

  • Il est possible d’utiliser des expressions régulières pour travailler sur des chaines de caractères avec des fonctions comme regexp_count(), regexp_instr() regexp_like() et regexp_substr().

 

  • Si vous utilisez la réplication logique, il vous sera possible à présent, avec la version 15 de PostgreSQL, de filtrer vos publications avec un sous ensemble.
    • Utiliser une clause WHERE directement dans la création de votre publication.
      Exemple :Création de notre publication avec condition, sur l’instance principale:

      (postgres@[local]:5434) [manu] create publication publication1 for table public.test where (nom = 'manu');
CREATE PUBLICATION

      Création de notre suscription sur l’instance secours :

      (postgres@[local]:5435) [manu] create subscription subscription1 connection 'port=5434' publication publication1
NOTICE: created replication slot "subscription1" on publisher
CREATE SUBSCRIPTION
  • La commande MERGE est enfin compatible sous PostgreSQL 15 !
    • Cette commande permet de faciliter grandement les ordres SQL conditionnelles. Le MERGE s’utilise avec un “WHEN MATCHED” sur la première action de la table et un “WHEN NOT MATCHED” sur seconde action sur la table cible.
      Un petit exemple, extrait du site postgresql.org pour mieux comprendre :

       MERGE INTO customer_account ca
USING recent_transactions t
ON t.customer_id = ca.customer_id
WHEN MATCHED THEN
  UPDATE SET balance = balance + transaction_value
WHEN NOT MATCHED THEN
  INSERT (customer_id, balance)
  VALUES (t.customer_id, t.transaction_value);
  • Et surtout, lire les enregistrements directement dans les WAL grâce à “pg_wallinspect”. Ce  que nous allons voir plus en détail ci dessous.

 

PG_WALINSPECT

 

Il s’agit d’une extension, proposée dans les contribs de la version 15 de PostgreSQL, qui va faciliter la lecture des différentes transactions au sein d’une instance de base de données en allant directement interroger les informations dans les WALs.

Les éditeurs comme Oracle et SQL Server permettent deja depuis un certain temps de faire ces opérations. On pense à Logminer pour Oracle, et l’utilisation du package DBMS_LOGMNR et des vues “v$logmnr_****”. Ou encore de la fonction “fn_dump_dblog()” pour SQL Server, et la je vous invite à vous remémorer cet article de notre blog qui permet de rechercher le coupable d’un malencontreux DELETE !

Avec PostgreSQL 15, c’est une extension qui doit être ajoutée pour gérer cette opération. Nous pouvions déjà utiliser “pg_waldump” pour aller chercher des informations directement dans les WALs. Mais ici, avec l’extension “pg_walinspect”, nous pouvons faire cela avec du code SQL sous l’outil psql.

[postgres~]$ vi $PGDATA/postgresql.conf
....
shared_preload_libraries = 'pg_walinspect' # (change requires restart)
...
[postgres ~]$ pg_ctl -D $PGDATA restart

 

Par la suite, on crée cette extension dans PostgreSQL, avec l’outil “psql’

(postgres@[local]:5434) [postgres] create extension pg_walinspect; 
CREATE EXTENSION 
Time: 21.598 ms

 

Voici les fonctions fournies par cette extension, et qui nous permettront de chercher les différents enregistrements dans les WALs

(postgres@[local]:5434) [postgres] \dx+ pg_walinspect
Objects in extension "pg_walinspect"
Object description
-----------------------------------------------------------
function pg_get_wal_record_info(pg_lsn)
function pg_get_wal_records_info(pg_lsn,pg_lsn)
function pg_get_wal_records_info_till_end_of_wal(pg_lsn)
function pg_get_wal_stats(pg_lsn,pg_lsn,boolean)
function pg_get_wal_stats_till_end_of_wal(pg_lsn,boolean)
(5 rows)

 

Exemple d’utilisation

 

Nous allons créer une table “employés”, dans laquelle nous insérons des données.
Avant cela, repérez à quel valeur “lsn” vous êtes dans votre WAL courant.

(postgres@[local]:5434) [manu] SELECT pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
0/1C51F80
(1 row)

 

Puis

(postgres@[local]:5434) [manu] create table employes (nom varchar(20), societe varchar(20), entree date);
CREATE TABLE
Time: 18.370 ms
(postgres@[local]:5434) [manu] insert into employes values ('Emmanuel','Capdata','12/01/2010');
INSERT 0 1
Time: 3.764 ms
(postgres@[local]:5434) [manu] insert into employes values ('Olivier','Osmozium','05/23/2015');
INSERT 0 1
Time: 1.410 ms
(postgres@[local]:5434) [manu] insert into employes values ('Jerome','Oracle','06/10/2002');
INSERT 0 1
Time: 1.453 ms
(postgres@[local]:5434) [manu] select * from employes;
nom | societe | entree
----------+----------+------------
Emmanuel | Capdata | 2010-12-01
Olivier | Osmozium | 2015-05-23
Jerome | Oracle | 2002-06-10
(3 rows)

 

Puis une mise à jour dans celle ci

(postgres@[local]:5434) [manu] update employes set entree='08/10/2002' where nom='Jerome';
UPDATE 1
Time: 11.179 ms
(postgres@[local]:5434) [manu] select * from employes;
nom | societe | entree
----------+----------+------------
Emmanuel | Capdata | 2010-12-01
Olivier | Osmozium | 2015-05-23
Jerome | Oracle | 2002-08-10
(3 rows)

 

et un DELETE

(postgres@[local]:5434) [manu] delete from employes where nom='Olivier';
DELETE 1
Time: 1.376 ms
(postgres@[local]:5434) [manu] select * from employes;
nom | societe | entree
----------+---------+------------
Emmanuel | Capdata | 2010-12-01
Jerome | Oracle | 2002-08-10
(2 rows)

 

Mais alors, si je veux retrouver à quel moment a été fait le DELETE ? Et surtout si je veux retrouver mes données de ma table, via une restauration PITR !

Pour cela, repérez à nouveau, la velur “lsn” courante dans le WAL, après les opérations:

(postgres@[local]:5434) [postgres] SELECT pg_current_wal_lsn();
pg_current_wal_lsn
--------------------
0/1C6B8C8
(1 row)

 

Utilisons à présent la fonction “pg_get_wal_records_info” pour aller chercher, directement dans les WALs, les ordres enregistrés. Il faudra passer en paramètres le 1er lsn trouvé, avant les ordres SQL exécutés pour notre table “employés”, puis en second paramètre, le lsn post opérations.

Ce qui donne les informations suivantes :

 

(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');
start_lsn | end_lsn | prev_lsn | xid | resource_manager | record_type | record_length | description
-----------+-----------+-----------+-----+------------------+-------------------+---------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
0/1C51F80 | 0/1C535E0 | 0/1C51D68 | 0 | XLOG | FPI_FOR_HINT | 5697 |
0/1C535E0 | 0/1C551A0 | 0/1C51F80 | 0 | XLOG | FPI_FOR_HINT | 7077 |
0/1C551A0 | 0/1C551D0 | 0/1C535E0 | 0 | Storage | CREATE | 42 | base/16388/16407
0/1C551D0 | 0/1C552A8 | 0/1C551A0 | 750 | Heap | INSERT | 211 | off 14 flags 0x00
0/1C552A8 | 0/1C566A8 | 0/1C551D0 | 750 | Btree | INSERT_LEAF | 5093 | off 250
0/1C566A8 | 0/1C566F0 | 0/1C552A8 | 750 | Btree | INSERT_LEAF | 72 | off 127
0/1C566F0 | 0/1C56748 | 0/1C566A8 | 750 | Heap2 | MULTI_INSERT | 85 | 1 tuples flags 0x02
0/1C56748 | 0/1C58480 | 0/1C566F0 | 750 | Btree | INSERT_LEAF | 7453 | off 159
0/1C58480 | 0/1C59B80 | 0/1C56748 | 750 | Btree | INSERT_LEAF | 5885 | off 136
0/1C59B80 | 0/1C5BB90 | 0/1C58480 | 750 | XLOG | FPI_FOR_HINT | 8177 |
0/1C5BB90 | 0/1C5BC68 | 0/1C59B80 | 750 | Heap | INSERT | 211 | off 15 flags 0x00
0/1C5BC68 | 0/1C5BCA8 | 0/1C5BB90 | 750 | Btree | INSERT_LEAF | 64 | off 250
0/1C5BCA8 | 0/1C5BCF0 | 0/1C5BC68 | 750 | Btree | INSERT_LEAF | 72 | off 45
0/1C5BCF0 | 0/1C5BD48 | 0/1C5BCA8 | 750 | Heap2 | MULTI_INSERT | 85 | 1 tuples flags 0x02
0/1C5BD48 | 0/1C5BD90 | 0/1C5BCF0 | 750 | Btree | INSERT_LEAF | 72 | off 159
0/1C5BD90 | 0/1C5D3A0 | 0/1C5BD48 | 750 | Btree | INSERT_LEAF | 5621 | off 108
0/1C5D3A0 | 0/1C5D3D8 | 0/1C5BD90 | 0 | Standby | RUNNING_XACTS | 54 | nextXid 751 latestCompletedXid 749 oldestRunningXid 750; 1 xacts: 750
0/1C5D3D8 | 0/1C5E868 | 0/1C5D3A0 | 750 | Heap | INSERT | 5238 | off 1 flags 0x01
0/1C5E868 | 0/1C5F1F0 | 0/1C5D3D8 | 750 | Btree | INSERT_LEAF | 2433 | off 117
0/1C5F1F0 | 0/1C5F238 | 0/1C5E868 | 750 | Btree | INSERT_LEAF | 72 | off 29
0/1C5F238 | 0/1C60BA0 | 0/1C5F1F0 | 750 | Btree | INSERT_LEAF | 6477 | off 277
0/1C60BA0 | 0/1C60D58 | 0/1C5F238 | 750 | Heap2 | MULTI_INSERT | 440 | 3 tuples flags 0x02
0/1C60D58 | 0/1C62628 | 0/1C60BA0 | 750 | Btree | INSERT_LEAF | 6321 | off 173
0/1C62628 | 0/1C630A0 | 0/1C60D58 | 750 | Btree | INSERT_LEAF | 2673 | off 129
0/1C630A0 | 0/1C630E8 | 0/1C62628 | 750 | Btree | INSERT_LEAF | 72 | off 174
0/1C630E8 | 0/1C63128 | 0/1C630A0 | 750 | Btree | INSERT_LEAF | 64 | off 130
0/1C63128 | 0/1C63170 | 0/1C630E8 | 750 | Btree | INSERT_LEAF | 72 | off 173
0/1C63170 | 0/1C631B0 | 0/1C63128 | 750 | Btree | INSERT_LEAF | 64 | off 131
0/1C631B0 | 0/1C63368 | 0/1C63170 | 750 | Heap2 | MULTI_INSERT | 440 | 3 tuples flags 0x00
0/1C63368 | 0/1C653B8 | 0/1C631B0 | 750 | XLOG | FPI_FOR_HINT | 8241 |
0/1C653B8 | 0/1C67408 | 0/1C63368 | 750 | XLOG | FPI_FOR_HINT | 8241 |
0/1C67408 | 0/1C69458 | 0/1C653B8 | 750 | XLOG | FPI_FOR_HINT | 8241 |
0/1C69458 | 0/1C69610 | 0/1C67408 | 750 | Heap2 | MULTI_INSERT+INIT | 434 | 3 tuples flags 0x02
0/1C69610 | 0/1C69658 | 0/1C69458 | 750 | Btree | INSERT_LEAF | 72 | off 173
0/1C69658 | 0/1C69698 | 0/1C69610 | 750 | Btree | INSERT_LEAF | 64 | off 129
0/1C69698 | 0/1C696E0 | 0/1C69658 | 750 | Btree | INSERT_LEAF | 72 | off 177
0/1C696E0 | 0/1C69720 | 0/1C69698 | 750 | Btree | INSERT_LEAF | 64 | off 129
0/1C69720 | 0/1C69768 | 0/1C696E0 | 750 | Btree | INSERT_LEAF | 72 | off 173
0/1C69768 | 0/1C697A8 | 0/1C69720 | 750 | Btree | INSERT_LEAF | 64 | off 129
0/1C697A8 | 0/1C697F0 | 0/1C69768 | 750 | Btree | INSERT_LEAF | 72 | off 178
0/1C697F0 | 0/1C69830 | 0/1C697A8 | 750 | Btree | INSERT_LEAF | 64 | off 129
0/1C69830 | 0/1C69878 | 0/1C697F0 | 750 | Btree | INSERT_LEAF | 72 | off 173
0/1C69878 | 0/1C698B8 | 0/1C69830 | 750 | Btree | INSERT_LEAF | 64 | off 129
0/1C698B8 | 0/1C69900 | 0/1C69878 | 750 | Btree | INSERT_LEAF | 72 | off 179
0/1C69900 | 0/1C69940 | 0/1C698B8 | 750 | Btree | INSERT_LEAF | 64 | off 129
0/1C69940 | 0/1C69998 | 0/1C69900 | 750 | Heap2 | MULTI_INSERT | 85 | 1 tuples flags 0x02
0/1C69998 | 0/1C6B390 | 0/1C69940 | 750 | Btree | INSERT_LEAF | 6617 | off 113
0/1C6B390 | 0/1C6B3D8 | 0/1C69998 | 750 | Btree | INSERT_LEAF | 72 | off 139
0/1C6B3D8 | 0/1C6B418 | 0/1C6B390 | 750 | Heap2 | PRUNE | 60 | latestRemovedXid 749 nredirected 0 ndead 3
0/1C6B418 | 0/1C6B448 | 0/1C6B3D8 | 750 | Standby | LOCK | 42 | xid 750 db 16388 rel 16407
0/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
0/1C6B620 | 0/1C6B670 | 0/1C6B448 | 751 | Heap | INSERT+INIT | 79 | off 1 flags 0x00
0/1C6B670 | 0/1C6B698 | 0/1C6B620 | 751 | Transaction | COMMIT | 34 | 2022-10-26 10:05:42.821037+00
0/1C6B698 | 0/1C6B6E8 | 0/1C6B670 | 752 | Heap | INSERT | 79 | off 2 flags 0x00
0/1C6B6E8 | 0/1C6B710 | 0/1C6B698 | 752 | Transaction | COMMIT | 34 | 2022-10-26 10:05:47.73708+00
0/1C6B710 | 0/1C6B748 | 0/1C6B6E8 | 0 | Standby | RUNNING_XACTS | 50 | nextXid 753 latestCompletedXid 752 oldestRunningXid 753
0/1C6B748 | 0/1C6B798 | 0/1C6B710 | 753 | Heap | INSERT | 75 | off 3 flags 0x00
0/1C6B798 | 0/1C6B7C0 | 0/1C6B748 | 753 | Transaction | COMMIT | 34 | 2022-10-26 10:05:53.606115+00
0/1C6B7C0 | 0/1C6B808 | 0/1C6B798 | 754 | Heap | HOT_UPDATE | 71 | off 3 xmax 754 flags 0x60 ; new off 4 xmax 0
0/1C6B808 | 0/1C6B830 | 0/1C6B7C0 | 754 | Transaction | COMMIT | 34 | 2022-10-26 10:06:08.786415+00
0/1C6B830 | 0/1C6B868 | 0/1C6B808 | 0 | Standby | RUNNING_XACTS | 50 | nextXid 755 latestCompletedXid 754 oldestRunningXid 755
0/1C6B868 | 0/1C6B8A0 | 0/1C6B830 | 755 | Heap | DELETE | 54 | off 2 flags 0x00 KEYS_UPDATED
0/1C6B8A0 | 0/1C6B8C8 | 0/1C6B868 | 755 | Transaction | COMMIT | 34 | 2022-10-26 10:06:17.172234+00
(63 rows)

Time: 0.775 ms

Nous voyons, un à un, les valeurs “lsn” s’incrémenter au fur et à mesure des transactions traitées sur l’instance.

On voit que notre ordre “DELETE” apparait au lsn 0/1C6B868. Le xid de notre transaction devient le 755.
L’ordre est directement traité dans la table heap.

 

(postgres@[local]:5434) [postgres] \x
Expanded display is on.
(postgres@[local]:5434) [postgres] select * from pg_get_wal_records_info('0/1C51F80','0/1C6B8C8') where record_type='DELETE';
-[ RECORD 1 ]----+------------------------------------------------
start_lsn | 0/1C6B868
end_lsn | 0/1C6B8A0
prev_lsn | 0/1C6B830
xid | 755
resource_manager | Heap
record_type | DELETE
record_length | 54
main_data_length | 8
fpi_length | 0
description | off 2 flags 0x00 KEYS_UPDATED
block_ref | blkref #0: rel 1663/16388/16407 fork main blk 0

Time: 0.610 ms

 

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.

0/1C6B830 | 0/1C6B868 | 0/1C6B808 | 0 | Standby | RUNNING_XACTS | 50 | nextXid 755 latestCompletedXid 754 oldestRunningXid 755
0/1C6B868 | 0/1C6B8A0 | 0/1C6B830 | 755 | Heap | DELETE | 54 | off 2 flags 0x00 KEYS_UPDATED

Dans le cas d’une restauration PITR, il sera indispensable de préciser les valeurs suivantes dans le fichier “postgrsql.conf” ou “postgresql.auto.conf” :

 

recovery_target_lsn = '0/1C6B868'
recovery_target_inclusive = false

 

Attention, tout de même, gardez à l’esprit, que les fonctions utilisées par l’extension “pg_walinspect” permettent de retrouver le type de transaction générée à une valeur “lsn” précise, elles permettent également d’avoir certaines statistiques sur le poids des enregistrements en base, et la taille de cet enregistrement.
Consultez la fonction “pg_get_wal_stats” pour avoir pas mal d’information à ce sujet.

(postgres@[local]:5434) [postgres] > select * from pg_get_wal_stats('0/1C51F80','0/1C6B8C8',true) where count>0;
resource_manager/record_type | count | count_percentage | record_size | record_size_percentage | fpi_size | fpi_size_percentage | combined_size | combined_size_percentage
------------------------------+-------+--------------------+-------------+------------------------+----------+---------------------+---------------+--------------------------
XLOG/FPI_FOR_HINT | 6 | 9.523809523809524 | 294 | 5.2285256980259645 | 45380 | 45.996351104804376 | 45674 | 43.798126252601094
Transaction/COMMIT | 6 | 9.523809523809524 | 639 | 11.364040547750312 | 0 | 0 | 639 | 0.61275567446276
Storage/CREATE | 1 | 1.5873015873015872 | 42 | 0.7469322425751378 | 0 | 0 | 42 | 0.04027502085670723
Standby/LOCK | 1 | 1.5873015873015872 | 42 | 0.7469322425751378 | 0 | 0 | 42 | 0.04027502085670723
Standby/RUNNING_XACTS | 3 | 4.761904761904762 | 154 | 2.7387515561088387 | 0 | 0 | 154 | 0.1476750764745932
Heap2/PRUNE | 1 | 1.5873015873015872 | 60 | 1.0670460608216255 | 0 | 0 | 60 | 0.05753574408101033
Heap2/MULTI_INSERT | 5 | 7.936507936507937 | 1135 | 20.184954650542416 | 0 | 0 | 1135 | 1.088384492199112
Heap2/MULTI_INSERT+INIT | 1 | 1.5873015873015872 | 434 | 7.718299839943091 | 0 | 0 | 434 | 0.41617521551930803
Heap/INSERT | 5 | 7.936507936507937 | 630 | 11.203983638627067 | 5184 | 5.254409081694709 | 5814 | 5.575213601449901
Heap/DELETE | 1 | 1.5873015873015872 | 54 | 0.960341454739463 | 0 | 0 | 54 | 0.051782169672909294
Heap/HOT_UPDATE | 1 | 1.5873015873015872 | 71 | 1.2626711719722568 | 0 | 0 | 71 | 0.06808396382919556
Heap/INSERT+INIT | 1 | 1.5873015873015872 | 79 | 1.4049439800818069 | 0 | 0 | 79 | 0.07575539637333026
Btree/INSERT_LEAF | 31 | 49.20634920634921 | 1989 | 35.37257691623689 | 48096 | 48.749239813500914 | 50085 | 48.027962371623374
(13 rows)

Notre ordre DELETE n’a été exécuté qu’une seule fois, et ne prend que 54 octets dans le WAL.
En revanche, vous n’aurez pas d’informations sur le nom de l’objet modifié, ni la requête complète avec le prédicat enregistrée.

Il faudra donc faire une recherche assez minutieuse et précise afin de filtrer la bonne transaction dans la cadre d’une instance hautement transactionnelle.

 

 

 

 

🙂

 

Continuez votre lecture sur le blog :

twitterlinkedinmail

Emmanuel RAMI

More Posts

Laisser un commentaire

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

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