Backup de toutes les bases de données à chaque nuit sous Windows

charleydc5 · 14/06/2011 18:20:13

Bonjour à tous,

Je suis présentement en train de migrer de MSSQL et DB2 vers PostgreSQL. Dans MSSQL et DB2 j'avais programmé un mécanisme qui permettait de faire un backup de toutes les bases de données dans chaque instance dans un répertoire spécifique à MSSQL et DB2.

J'aimerais reproduire la même chose avec PostgreSQL. J'ai lu à propos de la commande pg_dumpall, mais je n'ai pas trouvé d'information quant à l'intégration de cette commande dans un fichier batch qui s'exécuterait dans Windows.

Voici mes spécifications:
- Windows 2008 Server R2
- PostgreSQL 9.0.3 x64

Note: pgAgent n'est pas une option pour moi. Chaque logiciel que j'utilise doit être certifié nationalement, ce qui implique des délais et des coûts de certification. Un traitement batch serait préférable.

Merci pour votre aide

Charles M.

gleu · 14/06/2011 18:37:12

Il est tout à fait possible d'intégrer la commande pg_dumpall dans un batch. Cependant, je ne vous conseille pas pg_dumpall pour sauvegarde vos bases. Il est très bien pour les données globales comme les utilisateurs et les tablespaces. Par contre, pour chaque base, il est préférable d'utiliser pg_dump, de préférence avec le format de sortie tar ou custom.

charleydc5 · 14/06/2011 18:50:00

Merci pour les recommandations.

Idéalement j'aimerais utiliser une tâche planifiée de Windows qui exécuterait un batch file ou un script PowerShell.

gleu · 14/06/2011 20:51:21

Tout à fait possible de le faire avec une tâche planifiée sous Windows. En fait, sous Windows, je ne vois pas d'autres moyens de le faire.

damalaan · 15/06/2011 12:03:36

voilà comment je procède : un .bat avec le planificateur de tache (toutes les 4h dans mon cas)

"C:\PostgreSQL\bin\pg_dump.exe" --host localhost --port 5432 --username postgres --file "C:\Sauvegarde\pg_dump_indext.sql" Indext

et pour la restauration :

"C:\PostgreSQL\bin\psql.exe" --host localhost --port 5432 --usernanme postgres --file "C:\Sauvegarde\pg_dump_indext.sql" Indext

charleydc5 · 15/06/2011 19:04:18

Salut damalaan,

Merci pour l'info.

Est-ce que tu aurais une idée de comment faire pour faire le backup de chaque base de données (autre que template0, template1 et postgres)?

J'aimerais bien voir ton fichier pg_dump_indext.sql si tu le veux bien.

Merci beaucoup!

Dernière modification par charleydc5 (15/06/2011 19:05:20)

gleu · 15/06/2011 19:51:28

Le fichier pg_dump_indext.sql correspond au résultat de sa sauvegarde.

charleydc5 · 15/06/2011 20:17:58

gleu a écrit :

Le fichier pg_dump_indext.sql correspond au résultat de sa sauvegarde.

Donc si j'ai bien compris sa commande ne fait le traitement que sur une seule base de données.

meles · 15/06/2011 20:24:08

Bonsoir, dans le cas de Damalaan, "Indext" est le nom de la base à sauvegarder.

Donc, pour les bases "foo", "bar" et "truc", tu pourrais lancer les 3 commandes suivantes :

pg_dump -U postgres -f foo_save.sql foo
pg_dump -U postgres -f bar_save.sql bar
pg_dump -U postgres -f truc_save.sql truc

Je n'ai pas mis le port et l'host puisque par defaut à localhost et 5432.

