Sauvegarde PostgreSQL. Comment sauvegarder une base de données PostgreSQL ?

Qu’est-ce que PostgreSQL et pourquoi doit-il être sauvegardé ?

PostgreSQL est un plugin conçu pour simplifier les procédures de sauvegarde et de restauration. Il existe de nombreuses raisons pour lesquelles vos bases de données peuvent être en danger. Par exemple, une panne de périphérique (qui peut entraîner l’indisponibilité de toutes les instances), la corruption des données (qui peut également se propager entre les répliques), une erreur logicielle, une erreur humaine, une attaque de ransomware ou de malware, et les exigences de conformité (telles que les périodes de conservation des données et le stockage des données).

Si vous administrez une base de données, il est essentiel de conserver des sauvegardes régulières pour ne pas risquer de perdre des informations précieuses et des heures de travail. Disposer d’une solide stratégie de sauvegarde et de récupération des bases de données est un élément crucial de la gestion des données pour toute opération.

Un plugin tel que la sauvegarde PostgreSQL peut également vous aider à éviter des défaillances coûteuses qui pourraient être un motif de poursuites judiciaires. Si quelque chose se produit qui perturbe votre base de données (en raison d’erreurs innocentes ou d’attaques malveillantes), vous pouvez restaurer vos données et les faire fonctionner à nouveau rapidement. Cet article vous expliquera comment sauvegarder et restaurer facilement des bases de données Postgres.

Quelles méthodes de sauvegarde sont intégrées à Postgres ?

PostgreSQL peut aider les administrateurs à protéger l’intégrité des données, les développeurs à construire des environnements plus résilients et sécurisés, et les programmeurs à créer de nouvelles applications. Le plugin prend en charge trois techniques de sauvegarde : Le dump SQL, la sauvegarde au niveau du système de fichiers et l’archivage continu. Examinons chacune d’entre elles plus en détail.

Dump SQL

L’objectif de cette méthode de vidage est de générer un fichier contenant des commandes SQL qui peuvent être renvoyées au serveur. Ces informations peuvent l’aider à recréer la base de données dans l’état où elle se trouvait lorsque le dump a été effectué. Vous pouvez exécuter cette procédure depuis n’importe quel hôte distant, à condition qu’il ait accès à la base de données (vous devrez avoir un accès en lecture à toutes les tables que vous souhaitez sauvegarder, il est donc préférable d’exécuter le dump en tant que superutilisateur de la base de données). Les vidages créés à l’aide de la sauvegarde PostgreSQL sont cohérents en interne et représentent un instantané de la base de données telle qu’elle était au début du vidage.

La commande de base pour créer un dump est :

pg_dump dbname > dumpfile

Et pour en restaurer un :

psql dbname < dumpfile

Un avantage de cette méthode de sauvegarde est que vous pouvez généralement recharger cette sortie dans de nouvelles versions de PostgreSQL et transférer une base de données vers une architecture de machine différente (par exemple, d’un serveur 32 bits à un serveur 64 bits).

Sauvegarde au niveau du système de fichiers

Vous pouvez également sauvegarder et restaurer une base de données PostgreSQL en copiant directement les fichiers. Par exemple, en utilisant la commande :

tar -cf backup.tar /usr/local/pgsql/data

Cette méthode est cependant considérée comme légèrement plus peu pratique ou inférieure par rapport à une décharge, car :

1. Le serveur de la base de données doit être arrêté pour obtenir une sauvegarde utilisable. Vous ne pouvez pas vous contenter d’interdire les connexions ; vous devez arrêter complètement le serveur. La raison en est que des outils comme tar prennent un instantané non seulement de l’état du système de fichiers mais aussi de la mise en mémoire tampon interne).
2. Vous ne pouvez pas sauvegarder ou restaurer uniquement des tables individuelles ou des bases de données sélectionnées à partir de leurs répertoires et fichiers, car les informations qu’elles contiennent ne peuvent pas être utilisées sans leurs fichiers journaux de livraison respectifs.

Les sauvegardes au niveau du système de fichiers ne fonctionnent que pour la sauvegarde et la restauration complètes d’un cluster de base de données. Vous devez créer un « instantané cohérent » ou « instantané figé » de l’ensemble du volume contenant la base de données – puis copier l’ensemble du répertoire de données sur un périphérique de sauvegarde et libérer l’instantané figé.

Cette procédure de sauvegarde peut être effectuée pendant que le serveur est en cours d’exécution ; sachez simplement que la sauvegarde enregistrera les fichiers de la base de données dans un état comme si le serveur n’était pas correctement arrêté (vous pouvez effectuer un point de contrôle juste avant de prendre l’instantané pour réduire le temps de récupération). Gardez également à l’esprit qu’une sauvegarde du système de fichiers sera probablement plus volumineuse qu’un dump SQL.

