Gestion des sauvegardes

Sauvegardes logiques avec pg_dump

Nous allons nous familiariser avec l'outil pg_dump. Pour ce faire, nous allons utiliser le dataset Pagila.

Rappels sur les options de pg_dump

# Options communes aux outils postgres
-d nom de la base
-h nom d'hôte de l'instance
-p port de l'instance
-U nom d'utilisateur
-W demander le mot de passer à la connexion

# Options propres à pg_dump
-f écrire le résultat du dump dans un fichier/répertoire spécifié
-F c|d|t|p format de sortie (custom, directory, tar, sql)
-j nombre de jobs à exécuter en parallèle
-Z (0-9) niveau de compression
-a uniquement les données
-s uniquement la structure
-t une table en particulier
-n un schema en particulier

Ex :

pg_dump -d pagila -F p -f pagila.sql

Réaliser les sauvegardes suivantes :

Sauvegarde complète de la base au format SQL
Même sauvegarde mais avec un facteur de compression.

Quel est le contenu du fichier résultant ? Comment récupérer le fichier sous format lisible ?

Sauvegarde complète de la base, au format Custom

Ouvrir les fichiers de sauvegarde au format SQL et au format Custom, comparer le contenu de chaque.

Sauvegarde complète de la base, au format directory
Sauvegarde partielle de la base, seulement les tables film, actor et country

Comment s'assurer que toutes les tables demandées sont bien sauvegardées ? Essayer de sauvegarde une table qui n'existe pas. Essayer à nouveau avec l'option --strict-names.

Sauvegardes multi-schéma :

Créer un schéma supplémentaire test_dump et deux tables test_table1 et test_table2 à l'intérieur.

CREATE SCHEMA test_dump;
CREATE TABLE test_dump.test_table1 (num1 bigint, num2 double precision, num3 double precision);
CREATE TABLE test_dump.test_table2 (num1 bigint, num2 double precision, num3 double precision);

Réaliser les sauvegardes :

Sauvegarde partielle au format directory contenant la structure seule des tables test_table1 et actor

Bien penser à l'option --strict-names pour s'assurer de bien sauvegarder les tables.

Sauvegarde partielle au format directory contenant la structure seule des tables du schéma test_dump dont le nom commence par test_
Sauvegarde partielle au format SQL de la table payments

Ouvrir le fichier SQL issu de cette sauvegarde. Les données ne sont pas présentes à l'intérieur, pourquoi ?

Payments est une table partitionnée, ses données sont réparties dans les tables enfant payment_p2020_01/02/03 etc.

Sauvegarder l'ensemble des tables payments au format SQL. Comparer le SQL générer avec le précédent.

Parallelisation des sauvegardes :

Nous allons insérer quelques données dans les tables de test créees précedemment :

INSERT INTO test_dump.test_table1 (num1, num2, num3)
  SELECT round(random()*10), random(), random()*142
  FROM generate_series(1, 2000000) s(i);

INSERT INTO test_dump.test_table2 (num1, num2, num3)
  SELECT round(random()*10), random(), random()*142
  FROM generate_series(1, 2000000) s(i);

Nous allons réaliser deux sauvegardes en mesurant le temps d'exécution de la commande pg_dump : time pg_dump ....

Sauvegarde partielle au format directory du schema test_dump, sans parallelisation
Sauvegarde partielle au format directory du schema test_dump, avec parallelisation = 2

Comparer les temps d'exécution puis réaliser les deux sauvegardes suivantes :

Sauvegarde partielle au format directory de la table test_dump.test_table1, sans parallelisation
Sauvegarde partielle au format directory de la table test_dump.test_table1, avec parallelisation = 2

Restauration des sauvegardes

Restauration avec psql

La restauration des sauvegardes au format SQL est très simple et se fait avec psql.

Nous allons pour commencer créer une base dédiée à la restauration.

Nuance méconnue, les templates de création de base pouvant contenir des spécificités, il est conseillé de créer une base basée sur template0 pour qu'elle soit complètement vide et éviter les conflits.

CREATE DATABASE formation_restore TEMPLATE template0;

Il suffit ensuite de se connecter à cette base avec psql et d'exécuter le fichier de sauvegarde :

psql -d formation_restore -f formation.sql

ou

$ psql -d formation_restore
formation_restore=# \i formation.sql

Restauration avec pg_restore

Nous allons mettre en oeuvre les différentes possibilités de restauration avec pg_restore.

Pour se faire nous allons travailler avec une sauvegarde complète de notre base au format Custom

pg_dump -d formation -F c -f formation.dump

Rappel de quelques options utiles de pg_restore :

-d restaurer dans cette base de données
-f écrire le résultat de la restauration dans un fichier
-a restaurer uniquement les données
-n restaurer uniquement ce schema
-t restaurer uniquement cette table
-l afficher la table des matières de l'archive
-e s'arrêter à la première erreur
-c supprimer les objets avant des les restaurer
-C créer la base avant de restaurer à l'intérieur

Effectuer les opérations suivantes :

Créer la base formation_restore2 à la main puis restaurer dans les données dans cette base avec pg_restore
Répéter la dernière restauration telle quelle. Un nombre considérable d'erreurs doivent s'afficher
Répéter la dernière restauration en ajoutant l'option -e (--exit-on-error)

À partir de maintenant, on s'arrête dès qu'une erreur se produit. C'est mieux, mais cela peut malgré tout laisser la base dans un état incohérent après une restauration partielle. Pour s'en convaincre, on peut exécuter la commande de restauration encore une fois, l'erreur sera différente.

Pour se prémunir de cet effet, le mieux est d'exécuter la restauration dans une seule transaction.

Supprimer et recréer la base formation_restore2
Reprendre la commande précédente, en ajoutant --single-transaction, et l'exécuter plusieurs fois de suite

L'erreur doit toujours être la même.

Nous allons maintenant réaliser une restauration partielle en utilisant la table des matières du fichier dump.

Nous devons d'abord écrire cette table des matières dans un fichier :

pg_restore -l formation.dump > formation.toc

Ouvrir le fichier formation.toc
Commenter toutes les lignes (ajouter un ; devant chaque ligne)
Utiliser pg_restore pour créer un fichier formation-restore-preview.sql qui contient les commandes SQL qui seraient exécutées pour effectuer cette restauration

Ce fichier doit être vide car nous avons commenté toutes les lignes

Décommenter les lignes dans formation.toc de sorte à ce que nous restorions la table film et son contenu
Générer à nouveau le fichier formatoin-restore-preview.sql
Vérifier qu'il contient bien la structure de la table film et son contenu