Re: Consistent state for pg_dump and pg_dumpall

[John R Pierce <pierce@xxxxxxxxxxxx>]

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