Hi all
After a few hours of troubleshooting, I finally have my PostgreSQL database
backup running. My backup job creates separate schema/data backups for each
database, along with a separate backup of global objects. This is much easier
and safer to handle than "all-in-one-file" backups. Moreover, my scripts backup
the data through pipes. So there is no need for additional disk space for large
database backups. The script automatically determines which databases are on
the server. My server is a CentOS 4.2 w/SELinux enabled running bacula-1.38.5-4
with postgresql backend on a x86_64 dual xeon box with a ultrium-3 tape
attached. The data is always spooled through a separate RAID 0 array (the tape
is too fast for my other disks). My postgresql is 8.1.2, but my scripts should
also work with versions >= 7.3 or perhaps 7.4 .
I hope this is useful for somebody else, too.
regards
--Marcel
First, create the directory /var/lib/pgsql/data/dump and
/var/lib/pgsql/data/dump/fifo , chown postgres:bacula, chmod 750.
Ensure that the database user postgres running on the local host has "trust"
access to all databases (no passwords needed). This script also works for
backup of remote databases, but ensure that access rights are set properly.
If you prefer to have a password, you can uncomment the lines
EXPORT PGPASSWORD=xxxxxxxx
in my scripts.
Create these files:
/etc/bacula/make_database_backup: owner root, group postgres, chmod g+x:
<FILE>
#!/bin/sh
exec > /dev/null
DUMPDIR=/var/lib/pgsql/data/dump
FIFODIR=$DUMPDIR/fifo
export PGUSER=postgres
#export PGPASSWORD=xxxx # only when pg_hba.conf requires
it
/usr/bin/pg_dumpall -g >$DUMPDIR/globalobjects.dump # hopefully never a big
file, so no need for a fifo
rm -f $FIFODIR/*.data.dump
for dbname in `psql -d template1 -q -t <<EOF
select datname from pg_database where not datname in ('bacula','template0')
order by datname;
EOF
`
do
mkfifo $FIFODIR/$dbname.schema.dump
/usr/bin/pg_dump --format=c -a $dbname --file=$FIFODIR/$dbname.schema.dump
2>&1 < /dev/null &
mkfifo $FIFODIR/$dbname.data.dump
/usr/bin/pg_dump --format=c -a $dbname --file=$FIFODIR/$dbname.data.dump 2>&1
< /dev/null &
done
</FILE>
/etc/bacula/delete_database_backup: owner root, group postgres, chmod g+x:
<FILE>
#!/bin/sh
DUMPDIR=/var/lib/pgsql/data/dump
FIFODIR=$DUMPDIR/fifo
for dbname in `psql -U postgres -d template1 -q -t <<EOF
select datname from pg_database where not datname in ('bacula','template0')
order by datname;
EOF
`
do
rm -f $FIFODIR/$dbname.schema.dump
rm -f $FIFODIR/$dbname.data.dump
done
rm -f $DUMPDIR/globalobjects.dump
</FILE>
....use this helper file to determine the backups needed: /etc/bacula/listdbdump
<FILE>
#!/bin/sh
FIFODIR=/var/lib/pgsql/data/dump/fifo
for dbname in `psql -d template1 -q -U postgres -h $1 -p $2 -t <<EOF
select datname from pg_database where not datname in ('bacula','template0')
order by datname;
EOF
`
do
echo "$FIFODIR/$dbname.schema.dump"
echo "$FIFODIR/$dbname.data.dump"
done
</FILE>
....create these entries in bacula-dir.conf:
Job {
Name = "hymost-db"
JobDefs = "DefaultJob"
Level = Full
FileSet="myhost-db"
Client = myhost-fd
Schedule = "WeeklyCycleAfterBackup"
# This creates a backup of the databases with pg_dump to fifos
Client Run Before Job = "su - postgres -c
\"/etc/bacula/make_database_backup\""
# This deletes the backup and fifo files
Client Run After Job = "su - postgres -c
\"/etc/bacula/delete_database_backup\""
Priority = 17 # run after main backup
}
FileSet {
Name = "myhost-db"
Include {
Options {
signature = MD5
readfifo = yes
}
File = "/var/lib/pgsql/data/dump/globalobjects.dump"
File = "|/etc/bacula/listdbdump myhost-or-ip.mynet.lan 5432"
}
}
-------------------------------------------------------
This SF.net email is sponsored by: Splunk Inc. Do you grep through log files
for problems? Stop! Download the new AJAX search engine that makes
searching your log files as easy as surfing the web. DOWNLOAD SPLUNK!
http://sel.as-us.falkag.net/sel?cmd=lnk&kid3432&bid#0486&dat1642
_______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
