Restauration, PITR dans une timeline précédente avec pgBackRest

Inutile de compliquer les choses

      Avec PostgreSQL, les informations de journalisation correspondant aux transactions sont enregistrées dans des fichiers WAL (write ahead log). Ces WAL sont produits et archivés au fil du temps et appartiennent donc à une timeline, une ligne de temps.
      La séquence de WAL pourrait se poursuivre indéfiniment dans la même timeline mais il est parfois nécessaire de faire revenir le cluster en arrière pour annuler des transactions erronées ou encore une corruption. La restauration est alors un PITR (point in time recovery).
      Après un PITR, une nouvelle timeline (incarnation dans le vocabulaire Oracle Database) est créée.
      Les timelines sont souvent décrites avec du vocabulaire de science-fiction. J'aime beaucoup le Maître des Montagnes ou la Fin de l'Eternité mais nous allons ici simplifier les choses. Il est en effet douteux que vous vous intéressiez aux paradoxes spatio-temporels en situation de crise.
      Pour UN cluster donné, chaque transaction a été validée à UN timestamp et enregistrée dans UN fichier WAL qui appartient à UNE timeline. Une timeline peut ainsi être vue comme un ensemble de fichiers WAL produits dans un intervalle de temps. Il n'y a aucun chevauchement possible.
      Les PITR non planifiés sont la plupart du temps effectués en fonction d'un timestamp et avoir plusieurs timelines ne complique pas les choses. Si vous avez suivi le conseil que je donne dans cette page consacrée au PITR alors vous disposez d'au moins un backup de base effectué au début de chaque timeline avant toute activité transactionnelle significative.
      Nous allons supposer que nous disposons de sauvegardes pgBackRest 2.x locales dans notre environnement PostgreSQL 11 sous Debian 10 et que voulons réaliser un PITR au 24/07/2019 13:05:00 :

-- utilisateur postgres, une activite existe dans une table temoin avec une ligne inseree par seconde psql -p 5432 \set AUTOCOMMIT on create table temoin(col timestamp primary key); CREATE TABLE insert into temoin values(clock_timestamp()); INSERT 0 1 \watch 1 -- utilisateur postgres, controle direct des WAL archives et des backups disponibles cd /var/lib/pgbackrest/archive/apptra001/11-1 ls -ltR .: total 11 drwxr-x--- 2 postgres postgres 1024 juil. 24 13:19 0000000700000000 -rw-r----- 1 postgres postgres 248 juil. 24 13:17 00000007.history drwxr-x--- 2 postgres postgres 1024 juil. 24 13:15 0000000600000000 -rw-r----- 1 postgres postgres 205 juil. 24 12:49 00000006.history drwxr-x--- 2 postgres postgres 1024 juil. 24 11:37 0000000500000000 -rw-r----- 1 postgres postgres 196 juil. 24 11:07 00000005.history drwxr-x--- 2 postgres postgres 1024 juil. 24 11:04 0000000400000000 drwxr-x--- 2 postgres postgres 1024 juil. 23 17:52 0000000300000000 -rw-r----- 1 postgres postgres 153 juil. 23 17:52 00000004.history -rw-r----- 1 postgres postgres 101 juil. 23 16:55 00000003.history -rw-r----- 1 postgres postgres 50 juil. 23 15:51 00000002.history ./0000000700000000: total 160 -rw-r----- 1 postgres postgres 365 juil. 24 13:19 000000070000000000000020.00000028.backup -rw-r----- 1 postgres postgres 79648 juil. 24 13:19 000000070000000000000020-06c86ec9d82214fbdbc08d4099972e171b43aa42.gz -rw-r----- 1 postgres postgres 81970 juil. 24 13:18 00000007000000000000001F-4bf88b39d94f17641dd94a21950d03b79fc99b32.gz ./0000000600000000: total 590 -rw-r----- 1 postgres postgres 98931 juil. 24 13:15 00000006000000000000001E-f436faf30eabff8b92d95ea135cc2d1c3bdf3668.gz -rw-r----- 1 postgres postgres 136768 juil. 24 13:10 00000006000000000000001D-752f3ada0d24acc78a02d33b453ea92aaa7795ab.gz -rw-r----- 1 postgres postgres 212370 juil. 24 13:00 00000006000000000000001C-65e06e363b82939ba17bdf2b4c266552d85951f6.gz -rw-r----- 1 postgres postgres 365 juil. 24 12:50 00000006000000000000001B.00000028.backup -rw-r----- 1 postgres postgres 73370 juil. 24 12:50 00000006000000000000001B-e854400c8898237fdf25ccf400e7cb6cc216b8e7.gz -rw-r----- 1 postgres postgres 79460 juil. 24 12:50 00000006000000000000001A-59e6364438f3df718b7b4b458adfb12a58fbe19d.gz ./0000000500000000: ... ./0000000400000000: ... ./0000000300000000: ... stanza: apptra001 status: ok cipher: none db (current) wal archive min/max (11-1): 000000030000000000000015/000000070000000000000020 ... full backup: 20190724-110847F timestamp start/stop: 2019-07-24 11:08:47 / 2019-07-24 11:08:57 wal start/stop: 00000005000000000000001C / 00000005000000000000001C database size: 23.3MB, backup size: 23.3MB repository size: 3.2MB, repository backup size: 3.2MB full backup: 20190724-125018F timestamp start/stop: 2019-07-24 12:50:18 / 2019-07-24 12:50:28 wal start/stop: 00000006000000000000001B / 00000006000000000000001B database size: 23.3MB, backup size: 23.3MB repository size: 3.2MB, repository backup size: 3.2MB full backup: 20190724-131855F timestamp start/stop: 2019-07-24 13:18:55 / 2019-07-24 13:19:04 wal start/stop: 000000070000000000000020 / 000000070000000000000020 database size: 23.3MB, backup size: 23.3MB repository size: 3.2MB, repository backup size: 3.2MB


      Notre "ls" n'est pas très précis , d'autant plus que les fichiers WAL ne sont pas archivés instantanément dans pgBackRest, mais c'est suffisant pour affirmer que la timeline à considérer n'est pas la timeline courante, 7, mais la 6. Le dossier correspondant à la timeline 6 comprend le fichier WAL produit à l'heure souhaitée du PITR, le 24/07/2019 à 13h05min00s.
      Le backup à considérer est ici 20190724-125018F effectué lors de la timeline 6 comme l'indique un pgbackrest info (cf wal start/stop)
      A présent place au PITR :

