Thanks to everyone for their input on this. After reading all the emails and some of the documentation (section 23.3), I think this is all a little more than what I need. My database is basically read-only and all I was looking to do is to be able to take snap-shots of it and be able to restore on a developer's machine and not take 30 hours. So I was thinking I would zip the data directories associated to my database, then the developer could just copy the zip file and unzip in their own data directory. My question now is: what file would a developer need to change to add this new directory to their database list, or will it just automatically show up when they refresh the service?
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Michael Nolan
Sent: Thursday, May 17, 2007 7:03 PM
To: Ron Johnson; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Large Database Restore
On 5/17/07, Ron Johnson <ron.l.johnson@cox.net> wrote:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
On 05/17/07 16:49, Michael Nolan wrote:
> I don't know if my database is typical (as there probably is no such
> thing), but to restore a full dump (pg_dumpall) takes over 4 hours on my
> backup server, but to restore a low level backup (about 35GB)
Meaning a tarball of $PGDATA?
Actually, it's two different areas because I've got a second tablespace on a separate physical drive for indexes, but yes, it's a tarball of all the files that PG uses, following the procedures in section 23.3 of the documentation.
It works very well, though I still don't understand why, if there are no changes to the warm standby server tables, only queries, it isn't possible to keep restoring WAL files to keep the warm standby server in parallel with the live server. (I'm guessing there must be SOMETHING that happens at the end of the recovery process, or some time after that, to make the next WAL unprocessable., but I can't figure it out from the docs.)
--
Mike Nolan
This email and any files transmitted with it are confidential and intended solely for the use of the individual or entity to whom they are addressed. If you have received this email in error please notify the sender. This message contains confidential information and is intended only for the individual named. If you are not the named addressee you should not disseminate, distribute or copy this e-mail.