Re: Consistent state for pg_dump and pg_dumpall

On 5/20/2015 10:44 AM, Michael Nolan wrote:
>
> When I moved to a new release of pg (9.3) last December, I stopped all
> transaction processing first so that pg_dumpall had no consistency
> issues.

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