Par contre, n'est ce pas super dangereux de réécrire une sauvegarde avec le même nom de fichiers que la précédente ?
Si des données sont effacées en masse (drop table, truncate, je ne sais quoi d'autres), on risque de flinguer la dernière sauvegarde valable, non ?

Sous linux, je ferais un truc du genre :

pg_dump -U postgres -f foo-`date +%Y%m%d%H%M`.sql foo

mais sous windows ?

Cordialement

Dernière modification par meles (15/06/2011 20:36:27)

charleydc5 · 15/06/2011 20:32:34

meles a écrit :

Bonsoir, dans le cas de Damalaan, "Indext" est le nom de la base à sauvegarder.
Donc, pour les bases "foo", "bar" et "truc", tu pourrais lancer les 3 commandes suivantes :
pg_dump -U postgres -f foo_save.sql foo
pg_dump -U postgres -f bar_save.sql bar
pg_dump -U postgres -f truc_save.sql truc
Je n'ai pas mis le port et l'host puisque par defaut à localhost et 5432.
Cordialement

Oui je comprends très bien le fonctionnement. Par contre, j'aimerais rendre le tout dynamique, de sorte que si j'ajoute 5 bases de données demain, que le script puisse itérer dans toutes les bases de données du catalogue de PostgreSQL et ainsi faire la commande pg_dump pour chaque base de données.

J'utilise ce genre de méthode avec MSSQL et DB2 et cela ne requiert aucune maintenance de notre part. C'est vraiment génial!

Il est évident qu'il est possible de le faire avec PostgreSQL en batch. Il faut juste que je trouve comment :) Je suis encore un newbie avec PostgreSQL... :\

Merci à tous pour votre aide

meles · 15/06/2011 20:38:32

charleydc5 a écrit :

Oui je comprends très bien le fonctionnement. Par contre, j'aimerais rendre le tout dynamique, de sorte que si j'ajoute 5 bases de données demain, que le script puisse itérer dans toutes les bases de données du catalogue de PostgreSQL et ainsi faire la commande pg_dump pour chaque base de données.
J'utilise ce genre de méthode avec MSSQL et DB2 et cela ne requiert aucune maintenance de notre part. C'est vraiment génial!

HINT : psql -l (L minuscule)

je saurais faire en Linux, mais sous windows, je passe !

Cordialement

Dernière modification par meles (15/06/2011 20:39:00)

gleu · 15/06/2011 21:10:19

Il suffit d'exécuter la commande suivante :

psql -Atc "SELECT datname FROM pg_database WHERE datallowconn IS TRUE" postgres

Ensuite on récupère le résultat ligne par ligne pour exécuter pg_dump. Sous Linux, ça ressemble à ça :

psql -Atc "SELECT datname FROM pg_database WHERE datallowconn IS TRUE" postgres | while read base
do
  pg_dump $base > ${base}.sql
done

Évidemment, c'est très brut. Il vaut mieux que le nom du fichier change d'après la date. Il vaut mieux faire une sauvegarde dans un format binaire. Etc. Mais l'idée est là.

Reste plus qu'à le convertir en batch windows. Bon courage

damalaan · 16/06/2011 06:54:35

En fait qd je dis que je sauvegarde toutes les 4H, je garde l'historique sur une semaine, et c'est dynamique, càd qu'en fonction du jour et de l'heure, les sauvegardes vont dans un dossier créé à l'avance.
Je me suis fais des dossiers : lundi_8 (pour lundi 8h), lundi_12, lundi_16 et lundi_20, ainsi de suite jusqu'à dimanche
voici mon script, c'est un truc que j'ai longtemps cheché sur le net avant de le trouver!

REM -------- MEMORISATION ET DECOMPOSITON DE L'HEURE DE DEBUT ---------
set debut=%TIME%
set /a hd=%TIME:~0,2%*3600 
set /a md=%TIME:~3,2%*60 
set Sd=%TIME:~6,5%
set /a s1=hd+md+sd

REM s1= heure de début convertie en secondes

REM -------- MEMORISATION ET DECOMPOSITON DE LA DATE DU JOUR ---------
set /a J=%date:~0,2%
set /a M=%date:~3,2%
set /a A=%date:~6,2%

::Calcul du jour julien de la date saisie
set /a N=(1461 * (%A% + 4800 + (%M% - 14) / 12)) / 4 + (367 * (%M% - 2 - 12 * ((%M% - 14) / 12))) / 12 - (3 * ((%A% + 4900 + (%M% - 14) / 12) / 100)) / 4 + %J% - 32075
set N1=%N%

::Calcul numero du jour de la date saisie [Lundi = 0 ... Dimanche = 6]
set /a N%%=7
::Affectation Jours
REM -------- MEMORISATION ET DECOMPOSITON DE LA DATE DU JOUR ---------

echo %N%-
if %N%==0 set JOUR=LUNDI
if %N%==1 set JOUR=MARDI
if %N%==2 set JOUR=MERCREDI
if %N%==3 set JOUR=JEUDI
if %N%==4 set JOUR=VENDREDI
if %N%==5 set JOUR=SAMEDI
if %N%==6 set JOUR=DIMANCHE

set /a h=%time:~0,2%
REM j = jour de la sauvegarde
REM m= mois de la sauvegarde
REM a= année de la sauvegarde

REM -------- PROGRAMME DE SAUVEGARDE ---------


ECHO - SAUVEGARDE DES DONNEES VERS C:\Sauvegarde

set chemin=C^:^\Sauvegarde^\%JOUR%_%h%^\pg_dump_indext.sql
"C:\PostgreSQL\bin\pg_dump.exe" --host localhost --port 5432 --username postgres --file %chemin% Indext

à adapter avec la solution de gleu pour toutes les bases du serveur

bon courage

meles · 16/06/2011 07:25:48

Bonjour,
Et dire que c'est si simple sous linux !

Cela dit, je garde le script sous le coude, ça peut resservir :-)

