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 A65669DC891 for ; Wed, 15 Mar 2006 06:41:48 -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 75562-01 for ; Wed, 15 Mar 2006 06:41:49 -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 C7DD09DC8E1 for ; Wed, 15 Mar 2006 06:41:44 -0400 (AST) Received: from [192.168.0.5] (unknown [84.12.26.47]) by smtp.nildram.co.uk (Postfix) with ESMTP id 34E41333A2E; Wed, 15 Mar 2006 10:41:30 +0000 (GMT) Subject: Re: PITR as Online Backup Solution From: Simon Riggs To: "Thomas F. O'Connell" Cc: PGSQL Admin In-Reply-To: <7D2D1D2C-B4C8-4317-A9F9-F1B138E627F1@sitening.com> References: <38B613F1-5A7E-469E-AC5D-B68D24C5A307@sitening.com> <1141408492.27729.505.camel@localhost.localdomain> <94A88F1D-9A65-45D4-B410-21EB2D8B9872@sitening.com> <1141466197.27729.561.camel@localhost.localdomain> <7D2D1D2C-B4C8-4317-A9F9-F1B138E627F1@sitening.com> Content-Type: text/plain Date: Wed, 15 Mar 2006 10:41:23 +0000 Message-Id: <1142419283.3495.12.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.094 required=5 tests=[AWL=0.094] X-Spam-Score: 0.094 X-Spam-Level: X-Archive-Number: 200603/123 X-Sequence-Number: 20880 On Tue, 2006-03-14 at 16:41 -0600, Thomas F. O'Connell wrote: > On Mar 4, 2006, at 3:56 AM, Simon Riggs wrote: > > > On Fri, 2006-03-03 at 12:03 -0600, Thomas F. O'Connell wrote: > >> On Mar 3, 2006, at 11:54 AM, Simon Riggs wrote: > >> > >>> On Thu, 2006-03-02 at 16:38 -0600, Thomas F. O'Connell wrote: > >>>> 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. > >> > >> From the docs, I'm having a hard time determining which steps to > >> edit or omit in order to execute this scenario. Is it possible for > >> you (or anyone else on the list) to present an extension of section > >> 23.3.3 >> online.html#BACKUP-PITR-RECOVERY> that covered the continuous replay > >> scenario? I'd be happy to help contribute a patch to the docs once I > >> understand the procedure a bit better. > > > > The place the primary backs up to is the place where the secondary > > restores from, otherwise all procedures are as documented. Neither > > system knows about the other, so its simpler than maybe you think. > > > > The only thing you need is the wait-for-next-file script. > > A few more questions in this thread: > > 1. With the wait-for-next-file script scenario, what happens with > recovery.conf? Does it ever become recovery.done? What happens in the > event of needing to recover? Does the script need a trigger to say, > "Okay, stop waiting. We need to use what we have right now."? Yes. Everything you need is discussed in the admin archives. > 2. Are there any salient details when performing an online backup > from one machine to another? I'm assuming filesystem is an important > consideration since the recovery process is not like the pg_dump > recovery process. Any other gotchas? No Best Regards, Simon Riggs