pg_back

Il existe deux familles de méthodes de backup d'une base de données PostgreSQL :

• Backup logique
• Backup binaire à "chaud et à froid"

Voici une présentation simplifiée des différences entre ces deux modes de sauvegarde, qui peut comporter certaines imprécisions dues à cette vulgarisation.

Un backup logique est effectué par pg_dump sur une instance PostgreSQL en cours d'exécution (nommée "à chaud"). pg_dump supporte plusieurs formats d'archivage dont plain et custom.

Le format plain génère un fichier SQL classique, lisible "humainement".
Le format custom génère un fichier binaire, qui est plus flexible et a une taille bien plus réduite que le format plain. Il est toujours possible de générer un fichier SQL comme plain à partir d'un fichier custom : avec la commande pg_restore -f output.sql fichier_custom.

Il est possible de réaliser des sauvegardes et restaurations à "distance", via le protocole classique PostgreSQL Frontend Backend Protocol.
Il est possible d'importer un backup logique vers une instance PostgreSQL de version différente, en général plus récente.

Un backup binaire peut être effectué à "chaud" ou à "froid". En simplifiant, cela consiste à sauvegarder les fichiers PostgreSQL du filesystem et optionnellement sauvegarder aussi les journaux (WAL) de PostgreSQL. Pour effectuer un backup binaire, il existe la commande officielle pg_basebackup, mais aussi d'autres solutions plus complètes, comme pgBackRest ou barman.

Les systèmes de backup binaire de PostgreSQL ont l'avantage de pouvoir restaurer une sauvegarde à un point précis dans le temps (fonctionnalité PITR).

Je constate que la mise en place d'un backup binaire est plus complexe à mettre en place qu'un backup logique.

Voici mon POC le plus avancé concernant les backup binaire : poc-pg_basebackup_incremental.

Actuellement, pour sauvegarder des instances PostgreSQL, j'utilise pg_back-docker-sidecar qui est une solution de backup logique, basé sur pg_back, déployé sous la forme d'un Docker sidecar.

J'envisage aussi d'expérimenter une méthode basée sur parquet_s3_fdw que j'ai décrite dans 2025-04-18_1140.

Pour des informations plus approfondies à propos de ces sujets, je vous conseille la documentation de ces formations de Dalibo :

• Sauvegarde et restauration
• Point In Time Recovery
• Outils de sauvegarde physique
• Gestion d’un sinistre

Je viens de terminer une première itération de travail sur Projet 27 - "Créer un POC de pg_back".

Le résultat se trouve dans le repository GitHub : pg_back-docker-sidecar

J'ai passé en tout 17 h 30 sur ce projet, écriture de notes incluse.

Ce projet a évolué par rapport à mon objectif initial :

Initialement, dans ce dépôt, je voulais tester l'implémentation de pg_back déployé dans un conteneur Docker comme un « sidecar » pour sauvegarder une base de données PostgreSQL déployée via Docker.

Et progressivement, j'ai changé l'objectif de ce projet. Il contient maintenant

• le code source pour construire une image Docker Sidecar nommée stephaneklein/pg_back-docker-sidecar:2.5.0-delete-local-file-after-upload
• un tutoriel étape par étape qui présente tous les aspects de l'utilisation de ce conteneur
• un espace de travail qui me permet de contribuer au projet pg_back en amont : ./src/

source

Voici tous les éléments testés dans le tutoriel :

• pg_back est dépolyé dans un Docker sidecar
• L'instance PostgreSQL est sauvegardée dans une instance Minio
• Les archives sont chiffrées avec age
• Les archives sont générées au format custom
• J'ai documenté une méthode pour télécharger une archive dans un dossier du workspace du développeur
• J'ai documenté une méthode pour restaurer l'archive dans un serveur PostgreSQL déployé via Docker
• J'ai testé le fonctionnement du système d'expiration des archives
• J'ai testé la fonctionnalité de "purge" automatique

Éléments que j'ai implémentés

L'image Docker proposée par pg_back ne contient pas de scheduler de type cron et ne suit pas les recommandations The Twelve-Factors App.

J'ai décidé d'implémenter ma propre image Docker stephaneklein/pg_back-docker-sidecar:2.5.0-delete-local-file-after-upload avec les ajouts suivants :

• Support de configuration basé sur des variables d'environnement, par exemple :

  pg_back:
    image: stephaneklein/pg_back-docker-sidecar:2.5.0-delete-local-file-after-upload
    environment:
      POSTGRES_HOST: postgres1
      POSTGRES_PORT: 5432
      POSTGRES_USER: postgres
      POSTGRES_DBNAME: postgres
      POSTGRES_PASSWORD: password
      
      BACKUP_CRON: ${BACKUP_CRON:-0 3 * * *}
      UPLOAD: "s3"
      UPLOAD_PREFIX: "foobar"
      ...

• Intégration de Supercronic pour exécuter pg_back régulièrement, une fonctionnalité de type cron

Patch envoyé en upstream

J'ai proposé deux patchs à pg_back :

• Add upload_prefix option to pg_back.conf example file
• Add the --delete-local-file-after-upload to delete local file after upload

Le premier patch est totalement mineur.

Dans la version actuelle 2.5.0 de pg_back, les archives dump ne sont pas supprimées du filesystem de container après l'upload vers l'Object Storage.
Ce choix me perturbe, car je préfère éviter de surcharger le disque avec des fichiers d'archives volumineux qui risquent de saturer l'espace disponible.

