Astuces techniques : Comment sauvegarder et restaurer la base de données Postgres ?
·
19 juin 2013
Points Clés
Les sauvegardes PostgreSQL peuvent cibler une seule table, une seule base de données ou toutes les bases de données selon vos besoins.
pg_dumpcrée un fichier texte SQL contenant les DDL et les données nécessaires pour recréer la table ou la base de données cible.pg_dumpallsauvegarde chaque base de données sur l'instance Postgres.La restauration se fait avec
psql, qui exécute le dump SQL dans la base de données cible.Les restaurations au niveau de la table nécessitent de s'assurer que la table n'existe pas déjà pour éviter les conflits.
Lors de la sauvegarde de tables individuelles, soyez attentif aux dépendances relationnelles.
Les dumps peuvent être effectués à partir de Postgres local et restaurés sur des instances distantes.
Les sauvegardes incluent les commandes
CREATE,ALTER, etCOPYen fonction de ce qui est exporté.Une stratégie de sauvegarde appropriée dépend de la compréhension des relations de tables, de l'utilisation du schéma et des environnements de déploiement.
Assurez toujours l'intégrité des données en effectuant des sauvegardes qui incluent les tables référencées lorsque cela est nécessaire.
Points forts des Q&A
Que fait réellement pg_dump ?
pg_dumpgénère un fichier SQL basé sur du texte contenant l'ensemble complet des commandes nécessaires pour recréer une table ou une base de données exactement comme elle existait au moment de la sauvegarde.Puis-je sauvegarder juste une table au lieu d'une base de données complète ?
Oui. Utilisez
pg_dump --table TABLENAMEpour exporter une table spécifique. Si des tables portant le même nom existent dans différents schémas, utilisez--schemapour spécifier le bon.Devrais-je m'inquiéter des tables associées lors de la sauvegarde d'une seule table ?
Absolument. PostgreSQL est relationnel, donc d'autres tables peuvent faire référence à celle que vous sauvegardez. Si vous restaurez une seule table sans celles qui lui sont liées, vous risquez de briser l'intégrité référentielle.
Comment sauvegarder une base de données complète ?
Exécuter une commande comme :
pg_dump -U <user> <database> -f <output.sql>Cela exporte toutes les tables, définitions de schéma, index et données pour cette base de données.
Que fait pg_dumpall ?
pg_dumpallsauvegarde chaque base de données sur le serveur dans un seul fichier SQL. C’est utile lors de la migration ou de la création d'un instantané d'une instance Postgres entière.Puis-je restaurer une table dans une base de données existante ?
Oui. Utilisez
psql -U <user> -f <file.sql> <database>.Mais assurez-vous que la table n'existe pas déjà — sinon vous obtiendrez des erreurs "existe déjà" lors de la restauration.
Que se passe-t-il lors d'une restauration complète de la base de données ?
Le fichier de restauration s'exécute :
CREATE TABLEALTER TABLECOPY(pour les données de la table)
rétablissant la base de données à son état exact au moment du dump.
Puis-je restaurer sur un serveur différent de celui à partir duquel j'ai effectué la sauvegarde ?
Oui. Les dumps sont portables. Vous pouvez effectuer une sauvegarde localement et la restaurer sur une instance Postgres distante, à condition que les versions soient compatibles.
Est-ce que pg_dump affecte la base de données en cours d'exécution ?
Non. Cela fonctionne via des requêtes SQL et ne verrouille pas l'ensemble de la base de données. Il capture en toute sécurité une image instantanée cohérente.
Comment restaurer toutes les bases de données à partir d'un fichier pg_dumpall ?
Utilisation :
psql -U <user> -f all_dump.sqlCeci recrée chaque base de données trouvée dans le dump.
Tout d'abord, une mise en garde. Cet article fournit un guide général sur la façon de sauvegarder une table de base de données Postgres. Les exemples fournis ne se réfèrent à aucune table ou base de données spécifique. Si vous essayez de sauvegarder une table spécifique, vous devriez également sauvegarder les tables qui se réfèrent aux tables d'origine. Comme Postgres est une base de données relationnelle, il y aura toujours des références entre plusieurs tables.
Ceci est un document 'comment faire' qui ne fournit que des exemples de commandes, comment cela est réellement fait dépend uniquement de la personne qui le fait et des cas d'utilisation spécifiques. Toujours maintenir l'intégrité des données que vous essayez de sauvegarder. Sauvegarder la base de données entière est toujours une option sûre, mais lorsqu'on sauvegarde des tables spécifiques, il faut être prudent.
SQL-dump/pg_dump:
L'idée derrière la méthode SQL-dump est de générer un fichier texte avec des commandes SQL qui, une fois réintroduites dans le serveur, recréeront la base de données dans le même état qu'elle était au moment du dump. PostgreSQL fournit le programme utilitaire pg_dump à cette fin.
pg_dump est un outil efficace et complet pour effectuer des sauvegardes de bases de données Postgres et utiliser ces sauvegardes pour restaurer la base de données Postgres. Cependant, il n'est pas limité uniquement à la base de données. Nous pouvons utiliser pg_dump pour sauvegarder des tables et ensuite les utiliser pour restaurer des tables individuelles également. En utilisant pg_dump, vous pouvez sauvegarder la base de données locale et la restaurer sur une base de données distante.
Comment sauvegarder une base de données Postgres :
Comment restaurer Postgres Database
Postgres Restore Options Vue d'ensemble
Portée de la restauration | Outil utilisé | Fichier d'entrée | Exigence clé | Erreur courante |
|---|---|---|---|---|
Table unique | psql | Dump de table | La table ne doit pas déjà exister | Erreurs « existe déjà » |
Base de données unique | psql | Dump de base de données | La base de données cible doit exister | Écraser des données involontaires |
Toutes les bases de données | psql | Dump complet | Accès superutilisateur recommandé | Restaurer plus que prévu |
Restaurer une table Postgres :
$ /opt/msys/3rdParty/bin/psql -U ecuser -f ms_table.sql peLa commande ci-dessus installera la table qui a été sauvegardée dans le fichier ms_table.sql dans la base de données 'pe'. Assurez-vous que cette table n'existe pas déjà, sinon vous verrez une série d'erreurs « existe déjà ». Cette commande crée la table et exporte toutes les données vers la table nouvellement créée.
Restaurer une base de données Postgres :
$ /opt/msys/3rdParty/bin/psql -U ecuser -d pe -f pe_dump.sqlSemblable à la restauration de la table, nous pouvons utiliser la commande ci-dessus pour restaurer la base de données complète. Ici, nous restaurons la base de données 'pe' en utilisant le fichier pe_dump.sql que nous avions créé lors de la sauvegarde de la base de données dans la section Sauvegarde ci-dessus.
Restaurer toutes les bases de données :
$ /opt/msys/3rdParty/bin/psql -u ecuser -f all_dump.sqlRestaurer toutes les bases de données en utilisant la commande ci-dessus. 'all_dump.sql' était le fichier qui a été créé en utilisant
pg_dumpall. La commande ci-dessus nous donnera toutes les bases de données Postgres dans l'état exact où elles étaient lors de la prise du dump depuis le serveur de base de données original.
