Le backup et la restauration des bases de données PostgreSQL est par défault assez manuel :
- Le backup consiste à passer la base de données en mode backup via pg_start_backup, faire une copie des fichiers de base de données avant de désactiver le mode backup via pg_stop_backup. On peut aussi utiliser l’outil standard pg_base_backup qui effectue ces trois opérations.
- Le restore consiste à restaurer les fichiers du backup et à créer un fichier recovery.conf pour y spécifier les paramètres de la restauration qui sera lancée au démarrage de la base de données, cette étape ne peut pas être simplifiée avec les outils standards.
Pour aller plus loin dans la simplification de ces opérations, on peut utiliser l’un des nombreux outils tiers qui existent, parmi les plus connus pgbackrest et barman qui sont des outils très complets.
Ici, nous allons nous intéresser à Pitrery qui est un outil très minimaliste mais qui facilite grandement les tâches de backup & recovery.
Pitrery est un ensemble de scripts shell qui facilitent la prise de backup et la restauration de ces derniers. Ici, pas de notion d’emplacement central pour les sauvegardes, tout se passe sur le serveur postgreSQL local.
Installation
Pour installer Pitrery, plusieurs solutions :
- Le package est disponible dans le repository PGDP (PostgreSQL Global Development Group)
- Les RPMs sont également disponibles sur le site de l’outil
- Il est également possible de compiler depuis ses sources
Il s’agira d’installer le package sur le serveur postgreSQL qui héberge la base de données à sauvegarder.
Installer le package pitrery :
# Cas du repository PGDG yum install pitrery # Cas du RPM unitaire rpm -i pitrery-2.2-1.el7.noarch.rpm
Configuration
Sauvegarder le fichier de configuration et le vider de ses lignes :
cp /etc/pitrery/pitrery.conf /etc/pitrery/pitrery.conf.old > /etc/pitrery/pitrery.conf
Adapter et saisir la configuration suivante dans le fichier /etc/pitrery/pitrery.conf :
PGDATA="/pgdata" # Répertoire de base de l'instance, souvent referencé par le variable d'environnement $PGDATA PGUSER="pg" # Utilisateur unix proprietaire de l'instance, souvant "postgres" BACKUP_DIR="/pgbackup" # Répertoire de destination pour les backups. Vérifier que ce répertoire soit accessible en écriture par l'utilisateur PGUSER ARCHIVE_DIR="$BACKUP_DIR/arc" # Répertoire pour les wal archivés. Vérifier que ce répertoire soit accessible en écriture par l'utilisateur PGUSER PURGE_OLDER_THAN=3 # Politique de rétention des backups
Dans le fichier de configuration de l’instance postgresql.conf, vérifier que le wal_level soit à replica et que l’archivage des wal soit actif :
wal_level = replica archive_mode = on
Toujours dans le postgresql.com, mettre en place la archive_commande suivante faisant usage du script /usr/bin/archive_xlog fourni par pitrery :
archive_command = '/usr/bin/archive_xlog %p'
Si les paramètres wal_level et/ou archive_mode ont été modifiés, alors un redémarrage de l’instance est requis, sinon, recharger la configuration suffit.
En tant que l’utilisateur unix postgreSQL :
pg_ctl reload # Ou, redémarrage complet pg_ctl restart
Pour confirmer que tout est en ordre, il conviendra d’exécuter la vérification suivante :
pitrery check 2019-07-25 15:15:19 CEST INFO: ==> checking the configuration for inconsistencies 2019-07-25 15:15:19 CEST INFO: configuration seems correct 2019-07-25 15:15:19 CEST INFO: ==> checking backup configuration 2019-07-25 15:15:19 CEST INFO: backups are local, not checking SSH 2019-07-25 15:15:19 CEST INFO: target directory '/pgbackup' exists 2019-07-25 15:15:19 CEST INFO: target directory '/pgbackup' is writable 2019-07-25 15:15:19 CEST INFO: ==> checking WAL files archiving configuration 2019-07-25 15:15:19 CEST INFO: WAL archiving is local, not checking SSH 2019-07-25 15:15:19 CEST INFO: checking WAL archiving directory: /pgbackup/arc 2019-07-25 15:15:19 CEST INFO: target directory '/pgbackup/arc' exists 2019-07-25 15:15:19 CEST INFO: target directory '/pgbackup/arc' is writable 2019-07-25 15:15:19 CEST INFO: ==> checking access to PostgreSQL 2019-07-25 15:15:19 CEST INFO: psql command and connection options are: psql -X -U pg 2019-07-25 15:15:19 CEST INFO: connection database is: postgres 2019-07-25 15:15:19 CEST INFO: environment variables (maybe overwritten by the configuration file): 2019-07-25 15:15:19 CEST INFO: PGUSER=pg 2019-07-25 15:15:19 CEST INFO: PGDATA=/pgdata 2019-07-25 15:15:19 CEST INFO: PostgreSQL version is: 11.2 2019-07-25 15:15:19 CEST INFO: connection role can run backup functions 2019-07-25 15:15:19 CEST INFO: current configuration: 2019-07-25 15:15:19 CEST INFO: wal_level = replica 2019-07-25 15:15:19 CEST INFO: archive_mode = on 2019-07-25 15:15:19 CEST INFO: archive_command = 'test ! -f /pgbackup/arc/%f && cp %p /pgbackup/arc/%f' 2019-07-25 15:15:19 CEST INFO: ==> checking access to PGDATA 2019-07-25 15:15:19 CEST INFO: PostgreSQL and the configuration reports the same PGDATA 2019-07-25 15:15:19 CEST INFO: permissions of PGDATA ok 2019-07-25 15:15:19 CEST INFO: owner of PGDATA is the current user 2019-07-25 15:15:19 CEST INFO: access to the contents of PGDATA ok
Le pitrery check remonte les éventuels problèmes détectés sur la configuration actuelle. Si aucune erreur n’apparait, le script est prêt à être utilisé.
Usage
Dans un premier temps, nous allons effectuer un hot backup du cluster de base de données, pour ce faire la commande est très simple :
pitrery backup
L’outil va alors se charger de passer la base de données en mode backup, d’effectuer une sauvegarde compressée du répertoire $PG_DATA, pour enfin désactiver le mode backup :
[pg@pgsql1 ~]$ pitrery backup 2019-07-25 15:39:18 CEST INFO: preparing directories in /pgbackup/backups/ 2019-07-25 15:39:18 CEST INFO: listing tablespaces 2019-07-25 15:39:18 CEST INFO: starting the backup process 2019-07-25 15:39:18 CEST INFO: performing a non-exclusive backup 2019-07-25 15:39:22 CEST INFO: backing up PGDATA with tar 2019-07-25 15:39:22 CEST INFO: archiving /pgdata 2019-07-25 15:39:23 CEST INFO: stopping the backup process 2019-07-25 15:39:31 CEST INFO: copying the backup history file 2019-07-25 15:39:31 CEST INFO: copying the tablespaces list 2019-07-25 15:39:31 CEST INFO: copying PG_VERSION 2019-07-25 15:39:31 CEST INFO: backup directory is /pgbackup/backups//2019.07.25_15.39.30 2019-07-25 15:39:31 CEST INFO: done
On peut voir que le répertoire /pgbackup/backups/2019.07.25_15.39.30 contient un tar.gz contenant les données, ainsi que d’autres fichiers nécessaires au fonctionnement de pitrery :
[pg@pgsql1 2019.07.25_15.39.30]$ ls -lrth total 3,9M drwxrwxr-x 2 pg pg 6 25 juil. 15:39 tblspc -rw-rw-r-- 1 pg pg 3,9M 25 juil. 15:39 pgdata.tar.gz -rw-rw-r-- 1 pg pg 9 25 juil. 15:39 wal_segsize -rw-rw-r-- 1 pg pg 11 25 juil. 15:39 backup_timestamp -rw------- 1 pg pg 258 25 juil. 15:39 backup_label -rw------- 1 pg pg 46 25 juil. 15:39 tblspc_list -rw------- 1 pg pg 3 25 juil. 15:39 PG_VERSION -rw-rw-r-- 1 pg pg 131 25 juil. 15:39 backup_command -rw-r--r-- 1 pg pg 172 25 juil. 15:39 pitrery.conf
On peut voir le backup au niveau de pitrery également, via l’option list :
[pg@pgsql1 2019.07.25_15.39.30]$ pitrery list List of local backups /pgbackup/backups/2019.07.25_15.39.30 3,9M 2019-07-25 15:39:30 CEST
Un premier backup a donc été pris, admettons maintenant le scénario suivant :
- 1. La base de données contient un schéma pour gérer les employés d’une société
- 2. Un utilisateur exécute par erreur un delete sur la table des employés en omettant la clause where, vidant ainsi la table
- 3. Le projet demande la restauration de la base de données avant cette erreur humaine.
Exécution du script suivant pour charger le jeu de données :
create table dept ( id int PRIMARY KEY NOT NULL, name varchar(64) NOT NULL ); create table emp ( id int PRIMARY KEY NOT NULL, first_name varchar(64) NOT NULL, last_name varchar(64)NOT NULL, dept_id int NOT NULL, constraint fk_emp_dept FOREIGN KEY (dept_id) REFERENCES dept(id) ); -- Fill Departments insert into dept values (1,'Sales'); insert into dept values (2,'Accounting'); insert into dept values (3,'Direction'); -- Fill Employees insert into emp values (1,'Robert','KING',3); insert into emp values (2,'Emily','SANDERS',1); insert into emp values (3,'July','BROWN',1); insert into emp values (4,'Kate','DENSON',1); insert into emp values (5,'Daniel','PRICE',2); insert into emp values (6,'Lee','SONG',2); insert into emp values (7,'Kevin','SMITH',2);
L’utilisateur exécute la malheureuse requête sur la base de données à 16h20 :
delete from emp;
Il est alors demandé au DBA de restaurer la base de données avant cette requête.
Voici comment se déroule la restauration :
Arrêt de l’instance, et renommage du répertoire pg_data :
pg_ctl stop mv /pgdata/ /pgdata_old mkdir /pgdata # Ici, comme le répertoire est à la racine, on doit faire l'opération en tant que root et rendre le nouveau répertoire à l'utilisateur postgres chown postgres:postgres /pgdata
On peut alors lancer la restauration via pitrery :
[pg@pgsql1 ~]$ pitrery restore 2019-07-25 16:29:04 CEST INFO: searching backup directory 2019-07-25 16:29:05 CEST INFO: retrieving the PostgreSQL version of the backup 2019-07-25 16:29:05 CEST INFO: PostgreSQL version: 11 2019-07-25 16:29:05 CEST INFO: searching for tablespaces information 2019-07-25 16:29:05 CEST INFO: 2019-07-25 16:29:05 CEST INFO: backup: 2019-07-25 16:29:05 CEST INFO: directory: /pgbackup/backups/2019.07.25_15.39.30 2019-07-25 16:29:05 CEST INFO: storage: tar 2019-07-25 16:29:05 CEST INFO: encryption: no 2019-07-25 16:29:05 CEST INFO: 2019-07-25 16:29:05 CEST INFO: destinations directories: 2019-07-25 16:29:05 CEST INFO: PGDATA -> /pgdata 2019-07-25 16:29:05 CEST INFO: 2019-07-25 16:29:05 CEST INFO: recovery configuration: 2019-07-25 16:29:05 CEST INFO: target owner of the restored files: pg 2019-07-25 16:29:05 CEST INFO: restore_command = 'restore_xlog -C /etc/pitrery/pitrery.conf %f %p' 2019-07-25 16:29:05 CEST INFO: 2019-07-25 16:29:05 CEST INFO: checking if /pgdata is empty 2019-07-25 16:29:05 CEST INFO: setting permissions of /pgdata 2019-07-25 16:29:05 CEST INFO: extracting PGDATA to /pgdata 2019-07-25 16:29:05 CEST INFO: extraction of PGDATA successful 2019-07-25 16:29:05 CEST INFO: preparing pg_wal directory 2019-07-25 16:29:05 CEST INFO: preparing recovery.conf file 2019-07-25 16:29:05 CEST INFO: done 2019-07-25 16:29:05 CEST INFO: 2019-07-25 16:29:05 CEST INFO: please check directories and recovery.conf before starting the cluster 2019-07-25 16:29:05 CEST INFO: and do not forget to update the configuration of pitrery if needed: 2019-07-25 16:29:05 CEST INFO: /pgdata/recovery.conf 2019-07-25 16:29:05 CEST INFO:
L’outil restaure le backup et génère un recovery.conf dans le répertoire $PG_DATA :
restore_command = 'restore_xlog -C /etc/pitrery/pitrery.conf %f %p' #recovery_target_time = '' # e.g. '2004-07-14 22:39:00 EST' #recovery_end_command = '' #recovery_target_name = '' # e.g. 'daily backup 2011-01-26' #recovery_target_xid = '' #recovery_target_lsn = '' # e.g. '0/70006B8' #recovery_target_inclusive = true #recovery_target = 'immediate' #recovery_target_timeline = 'latest' #recovery_target_action = 'pause'
C’est ce fichier qui va spécifier les modalités du recover.
Si l’on n’y apporte aucune modification, l’instance, au démarrage, va rejouer l’ensemble des wal disponibles, et ce jusqu’au dernier êtat connu : celui dans lequel la table est vide.
Il convient donc de décommenter et spécifier le timestamp dans la ligne recovery_target_time qui permet de spécifier jusqu’à quel timestamp les wal doivent être appliqués.
Nous souhaitons ici disposer de l’état avant 16h20, disons 16h15 :
restore_command = 'restore_xlog -C /etc/pitrery/pitrery.conf %f %p' recovery_target_time = '2019-07-25 16:15:00 CEST' [...]
Au lancement de l’instance, le fichier recovery.conf est lu et l’application des wal commence :
[pg@pgsql1 pgdata]$ pg_ctl start waiting for server to start....2019-07-25 16:46:27.208 CEST [7094] LOG: listening on IPv6 address "::1", port 5432 2019-07-25 16:46:27.208 CEST [7094] LOG: listening on IPv4 address "127.0.0.1", port 5432 2019-07-25 16:46:27.211 CEST [7094] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2019-07-25 16:46:27.220 CEST [7095] LOG: database system was interrupted; last known up at 2019-07-25 15:39:20 CEST 2019-07-25 16:46:27.504 CEST [7095] LOG: starting point-in-time recovery to 2019-07-25 16:10:00+02 2019-07-25 16:46:27.626 CEST [7095] LOG: restored log file "00000007000000000000000E" from archive 2019-07-25 16:46:27.650 CEST [7095] LOG: redo starts at 0/E000060 2019-07-25 16:46:27.651 CEST [7095] LOG: consistent recovery state reached at 0/E000130 2019-07-25 16:46:27.651 CEST [7094] LOG: database system is ready to accept read only connections 2019-07-25 16:46:27.777 CEST [7095] LOG: restored log file "00000007000000000000000F" from archive 2019-07-25 16:46:27.801 CEST [7095] LOG: recovery stopping before commit of transaction 589, time 2019-07-25 16:20:59.051885+02 2019-07-25 16:46:27.801 CEST [7095] LOG: recovery has paused 2019-07-25 16:46:27.801 CEST [7095] HINT: Execute pg_wal_replay_resume() to continue.
L’instance est désormais accessible en read-only pour permettre au DBA de vérifier que l’état attendu est atteint :
[pg@pgsql1 pgdata]$ psql psql (11.2) Type "help" for help. pg=# select * from emp; id | first_name | last_name | dept_id ----+------------+-----------+--------- 1 | Robert | KING | 1 2 | Emily | SANDERS | 1 3 | July | BROWN | 1 4 | Kate | DENSON | 1 5 | Daniel | PRICE | 2 6 | Lee | SONG | 2 7 | Kevin | SMITH | 2 (7 rows)
La table n’est pas vide, nous somme bien à l’état attendu, on peut alors ouvrir la base de données et reprendre l’activité transactionnelle via la fonction pg_wal_replay_resume :
select pg_wal_replay_resume()
La base de données est désormais ouverte aux écritures, avec les données perdues restaurées.
Et si vous souhaitez vous former sur PostgreSQL, découvrez notre offre de formations PostgreSQL.
1 réflexion sur “Backup & Recovery PostgreSQL avec Pitrery”
Merci Arnaud ! Comme d’habitude avec pg… c’est super simple ;).
Au passage, une petite coquille de typo : « Toujours dans le postgresql.com, « , tu voulais parler du .conf je présume.
Les commentaires sont fermés.