1 From: Mathieu Arnold <mat@mat.cc>
2 To: bacula-users@lists.sourceforge.net
3 Subject: Re: [Bacula-users] backup postgresql databases
4 Date: Fri, 12 Mar 2004 22:31:58 +0100
6 +-Le 11/03/2004 15:20 +0100, Mathieu Arnold a dit :
9 | I was wondering if someone already had some script, or ways of doings
10 | scripts to backup (and maybe restore) pgsql databases. I'm balancing
11 | between taking a snapshot of the database directory and backuping that,
12 | dumping the datas into .sql.gz files, into .tgz files, or into a pipe
13 | letting bacula deal with the compression.
17 Thanks to all ppl I got answers from (many used awfully hard way to get
18 databases), I cooked up my scripts (I needed mysql too), and here they are :
20 --------------------------------------------
23 export TMPDIR="/usr/tmp/"
24 export TEMP="/usr/tmp/"
25 export SAVE="/usr/tmp/dumps/"
35 pg_dump=/usr/local/bin/pg_dump
36 pg_dumpall=/usr/local/bin/pg_dumpall
37 psql=/usr/local/bin/psql
39 gzip="| /usr/bin/gzip -nc9"
46 rm -f $SAVE/$host-pgsql*
49 su - $pg_user -c "$pg_dumpall -g $gzip" > $SAVE/$host-pgsql$gzext
51 for i in $($psql -l $pg_template $pg_dbuser|sed -e '1,4d' -e
52 '/rows)$/,/\eof/d' -e '/template/d' -e 's/ \([^ ]*\).*$/\1/')
54 su - $pg_user -c "$pg_dump -c -F p $i $gzip" > $SAVE/$host-pgsql-$i$gzext
56 --------------------------------------------
58 For those using complicate selects to get databases list, I advise psql -l
63 --------------------------------------------
66 export TMPDIR="/usr/tmp/"
67 export TEMP="/usr/tmp/"
68 export SAVE="/usr/tmp/dumps/"
76 mysql=/usr/local/bin/mysql
77 mysqldump=/usr/local/bin/mysqldump
79 gzip="/usr/bin/gzip -nc9"
86 rm -f $SAVE/$host-mysql*
89 for i in $($mysql -u $my_user -p$my_passwd -e 'show databases'|$sed '1d')
91 $mysqldump -u $my_user -p$my_passwd $i | $gzip > $SAVE/$host-mysql-$i$gzext
93 --------------------------------------------
95 maybe those scripts will save some ppl some time :)