Archivage continu Sauvegarde PostgreSQL

PostgreSQL maintient en permanence un write ahead log dans le sous-répertoire pg_wal/, enregistrant chaque modification apportée aux fichiers de données de la base. Le but de ce journal est de s’assurer que, dans le cas d’une panne du système, la base de données peut être restaurée à la cohérence. Pour ce faire, PostgreSQL « rejoue » les entrées du journal qui ont été effectuées depuis le dernier point de contrôle.

Cette technique de sauvegarde peut être combinée à celle de la sauvegarde au niveau du système de fichiers pour sauvegarder les fichiers WAL. Ensuite, si vous avez besoin d’effectuer une restauration, vous pouvez restaurer le système de fichiers et ensuite rejouer à partir des fichiers WAL pour le ramener à l’état actuel. La relecture du journal corrigera toute incohérence interne car vous pouvez combiner une longue séquence de fichiers WAL et réaliser une sauvegarde continue en archivant les fichiers WAL. De plus, si vous alimentez ces séries de fichiers vers une autre machine qui possède le même fichier de sauvegarde de base, vous pouvez disposer d’un système de secours à chaud avec une copie presque à jour de la base de données.

Gardez simplement à l’esprit que cette technique de sauvegarde ne peut restaurer que des clusters de bases de données entiers et non des sous-ensembles. Elle utilise également beaucoup de stockage d’archivage car elle archive non seulement la base de données de base mais aussi tout le trafic WAL. Cependant, de nombreuses personnes préfèrent cette méthode en raison de sa plus grande fiabilité.

Quels sont les produits existants sur le marché de la sauvegarde PostgreSQL, et ce qu’ils peuvent apporter ?

PostgreSQL est un système de gestion de base de données incroyablement populaire (Stack Overflow l’a en fait classé deuxième devant Microsoft SQL Server, Mongo et Oracle).

En raison de ses capacités et de son utilisation croissante, plusieurs entreprises proposent ce système dans le cadre de leur service pack. En voici quelques-unes :

• Veeam: Cet outil simple et pratique gère efficacement la reprise après sinistre et le stockage des données. Il permet également aux utilisateurs de créer différentes stratégies en fonction de divers travaux. Cependant, les clients ont signalé des temps de réponse longs de la part de l’équipe d’assistance.
• Dell Technologies: Vous pouvez utiliser leur centrale de protection des données comme un tableau de bord consolidé pour la santé globale de toutes vos sauvegardes utilisant Networker et Avamar. Le produit, cependant, est considéré comme légèrement désuet et n’a pas beaucoup évolué ces dernières années.
• Commvault Backup and Recovery: Commvault vous permet de restaurer les données par le biais d’une console de gestion centralisée, éliminant ainsi la nécessité pour les administrateurs de travailler sur le terrain. Leur fonction « Edge Drive » permet également aux utilisateurs d’utiliser le système comme un nuage privé. Certains clients ont toutefois signalé de graves problèmes d’infrastructure sous-jacente. Par exemple, une panne de courant entraîne la corruption des bases de données de déduplication.

• Percona PostgreSQL distribution: Percona offre une collection d’outils pour vous aider à gérer votre système de base de données PostgreSQL en installant PostgreSQL et en le complétant avec des extensions pour résoudre les tâches pratiques essentielles et permettre aux programmes clients d’envoyer et de recevoir des requêtes vers et depuis le serveur backend PostgreSQL.

• Veritas: Veritas est un outil de protection des données d’entreprise qui s’attaque aux systèmes hérités et vous permet d’étendre vos processus d’automatisation, de gouvernance et de gestion existants aux bases de données EDB Postgres Advanced Server et PostgreSQL.

• MicroFocus: MicroFocus est l’un des plus grands fournisseurs de logiciels d’entreprise au monde. Il propose plusieurs options de sauvegarde pour SQL Server, Oracle et PostgreSQL. Ils peuvent également faire des recommandations pour la sauvegarde des bases de données utilisées par les configurations de serveur.

• Cohesity: Cohesity simplifie et rationalise la gestion des données dans les bases de données distribuées et relationnelles. Cette option est généralement choisie par les entreprises dont les volumes de données sont importants et en expansion. Cohesity utilise une fonction Remote Adapter pour sauvegarder les bases de données PostgreSQL et les journaux WAL.

• Rubrik: Rubrik offre une protection contre les pirates informatiques et peut aider les entreprises à se remettre rapidement des attaques. La plateforme prend en charge la sensibilisation aux applications pour Oracle, SAP HANA, PostgreSQL, Microsoft SQL Server, MySQL et NoSQL.

Capacités de Bacula Enterprise en matière de sauvegarde et de restauration Postgres

