#Automatisation des sauvegardes et restaurations de Bases de données #PostgreSQL

postgresql-backup

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.

planification2planification1

 

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"

 

 

 

Publicités

Laisser un commentaire

Entrez vos coordonnées ci-dessous ou cliquez sur une icône pour vous connecter:

Logo WordPress.com

Vous commentez à l'aide de votre compte WordPress.com. Déconnexion / Changer )

Image Twitter

Vous commentez à l'aide de votre compte Twitter. Déconnexion / Changer )

Photo Facebook

Vous commentez à l'aide de votre compte Facebook. Déconnexion / Changer )

Photo Google+

Vous commentez à l'aide de votre compte Google+. Déconnexion / Changer )

Connexion à %s