Received: from localhost (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 5087D64FF09 for ; Fri, 19 Dec 2008 10:14:57 -0400 (AST) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 82376-06 for ; Fri, 19 Dec 2008 10:14:54 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.autorevenue.com (mail.autorevenue.com [208.178.167.106]) by mail.postgresql.org (Postfix) with ESMTP id 21BD264FD3D for ; Fri, 19 Dec 2008 10:14:52 -0400 (AST) Received: (qmail 18311 invoked from network); 19 Dec 2008 09:16:06 -0500 Received: from unknown (HELO dei26g028534) (msteben@10.10.6.168) by mail.autorevenue.com with SMTP; 19 Dec 2008 09:16:06 -0500 From: "Mark Steben" To: "'Kevin Grittner'" , References: <494A7819.EE98.0025.0@wicourts.gov> Subject: Re: Warm Standby - log shipping Date: Fri, 19 Dec 2008 09:14:14 -0500 Message-ID: MIME-Version: 1.0 Content-Type: text/plain; charset="koi8-r" Content-Transfer-Encoding: quoted-printable X-Mailer: Microsoft Office Outlook 11 In-Reply-To: <494A7819.EE98.0025.0@wicourts.gov> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.4325 Thread-Index: AclhXr4Lfv/GfVkERGidsVrTueeZjgAhJylg X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests=none X-Spam-Level: X-Archive-Number: 200812/160 X-Sequence-Number: 30993 What I'm hearing is that I have to perform a base backup on my master in Mass. after recovery completes, send that over a secure network To Virginia, and lay it down there. Simple enough but the time to = travel Over the network becomes an issue - 12 - 13 hours at best. If we have to do this then we will. I just want to make sure I'm understanding your advice. Thanks Mark Steben=81Database Administrator=81=20 @utoRevenue-R- "Join the Revenue-tion" COME SEE US AT NADA BOOTH #1021, HALL B, IN NEW ORLEANS! 95 Ashley Ave. West Springfield, MA., 01089=20 413-243-4800 x1512 (Phone) =81 413-732-1824 (Fax) @utoRevenue is a registered trademark and a division of Dominion = Enterprises =20 =20 =20 -----Original Message----- From: Kevin Grittner [mailto:Kevin.Grittner@wicourts.gov]=20 Sent: Thursday, December 18, 2008 5:20 PM To: Mark Steben; pgsql-admin@postgresql.org Subject: Re: Warm Standby - log shipping >>> "Mark Steben" wrote:=20 =20 > We are at postgresql 8.2.5 =20 You really should update to 8.2.11 or consider going to 8.3.5. =20 http://www.postgresql.org/support/versioning =20 > We plan on using the Norfolk server not so much as a recovery failover but > as a replicated database > To run reports and establish a data warehousing environment. As such the > plan is to run the=20 > Standby in recovery state for the majority of the day, then 'complete' > recovery there, bring it > Online, perform our reporting and data warehousing functions (in read-only > mode of course), > Then bring it back into recovery mode, letting the updates catch up for the > next days processing. >=20 > My questions are: >=20 > 1. Is this a proper usage of log shipping? =20 Once you complete recovery you don't have a good way back, short of getting a new base backup. If you have space, you could stop the replica server without leaving recovery state, do a copy of the cluster to another directory, restart your replica, start the copied cluster's server, complete recovery, and start running your reports. =20 > 3. I am currently in a state where a log got partially copied and postgres > cannot find a valid checkpoint to restart. What is the best way to remedy > this situation? Pg_resetxlog perhaps? =20 I'd get a fresh base backup from which to start. =20 -Kevin