Cordialement

SQLpro · 16/06/2011 13:53:58

Ce serait effectivement beaucoup plus simple en PowerShell !

A +

charleydc5 · 16/06/2011 14:35:56

Effectivement, sauf que je n'ai pas trouvé beaucoup de documentation sur Powershell vs PostgreSQL...

Je suis à quelques minutes de réussir à faire marcher via batch. Dès que j'ai la solution prête je vous envois ça pour votre connaissance personnelle

Merci à tous, c'est grandement apprécié

Charles

charleydc5 · 16/06/2011 15:59:04

Le seul problème auquel je fais face est que postgresql me demande toujours un mot de passe pour chaque base de données. J'ai ajouté la ligne suivante dans pg_hba.conf et je continue de me faire demander le mot de passe :
host all postgres 127.0.0.1/32 trust

Des idées?

Merci

EDIT: J'ai également redémarré le service de PostgreSQL.

Dernière modification par charleydc5 (16/06/2011 16:07:41)

gleu · 16/06/2011 16:19:31

Utiliser un fichier pgpass me paraît une bien meilleure idée : http://docs.postgresqlfr.org/9.0/libpq-pgpass.html

charleydc5 · 16/06/2011 16:41:41

gleu a écrit :

Utiliser un fichier pgpass me paraît une bien meilleure idée : http://docs.postgresqlfr.org/9.0/libpq-pgpass.html

Super! Merci infiniment

Je vous reviens avec le script final dès que possible.

charleydc5 · 16/06/2011 18:28:19

Est-ce que c'est une bonne pratique de laisser le mot de passe en texte clair dans le fichier pgpass.conf? Y a-t-il un moyen d'encrypter ce mot de passe (ex: md5)?

J'ai sécurisé le fichier au maximum, mais je dois avouer que j'aimerais beaucoup mieux être en mesure de cacher complètement le mot de passe.

Merci!

Charles

gleu · 16/06/2011 18:47:35

Il doit être en clair dans le fichier. Il sera envoyé chiffré sur le réseau si la méthode d'authentification le demande. Il ne faut autoriser la lecture du fichier que par son propriétaire. Peut-être plus difficile à assurer sous Windows que sous Linux, je ne sais pas. Sous Linux, ce fichier ne sera pas utilisé s'il est lisible par d'autres personnes que le propriétaire. Sous Windows, cette vérification n'est pas faite.

charleydc5 · 16/06/2011 20:40:25

De toute façon, c'est utilisé dans un Intranet donc c'est moins alarmant. Les serveurs sont très bien sécurisés donc on va laisser ça comme ça.

Merci beaucoup pour votre aide.

Voici le batch file en question:

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
"D:\PostgreSQL\9.0\bin\psql.exe" -Atc "SELECT datname FROM pg_database WHERE datallowconn IS TRUE AND datname NOT IN('template0', 'template1')" --username postgres > D:\databases.txt

REM  Itération dans les bases de données à sauvegarder
for /f "tokens=*" %%a in (D:\databases.txt) do (

	REM  Sauvegarde de la base de données en cours d'itération (format custom, compressé)
	"D:\PostgreSQL\9.0\bin\pg_dump.exe" -C -Fc -U postgres %%a > %%a-%date:/=-%.dump

	REM  Compression du fichier .dump dans un fichier .zip (compression maximale)
	"C:\Program Files (x86)\WinZip\WINZIP32.EXE" -min -a -ex E:\PostgreSQL\9.0\BACKUP\SCHEDULED\%%a-%date:/=-%.zip %%a-%date:/=-%.dump

	REM  Suppression de l'archive dump une fois archivée
	DEL %%a-%date:/=-%.dump
)

