Comment sauvegarder et restaurer avec PostgreSQL : Apprenez PostgreSQL avec Grant - Simple Talk (2023)

Série à ce jour :

1. Connectez-vous à PostgreSQL : découvrez PostgreSQL avec Grant
2. Créer des bases de données et des tables dans PostgreSQL : Apprenez PostgreSQL avec Grant
3. Comment sauvegarder et restaurer avec PostgreSQL : Apprendre PostgreSQL avec Grant
4. Types de données PostgreSQL : découvrez PostgreSQL avec Grant

L'une des premières préoccupations lors de l'examen d'un nouveau système est l'état des sauvegardes. Je ne vois rien dans PostgreSQL qui me mènerait à une conclusion différente. Mais je n'avais pas réalisé à quel point j'ouvrais une boîte de bogues dans la poursuite de la sauvegarde et de la restauration.

Au lieu de parler tout de suite de sauvegarde, parlons de restauration.

Stratégie de restauration

Autant que je sache, il y a un adage dont il est issu.Kimberly Tripp: La qualité de la sauvegarde est aussi bonne que la dernière restauration. En d'autres termes, vous avez besoin d'une sauvegarde, mais l'important est de pouvoir restaurer vos données. Vous pouvez créer un nombre infini de sauvegardes, mais si vous ne pouvez pas en restaurer ne serait-ce qu'une seule, c'est juste une perte d'espace. Par conséquent, avant de commencer à penser aux sauvegardes, vous devez réfléchir à la manière dont vous allez restaurer votre système.

La restauration de base de données est un sujet important. Cet article pourrait couvrir toutes sortes de permutations de différentes méthodes de restauration d'une base de données. Cependant, afin de garder la taille de cet article raisonnable, nous nous concentrerons sur un mécanisme de restauration, la restauration à partir d'une sauvegarde, car nous n'avons pas encore appris tout ce que nous devons savoir. Il est possible de restaurer à un moment précis, mais je garderai les détails sur la façon de le faire pour un prochain article.

Une simple restauration à partir d'une sauvegarde vise à remettre la base de données dans l'état où elle se trouvait lors de la création de la sauvegarde. Ce type d'opération de restauration est acceptable sur les systèmes de développement et de test. Il s'agit d'un moyen simple de migrer rapidement votre base de données vers un nouveau serveur. Cependant, il existe une limitation selon laquelle vous ne pouvez restaurer que jusqu'au point de sauvegarde. Si des données ou des modifications structurelles se produisent après la sauvegarde et que vous souhaitez les restaurer, ce n'est pas possible.

La restauration ponctuelle peut aider ici. Généralement, la plupart des organisations sont préoccupées par la perte de données. Par exemple, si vous créez une sauvegarde une fois par jour et que quelque chose ne va pas environ 12 heures plus tard dans la journée, vous perdrez toutes ces données. La restauration à un instant donné signifie être capable de récupérer la dernière sauvegarde et toutes les modifications apportées à la base de données depuis cette sauvegarde. La récupération ponctuelle est effectuée via un journal d'écriture anticipée (WAL) maintenu par PostgreSQL.

WAL est automatiquement créé et maintenu par le serveur. Vous pouvez configurer la taille et le nombre de fichiers dans WAL. Théoriquement, un nombre infini de fichiers WAL peuvent être lus avant d'atteindre un certain point. Cependant, cela introduit un autre aspect de la stratégie de restauration : le temps de récupération.

Vous devez élaborer une stratégie de récupération qui définit comment la récupération sera effectuée et combien de temps cela prendra. Les systèmes de développement nécessitent généralement des sauvegardes de base de données faciles. Vous avez terminé. Étant donné que les systèmes de production nécessitent des restaurations ponctuelles, vous devrez peut-être sauvegarder les bases de données qui nécessitent une gestion de fichiers WAL.

Une fois que vous avez défini vos besoins, vous pouvez choisir le bon outil de sauvegarde.

Sauvegarde de la base de données

Si vous n'êtes intéressé que par une simple restauration de base de données, les meilleurs outils pour le travail sont :pg_dump. La syntaxe de base est simple.

Cependant, cette syntaxe simple ne fonctionnera pas car vous exécutez votre base de données dans un conteneur ou sur le cloud, et votre service s'exécute sous une connexion différente de celle que vous utilisez pour vous connecter à distance. Alternativement, vous pouvez modifier légèrement la commande.

Cette commande fonctionne car vous spécifiez un utilisateur pour exécuter le service en tant que.

