Re: pg_dumpall for Postgres Database Daily Backup

On Thu, May 13, 2010 at 8:16 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com>wrote:

> On Thu, May 13, 2010 at 9:50 AM, Wang, Mary Y <mary(dot)y(dot)wang(at)boeing(dot)com>
> wrote:
> > Hi,
> >
> > I'm running on Postgres 8.3.8. My system admin is ready to set up a cron
> job for a daily database backup.
> > By reading the documentation over here:
> http://www.postgresql.org/docs/8.3/static/backup-dump.html#BACKUP-DUMP-ALL(I only found the documentation for 8.3.10), and it looks like pg_dumpall >
> outfile is the best choice. I'd like to ask the community to reconfirm.
>
> That will work. So will pg_dumpall | gzip > filename.gz if you want
> compression.
>

My preference is to use

pg_dumpall -g > globals.bak
pg_dump -Fc database > database.bak
pg_dump -Fc database1 > database1.bak

etc..

The major reason for this is because this allows selective restores. If
something goes wrong with my 'users' table in 'database1' :

Using the pg_dumpall strategy
- uncompress large text file
- manually cut 'users' table from large text file
- restore

With the pg_dump -Fc strategy
- pg_restore -t users -d database1 < database1.bak

The pg_dump -Fc strategy also allows you to leverage the new, parallel
restore feature (-j option to pg_restore) in 8.4.

--Scott

>
> What's more important is that you first prove your backups are working
> by restoring them elsewhere, then check every month or so to make sure
> they're still happening.
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>