Обсуждение: pg_dumpall for Postgres Database Daily Backup
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 onlyfound the documentation for 8.3.10), and it looks like pg_dumpall > outfile is the best choice. I'd like to ask thecommunity to reconfirm. Thanks in advance. Mary
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.
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 onlyfound the documentation for 8.3.10), and it looks like pg_dumpall > outfile is the best choice. I'd like to ask thecommunity 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.
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:That will work. So will pg_dumpall | gzip > filename.gz if you want
> 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.
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
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
Вложения
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