-- utilisateur root pg_ctlcluster 11 apptra001 stop rm -fr /var/lib/postgresql/11/apptra001/* -- utilisateur postgres, restore avec pgbackrest depuis le backup identifie (note : si le cluster d'origine n'est pas supprime, il est possible d'utiliser --delta, ce qui est tres interessant sur les clusters volumineux) pgbackrest --set=20190723-175248F --log-level-console=info --stanza=apptra001 --type=time "--target=2019-07-24 13:05:00" restore -- utilisateur postgres, on edite le contenu de recovery.conf pour indiquer explicitement la timeline sur laquelle effectuer le recover vi /var/lib/postgresql/11/apptra001/recovery.conf restore_command = 'pgbackrest --log-level-console=info --stanza=apptra001 archive-get %f "%p"' recovery_target_time = '2019-07-24 13:05:00' -- ajouter recovery_target_timeline = '6' -- utilisateur root, on redemarre le cluster pg_ctlcluster 11 apptra001 start -- utilisateur postgres, on regarde le fichier de log du cluster et on interroge la table temoin tail -50 /var/log/postgresql/postgresql-11-apptra001.log ... 2019-07-24 13:35:29.387 P00 INFO: found 00000006000000000000001C in the archive 2019-07-24 13:35:29.387 P00 INFO: archive-get command end: completed successfully (87ms) 2019-07-24 13:35:29.388 CEST [23151] LOG: restauration du journal de transactions « 00000006000000000000001C » à partir de l'archive 2019-07-24 13:35:29.506 P00 INFO: archive-get command begin 2.10: [00000006000000000000001D, pg_wal/RECOVERYXLOG] --log-level-console=info --pg1-path=/var/lib/postgresql/11/apptra001 --repo1-path=/var/lib/pgbackrest --stanza=apptra001 2019-07-24 13:35:29.591 P00 INFO: found 00000006000000000000001D in the archive 2019-07-24 13:35:29.591 P00 INFO: archive-get command end: completed successfully (86ms) 2019-07-24 13:35:29.593 CEST [23151] LOG: restauration du journal de transactions « 00000006000000000000001D » à partir de l'archive 2019-07-24 13:35:29.780 CEST [23151] LOG: arrêt de la restauration avant validation de la transaction 3489, 2019-07-24 13:05:00.276708+02 2019-07-24 13:35:29.780 CEST [23151] LOG: restauration en pause 2019-07-24 13:35:29.780 CEST [23151] ASTUCE : Exécuter pg_wal_replay_resume() pour continuer. psql -p 5432 select max(col) from temoin; max ---------------------------- 2019-07-24 13:04:59.274361 (1 ligne) -- utilisateur postgres, tout est OK, les operations de recover sont terminees select pg_wal_replay_resume(); pg_wal_replay_resume ---------------------- (1 ligne) tail -50 /var/log/postgresql/postgresql-11-apptra001.log ... 2019-07-24 13:39:24.207 CEST [23151] LOG: restauration terminée de l'archive 2019-07-24 13:39:24.218 P00 INFO: archive-get command begin 2.10: [00000006.history, pg_wal/RECOVERYHISTORY] --log-level-console=info --pg1-path=/var/lib/postgresql/11/apptra001 --repo1-path=/var/lib/pgbackrest --stanza=apptra001 2019-07-24 13:39:24.227 P00 INFO: found 00000006.history in the archive 2019-07-24 13:39:24.227 P00 INFO: archive-get command end: completed successfully (10ms) 2019-07-24 13:39:24.228 CEST [23151] LOG: restauration du journal de transactions « 00000006.history » à partir de l'archive 2019-07-24 13:39:24.337 CEST [23150] LOG: le système de bases de données est prêt pour accepter les connexions


      L'existence de plusieurs timelines n'a pas changé la manière d'effectuer notre PITR qui a donné le résultat escompté. En situation réelle, je conseille bien sûr de sauvegarder le cluster d'origine ou d'effectuer le PITR vers un emplacement distinct afin d'éviter le suraccident, d'effectuer des comparaisons etc.
      PostgreSQL est capable de traverser les timelines comme Oracle Database est capable depuis la version 10g de traverser les incarnations mais je conseille malgré tout fortement de réaliser un backup après chaque ouverture de timeline.

Mise à jour : 24/07/2019