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.