X-Original-To: pgsql-hackers-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id DFD6ED1E93A for ; Fri, 2 Apr 2004 04:33:27 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 39249-07 for ; Fri, 2 Apr 2004 00:33:25 -0400 (AST) Received: from smtp.istop.com (dci.doncaster.on.ca [66.11.168.194]) by svr1.postgresql.org (Postfix) with ESMTP id C9067D1E8AC for ; Fri, 2 Apr 2004 00:33:24 -0400 (AST) Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162]) by smtp.istop.com (Postfix) with ESMTP id 3660217C464; Thu, 1 Apr 2004 23:33:24 -0500 (EST) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1B9GMt-0005PV-00; Thu, 01 Apr 2004 23:33:23 -0500 To: pgsql-hackers@postgresql.org Subject: Re: PITR for replication? References: <5674.1080750914@sss.pgh.pa.us> <406B6EDB.5040406@familyhealth.com.au> <1080867508.29882.8.camel@localhost.localdomain> In-Reply-To: <1080867508.29882.8.camel@localhost.localdomain> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 01 Apr 2004 23:33:23 -0500 Message-ID: <87isgjov70.fsf@stark.xeocode.com> Lines: 36 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200404/17 X-Sequence-Number: 51939 "J. Andrew Rogers" writes: > I may be completely missing the point here, but it looks to me as though > the PITR archival mechanism is also most of a native replication > facility. Is there anyone reason this couldn't be extended to > replication, and if so, is anyone planning on using it as such? > > My memory is fuzzy on this point, but I seem to recall that this is > (was?) how replication is more-or-less done for many of the big > commercial RDBMS. Well "replication" is one of those things that means different things to different people. IMHO, this is one of the simpler, more reliable, mechanisms and would be nice to have. And you're right that it shouldn't require much more work, in fact it's probably easier than a lot of other cases that PITR requires. For a long time Oracle has supported this mechanism for running warm standby databases. Basically you maintain a second database and every time an archived log is finished you ship it over and immediately "restore" it on the standby machine. Whenever you have a failure you can quickly fail over to the standby database which is all ready to go and up-to-date within minutes of your failure. Since 8i Oracle has also supported a more advanced version where you can open the standby database in read-only mode. This makes it useful for running batch reports and so on. In postgres this wouldn't work nearly so well since even read-only queries require write access to the database in postgres. Perhaps it's not nearly so urgent since running long-running batch queries on a busy system in postgres doesn't pose all the same dangers it does in Oracle (the dreaded "snapshot too old" error) -- though there are other analogous dangers (fsm settings being too small unexpectedly). -- greg