Horton

Thread

  1. pg_dumpall for Postgres Database Daily Backup

    Wang, Mary Y <mary.y.wang@boeing.com> — 2010-05-13T15:50:31Z

    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.

Thanks in advance.
Mary

  2. Re: pg_dumpall for Postgres Database Daily Backup

    Vick Khera <vivek@khera.org> — 2010-05-13T19:37:12Z

    On Thu, May 13, 2010 at 11:50 AM, Wang, Mary Y <mary.y.wang@boeing.com> wrote:
> it looks like pg_dumpall > outfile is the best choice.  I'd like to ask the community to reconfirm.

We do an individual pg_dump on each DB separately, using the '-Fc'
format.  The only thing we miss from pg_dumpall we get via "pg_dumpall
--globals-only > globals.sql".  The "c" format is much more flexible
to restore bits of the database with judicious use of pg_restore and
awk or grep.

  3. Re: pg_dumpall for Postgres Database Daily Backup

    Scott Marlowe <scott.marlowe@gmail.com> — 2010-05-14T00:16:18Z

    On Thu, May 13, 2010 at 9:50 AM, Wang, Mary Y <mary.y.wang@boeing.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.

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.

  4. Re: pg_dumpall for Postgres Database Daily Backup

    Scott Mead <scott.lists@enterprisedb.com> — 2010-05-14T10:40:34Z

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

> On Thu, May 13, 2010 at 9:50 AM, Wang, Mary Y <mary.y.wang@boeing.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@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

  5. Re: pg_dumpall for Postgres Database Daily Backup

    Adrian von Bidder <avbidder@fortytwo.ch> — 2010-05-16T15:59:24Z

    On Thursday 13 May 2010 17.50:31 Wang, Mary Y wrote:
> http://www.postgresql.org/docs/8.3/static/backup-dump.html#BACKUP-DUMP-A
> LL (I only found the documentation for 8.3.10), and it looks like
> pg_dumpall > outfile is the best choice.

pg_dumpall or by-database pg_dump ha the advantage that it is uncomplicated 
and restore is possible to newer pg versions without problem.

Another strategy is taking snapshots of the data/ directory and archiving 
WAL files as described in 
http://www.postgresql.org/docs/8.4/interactive/continuous-archiving.html

This has various trade-offs against the pg_dump approach:
 * pint in time recovery
 * you can get away with taking a full backup only occasionally, as long
   as you keep the WAL files
 * you need much more space (data/ dir plus WAL)
 * it's a bit more complex to set up
 * reovery needs to be to a database with the same version (and 
architecture?  Not sure.  Only same minor version, or patchlevel too?)

We decided to use the continuous archiving way becase (since we ship the WAL 
files off site) we lose almost no data if a crash happens (hasn't, so far 
:-) and transferring the WAL file over the network (on a only light db load) 
is quite light.  (transferring pg_dump files with rsync should be quite 
light as well, though; I guess the ordering of the data is more or less 
constant for parts of tables that are not changed.  I've not tested this 
though.)

cheers
-- vbi

-- 
this email is protected by a digital signature: http://fortytwo.ch/gpg

  6. Re: pg_dumpall for Postgres Database Daily Backup

    Raymond O'Donnell <rod@iol.ie> — 2010-05-16T17:17:47Z

    On 16/05/2010 16:59, Adrian von Bidder wrote:
>  * pint in time recovery

This is what happens *after* work - this side of the pond, anyway.... :-)

Ray.


-- 
Raymond O'Donnell :: Galway :: Ireland
rod@iol.ie