X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 7F4479DC93F for ; Fri, 3 Mar 2006 13:55:09 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 17771-10 for ; Fri, 3 Mar 2006 13:55:08 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey- Received: from smtp.nildram.co.uk (smtp.nildram.co.uk [195.112.4.54]) by postgresql.org (Postfix) with ESMTP id 842849DC9A9 for ; Fri, 3 Mar 2006 13:55:06 -0400 (AST) Received: from [192.168.0.5] (unknown [84.12.26.47]) by smtp.nildram.co.uk (Postfix) with ESMTP id 337A825A210; Fri, 3 Mar 2006 17:54:58 +0000 (GMT) Subject: Re: PITR as Online Backup Solution From: Simon Riggs To: "Thomas F. O'Connell" Cc: PGSQL Admin In-Reply-To: <38B613F1-5A7E-469E-AC5D-B68D24C5A307@sitening.com> References: <38B613F1-5A7E-469E-AC5D-B68D24C5A307@sitening.com> Content-Type: text/plain Date: Fri, 03 Mar 2006 17:54:52 +0000 Message-Id: <1141408492.27729.505.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.2.3 (2.2.3-2.fc4) Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.091 required=5 tests=[AWL=0.091] X-Spam-Score: 0.091 X-Spam-Level: X-Archive-Number: 200603/40 X-Sequence-Number: 20797 On Thu, 2006-03-02 at 16:38 -0600, Thomas F. O'Connell wrote: > I'm administering a database that is not immediately a good candidate > for replication via Slony. As an interim solution, I'd like to give > PITR a shot. The primary goal is to have a failover scenario that > allows for recovery within a window that's much smaller than it would > be if the only option were the output of the previous night's > pg_dump, and the need has arisen jointly with the fact that pg_dump > has become an unwieldy process on this database. > > Here's the main question: Is it possible to replay logs continuously > against a database that has already been recovered? The documentation > only covers the scenario of full recovery from scratch, and I'm > wondering if this is because this is the only scenario possible with > the current level of PITR functionality. > Ideally, I'd be able to take a base backup of a production system, > copy it to a remote system, which is also the repository for segment > files generated by archive_command, and complete the recovery process > outlined in the docs. From that point, it would make sense to me that > I should be able to continuously replay WAL files against the new > database (possibly as soon as archive_command generates a new one) > without having to purge my data directory. Is that a reasonable > assumption? Yes, it was designed to be able to do this. > If so, then I need to know more about which steps to use from the > recovery scenario presented in the docs and how to identify the state > in which a given segment file exists. > For instance, it's not > immediately clear from the docs what happens to the segment files > after recovery_command runs during the recovery scenario. It says > those segments are copied from the archive directory, but then what? > Are they recycled as in a basic postgres installation? They overwrite each other, thus avoiding a build up of logs. It is designed to support "infinite" recovery. > Also, if this system eventually works as I expect it might be able > to, would there ever be a reason to redo it from scratch (i.e., is > one base backup sufficient ad infinitum)? As long as you've read the documented caveats there are no design limitations...but I would restart from a base backup weekly, to be sure. It's all about certainty after all. Best Regards, Simon Riggs