Re: [GENERAL] pg_dumpall for Postgres Database Daily Backup
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 r...@iol.ie -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dumpall for Postgres Database Daily Backup
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 signature.asc Description: This is a digitally signed message part.
Re: [GENERAL] pg_dumpall for Postgres Database Daily Backup
On Thu, May 13, 2010 at 8:16 PM, Scott Marlowe wrote: > On Thu, May 13, 2010 at 9:50 AM, Wang, Mary Y > 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 >
Re: [GENERAL] pg_dumpall for Postgres Database Daily Backup?
On Thu, 2010-05-13 at 09:15 -0700, Wang, Mary Y wrote: > 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), If you replace 8.3 with current, you will get the current docs. > and it looks like pg_dumpall > outfile is the best choice. Depends. Many people tend to dump each database individually, especially using -Fc parameters in order to restore them easily. If you choose this route, please note that you will also need to dump globals using pg_dumpall -g. Regards, -- Devrim GÜNDÜZ PostgreSQL Danışmanı/Consultant, Red Hat Certified Engineer PostgreSQL RPM Repository: http://yum.pgrpms.org Community: devrim~PostgreSQL.org, devrim.gunduz~linux.org.tr http://www.gunduz.org Twitter: http://twitter.com/devrimgunduz signature.asc Description: This is a digitally signed message part
Re: [GENERAL] pg_dumpall for Postgres Database Daily Backup
On Thu, May 13, 2010 at 9:50 AM, Wang, Mary Y 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dumpall for Postgres Database Daily Backup
On Thu, May 13, 2010 at 11:50 AM, Wang, Mary Y 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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] pg_dumpall for Postgres Database Daily Backup?
On Thu, 2010-05-13 at 09:15 -0700, Wang, Mary Y 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. It entirely depends on your needs. I generally use pg_dump -Fc over a loop to each database. The plain text version of the dump is particularly difficult to work with for partial restores. Joshua D. Drake > > Thanks in advance. > Mary > > > > > -- PostgreSQL.org Major Contributor Command Prompt, Inc: http://www.commandprompt.com/ - 503.667.4564 Consulting, Training, Support, Custom Development, Engineering -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
[GENERAL] 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 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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