Pour éviter cela, j'ai implémenté "Add the --delete-local-file-after-upload to delete local file after upload" qui permet de supprimer les fichiers intermédiaires après upload.

Bilan

J'ai réussi à effectuer un cycle complet de la sauvegarde à la restauration.
J'ai décidé d'utiliser pg_back pour mes sauvegardes PostgreSQL automatique vers Object Storage.

J'ai déprécié le projet restic-pg_dump-docker pour inviter à utiliser pg_back.

Idée d'amélioration

#JaimeraisUnJour créer et implémenter les issues suivantes.

1. Implémenter une commande pg_back snapshots pour lister les snapshots sous une forme facilement lisible par un humain. Actuellement, le retour de la commande ressemble à ceci :

$ pg_back --list-remote s3
foobar/hba_file_2025-04-14T14:58:08Z.out.age
foobar/hba_file_2025-04-14T14:58:39Z.out.age
foobar/ident_file_2025-04-14T14:58:08Z.out.age
foobar/ident_file_2025-04-14T14:58:39Z.out.age
foobar/pg_globals_2025-04-14T14:58:08Z.sql.age
foobar/pg_globals_2025-04-14T14:58:39Z.sql.age
foobar/pg_settings_2025-04-14T14:58:08Z.out.age
foobar/pg_settings_2025-04-14T14:58:39Z.out.age
foobar/postgres_2025-04-14T14:58:08Z.dump.age
foobar/postgres_2025-04-14T14:58:39Z.dump.age

Je ne trouve pas ce rendu agréable à lire. J'aimerais afficher quelque chose qui ressemble à la sortie de restic. Par exemple :

$ pg_back snapshots
ID        Date                 Folder
---------------------------------------
40dc1520  2025-04-14 14:58:08  foobar
79766175  2025-04-14 14:58:39  foobar

2. Implémenter un système de suppressions des archives basé sur des règles plus avancées, comme celle de restic

3. Implémenter un refactoring vers cobra pour utiliser des sous-commandes (subcommands) et éviter le mélange entre paramètres et commandes.

Je me relance sur mes sujets de backup de PostgreSQL.

Au mois de février dernier, j'ai initié le « Projet 23 - "Ajouter le support pg_basebackup incremental à restic-pg_dump-docker" ».

J'ai ensuite publié les notes suivantes à ce sujet :

• 2025-02-09_1705
• 2025-02-12_1044
• 2025-02-12_1511
• 2025-02-12_2305
• 2025-02-13_1409

À ce jour, je n'ai pas fini mes POC suivants :

• poc-pgbackrest
• poc-barman
• poc-pg_basebackup_incremental

poc-pg_basebackup_incremental est la seule méthode que j'ai réussi à faire fonctionner totalement.

#JaimeraisUnJour terminer ces POC.

Aujourd'hui, je m'interroge sur les motivations qui m'ont conduit en 2020 à intégrer restic dans mon projet restic-pg_dump-docker. Avec le recul, l'utilisation de cet outil pour la simple sauvegarde d'archives pg_dump me semble désormais moins évidente qu'à l'époque.

J'ai fait ce choix peut-être pour bénéficier directement du support des fonctionnalités suivantes :

• Uploader vers différents Object Storage : S3-compatible Storage
• Le système de rétention : Removing snapshots according to a policy
• Le chiffrement : Encryption
• Et naïvement, je pensais peut-être pouvoir utiliser le système de déduplication des données : Backups and Deduplication

Après réflexion, je pense que pour la sauvegarde d'archives pg_dump, les fonctionnalités de déduplication et de sauvegarde incrémentale offertes par restic génèrent en réalité une surconsommation d'espace disque et de ressources CPU sans apporter aucun bénéfice.

J'ai ensuite effectué quelques recherches pour savoir s'il existait un système de sauvegarde PostgreSQL basé sur pg_dump et un système d'upload vers Object Storage et #JaiDécouvert pg_back (https://github.com/orgrim/pg_back/).

En 2020, quand j'ai créé restic-pg_dump-docker, je pense que je n'avais pas retenu pg_back car celui-ci était minimaliste et ne supportait pas encore l'upload vers de l'Object Storage.

En 2025, pg_back supporte toutes les fonctionnalités dont j'ai besoin :

pg_back is a dump tool for PostgreSQL. The goal is to dump all or some databases with globals at once in the format you want, because a simple call to pg_dumpall only dumps databases in the plain SQL format.

Behind the scene, pg_back uses pg_dumpall to dump roles and tablespaces definitions, pg_dump to dump all or each selected database to a separate file in the custom format. ...

Features

• ...
• Choose the format of the dump for each database
• ...
• Dump databases concurrently
• ...
• Purge based on age and number of dumps to keep
• Dump from a hot standby by pausing replication replay
• Encrypt and decrypt dumps and other files
• Upload and download dumps to S3, GCS, Azure, B2 or a remote host with SFTP

source

Je souhaite :

• Créer et publier un playground pour tester pg_back
• Si le résultat est positif, alors je souhaite ajouter une note en introduction de restic-pg_dump-docker pour inviter à ne pas utiliser ce projet et renvoyer les lecteurs vers le projet pg_back.