Hello Ismael,
Please consider using bpipe:
https://www.bacula.lat/using-bpipe-to-stream-dumps-vm-clones-and-another-data-to-your-backup/?lang=en
It is a much more automated way of performing backups using tubes. You can also
automate the restore.
Examples from my book:
PostgreSQL
This method is very similar to the one used for MySQL. The only difference is,
in order to automatically import the restored database into the PostgreSQL
service, the dump must be generated in binary mode.
Restoring to a file:
Plugin = "bpipe:/fifo/anydb.sql:sudo -u postgres pg_dump heitorpg:dd
of=/root/anydb.sql"
Direct restore to PostgreSQL (different FIFO read and write commands):
Plugin = "bpipe:/fifo/anydb.sql:sudo -u postgres pg_dump --format=c
heitorpg:sudo -u postgres pg_restore -d template1 -C"
Dynamic configuration script for granular backup of all databases (example)1:
#!/bin/sh
# Suggested name: \\|/etc/bacula/scripts/bpipe-pg-db
# Set the postgres access command
SUCMD="su - postgres -c"
# Fetch existing databases names
for dbname in `$SUCMD 'psql -d template1 -q -U postgres -t' <<EOF
select datname from pg_database where not datname in ('bacula','template0')
order by datname;
EOF
`
of
echo "bpipe:/fifo/$dbname.dump:$SUCMD '/usr/bin/pg_dump --format=c
$dbname':$SUCMD 'pg_restore -d template1 -C'"
done
1Adapted from:
http://wiki.bacula.org/doku.php?id=application_specific_backups:postgresql
You can also upgrade your Community Edition for the Enterprise one. It brings a
PostgreSQL plugin with much more advanced and automated features, while being
able to restore all your legacy backups. I can assist you with that if you send
me a private email.
Also please do not use GZIP compression over level 6. It will fry your clients
CPU with marginal compression rate reduction gains.
Rgds.
MSc Heitor Faria (Miami/USA)
Bacula LATAM CIO
mobile1: + 1 909 655-8971
mobile2: + 55 61 98268-4220
bacula.lat | bacula.com.br
From: "Ismael Matos" <iabma...@gmail.com>
To: "undefined" <bacula-users@lists.sourceforge.net>
Sent: Saturday, January 13, 2024 5:07 PM
Subject: [Bacula-users] Backup of Postgres databases using FIFO Files
Dear all,
One of my production Postgres databases is growing faster than anticipated and
I'm exploring the use of FIFO files instead of writing the output of pg_dump to
intermediate files within /tmp structure. Currently all jobs work fine while
using the intermediate files. But when I test with the FIFO files, data is not
pulled from the pipes. All files are properly located, but ZERO bytes get
backed up. The pg_dump tasks are still running after the backup job completed
successfully. Looks like the FIFO files have not been touched by bacula-fd
after the script ended and the "readfifo=yes" option has not been honored.
What am I missing? Is anyone using successfully the FIFO files with postgres
databases able to share some tips?
Many thanks in advance.
Cheers,
Ismael
Here is the script used in the bacula clients:
# Database Backups to temporary storage
export DUMPDIR=/tmp/bacula_pgdatadump
# make the redirections to null
exec > /dev/null
rm -f -r $DUMPDIR
mkdir $DUMPDIR
mkdir $DUMPDIR/backups
mkdir $DUMPDIR/logs
pg_dumpall -g -v -f $DUMPDIR/backups/dumpallGlobal.bkp
2>$DUMPDIR/logs/dumpallGlobal.log
for dbname in `psql -d template1 -q -t <<EOF
select datname from pg_database where not datname in ('template0') order by
datname;
EOF
`
do
mkfifo $DUMPDIR/backups/$dbname.bkp
nohup pg_dump -Fc -Z0 -v $dbname -f $DUMPDIR/backups/$dbname.bkp
2>$DUMPDIR/logs/$dbname.log &
done
# ends output redirection
exit
Here is the relevant section of the bacula-dir.conf file in the server:
...
Fileset {
Name = FullSet
Include {
File = /etc
File = /home
File = /opt
File = /root
File = /tmp/bacula_pgdatadump
File = /usr/local/sbin
File = /usr/sbin
File = /usr/share
File = /var
ExcludeDirContaining = .baculaexclude
Options {
Compression = Gzip9
Signature = Md5
readfifo=yes
}
}
Exclude {
File = /var/cache
..._______________________________________________
Bacula-users mailing list
Bacula-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/bacula-users