Si vous souhaitez exécuter PostgreSQL dans Docker, vous devrez ouvrir une commande shell dans votre conteneur comme suit : PostgreHome est le nom du conteneur.

Après cela, l'exécution de la syntaxe d'origine fonctionne très bien et vous trouverez le fichier de sauvegarde dans le répertoire à partir duquel vous exécutez la commande, sauf si vous spécifiez un chemin.

Lorsque j'exécute une commande avec un conteneur à partir d'une ligne de commande externe, l'utilisateur est invité à entrer un mot de passe. Mais une fois que vous l'avez nourri, cela fonctionne à nouveau.

Vous pouvez exercer plus de contrôle en définissant diverses options.pg_dumpNous couvrirons certains d'entre eux plus tard. Cependant, une option à envisager immédiatement, en particulier lorsque vous réfléchissez à la manière de restaurer une base de données, consiste à modifier la sortie. Par défaut, pg_dump est dirigé vers des fichiers SQL bruts.Pg_dumpVous pouvez également en faire un format de fichier numérique compressé en ajoutant un fichier .-FCIl s'agit d'un format personnalisé :

Vous pouvez sauvegarder votre base de données en utilisant l'une de ces deux méthodes. Parlons maintenant de l'exécution d'une restauration.

récupération de base de données

Sortie par défautpg_dumpLes commandes sont intéressantes. Cependant, cela devient très long et devient long très rapidement. J'ai une base de données très simple créée à partir d'autres scripts de cette série jusqu'à présent. Ci-dessous une partie de la sortiepg_dumpformat par défaut qui peut être affiché à l'aideChat:

Cela continuera à partir de là. en bref,pg_dumpScriptez la base de données, la structure et les données dans un fichier. Il s'agit d'un fichier SQL qui peut être exécuté à partir de psql ou d'autres outils. Récupérez les objets et les données créés à l'aide de SQL à partir du processus pg_dump. Ce fichier contient des données et peut être très volumineux selon la base de données.

La récupération consiste simplement à réexécuter le script, c'est-à-dire à exécuter simplement le SQL. Vous pouvez l'ouvrir dans l'interface graphique de votre choix ou utiliser l'utilitaire de ligne de commande comme suit :

Bien sûr, vous devez vous assurer que la base de données est vide. Sinon, les données déjà existantes seront reconstruites et réinsérées. Vous pouvez soit supprimer la base de données existante et créer une nouvelle base de données avant d'exécuter le script, soit supprimer tous les objets de la base de données avant d'exécuter le script.

Si vous choisissez d'obtenir des formats de fichiers personnalisés, vous aurez besoin de nouvelles commandes pour les lire car il n'y a pas d'ensemble simple de scripts SQL.pg_restore。

Commande de base à utiliserpg_restoreAussi simple que pg_dump.

Les mêmes règles s'appliquent. La base de données doit être soit une nouvelle base de données, soit une base de données nettoyée, sauf si vous profitez des fonctionnalités supplémentaires décrites lors de l'utilisation de formats de fichiers personnalisés. Par exemple, vous pouvez restaurer vers une base de données où tous les objets restent à l'intérieur. Vous pouvez choisir de supprimer d'abord les objets existants en remplaçant la commande par :

commande-c, Aussi- beau, supprimez d'abord l'objet, mais si vous ajoutez un nouvel objet après la création de la sauvegarde, vous risquez d'obtenir un message d'erreur. Vous pouvez également direpg_restorePour créer d'abord la base de données :

de-C, Aussi- créer, La commande essaiera de créer la base de données postgrelearning. S'il existe, vous obtiendrez une erreur et vous pourrez le modifier à nouveau.pg_restoreInstructions:

courir les deux- beauet- créerpar conséquentpg_restoreSupprimez et recréez la base de données. N'oubliez pas de vous connecter d'abord à une autre base de données.-dSélectionnez l'option pour utiliser le nom de la base de données du fichier de vidage dans la restauration.

Lors de l'exécution d'un script SQL de base,pg_dumpLes commandes sont simples, mais ne vous donnent pas beaucoup de contrôle. Vous constaterez que vous disposez de plusieurs options lorsque vous travaillez avec des formats de fichiers personnalisés.pg_restore. utiliserpg_restoretu peux:

• Restaurer uniquement les données, pas le schéma
• Restaurer le schéma uniquement, pas de données
• Imprimer le contenu du fichier de vidage sous forme de liste d'objets.
• Définir une liste spécifique d'objets à restaurer
• désactiver le déclencheur
• Utilisez plusieurs tâches simultanées pour accélérer l'exécution de la restauration.
• Sélectionnez des objets individuels à restaurer

