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 925E59DCA39 for ; Wed, 22 Feb 2006 15:55:47 -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 69703-07 for ; Wed, 22 Feb 2006 15:55:47 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from web32512.mail.mud.yahoo.com (web32512.mail.mud.yahoo.com [68.142.207.222]) by postgresql.org (Postfix) with SMTP id CDFE79DCA24 for ; Wed, 22 Feb 2006 15:55:44 -0400 (AST) Received: (qmail 39916 invoked by uid 60001); 22 Feb 2006 19:55:45 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=Message-ID:Received:Date:From:Reply-To:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding; b=hXnceII42MfD+jGUaK4WOmV4g4A3pdxJsri0R51Owjl2xiu/AqHkq0z58UMFIaDxnyLQ8SfLiuc9Q0g3mvP4O2V/N0lQoECnC8t+Sk7PD+fB+tZEiYxyNwrukEKrv9EH4yu/3KULVGt/rhqRGqEM91iIa3B+1ylyyZi+uV8ETYk= ; Message-ID: <20060222195545.39914.qmail@web32512.mail.mud.yahoo.com> Received: from [216.173.173.66] by web32512.mail.mud.yahoo.com via HTTP; Wed, 22 Feb 2006 11:55:45 PST Date: Wed, 22 Feb 2006 11:55:45 -0800 (PST) From: CG Reply-To: cgg007@yahoo.com Subject: Re: WAL recovery To: pgsql-admin@postgresql.org, Jeff Frost , Andy Shellam In-Reply-To: MIME-Version: 1.0 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=1.185 required=5 tests=[AWL=-0.173, DNS_FROM_RFC_ABUSE=0.479, DNS_FROM_RFC_WHOIS=0.879] X-Spam-Score: 1.185 X-Spam-Level: * X-Archive-Number: 200602/268 X-Sequence-Number: 20689 Andy, FWIW, I /think/ the replication scheme you are trying to carry out is implemented in Command Prompt's "Mammoth PostgreSQL Replicator" product. From what I understand, their system will independently copy over and replay "TransactionLog" files (translate: WAL files?) on your slave databases in a continuous stream or in a batch. They describe a few other features in their product that might make the commercial option worthwhile to try. CG --- Jeff Frost wrote: > On Wed, 22 Feb 2006, Andy Shellam wrote: > > > However, if I delete my PG data directory, restore the same base backup > from > > yesterday, and begin recovery, it recovers right up until the last log > file, > > which the previous roll-forward attempt fails. > > The log files were fully archived off the live server to begin with so I > > can't see it's that they've changed or anything. > > > > Is this scenario possible - that you can keep rolling forward over log > files > > as long as necessary, or do you always have to start from a base backup? > > Nothing is changing on the spare, it's literally a sitting duck. > > You must always start with a base backup when you begin the restore otherwise > > the system does not know where to begin the recovery. What I have done in > the > past on a similar system is keep the secondary DB shutdown and make base > backups as often as possible so the recovery time is suitably short when the > secondary is needed. Also a good idea to do test recovers on occassion to > make sure you don't have any corrupt WAL files. If you use rsync, then you > can make the base backup pretty quickly depending on how much of your DB > changes. > > -- > Jeff Frost, Owner > Frost Consulting, LLC http://www.frostconsultingllc.com/ > Phone: 650-780-7908 FAX: 650-649-1954 > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com