From: Josh Kupershmidt <schmiddy@gmail.com>
To: Renato Oliveira <renato.oliveira@grant.co.uk>
Cc: pgsql-admin@postgresql.org <pgsql-admin@postgresql.org>
Subject: Re: PG_DUMP backup
Date: Fri, 12 Feb 2010 10:29:30 -0500
Message-ID: <25CE55AD-6515-4C0B-A330-4BE6E7C324A4@gmail.com> (raw)
In-Reply-To: <7965A9DCF12CC14984420BCC37B1608F25A9E2D038@Elzar.grant.co.uk>
References: <20100211220907.GA13352@obspm.fr>
<4B742E4A020000250002F1F4@gw.wicourts.gov>
<7965A9DCF12CC14984420BCC37B1608F25A9E2D038@Elzar.grant.co.uk>
On Feb 12, 2010, at 4:58 AM, Renato Oliveira wrote:
> Dear all,
>
> I have a server running 8.2.4 and has a database 170GB in size.
> Currently I am backing it up using pg_dump and it takes around 28 hours, sadly.
That's suspiciously slow for a pg_dump alone. I have a ~168 GB database which gets pg_dumped nightly, taking about 2.5 hours, all on 2+ year-old commodity hardware.
> I was asked to check and compare the newly created DUMP file to the live database and compare records.
>
If you really must run this comparison, maybe you can check out "pg_comparator" (I think you would restore first, then use pg_comparator to run the diffs). However, it sounds like your assignment really is more about making sure that your backup server is functional and ready to take over if the master dies. There are easier, and better, ways to establish this than doing a row-by-row comparison of your backup and live server
> I personally cannot see an easy or quick way of doing this, and even the point in doing so.
> I am already restoring the full database to a separate server and no errors were reported.
>
There's probably a much easier way of ensuring the validity of your backup server without running this diff, but that'll of course depend on your environment and your boss' wishes.
> My question is:
> 1 - Is there a more efficient way of backing up such large database, using pg_dump or any other tool?
Only other ways, other than PITR which you rule out, are documented here, but I doubt you'll like them:
http://developer.postgresql.org/pgdocs/postgres/backup-file.html> 2 - Is there an easy way to compare the live database with the DUMP file just created?
Take another dump, and compare the two dumps? This borders on absurdity, of course.
> Idea:
> Pg_dump to split the file into smaller usable chuncks, which could be restored one at time, is that possible?
You can dump a table at a time, or a few at a time, using pg_dump --table=... I doubt this will speed the restore up, though. If you can upgrade to 8.4, or upgrade the backup server to 8.4, your pg_restore should be faster with parallel restores.
Also, I would look into tuning your backup server to make pg_restore as fast as possible. See e.g.
http://wiki.postgresql.org/wiki/Bulk_Loading_and_Restores
Josh
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: schmiddy@gmail.com, renato.oliveira@grant.co.uk
Subject: Re: PG_DUMP backup
In-Reply-To: <25CE55AD-6515-4C0B-A330-4BE6E7C324A4@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox