PostgreSQL : Sauvegarder et restaurer ses bases de données
Table des matières
Dans cet article, on va voir comment sauvegarder et restaurer ses bases de données.
Pour manipuler les extractions et importations de ses bases de données PostgreSQL, on va utiliser des commandes spécifiques.
L'utilisation des mots "sauvegarde" et "restauration" sont un peu abusifs.
En effet, un dump de base de données est une représentation structurée des données et de la structure de la base de données dans un format spécifique, tel qu'un fichier texte ou binaire à un instant précis.
Il ne s'agit pas d'une copie exacte de l'état actuel de la base de données telle que la sauvegarde pourrait se définir.
C'est pour cela que le terme exact serait un export des données plutôt qu'une sauvegarde.
Cependant, c'est un bon début pour "sauvegarder" ses bases de données, et que cela convient pour la majorité des cas.
Evidemment, à l'inverse, on ne pourra pas parler de "restauration", mais d'un import.
J'utiliserai l'abus de langage "Sauvegarde" et "Restauration" pour ne pas vous perdre.
Pour sauvegarder sa base de données, on va utiliser la commande pg_dump !
Si vous voulez ajouter les instructions DROP TABLE avant chaque import de table, il faudra indiquer l'option -c (ce que je mettrai systématiquement). Sans cette option le dump ne contient que les instructions CREATE TABLE.
Pour sauvegarder une base de donnée précise, l'utilisateur doit avoir les pleins droits sur la base et toutes les tables (ou utiliser l'utilisateur postgres qui est l'admin) :
L'option -U permet de spécifier l'utilisateur qui va exécuter le dump.
Vous pouvez ajouter des variables comme bash le permet (exemple la date) :
Vous pouvez ne sauvegarder que certaines tables de la base de données si vous le souhaitez (ajoutez autant d'option --table que de tables à dumper) :
La sauvegarde étant des instructions SQL, c'est du texte. C'est donc parfois volumineux et facilement compressible !
Voici un exemple pour compresser à la volée en une seule commande :
Voici la partie restauration. On peut restaurer pour :
- Tester la sauvegarde en restaurant sur une autre base
- Restaurer car on a besoin de restaurer donc sur la même base
Quand on a sauvegardé la base, pour la restaurer, la base doit déjà exister (si c'est sur une nouvelle installation) (cf https://www.linuxtricks.fr/wiki/postgresql-memo-des-commandes-sql ) :
Pour restaurer un dump où on n'a sauvegardé que certaines tables, c'est la même manipulation. Seules les tables sauvegardées seront restaurées.
Pour restaurer une base sauvegardée gzippée :
Il est tout à fait possible de copier à chaud une base vers une autre en combinant psql et pg_sql.
Pratique pour créer une base de test à partir de la prod :
Introduction
Dans cet article, on va voir comment sauvegarder et restaurer ses bases de données.
Pour manipuler les extractions et importations de ses bases de données PostgreSQL, on va utiliser des commandes spécifiques.
A propos de la terminologie
L'utilisation des mots "sauvegarde" et "restauration" sont un peu abusifs.
En effet, un dump de base de données est une représentation structurée des données et de la structure de la base de données dans un format spécifique, tel qu'un fichier texte ou binaire à un instant précis.
Il ne s'agit pas d'une copie exacte de l'état actuel de la base de données telle que la sauvegarde pourrait se définir.
C'est pour cela que le terme exact serait un export des données plutôt qu'une sauvegarde.
Cependant, c'est un bon début pour "sauvegarder" ses bases de données, et que cela convient pour la majorité des cas.
Evidemment, à l'inverse, on ne pourra pas parler de "restauration", mais d'un import.
J'utiliserai l'abus de langage "Sauvegarde" et "Restauration" pour ne pas vous perdre.
Sauvegarde
Pour sauvegarder sa base de données, on va utiliser la commande pg_dump !
Si vous voulez ajouter les instructions DROP TABLE avant chaque import de table, il faudra indiquer l'option -c (ce que je mettrai systématiquement). Sans cette option le dump ne contient que les instructions CREATE TABLE.
Pour sauvegarder une base de donnée précise, l'utilisateur doit avoir les pleins droits sur la base et toutes les tables (ou utiliser l'utilisateur postgres qui est l'admin) :
Code BASH :
pg_dump -c -U postgres adrien_test > adrien_test.sql
L'option -U permet de spécifier l'utilisateur qui va exécuter le dump.
Vous pouvez ajouter des variables comme bash le permet (exemple la date) :
Code BASH :
pg_dump -c -U postgres adrien_test > adrien_test_$(date +%Y-%m-%d--%H-%M-%S).sql
Vous pouvez ne sauvegarder que certaines tables de la base de données si vous le souhaitez (ajoutez autant d'option --table que de tables à dumper) :
Code BASH :
pg_dump -c -U postgres adrien_test --table='public.table1' > adrien_test.sql
La sauvegarde étant des instructions SQL, c'est du texte. C'est donc parfois volumineux et facilement compressible !
Voici un exemple pour compresser à la volée en une seule commande :
Code BASH :
pg_dump -c -U postgres adrien_test | gzip -c > adrien_test.sql.gz
Restauration
Voici la partie restauration. On peut restaurer pour :
- Tester la sauvegarde en restaurant sur une autre base
- Restaurer car on a besoin de restaurer donc sur la même base
Quand on a sauvegardé la base, pour la restaurer, la base doit déjà exister (si c'est sur une nouvelle installation) (cf https://www.linuxtricks.fr/wiki/postgresql-memo-des-commandes-sql ) :
Code BASH :
psql -U postgres adrien_test < adrien_test.sql
Pour restaurer un dump où on n'a sauvegardé que certaines tables, c'est la même manipulation. Seules les tables sauvegardées seront restaurées.
Pour restaurer une base sauvegardée gzippée :
Code BASH :
gunzip -c adrien_test.sql.gz | psql -U postgres adrien_test
Copier à chaud une base à une autre
Il est tout à fait possible de copier à chaud une base vers une autre en combinant psql et pg_sql.
Pratique pour créer une base de test à partir de la prod :
Code BASH :
pg_dump -c -U postgres adrien_prod | psql -d adrien_test -U postgres