Comme avec tout ce qui contient des données importantes, les bases de données PostgreSQL™ doivent être sauvegardées régulièrement. Bien que la procédure soit plutôt simple, il est important de comprendre les techniques sous-jacentes ainsi que les hypothèses prises. Pour cela nous créer un fichier bacth pour automatiser les sauvegardes.Il y a trois approches fondamentalement différentes pour sauvegarder les données de PostgreSQL™ (la sauvegarde SQL, la sauvegarde de niveau système de fichiers et la sauvegarde à chaud (ou en ligne). Chacune a ses avantages et inconvénients.Pour des raisons pratiques, nous allons nous intéresser uniquement sur la sauvegarde en mode SQL.
Sauvegarde SQL
Le principe est de générer un fichier texte de commandes SQL (appelé « fichier .sql »), qui, si on le renvoie au serveur, recrée une base de données identique à celle sauvegardée. PostgreSQL™ propose pour cela le programme utilitaire. L’usage basique est :
pg_dump base_de_donnees > fichier_de_sortie
Comme vous le voyez, pg_dump écrit son résultat sur la sortie standard. Nous verrons plus loin que cela peut être pratique.
pg_dump est un programme client PostgreSQL™ classique (mais plutôt intelligent). Ceci veut dire que vous pouvez faire une sauvegarde depuis n’importe quel ordinateur ayant accès à la base. Mais rappelez-vous que pg_dump n’a pas de droits spéciaux. En particulier, il doit avoir accès en lecture à toutes les tables que vous voulez sauvegarder, si bien qu’il doit être lancé pratiquement toujours en tant que super-utilisateur de la base.
Pour préciser quel serveur de bases de données pg_dump doit contacter, utilisez les options de ligne de commande -h serveur et -p port. Le serveur par défaut est le serveur local ou celui indiqué par la variable d’environnement PGHOST. De la même façon, le port par défaut est indiqué par la variable d’environnement PGPORT ou, en son absence, par la valeur par défaut précisée à la compilation. Heureusement, le serveur et le client possèdent généralement la même valeur de compilation par défaut.
Exemple de dump
Créer la une base de données bd1 et bd2 bd3 dans PostgreSQL
"C:\Program Files\PostgreSQL\9.6\bin\pg_dump.exe" -F c -b -v -f F:\archive.sql "bd1" -F correspond au format de sortie c permet la sélection manuelle dur format de sortie. Ici en en .SQL. -b Inclut les objets larges dans la sauvegarde. -v en mode verbeux -f fichier de sortie.
Maintenant nous allons voir comment automatiser
Automatisation des sauvegardes
Nous allons créer un fichier. Batch pour exécuter la sauvegarde. Pour cela ouvrer un éditeur de texte comme Notepad puis copier le texte, ci-dessous, et enregistrer sous archive.bat. Pour finir exécuter le fichier pour sauvegarder votre base
REM Paramètres de connections SET PGPORT=5432 SET PGHOST=localhost SET PGUSER=admin SET PGPASSWORD=admin REM Sauvegarde de la Base de données "bd1" "C:\Program Files\PostgreSQL\9.6\bin\pg_dump.exe" -F c -b -v -f F:\archive.sql "bd1"
En exécutant cette commande vous allez uniquement sauvegarder la base de données bd1. Mais vous allez me dire comment je vais faire si j’ai plusieurs bases de données ? Ou alors comment les sauvegarder en gardant les mêmes noms des bases ? Mais aussi comment gérer l’historique de mes sauvegardes ? Ou alors comment savoir le script a bien marcher, combien de temps la sauvegarde a duré etc… ?
Pour ce faire, nous allons utiliser le même principe en mode batch en incluant des variables qui nous permettent de sélectionner un ou plusieurs bases, de les renommer en fonction de la date de création, et de générer un fichier log pour estimer le temps d’exécution de la sauvegarde
REM SAUVEGARDE AUTOMATIQUE DE BASES DE DONNEES POSTGRESQL
SetLocal EnableDelayedExpansion
ECHO OFF
SET time_d=%time%
REM Parametre de connections
SET PGPORT=5432
SET PGHOST=localhost
SET PGUSER=admin
SET PGPASSWORD=admin
REM Recherche de toutes les bases de données à sauvegarder dans la table pg_database. Le résultat est envoyé dans un fichier temporaire databases.txt
"C:\Program Files\PostgreSQL\9.6\bin\psql.exe" -Atc "SELECT datname FROM pg_database WHERE datallowconn IS TRUE AND datname IN('bd2','bd3' )" --username postgres > F:\Archives\databases.txt
REM Itération dans les bases de données à sauvegarder
for /f "tokens=*" %%a in (F:\Archives\databases.txt) do (
REM Sauvegarde de la base de données en cours d'itération (format custom, compressé)
"C:\Program Files\PostgreSQL\9.6\bin\pg_dump.exe" -C -Fc -U postgres %%a > F:\Archives\%%a-%date:/=-%.sql
)
REM Suppression du fichier catalogue des bases de données
DEL databases.txt
REM Calculer les temps
SET time_e=%time%
SET hour_d=%time_d:~0,2%
SET min_d=%time_d:~3,2%
SET sec_d=%time_d:~6,2%
SET hour_e=%time_e:~0,2%
SET min_e=%time_e:~3,2%
SET sec_e=%time_e:~6,2%
SET /a total_d=(%hour_d%*3600)+(%min_d%*60)+%sec_d%
SET /a total_e=(%hour_e%*3600)+(%min_e%*60)+%sec_e%
SET /a time_run=%total_e%-%total_d%
ECHO.
ECHO heure de debut de l'execution: %time_d%
ECHO heure de fin de l'execution: %time_e%
ECHO temps d'execution: %time_run% s
ECHO --------------------------------------------------------------->>Logtemp.txt
ECHO ----------- Sauvegarde du %date:/=-% -------------------------->>Logtemp.txt
ECHO Debut de l'execution: %time_d%>>Logtemp.txt
ECHO Fin de l'execution: %time_e%>>Logtemp.txt
ECHO Temps d'execution: %time_run% s>>Logtemp.txt
ECHO --------------------------------------------------------------->>Logtemp.txt
ECHO --------------------------------------------------------------->>Logtemp.txt
REM Ouvrir notepad pour afficher le Logtemp.txt
notepad Logtemp.txt
REM PAUSE>nul
Planification des sauvegardes
Vous l’avez compris, soit vous exécuter manuellement la sauvegarde ou vous planifier une tache de sauvegarde journalière, quotidienne, mensuelle …
Ouvrer le planificateur de tache sous Windows et paramétrer le a votre guise. Et ajouter le fichier batch.
Restauration des sauvegardes
Pour la restauration rien de plus simple. Même principe, créer un fichier batch comme ci-dessous et exécuter le.
REM Paramètres de connections SET PGPORT=5432 SET PGHOST=localhost SET PGUSER=admin SET PGPASSWORD=admin REM restauration de la Base de données "bd1" "C:\Program Files\PostgreSQL\9.4\bin\ pg_restore.exe" -d bd1 -f F:\Archives \bd1-30-08-2017.sql
Dump d’un schéma
Rien de plus simple. Si j’ai besoin de sauvegarder uniquement un schéma voici la recette
REM Parametre de connections SET PGPORT=5432 SET PGHOST=localhost SET PGUSER=admin "C:\Program Files\PostgreSQL\9.6\bin\pg_dump.exe" -F c -b -v F:\Archives \bd1-30-08-2017.sql -n "mon_schema" "bd1"