Imaginez le scénario : nous sommes vendredi soir, en fin de journée, et pour une raison quelconque, un développeur qui a eu une semaine fatiguante se dit qu’il serait bien de supprimer les lignes qu’il a modifiées dans la base de données de développement, afin que lundi il puisse reprendre le travail à zéro. Il se connecte donc et, à l’aide d’une commande de suppression sur la table concernée, il efface ses quelques lignes… Avant de se rendre compte qu’il vient de le faire en production.
Cela nous est arrivé à tous, au moins une fois dans notre carrière. Un manque d’attention, une absence de sécurisation, une erreur est si vite arrivée. Oui, mais voilà. La solution pour pallier à ce genre de problèmes, c’est généralement de recharger une sauvegarde de la base de données, pour ne surtout pas perdre cette ligne essentielle des paiements enregistrés… C’est long. La base est volumineuse… Et puis, nous n’avons pas un plan de sauvegarde fiable… Au secours !
Avant de céder à la panique, peut-être existe-t-il une autre solution à votre problème.
L’extension pg_dirtyread pourrait être une idée. Cette extension permet aux utilisateurs de lire des données supprimées dans les tables, ce qui est normalement impossible en temps normal. Cette extension est disponible gratuitement sur GIT, et elle peut vous sauver la vie dans le scénario évoqué juste avant. Nous allons voir ensemble comment l’utiliser :
Installer PostgreSQL et pg_dirtyread :
C’est assez rare pour le souligner, mais pg_dirtyread possède son propre package Ubuntu tout inclus. Ce package, si vous pouvez vous en servir, contient l’installation du moteur, l’installation des dépendances de développement de PostgreSQL, le client, et l’extension elle-même. Cela simplifie grandement le travail. Vous n’avez qu’à télécharger le package dans la version qui vous intéresse. Pour cet article, j’ai choisi de télécharger et installer la dernière version de PostgreSQL ainsi que la dernière version de l’extension.
root:~/pg_dirtyread# sudo apt -y install postgresql-16-dirtyread Reading package lists... Done Building dependency tree... Done Reading state information... Done The following additional packages will be installed: libcommon-sense-perl libjson-perl libjson-xs-perl libpq5 libsensors-config libsensors5 libtypes-serialiser-perl postgresql-16 postgresql-client-16 postgresql-client-common postgresql-common ssl-cert sysstat Suggested packages: lm-sensors postgresql-doc-16 isag The following NEW packages will be installed: libcommon-sense-perl libjson-perl libjson-xs-perl libpq5 libsensors-config libsensors5 libtypes-serialiser-perl postgresql-16 postgresql-16-dirtyread postgresql-client-16 postgresql-client-common postgresql-common ssl-cert sysstat 0 upgraded, 14 newly installed, 0 to remove and 0 not upgraded. Need to get 21.3 MB of archives. ... Running kernel seems to be up-to-date. No services need to be restarted. No containers need to be restarted. No user sessions are running outdated binaries. No VM guests are running outdated hypervisor (qemu) binaries on this host.
Il existe une autre méthode d’installation, la plus classique. Il suffit de télécharger les sources depuis le dépôt GIT et de les compiler sur votre machine en utilisant les commandes ‘make’ et ‘make install’.
Mise en place d’un environnement :
Pour notre test, j’ai choisit de me servir encore une fois de la base de données dvdrental, accessible à tous. Il me faut donc la télécharger :
postgres:~$ wget https://www.postgresqltutorial.com/wp-content/uploads/2019/05/dvdrental.zip --2024-03-11 08:34:54-- https://www.postgresqltutorial.com/wp-content/uploads/2019/05/dvdrental.zip Resolving www.postgresqltutorial.com (www.postgresqltutorial.com)... 104.21.2.174, 172.67.129.129, 2606:4700:3037::6815:2ae, ... Connecting to www.postgresqltutorial.com (www.postgresqltutorial.com)|104.21.2.174|:443... connected. HTTP request sent, awaiting response... 200 OK Length: 550906 (538K) [application/zip] Saving to: ‘dvdrental.zip’ dvdrental.zip 100%[========================================================================================================================================>] 537.99K --.-KB/s in 0.01s 2024-03-11 08:34:54 (46.0 MB/s) - ‘dvdrental.zip’ saved [550906/550906]
Une fois téléchargée, on la dezippe :
postgres:~$ ls -l total 544 drwxr-xr-x 3 postgres postgres 4096 Mar 11 08:30 16 -rw-rw-r-- 1 postgres postgres 550906 May 12 2019 dvdrental.zip postgres:~$ unzip dvdrental.zip Archive: dvdrental.zip inflating: dvdrental.tar postgres:~$ ls -l total 3316 drwxr-xr-x 3 postgres postgres 4096 Mar 11 08:30 16 -rw-rw-r-- 1 postgres postgres 2835456 May 12 2019 dvdrental.tar -rw-rw-r-- 1 postgres postgres 550906 May 12 2019 dvdrental.zip
On créé la base de données pour accueillir nos données, et on charge le fichier de sauvegarde :
postgres:~$ psql psql (16.2 (Ubuntu 16.2-1.pgdg22.04+1)) Type "help" for help.
postgres=# create database dvdrental;
CREATE DATABASE
postgres=# \l
List of databases
Name | Owner | Encoding | Locale Provider | Collate | Ctype | ICU Locale | ICU Rules | Access privileges
-----------+----------+----------+-----------------+---------+---------+------------+-----------+-----------------------
dvdrental | postgres | UTF8 | libc | C.UTF-8 | C.UTF-8 | | |
postgres | postgres | UTF8 | libc | C.UTF-8 | C.UTF-8 | | |
template0 | postgres | UTF8 | libc | C.UTF-8 | C.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
template1 | postgres | UTF8 | libc | C.UTF-8 | C.UTF-8 | | | =c/postgres +
| | | | | | | | postgres=CTc/postgres
(4 rows)postgres:~$ pg_restore -U postgres -d dvdrental dvdrental.tar
Une fois que c’est fait, on peut se connecter pour vérifier que tout a bien été chargé :
postgres:~$ psql psql (16.2 (Ubuntu 16.2-1.pgdg22.04+1)) Type "help" for help.
postgres=# \c dvdrental
You are now connected to database "dvdrental" as user "postgres".
dvdrental=# \dt
List of relations
Schema | Name | Type | Owner
--------+---------------+-------+----------
public | actor | table | postgres
public | address | table | postgres
public | category | table | postgres
public | city | table | postgres
public | country | table | postgres
public | customer | table | postgres
public | film | table | postgres
public | film_actor | table | postgres
public | film_category | table | postgres
public | inventory | table | postgres
public | language | table | postgres
public | payment | table | postgres
public | rental | table | postgres
public | staff | table | postgres
public | store | table | postgres
(15 rows)
Premier scénario : Suppression de lignes dans une table
Prenons ici le cas de la situation décrite dans l’introduction. Une ou plusieurs lignes ont été supprimées d’une ou plusieurs tables. Recharger l’intégralité d’une base de données juste pour ces quelques lignes, aussi essentielles soient-elles, demande un investissement énorme.
Pour notre exemple, je vais supprimer un certain nombre de lignes de la table ‘payment’.
dvdrental=# select * from payment limit 10;
payment_id | customer_id | staff_id | rental_id | amount | payment_date
------------+-------------+----------+-----------+--------+----------------------------
17503 | 341 | 2 | 1520 | 7.99 | 2007-02-15 22:25:46.996577
17504 | 341 | 1 | 1778 | 1.99 | 2007-02-16 17:23:14.996577
17505 | 341 | 1 | 1849 | 7.99 | 2007-02-16 22:41:45.996577
17506 | 341 | 2 | 2829 | 2.99 | 2007-02-19 19:39:56.996577
17507 | 341 | 2 | 3130 | 7.99 | 2007-02-20 17:31:48.996577
17508 | 341 | 1 | 3382 | 5.99 | 2007-02-21 12:33:49.996577
17509 | 342 | 2 | 2190 | 5.99 | 2007-02-17 23:58:17.996577
17510 | 342 | 1 | 2914 | 5.99 | 2007-02-20 02:11:44.996577
17511 | 342 | 1 | 3081 | 2.99 | 2007-02-20 13:57:39.996577
17512 | 343 | 2 | 1547 | 4.99 | 2007-02-16 00:10:50.996577
(10 rows)
dvdrental=# delete from payment where payment_id between 17523 and 17532;
DELETE 10La bêtise est faite, les données ont disparu. Lorsque j’essaie de les requêter pour voir si elles sont encore là, je me heurte à la réalité : elles ont été supprimées.
dvdrental=# select * from payment where payment_id between 17523 and 17532; payment_id | customer_id | staff_id | rental_id | amount | payment_date ------------+-------------+----------+-----------+--------+-------------- (0 rows)
La première chose à faire, en toute urgence, c’est de désactiver l’auto-vacuum. C’est votre pire ennemi dans notre scénario. C’est même la première chose à faire lorsque vous vous rendez compte que l’erreur a été commise :
dvdrental=# alter table payment set (autovacuum_enabled=false);
Si l’auto-vacuum est passé sur la table avant que vous n’ayez eu le temps de le désactiver, malheureusement, pg_dirtyread ne vous servira plus à rien. Une fois cela fait, on initialise l’extension :
dvdrental=# CREATE EXTENSION pg_dirtyread; CREATE EXTENSION
L’extension étant maintenant créée, on peut requêter les données perdues. La syntaxe d’utilisation exige que vous rappeliez le schéma de la table en question au moment de requêter les lignes supprimées. Vous pouvez choisir de ne pas inclure toutes les colonnes, ou même d’en ajouter, comme nous le verrons à la fin de l’article. Dans notre exemple, la requête pour consulter les données effacées sera la suivante :
dvdrental=# select * from pg_dirtyread('payment') AS t(payment_id integer, customer_id smallint, staff_id smallint, rental_id integer, amount numeric(5,2), payment_date timestamp without time zone);
payment_id | customer_id | staff_id | rental_id | amount | payment_date
------------+-------------+----------+-----------+--------+----------------------------
17523 | 345 | 1 | 1457 | 4.99 | 2007-02-15 18:34:15.996577
17524 | 345 | 2 | 1550 | 0.99 | 2007-02-16 00:27:01.996577
17525 | 345 | 2 | 2766 | 4.99 | 2007-02-19 16:13:41.996577
17526 | 346 | 1 | 1994 | 5.99 | 2007-02-17 09:35:32.996577
17527 | 346 | 2 | 3372 | 2.99 | 2007-02-21 12:02:45.996577
17528 | 346 | 1 | 3421 | 2.99 | 2007-02-21 15:51:24.996577
17529 | 347 | 2 | 1711 | 8.99 | 2007-02-16 12:40:18.996577
17530 | 347 | 2 | 2274 | 0.99 | 2007-02-18 04:59:41.996577
17531 | 347 | 1 | 3026 | 4.99 | 2007-02-20 10:16:26.996577
17532 | 347 | 1 | 3092 | 8.99 | 2007-02-20 14:33:08.996577
17533 | 347 | 1 | 3326 | 7.99 | 2007-02-21 07:33:16.996577
17534 | 348 | 1 | 1654 | 2.99 | 2007-02-16 08:11:14.996577
17535 | 348 | 1 | 2041 | 8.99 | 2007-02-17 12:47:26.996577
17536 | 348 | 2 | 2499 | 0.99 | 2007-02-18 21:30:02.996577On voit bien apparaître en début de liste nos lignes qui sont censées ne plus exister. Cela nous permet éventuellement d’extraire les données qu’elles contiennent pour pouvoir les réinjecter ensuite avec un INSERT. On peut également filtrer les données pour ne rechercher que celles que l’on a supprimées :
dvdrental=# select * from pg_dirtyread('payment') payment(payment_id integer, customer_id smallint, staff_id smallint, rental_id integer, amount numeric(5,2), dropped_6 timestamp without time zone) where payment_id = 17523;
payment_id | customer_id | staff_id | rental_id | amount | dropped_6
------------+-------------+----------+-----------+--------+----------------------------
17523 | 345 | 1 | 1457 | 4.99 | 2007-02-15 18:34:15.996577
(1 row)
Deuxième scénario : On a supprimé une colonne entière
Deuxième cas typique, sûrement plus rare, celui où une colonne entière est supprimée. Pour notre exemple, je vais supprimer la colonne contenant la date de paiement :
dvdrental=# alter table payment drop column payment_date; ALTER TABLE
pg_dirtyread nous permet de retrouver les informations de cette colonne à condition qu’aucun VACUUM FULL ou CLUSTER n’ait été exécuté. Pour ce faire, il faut connaître la position de la colonne dans l’ordre des colonnes de la table. Pour notre exemple, la colonne de date est la dernière colonne de la table, donc en position 6. Pour retrouver nos données, on peut donc exécuter la commande suivante :
dvdrental=# select * from pg_dirtyread('payment') payment(payment_id integer, customer_id smallint, staff_id smallint, rental_id integer, amount numeric(5,2), dropped_6 timestamp without time zone);
payment_id | customer_id | staff_id | rental_id | amount | dropped_6
------------+-------------+----------+-----------+--------+----------------------------
17523 | 345 | 1 | 1457 | 4.99 | 2007-02-15 18:34:15.996577
17524 | 345 | 2 | 1550 | 0.99 | 2007-02-16 00:27:01.996577
17525 | 345 | 2 | 2766 | 4.99 | 2007-02-19 16:13:41.996577
17526 | 346 | 1 | 1994 | 5.99 | 2007-02-17 09:35:32.996577
17527 | 346 | 2 | 3372 | 2.99 | 2007-02-21 12:02:45.996577
Le cas d’une restauration d’une colonne entière supprimée est compliqué à gérer. Il faudrait la recréer, puis modifier toutes les lignes une à une pour réajouter les valeurs de cette fameuse colonne. Cela peut s’avérer un peu complexe à réaliser. Cependant, si vous ne disposez pas d’une sauvegarde pg_dump, vous n’aurez peut-être pas d’autres alternatives que cela, à part recharger entièrement la base.
Bonus : Récupérer les informations système des lignes supprimées
Avec pg_dirtyread, il est possible de récupérer les informations système des lignes qui ont été supprimées. Pour ce faire, rien de plus simple : il suffit d’indiquer dans la requête les informations que vous souhaitez récupérer. De plus, il existe une colonne qui indique si la ligne est supposément supprimée ou non, ce qui pourrait être pratique pour trier les différentes lignes selon leur état :
dvdrental=# SELECT * FROM pg_dirtyread('payment') AS t(tableoid oid, ctid tid, xmin xid, xmax xid, cmin cid, cmax cid, dead boolean);
tableoid | ctid | xmin | xmax | cmin | cmax | dead
----------+-----------+------+------+------+------+------
16505 | (0,21) | 835 | 941 | 0 | 0 | f
16505 | (0,22) | 835 | 941 | 0 | 0 | f
16505 | (0,23) | 835 | 941 | 0 | 0 | f
16505 | (0,24) | 835 | 941 | 0 | 0 | f
16505 | (0,25) | 835 | 941 | 0 | 0 | f
16505 | (0,26) | 835 | 941 | 0 | 0 | f
16505 | (0,27) | 835 | 941 | 0 | 0 | f
16505 | (0,28) | 835 | 941 | 0 | 0 | f
16505 | (0,29) | 835 | 941 | 0 | 0 | f
16505 | (0,30) | 835 | 941 | 0 | 0 | f
Conclusion :
En conclusion, l’extension pg_dirtyread offre une solution précieuse pour accéder aux données supprimées dans une base de données PostgreSQL. Son utilisation peut être cruciale dans des cas d’incidents critiques tels que la récupération de données perdues accidentellement ou la résolution d’incohérences de données. Cependant, il est essentiel de rappeler que son application reste extrêmement situationnelle et que pour qu’elle puisse être efficace, l’autovacuum doit être désactivé, ce qui n’est pas forcément recommandé.
Continuez votre lecture sur le blog :
- pg_recursively_delete : Simplifier les suppressions récursives (Sarah FAVEERE) [PostgreSQL]
- Pyrseas et Postgresql : Comparer facilement des schémas de base de données (Sarah FAVEERE) [PostgreSQL]
- PostgreSQL : planifier une tâche avec pg_cron (Emmanuel RAMI) [Non classéPostgreSQL]
- AWS RDS : les extensions PostgreSQL (Emmanuel RAMI) [AWSPostgreSQL]
- Pseudonymiser vos bases PostgreSQL (Sarah FAVEERE) [PostgreSQL]