Restauration, PITR

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ées 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 10 TOUTES les bases de données.

Quel point ?

      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 :

Conseils

      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.

En pratique

      Trêve de théorie, nous allons supposer que nous disposons d'une sauvegarde Barman distante et que nous voulons effectuer un PITR au 14/02/2018 11h00min00s :

-- serveur pgpr10, root pg_ctlcluster 10 apptra001 stop rm -fr /var/lib/postgresql/10/apptra001/* -- serveur bapr, barman, selection du backup le plus recent anterieur au PITR barman list-backup all apptra001 20180214T125014 - Wed Feb 14 12:50:17 2018 - Size: 58.2 MiB - WAL Size: 44.1 MiB apptra001 20180213T214303 - Tue Feb 13 21:43:06 2018 - Size: 22.7 MiB - WAL Size: 13.9 MiB barman recover --target-time "2018-02-14 11:00:00" --remote-ssh-command "ssh postgres@pgpr10" apptra001 20180213T214303 /var/lib/postgresql/10/apptra001 ... Your PostgreSQL server has been successfully prepared for recovery! -- serveur pgpr10, root pg_ctlcluster 10 apptra001 start pg_lsclusters Ver Cluster Port Status Owner Data directory Log file 10 apptra001 5432 online,recovery postgres /var/lib/postgresql/10/apptra001 /var/log/postgresql/postgresql-10-apptra001.log tail -f /var/log/postgresql/postgresql-10-apptra001.log 2018-02-17 14:18:00.178 CET [1785] LOG: le système de bases de données est prêt pour accepter les connexions en lecture seule 2018-02-17 14:18:00.718 CET [1796] [inconnu]@[inconnu] LOG: paquet de démarrage incomplet 2018-02-17 14:18:01.826 CET [1786] LOG: restauration du journal de transactions « 000000010000000000000005 » à partir de l'archive 2018-02-17 14:18:03.460 CET [1786] LOG: restauration du journal de transactions « 000000010000000000000006 » à partir de l'archive 2018-02-17 14:18:05.955 CET [1786] LOG: restauration du journal de transactions « 000000010000000000000007 » à partir de l'archive 2018-02-17 14:18:07.074 CET [1786] LOG: restauration du journal de transactions « 000000010000000000000008 » à partir de l'archive 2018-02-17 14:18:08.159 CET [1786] LOG: restauration du journal de transactions « 000000010000000000000009 » à partir de l'archive 2018-02-17 14:18:09.051 CET [1786] LOG: arrêt de la restauration avant validation de la transaction 23269, 2018-02-14 11:00:00.042266+01 2018-02-17 14:18:09.051 CET [1786] LOG: restauration en pause 2018-02-17 14:18:09.051 CET [1786] ASTUCE : Exécuter pg_wal_replay_resume() pour continuer. -- serveur pgpr10, postgres psql select max(col) from temoin; max ---------------------------- 2018-02-14 10:59:59.037058 (1 ligne) select pg_wal_replay_resume(); pg_wal_replay_resume ---------------------- (1 ligne) select max(col) from temoin; max ---------------------------- 2018-02-14 10:59:59.037058 (1 ligne) tail -f /var/log/postgresql/postgresql-10-apptra001.log 2018-02-17 14:24:02.725 CET [1916] LOG: échec de la commande d'archivage avec un code de retour 1 2018-02-17 14:24:02.725 CET [1916] DéTAIL: La commande d'archivage qui a échoué était : false 2018-02-17 14:24:02.725 CET [1916] ATTENTION: l'archivage du journal de transactions « 00000002.history » a échoué trop de fois, nouvelle tentative repoussée -- serveur pgpr10, root mv /var/lib/postgresql/10/apptra001/postgresql.auto.conf.origin /var/lib/postgresql/10/apptra001/postgresql.auto.conf pg_ctlcluster 10 apptra001 reload tail -f /var/log/postgresql/postgresql-10-apptra001.log 2018-02-17 14:25:49.357 CET [1873] LOG: a reçu SIGHUP, rechargement des fichiers de configuration 2018-02-17 14:25:49.357 CET [1873] LOG: paramètre « archive_command » modifié par « rsync -a %p barman@bapr:/var/lib/barman/apptra001/incoming/%f » -- serveur bapr, barman barman receive-wal --create-slot apptra001 Creating physical replication slot 'barman' on server 'apptra001' Replication slot 'barman' created barman receive-wal --reset apptra001 Starting receive-wal for server apptra001 Resetting receive-wal directory status Removing status file /var/lib/barman/apptra001/streaming/00000001000000000000001B.partial barman cron Starting WAL archiving for server apptra001 Starting streaming archiver for server apptra001 barman check apptra001 ...OK barman backup apptra001


      Le PITR s'est bien passé. La seule difficulté est de ne pas se tromper de backup à employer avec la commande "barman recover" si le point de recover est antérieur au dernier backup effectué. Effectuer un "barman diagnose" donne des détails sur chaque backup et permet si besoin de lever les doutes.
      Autre point à noter par rapport au recover maximal, une nouvelle timeline (incarnation dans le vocabulaire Oracle Database) a été ouverte (équivalent d'un OPEN RESETLOGS pour les DBA Oracle). Il est nécessaire pour reprendre le streaming WAL de faire un reset sur le slot de réplication. A noter que ce slot est recommandé mais optionnel dans le cas de la sauvegarde distante telle que nous l'avons configurée. Les informations WAL ne peuvent de toute façon pas être effacées avant leur archivage dans Barman car nous nous appuyons sur l'archivage classique via rsync en complément du streaming WAL (un WAL n’est réutilisable que si archive_command renvoie 0).
      Les sessions de recover PostgreSQL comme les sessions de recover Oracle Database peuvent "traverser les incarnations" mais, néanmoins, je recommande d'effectuer immédiatement un premier backup dans la nouvelle timeline, avant la remise en production, pour simplifier les choses. Encore une fois, un "barman diagnose" vous donne tous les détails sur chaque backup, notamment la timeline dans laquelle il a été réalisé.

Mise à jour : 17/02/2018