Received: from localhost (maia-5.hub.org [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id C558E9F98F3 for ; Wed, 22 Aug 2007 17:17:24 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 80200-04 for ; Wed, 22 Aug 2007 17:17:18 -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 7CF7E9F9845 for ; Wed, 22 Aug 2007 17:17:20 -0300 (ADT) Received: from Courts-MTA by gwmta.wicourts.gov with Novell_GroupWise; Wed, 22 Aug 2007 15:17:19 -0500 Message-Id: <46CC5374.EE98.0025.0@wicourts.gov> X-Mailer: Novell GroupWise Internet Agent 7.0.2 Date: Wed, 22 Aug 2007 15:17:08 -0500 From: "Kevin Grittner" To: Subject: "Stand-in" server recovery techniques 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/277 X-Sequence-Number: 26608 I'm looking for advice on how to best switch from warm standby processing = to "stand-in" production use in our (rather unusual) environment. =20 THE ENVIRONMENT =20 We have 72 counties spread around the state, each with their own database, which contains the official data for that county's court system. We do = PITR backups to the file server in each county, and replicate the files (both base backups and WAL files) back to a single central machine which hosts a "farm" of warm standby PostgreSQL instances -- one for each county, all on the same machine. This serves to validate the integrity of the files brought back from the counties. =20 Base backups are done weekly, and the WAN speed to a county depends primarily on the size of its base backup -- we try to get a speed = sufficient to bring back the base backup within 24 hours. We force WAL files to be written at least once per hour, although many counties generate them much more frequently during the work day. =20 It turns out that 72 warm standby instances can keep a machine pretty = busy, so we won't want to use that machine for production. We have two machines ready to use for temporary "stand-ins" over the WAN in the event of a catastrophic failure of a county machine. We will get a database going on one of these, in production mode. This would only be done after recovery efforts on the county machine have failed, and that machine has been = powered down. We will bring the warm standby machine as current as we can using WAL = files, copy it to a stand-in machine, start our application middle tier and "top off" the stand-in database with transactions from the transaction = repository our framework maintains (normally current to within a second). =20 Once this stand-in is running, we will want to become the source of WAL files which can be applied to the warm standby on "the farm". =20 THE QUESTION =20 How do we create the PostgreSQL instance on the stand-in box? =20 I see four possibilities: =20 (1) Restore the latest base backup and apply all WAL files available. This is likely to be the slowest option. =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 (3) Stop the warm standby while it is in recovery mode, copy the instance directory, and restart it. On the stand-in box, have the script specified by recovery.conf just "exit 1" to kick it immediately into production = mode. As long as the WAL files generated in this situation work with the old = warm standby, I don't see a down side, but I'm not sure if that is a safe assumption. =20 (4) Capture information about where the warm standby is at and attempt a PITR-style copy of the instance while the warm stanby is running. This = one seems riskier than (3) without significant benefit. =20 So, I'm leaning toward option (3). Does that sound safe and workable? = Will the WAL files from the stand-in work with the old warm standby? Did I = miss any good alternatives? =20 Thanks, =20 -Kevin =20