Bacula est une solution de sauvegarde des données d’entreprise par abonnement qui aide les centres de données à gérer un volume élevé de données à un coût très faible. Les clients lui ont attribué des notes élevées, car il s’agit d’une solution de sauvegarde d’entreprise extrêmement personnalisable, aux performances solides et à l’excellente évolutivité.

Bacula offre plusieurs avantages par rapport à ses concurrents : Il peut fonctionner avec des environnements physiques, virtuels, conteneurs et Cloud, des snapshots, une déduplication avancée, des restaurations et une vérification des données, le tout sur une seule plateforme. Vous pouvez facilement utiliser PostgreSQL avec Bacula Enterprise.

Vous pouvez choisir entre deux techniques principales pour sauvegarder votre base de données Postgres avec Bacula Enterprise : PITR (point-in-time recovery) ou Dump. La configuration du plugin est simple.

Tout d’abord, vous devez spécifier la directive Plugin Directory dans la ressource FileDaemon du fichier bacula-fd.conf.

Pour configurer PITR, vous devrez activer l’archivage WAL afin d’utiliser la fonctionnalité Point In Time Recovery de PostgreSQL. La configuration varie légèrement selon la version de votre cluster (vous devrez soit configurer la archive_command et archive_mode, soit la archive_command, wal_level, et archive_mode). Vous devez également activer l’option Accurate dans votre ressource Job car le plugin utilise les informations du mode Accurate pour gérer les sauvegardes différentielles.

Le plugin peut effectuer les actions suivantes en mode PITR :

• Sauvegarde complète : Sauvegardez l’ensemble du répertoire de données et tous les fichiers WAL.
• Sauvegarde incrémentielle : Force le passage du WAL actuel et sauvegarde tous les fichiers WAL qui ont été générés depuis la dernière sauvegarde.
• Sauvegarde différentielle : Sauvegarde des fichiers de données qui ont été modifiés depuis la dernière sauvegarde complète et des fichiers WAL générés depuis la dernière sauvegarde.

Vous ne pourrez pas effectuer de sauvegardes incrémentielles ou différentielles lorsque vous utilisez le mode Dump, mais le plugin détectera toutes les bases de données de votre serveur.

Vous pouvez également restaurer les utilisateurs et les rôles dans votre cluster PostgreSQL (il suffit de sélectionner le fichier roles.sql), une seule base de données (sélectionnez le répertoire de la base de données dans la commande de restauration, qui doit contenir le fichier de données et le script de création de la base de données), ainsi que les fichiers Dump dans un répertoire, restaurer une seule table ou un cluster complet en utilisant PITR. Dans ce cas, vous devrez :

1. Arrêtez le serveur s’il est en cours d’exécution.
2. Copiez l’ensemble du répertoire de données du cluster et tous les tablespaces vers un emplacement temporaire (par précaution).
3. Nettoyez tous les fichiers et sous-répertoires existants.
4. Restaurez les fichiers de la base de données à partir de votre vidage de sauvegarde.
5. Supprimez les fichiers présents dans pg_xlog, copiez le fichier de segments WAL non archivés dans le fichier.
6. Modifiez le fichier de commande de récupération dans le répertoire de données du cluster et renommez-le en recovery.conf.
7. Démarrez le serveur.

Bacula utilise un service de connexion qui permet d’associer des paramètres PostgreSQL à un seul nom de service – qui peut être spécifié par une connexion PostgreSQL et ses paramètres associés. Vous pouvez tester l’accès à la base de données en utilisant la commande estimate pour vous assurer que le plugin est bien configuré.

Le plugin PostgreSQL est disponible sous forme de package Bacula Enterprise pour toutes les plateformes supportées et supporte les versions 8.4, 9.0, 9.1, 9.2, 9.3, 9.4 et 9.5 de PostgreSQL. Il vous suffit de l’installer sur le Client où réside votre cluster PostgreSQL.

Avec Bacula, vous pouvez sauvegarder et restaurer les bases de données PostgreSQL rapidement et sans avoir recours à des scripts complexes. Le logiciel prend en charge toutes les informations essentielles tout en supportant les techniques de sauvegarde et de restauration de type dump et Point in Time Recovery (ou PITR), y compris la sauvegarde incrémentale des bases de données PostgreSQL et la sauvegarde différentielle des bases de données PostgreSQL.

Commencer avec Bacula Enterprise est facile ; il suffit de remplir le formulaire et télécharger l’essai. Bacula possède des niveaux de sécurité très élevés, n’a pas de coût de volume de données, un modèle de licence par abonnement, et aucun verrouillage du fournisseur. L’entreprise inclut non seulement le meilleur logiciel de sauvegarde des données, mais aussi un support, des correctifs et des mises à jour.