De nombreux autres comportements peuvent être contrôléspg_restoreCe n'est pas le cas des articles d'introduction. Mais en résumé, ce qui suit vous donne plus de contrôle sur ce qui est restauré.pg_restoreUtilisez un format de vidage personnalisé au lieu du SQL standard.

Option pg_dump

Nous vous avons déjà montré une option disponiblepg_dump, un format de fichier personnalisé. Quelques options supplémentaires vous permettent de contrôler ce qui est sauvegardé et comment.

Les options sont définies sur la ligne de commande comme indiqué précédemment. Vous pouvez utiliser des abréviations comme-unCela signifie ne vider que les données d'une base de données spécifique. Alternativement, vous pouvez utiliser l'option complète.--data_only. Certaines options sont complètes, mais certaines nécessitent des informations supplémentaires.

Par exemple, vous pouvez spécifier qu'un vidage est prêt à nettoyer la base de données dans le cadre de la restauration.

Maintenant, lorsque je lance le script, il contient:baissecommande.

Vous pouvez utiliser des modèles d'inclusion ou d'exclusion pour déplacer uniquement certains objets dans la base de données. Par exemple, pour exclure les schémas commençant par la chaîneDéveloppeurEn eux, vous pouvez faire ceci:

Il existe également des commandes qui vous permettent de :

• Schéma de sauvegarde uniquement sans données
• Sauvegarde uniquement les données, sans définitions de schéma.
• La définition de plusieurs tâches simultanées accélérera l'exécution de la sauvegarde.
• Ajoutez des commandes conditionnelles.S'il y a, faire le ménage

Il y en a beaucoup d'autres.

Comment sauvegarder toutes les bases de données

Il est très probable que vous ayez plusieurs bases de données. peut scripterpg_dumpVous devez donc l'exécuter plusieurs fois, en spécifiant la base de données à sauvegarder et la destination de la sauvegarde. Si vous disposez d'un processus de sauvegarde hautement personnalisé et propre à chaque base de données, il s'agit probablement de la meilleure option. Mais vous avez également la possibilité d'utiliserpg_dumpall. Exporte toutes les bases de données sur le serveur spécifié dans un seul fichier au lieu d'exporter une base de données à la fois. depg_dumpallLa commande est fondamentalement juste un wrapperpg_dump. Cependant, puisque toutes les bases de données sont vidées,pg_dumpallIl collecte également les objets dépendant de la base de données dans les tables système.

Problèmespg_dumpallLa seule différence est que la sortie est uniquement au format SQL standard. Vous ne pouvez pas obtenir un format personnalisé compressé. Cela signifie également que toutes les fonctionnalités ne sont pas disponiblespg_restore. Comme auparavant, cela signifie que vous ne pouvez restaurer que vers une base de données vide.

Autres mécanismes de sauvegarde

Comme avec la plupart des principaux SGBDR, des solutions tierces existent pour PostgreSQL. En outre, si vous exécutez AWS RDS, AWS Aurora, Azure Database pour PostgreSQL ou GCP SQL Database pour PostgreSQL, ils disposent tous de mécanismes de sauvegarde et de restauration intégrés. Vous pouvez également sauvegarder et restaurer à partir de PGAdmin4 et restaurer à partir d'Azure Data Studio. De plus, plusieurs solutions scriptées sont disponibles.Communauté PostgreSQL. Nous vous recommandons vivement d'apprendre à sauvegarder et à restaurer votre base de données à l'aide d'outils natifs. Cependant, une fois que vous avez développé vos compétences, vous pouvez passer à une autre solution.

en conclusion

La possibilité de restaurer une base de données PostgreSQL est importante. Obtenir une sauvegarde rapide est très facilepg_dumpC'est une commande et excellent pour le développement. Cette commande crée un fichier de script qui peut être lu par défaut. Il existe également d'autres options.-FCPour créer une sauvegarde dans un format personnalisé pouvant être utilisé avecpg_restoreinstructions. depg_dumpCette commande offre plusieurs options, notamment la possibilité de supprimer et de créer des bases de données. Si vous devez sauvegarder toutes les bases de données,pg_dumpallCependant, il n'est pas très flexible.

Les bases de données de production doivent généralement pouvoir être restaurées à un moment donné, avoir des sauvegardes cryptées et ont souvent d'autres exigences qui nécessitent l'utilisation d'autres options telles que WAL ou des outils tiers. L'explication de la restauration ponctuelle est intentionnellement réservée à un article séparé, car elle est beaucoup plus complexe que les autres articles.pg_dump。