Analyse et choix du types de sauvegarde

Il existe différentes approches possibles pour la sauvegarde de base de données :

  1. Sauvegardes logiques
  2. Sauvegarde physique
    • Standalone
    • PITR (Point In Time Recovery)

La première approche à laquelle nous allons nous intéresser est la sauvegarde logique, autrement appelée sauvegarde SQL. Il s’agit de la solution actuellement utilisée, via la commande pg_dump. La base de données produite est cohérente.

Il s’agit d’une solution simple à déployer, à maintenir, et à utiliser pour la restauration. Sur le plan de l’efficacité la question est cependant plus complexe. Un discours simplificateur énoncerait que “La sauvegarde s’effectue en parallèle, et ne gène pas le fonctionnement du service”. Les plus sceptiques comprendront que cette réponse ne peut être satisfaisante sans explication.

Pour bien comprendre comment fonctionne la commande pg_dump, il faut étudier l’instruction de déclaration de transaction utilisée par celle-ci :

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE


L’instruction démarre une transaction ayant le niveau d'isolation le plus élevé : c’est à dire qu’il doit exister un ordre permettant d’effectuer l’ensemble des instructions de cette transaction et des autres séquentiellement. De plus, les instructions effectuées lors de la transactions accèdent aux données telles qu’elles existaient au début de la transaction.

Lorsque l’on y réfléchit, cette approche pose un problème d’efficacité puisque toute écriture provenant d’une transaction concurrente doit être mise en pause, sous la forme d’un verrou de lecture. Elle n’est donc pas envisageable en temps que tel du point de vue de la continuité de service.

Cependant, PostgreSQL est doté d’un mécanisme appelé MVCC (Multi Version Concurrency Control) permettant de retenir plusieurs états de la base de donnée. Des scénarios problématiques apparaissent cependant lorsque plusieurs transactions sérialisables effectuent des opérations d’écriture concurrentielles, ce qui n’est pas le cas de pg_dump qui effectue uniquement des opérations de lecture.

Note:

Nous pouvons donc en déduire que l’opération de sauvegarde effectuée par pg_dump ne nuit pas à la continuité de service. Un autre point positif est l’interopérabilité de cette approche, car elle propose une sauvegarde indépendante de la version de PostgreSQL.

Cependant, cette approche dispose de deux points négatifs notables. D’une part, le RPO (Recovery Point Objective) dépend de la fréquence des sauvegardes, il est donc généralement assez haut, et ne permet pas de faire de la haute fiabilité. D’autre part, la durée de restauration (RTO) dépend de la taille de la base de donnée car il est nécessaire d’exécuter de nombreuses instructions pour afin d’effectuer une restauration complète.

MySQL permet grâce au moteur de base de donnée InnoDB (moteur par défaut depuis la version 5.5.5 de Mysql), d’effectuer des transactions, et à fortiori, une sauvegarde transactionnelle.
L’analogue de pg_dump pour MySQL se nomme mysqldump. Il effectue une sauvegarde logique, par défaut non transactionnelle. Les tables sont donc verrouillées en écriture durant la sauvegarde.

Pour remédier à ce problème, Le paramètre –single-transaction permet d’effectuer une sauvegarde transactionnelle, et donc non bloquante. Seul point négatif, les instructions ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE ne doivent pas être utilisées durant la sauvegarde, sous peine d’obtenir des tables vides. Bien heureusement, ces instructions sont peu fréquemment utilisées dans l’état de fonctionnement normal d’un service.

Cette approche consiste à effectuer une sauvegarde physique complète des fichiers utilisés par la base de donnée.

Attention:

Elle dispose d’un inconvénient majeur: l’obligation d’arrêter la base de donnée lors de la sauvegarde, et ne permet donc pas d’assurer la continuité de service lors des opérations de sauvegarde.

De plus, elle dispose des même désavantages que la sauvegarde logique, c’est à dire un RPO dépendant de la fréquence de sauvegarde (d’autant plus que celle-ci est bloquante), ainsi qu’un RTO non nul, bien que techniquement plus faible puisque la restauration sera plus rapide.

Des solutions limitant la durée d’arrêt lors d’une sauvegarde (via l’utilisation de rsync) existent. Cependant, la complexité d’une telle solution, sa non-interopérabilité, ainsi que le fait qu’elle n’apporte que peu d’avantages par rapport à une sauvegarde logique (une restauration plus rapide), n’en font pas le candidat idéal.

L’archivage continu est la solution la plus puissante. Elle consiste en une sauvegarde complète de la base de donnée, complétée par une sauvegarde en continu des changements effectuées.

Cette approche a comme avantage principal d’offrir un RPO quasiment nul, et donc de garantir la fiabilité des données. L’ensemble des changements étant archivés, il est possible d’effectuer une restauration très précise dans le temps (PITR).

La mise en place d’une une solution “maison” pour effectuer ce type de sauvegarde s’avère cependant complexe, notamment concernant la restauration. Des applications telles que pg_barman, pgBackRest pour PostgreSQL et Percona XtraBackup pour MySQL proposent une mise en place et une gestion simplifiée de ce type de sauvegarde.

Avec PostgreSQL

L’archivage continu sous PostgreSQL fonctionne grâce à la récupération des journaux WAL (Write Ahead Log).

Note:

Ceux-ci enregistrent l’ensemble des modifications effectuées sur la base de donnée.

Grâce à leur archivage, une restauration consiste donc a un redéploiement de la sauvegarde physique, sur laquelle sont rejoués les journaux WAL. De plus, le nombre de sauvegardes complètes est drastiquement réduit, car de nouvelles sauvegardes servent uniquement à éviter d’avoir à exécuter un très grand nombre de fichiers WAL lors de la restauration.

Par ailleurs, cette approche facilite la mise en place d’un système de réplication, permettant de fournir de la haute disponibilité, via des outils tels que repmgr pour PosgreSQL.

Avec MySQL

L’archivage continu sous MySQL fonctionne de manière similaire grâce à l’utilisation de “binary log files”, produit par le moteur de base de donnée lorsque celui-ci est lancé avec l’option –log-bin.

L’analyse de ces 3 modes de sauvegarde nous montrent que la solution dite ‘Standalone’ se révèle être la moins intéressante, les défauts majeurs étant :

  • Une mise en place complexe (mauvaise d’accessibilité technique)
  • Sauvegarde bloquante
  • Sauvegarde non continue

Quant aux deux autres solutions, elles ont leurs avantages respectifs: le choix d’un système de sauvegarde logique permet une très faible accessibilité technique, tandis que son opposant propose un système de sauvegarde continue, éliminant la perte de donnée.

Dans le cadre de l’association Picasoft, une sauvegarde logique représente donc la solution préférable, puisqu’elle s’avère être la plus simple à comprendre, facilitant le travail de ceux qui seront, dans le futur, amener à comprendre et utiliser le système de sauvegarde/restauration. Aussi, contrairement à une entreprise pour laquelle une perte de donnée de l’ordre de l’heure est critique, il est acceptable pour les services proposés par Picasoft de ne pas proposer de sauvegarde en continu.

Des améliorations de ce système de sauvegarde subsistent, notamment concernant les points suivants :

  • Mise en place d’un système de stockage des sauvegardes extérieures
  • Chiffrement des sauvegardes exportées
  • technique/old/adminsys/backup/db/etude.txt
  • de rdelaage