postgresql
Sauvegarde et restauration
Recherche…
Remarques
Sauvegarde du système de fichiers au lieu d'utiliser pg_dumpall et pg_dump
Il est très important que si vous utilisez ceci, vous appelez la fonction pg_start_backup() avant et la fonction pg_stop_backup() après. Faire des sauvegardes du système de fichiers n'est pas sûr autrement; même un instantané ZFS ou FreeBSD du système de fichiers sauvegardé sans ces appels de fonctions placera la base de données en mode de récupération et risque de perdre des transactions.
J'éviterais de faire des sauvegardes de systèmes de fichiers plutôt que des sauvegardes classiques de Postgres, à la fois pour cette raison et parce que les fichiers de sauvegarde Postgres (en particulier dans le format personnalisé) sont extrêmement polyvalents pour prendre en charge des restaurations alternatives. Comme ils sont des fichiers uniques, ils sont aussi moins compliqués à gérer.
Sauvegarde d'une base de données
pg_dump -Fc -f DATABASE.pgsql DATABASE
-Fc sélectionne le "format de sauvegarde personnalisé" qui vous donne plus de puissance que le SQL brut; voir pg_restore pour plus de détails. Si vous voulez un fichier SQL complet, vous pouvez le faire à la place:
pg_dump -f DATABASE.sql DATABASE
ou même
pg_dump DATABASE > DATABASE.sql
Restauration des sauvegardes
psql < backup.sql
Une alternative plus sûre utilise -1 pour envelopper la restauration dans une transaction. Le -f spécifie le nom du fichier plutôt que d'utiliser la redirection du shell.
psql -1f backup.sql
Les fichiers au format personnalisé doivent être restaurés à l'aide de pg_restore avec l'option -d pour spécifier la base de données:
pg_restore -d DATABASE DATABASE.pgsql
Le format personnalisé peut également être reconverti en SQL:
pg_restore backup.pgsql > backup.sql
L'utilisation du format personnalisé est recommandée car vous pouvez choisir les éléments à restaurer et éventuellement activer le traitement parallèle.
Vous devrez peut-être faire un pg_dump suivi d'un pg_restore si vous effectuez une mise à niveau d'une version postgresql à une version plus récente.
Sauvegarde de l'ensemble du cluster
$ pg_dumpall -f backup.sql
Cela fonctionne en arrière-plan en effectuant plusieurs connexions au serveur une fois pour chaque base de données et en exécutant pg_dump dessus.
Parfois, vous pourriez être tenté de définir cela comme une tâche cron, de sorte que vous voulez voir la date à laquelle la sauvegarde a été prise dans le cadre du nom de fichier:
$ postgres-backup-$(date +%Y-%m-%d).sql
Cependant, veuillez noter que cela pourrait produire des fichiers volumineux sur une base quotidienne. Postgresql dispose d'un mécanisme bien meilleur pour les sauvegardes régulières - archives WAL
La sortie de pg_dumpall est suffisante pour restaurer une instance Postgres à configuration identique, mais les fichiers de configuration dans $PGDATA ( pg_hba.conf et postgresql.conf ) ne font pas partie de la sauvegarde, vous devrez donc les sauvegarder séparément.
postgres=# SELECT pg_start_backup('my-backup');
postgres=# SELECT pg_stop_backup();
Pour effectuer une sauvegarde du système de fichiers, vous devez utiliser ces fonctions pour vous assurer que Postgres est dans un état cohérent pendant la préparation de la sauvegarde.
Utilisation de la copie pour importer
Pour copier des données d'un fichier CSV dans une table
COPY <tablename> FROM '<filename with path>';
Pour insérer dans l' user de la table à partir d'un fichier nommé user_data.csv placé dans /home/user/ :
COPY user FROM '/home/user/user_data.csv';
Pour copier des données d'un fichier séparé par des tuyaux vers une table
COPY user FROM '/home/user/user_data' WITH DELIMITER '|';
Note: En l'absence de l'option with delimiter , le délimiteur par défaut est la virgule ,
Pour ignorer la ligne d'en-tête lors de l'importation du fichier
Utilisez l'option En-tête:
COPY user FROM '/home/user/user_data' WITH DELIMITER '|' HEADER;
Remarque: si des données sont citées, par défaut, les guillemets sont des guillemets doubles. Si les données sont citées en utilisant un autre caractère, utilisez l'option QUOTE ; Cependant, cette option est autorisée uniquement lors de l'utilisation du format CSV.
Utiliser la copie pour exporter
Copier le tableau en standard o / p
COPY <nomtable> TO STDOUT (DELIMITER '|');
Pour exporter un utilisateur de table vers la sortie standard:
COPY user TO STDOUT (DELIMITER '|');
Pour copier une table dans un fichier
COPY utilisateur FROM '/ home / user / user_data' WITH DELIMITER '|';
Pour copier la sortie de l'instruction SQL dans un fichier
COPY (instruction sql) TO '<nomfichier avec chemin>';
COPY (SELECT * FROM utilisateur WHERE nom_utilisateur LIKE 'A%') TO '/ home / utilisateur / user_data';
Copier dans un fichier compressé
COPY user TO PROGRAM 'gzip> /home/user/user_data.gz';
Ici, le programme gzip est exécuté pour compresser les données de la table utilisateur.
Utiliser psql pour exporter des données
Les données peuvent être exportées à l'aide de la commande copy ou en utilisant les options de ligne de commande de la commande psql.
Pour exporter des données csv d'un utilisateur de table vers un fichier csv:
psql -p \<port> -U \<username> -d \<database> -A -F<delimiter> -c\<sql to execute> \> \<output filename with path>
psql -p 5432 -U postgres -d test_database -A -F, -c "select * from user" > /home/user/user_data.csv
Ici, la combinaison de -A et -F fait l'affaire.
-F est de spécifier le délimiteur
-A or --no-align
Passe au mode de sortie non aligné. (Le mode de sortie par défaut est aligné autrement.)