Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 62DB19F97AA for ; Thu, 23 Aug 2007 10:44:13 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 33932-02 for ; Thu, 23 Aug 2007 10:44:01 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from gwmta.wicourts.gov (gwmta.wicourts.gov [165.219.244.99]) by postgresql.org (Postfix) with ESMTP id 9A4459F9737 for ; Thu, 23 Aug 2007 10:44:09 -0300 (ADT) Received: from Courts-MTA by gwmta.wicourts.gov with Novell_GroupWise; Thu, 23 Aug 2007 08:44:07 -0500 Message-Id: <46CD48C8.EE98.0025.0@wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 7.0.2 Date: Thu, 23 Aug 2007 08:43:54 -0500 From: "Kevin Grittner" To: "Scott Marlowe" Cc: Subject: Re: "Stand-in" server recovery techniques References: <46CC5374.EE98.0025.0@wicourts.gov> In-Reply-To: Mime-Version: 1.0 Content-Type: text/plain; charset=US-ASCII Content-Transfer-Encoding: quoted-printable Content-Disposition: inline X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/287 X-Sequence-Number: 26618 Thanks for the reply, Scott. >>> On Wed, Aug 22, 2007 at 5:04 PM, in message , "Scott = Marlowe" wrote:=20 > On 8/22/07, Kevin Grittner wrote: >=20 >> How do we create the PostgreSQL instance on the stand-in box? >> >> (1) Restore the latest base backup and apply all WAL files available. >> This is likely to be the slowest option. >=20 > I'm leaning towards option 1, because you mentioned that you will be > doing this ONLY in the event the primary on site server can't come > back up. So, I'm going to assume that you're going to spend a few > minutes (30 or so) trying to resurrect the primary server. >=20 > WHILE doing that, I would have the backup server running the WAL files > to get ready to go. >=20 > The nice thing about this setup is that it's the simplest to > implement, hence the least error prone =20 Point taken. That would mean kicking off a process while still in the = early stages of analyzing the problem, and the results would often not be used, but the cost is minimal. =20 >> (2) Kick the warm standby on "the farm" into production mode, shut = down >> the instance, and then copy the instance directory. This should be >> relatively quick and safe, but has the down side of needing to restart >> the warm standby from the latest base backup afterwards, if that is = even >> possible. It seems like we might need to make a fresh base backup from = the >> (stopped) instance on the warm standby farm. >=20 > I'm not sure exactly what you're saying here. If you're saying what I > think you're saying, then you're already constantly playing the WAL > files as they arrive from off site. If that's the case then this > option seems quite attractive in terms of getting you back up and > running fast. >=20 > Since the standby would now become production, making a snapshot of > the standby before making it production would mean that you can then > use the snapshot elsewhere for the standby. >=20 > If I understand what you're saying correctly. =20 I'm not talking about running production on the warm standby farm. Given the load of running all the other warm standby instances, I don't believe that performance would be acceptable. What I'm talking about is creating the semaphore file which would kick the PostgreSQL instance for that = county out of archive recovery mode. (Our script would see the semaphore file = and exit with "file not found" when it was unable to immediately provide the requested file.) The database cluster state reported by pg_controldata switches from "in archive recovery" to "in production" at that point. We would then stop that PostgreSQL instance and scp it to the stand-in = server. We would start that up and be in business again within a few minutes. =20 My biggest concern with this approach is how we would get back into a backed-up state. I don't want to burden the stand-in machine with a PITR base backup when users are working remotely and trying to make up for lost time. If I could somehow use the copy from the warm standby box to make a base, that would be ideal. That seems like it should be possible, since = it is the starting point from which we would then be generating WAL files in production, but I'm not confident that I'd know what I was doing. =20 I could wait for day-end and keep my fingers crossed that the stand-in didn't also fail, but that's not an entirely comfortable position, even with our transaction repository. =20 -Kevin =20