REM  Suppression du fichier catalogue des bases de données
DEL databases.txt

En espérant que ça pourra aider quelqu'un

Merci encore

Charles

Dernière modification par charleydc5 (16/06/2011 20:41:06)

charleydc5 · 29/06/2011 19:42:51

Re-bonjour à tous,

Comme l'indique ma réponse ci-dessus, mon script est fonctionnel. Par contre, lorsque j'essaie de le céduler dans le planificateur de tâches de Windows 2008 Server, je n'arrive pas à rouler le batch file (run as) sous le compte service "postgres". Lorsque je la roule avec mon compte, ça fonctionne parfaitement.

Je n'ai pas de message d'erreur lorsque j'essaie de rouler la tâche en tant que "postgres". Par contre, le statut de la tâche reste figé sur "Running".

Y aurait-il une configuration requise dans pg_ident.conf ou pg_hda.conf pour permettre à un compte local de se connecter et exécuter des requêtes via psql?

Merci à tous pour votre collaboration habituelle.

Charles M.

charleydc5 · 29/06/2011 19:55:42

Je viens de réaliser qu'il me fallait un fichier pgpass dans le répertoire du compte local postgres, car autrement, lorsque j'ai essayé d'exécuter le script manuellement à partir du serveur directement le compte local, le script me demandait un mot de passe.

Merci!

#1 14/06/2011 18:20:13

Backup de toutes les bases de données à chaque nuit sous Windows

#2 14/06/2011 18:37:12

Re : Backup de toutes les bases de données à chaque nuit sous Windows

#3 14/06/2011 18:50:00

Re : Backup de toutes les bases de données à chaque nuit sous Windows

#4 14/06/2011 20:51:21

Re : Backup de toutes les bases de données à chaque nuit sous Windows

#5 15/06/2011 12:03:36

Re : Backup de toutes les bases de données à chaque nuit sous Windows

#6 15/06/2011 19:04:18

Re : Backup de toutes les bases de données à chaque nuit sous Windows

#7 15/06/2011 19:51:28

Re : Backup de toutes les bases de données à chaque nuit sous Windows

#8 15/06/2011 20:17:58

Re : Backup de toutes les bases de données à chaque nuit sous Windows

#9 15/06/2011 20:24:08

Re : Backup de toutes les bases de données à chaque nuit sous Windows

#10 15/06/2011 20:32:34

Re : Backup de toutes les bases de données à chaque nuit sous Windows

#11 15/06/2011 20:38:32

Re : Backup de toutes les bases de données à chaque nuit sous Windows

#12 15/06/2011 21:10:19

Re : Backup de toutes les bases de données à chaque nuit sous Windows

#13 16/06/2011 06:54:35

Re : Backup de toutes les bases de données à chaque nuit sous Windows

#14 16/06/2011 07:25:48

Re : Backup de toutes les bases de données à chaque nuit sous Windows

#15 16/06/2011 13:53:58

Re : Backup de toutes les bases de données à chaque nuit sous Windows

#16 16/06/2011 14:35:56

Re : Backup de toutes les bases de données à chaque nuit sous Windows

#17 16/06/2011 15:59:04

Re : Backup de toutes les bases de données à chaque nuit sous Windows

#18 16/06/2011 16:19:31

Re : Backup de toutes les bases de données à chaque nuit sous Windows

#19 16/06/2011 16:41:41

Re : Backup de toutes les bases de données à chaque nuit sous Windows

#20 16/06/2011 18:28:19

Re : Backup de toutes les bases de données à chaque nuit sous Windows

#21 16/06/2011 18:47:35

Re : Backup de toutes les bases de données à chaque nuit sous Windows

#22 16/06/2011 20:40:25

Re : Backup de toutes les bases de données à chaque nuit sous Windows

#23 29/06/2011 19:42:51

Re : Backup de toutes les bases de données à chaque nuit sous Windows

#24 29/06/2011 19:55:42

Re : Backup de toutes les bases de données à chaque nuit sous Windows

Pied de page des forums

À propos

Nous contacter

Groupes Locaux

Réseaux Sociaux