DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=subject:mime-version:content-type:from:x-priority:in-reply-to:date
	:cc:content-transfer-encoding:message-id:references:to:x-mailer;
	b=L9G0WzxEnDTrgAekBV5j14FpqAvAKGrzKU/4tcQjoIUIqrfJMGTNMaja3qscB49ddT
	hJm1Tm2D355wNhS7m3RRecMV25CJ7kUoHmQGjVxRvk6avkPvkqOlU0oGWbq9ZN8I6HHZ
	cvIyuDiHqKHl/wSNngV+VXmgqRkiI57RVSC2M=
Subject: Re: PG_DUMP backup
Mime-Version: 1.0 (Apple Message framework v1077)
Content-Type: text/plain; charset=us-ascii
From: Josh Kupershmidt <schmiddy@gmail.com>
In-Reply-To: <7965A9DCF12CC14984420BCC37B1608F25A9E2D038@Elzar.grant.co.uk>
Date: Fri, 12 Feb 2010 10:29:30 -0500
Cc: "pgsql-admin@postgresql.org" <pgsql-admin@postgresql.org>
Content-Transfer-Encoding: quoted-printable
Message-Id: <25CE55AD-6515-4C0B-A330-4BE6E7C324A4@gmail.com>
References: <20100211220907.GA13352@obspm.fr>
	<4B742E4A020000250002F1F4@gw.wicourts.gov>
	<7965A9DCF12CC14984420BCC37B1608F25A9E2D038@Elzar.grant.co.uk>
To: Renato Oliveira <renato.oliveira@grant.co.uk>


On Feb 12, 2010, at 4:58 AM, Renato Oliveira wrote:

> Dear all,
>=20
> 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.
>=20

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.
>=20

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.=20

> 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=3D... 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.=20

Also, I would look into tuning your backup server to make pg_restore as =
fast as possible. See e.g.=20
http://wiki.postgresql.org/wiki/Bulk_Loading_and_Restores


Josh=