On 5/20/2015 10:44 AM, Michael Nolan wrote:
the only possible consistency issue would be if you have applications doing 2-phase commits to two different databases on the same server, otherwise each database is dumped as a single transaction and all data elements within that database are point-in-time consistent.
my preferred backup procedure for a whole server dump is to
A) pg_dumpall --globals-only | gzip > ...
B) for each database, do pg_dump -Fc -f $database.Fc.pgdump $database
I do this via the following crontab entry for hte postgres user...
$ crontab -l
30 1 * * * /var/lib/pgsql/cronbackup.sh
and this script...
#!/bin/bash
#
/usr/pgsql-9.3/bin/pg_dumpall --globals-only | gzip > /home2/backups/pgsql/pgdumpall.globals.`date +\%a`.sql.gz
for i in $(psql -tc "select datname from pg_database where not datistemplate"); do \
pg_dump -Fc -f /home2/backups/pgsql/pgdump.$i.$(date +\%a).dump $i
done
--
john r pierce, recycling bits in santa cruz