PITR ?
Erreur humaine, malveillance, corruption logique...une ou plusieurs transactions sont indésirables et vous allez devoir trouver un moyen de remonter dans le temps.
Tout d'abord, ne vous lancez pas dans une opération complexe si vous pouvez simplement annuler la transaction. Si une instruction indésirable est terminée mais que la transaction n'est pas validée alors un ROLLBACK explicite ou un kill annuleront toute la transaction. Je suggère de désactiver par défaut l'autocommit au niveau de psql comme des outils graphiques pour tous les administrateurs et utilisateurs ayant un accès direct aux données.
Si vous êtes un habitué d'Oracle Database, n'oubliez pas que PostgreSQL est plus puissant au niveau de la gestion transactionnelle. Une opération DDL (drop table, truncate etc.) ne fait PAS de commit implicite et peut être annulée via un ROLLBACK de la même façon qu'une opération DML (delete, update etc.). Oracle Database compense partiellement cette erreur fondamentale de design via diverses fonctionnalités de flashback mais c'est une autre histoire.
Comme avec la plupart des SGBDR, faire revenir une base de donné1es en arrière consiste avec PostgreSQL à effectuer une restauration des fichiers de données puis à appliquer une partie des informations de journalisation, c'est à dire effectuer une opératon de recover. Cette opération est communément appelée PITR (point in time recovery, récupération à un moment dans le temps).
Attention, un cluster PostgreSQL peut contenir plusieurs bases de données et un PITR concerne toujours en version 11 TOUTES les bases de données. Le logiciel pbBackRest permet toutefois de ne restaurer qu'une partie des bases d'un cluster. Les autres bases seront cependant dans ce cas perdues. Cette fonctionnalité est donc applicable dans le cas d'une restauration vers un dossier différent de celui d'origine. Vous aurez deux clusters aprés l'opération de restauration, celui contenant les bases sur lesquelles vous ne vouliez pas de PITR et celui contenant les bases sur lesquelles vous vouliez effectuer un PITR.
Si vous disposez d'une sauvegarde des fichiers de données et d'un archivage WAL fonctionnel, aucune raison de s'inquiéter. La difficulté d'un PITR n'est pas de réaliser l'opération technique mais de déterminer le bon point d'arrêt du recover.
Avec PostgreSQL, il peut s'agir d'un point de restauration préalablement créé avec la fonction pg_create_restore_point(), d'un identifiant de transaction (transaction ID), d'un LSN (log sequence number) qui est un entier désignant un emplacement dans les fichiers WAL ou encore d'un timestamp, c'est à dire un point dans le temps.
L'idéal est bien sûr de disposer d'un point de restauration mais, malheureusement, ce n'est pas le cas le plus fréquent. La pire situation est de disposer d'une indication de temps très vague. Il est souvent possible d'affiner :
Malgré toutes les précautions, des erreurs sont toujours possibles. Il est donc essentiel d'effectuer des sauvegardes avec un outil éprouvé et de tester régulièrement les différents scenarii de restauration. N'hésitez pas à faire appel à une entreprise spécialisée comme Dalibo, 2ndQuadrant etc. L'assurance ne parait chère qu'avant l'accident.
Lors d'un incident nécessitant une restauration, l’idéal est de disposer d'une personne gérant la communication différente du technicien effectuant les opérations. Un seul technicien doit piloter la restauration. Il ne doit être en contact qu'avec les personnes susceptibles de donner des informations utiles et surtout UNE information essentielle : le point de recover. Les utilisateurs, chefs etc. solliciteront des informations. C'est la personne chargée de la communication qui doit leur en donner et non le technicien qui doit pouvoir se concentrer sur le travail à accomplir.
Pour éviter le suraccident, il est plus rassurant d'effectuer les opérations de PITR sur un serveur différent du serveur de production. Un serveur prêt à tous niveaux (autorisations firewall, binaires, équivalences ssh etc.) peut être dédié en permanence pour les restaurations afin de gagner du temps. Travailler avec un cluster distinct est spécialement recommandé si des transactions valides ont été effectuées après le traitement erroné. Vous pourrez effectuer votre PITR puis injecter les données manquantes du cluster restauré vers le cluster de production par export/import, foreign data wrapper ou autre.
Dernier point à considérer si vous n'êtes pas tout à fait certain du point de recover et/ou si le cluster à restaurer est particulièrement volumineux. Il peut être utile de réaliser ou faire réaliser un snapshot au niveau de la baie ou du filesystem APRÈS la restauration des fichiers de données mais AVANT de lancer les opérations de recover. Vous pourrez ainsi annuler rapidement l'opération de recover et la relancer sans avoir besoin de restaurer à nouveau les données.