Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 2783C9FB387 for ; Tue, 31 Oct 2006 05:42:15 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 10921-05 for ; Tue, 31 Oct 2006 05:42:11 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.171]) by postgresql.org (Postfix) with ESMTP id D839D9F9F96 for ; Tue, 31 Oct 2006 05:42:10 -0400 (AST) Received: by ug-out-1314.google.com with SMTP id 30so1214100ugc for ; Tue, 31 Oct 2006 01:42:09 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=qa/iSfYJeFpU0tkH/65SdRticaNWcXv7L9PG0+xwa2ukZXATzBlN6fjE9h+laE06/qbs3nDBKWnr51Sf4z4NZSB1Ts9YNuvuz3Js18HK5Ff6Hhi/md0L/BA3iLGawtilvWfXf8gRSjkrQ6yQSf2dPgnTmYHMsMOmxNObu3Fhtf8= Received: by 10.67.105.19 with SMTP id h19mr5745941ugm; Tue, 31 Oct 2006 01:42:09 -0800 (PST) Received: by 10.67.26.19 with HTTP; Tue, 31 Oct 2006 01:42:08 -0800 (PST) Message-ID: <82e1a9bd0610310142s22ce7d1ahdb7ccd5357a18b65@mail.gmail.com> Date: Tue, 31 Oct 2006 15:12:08 +0530 From: "Moiz Kothari" To: "Shoaib Mir" Subject: Re: 100% failover + replication solution Cc: "Andrew Sullivan" , pgsql-admin@postgresql.org In-Reply-To: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_3716_3599128.1162287728751" References: <82e1a9bd0610300147m7e214893ue1306f1637bb489f@mail.gmail.com> <7CA68A94-ED1E-49C9-ACF2-2A34936333DC@vanilla.net> <82e1a9bd0610300253y1c999f31k5ba6ac086bcfb802@mail.gmail.com> <82e1a9bd0610300511u35a3e156u22b3c600c69edd24@mail.gmail.com> <20061030133806.GA16730@phlogiston.dyndns.org> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200610/218 X-Sequence-Number: 23521 ------=_Part_3716_3599128.1162287728751 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Content-Disposition: inline Shoaib, This sounds really like what i need, but again 8.2 is in beta. Just one question, i dunno if i am thinking in right direction, but is there anyway of finding out the END XID, transaction id and OID of last archived WAL log applied to the database. I was thinking if i can get that value then i can reset XLOGs using pg_resetxlog to that point and then start applying the new WAL logs. Am i thinking it correctly or there is some flaw here. Also if i am thinking it right, then how can i find the details i asked above. Regards, Moiz Kothari On 10/30/06, Shoaib Mir wrote: > > Hi Moiz, > > This might help you :) --> http://developer.postgresql.org/pgdocs/postgres/warm-standby.html > > > Thanks, > ------- > Shoaib Mir > EnterpriseDB (www.enterprisedb.com) > > On 10/30/06, Andrew Sullivan < ajs@crankycanuck.ca> wrote: > > > > On Mon, Oct 30, 2006 at 06:41:54PM +0530, Moiz Kothari wrote: > > > I agree that PGCluster might be a better option, i dont want to go > > with > > > Slony because of primary key constraints. > > > > I don't know what the "primary key constraints" issue you have is, > > but Slony would be inappropriate for a "100% failover" system anyway: > > you can't know you haven't trapped data on the origin. This is in > > fact true for the WAL shipping you suggested, also. The only way to > > achieve 100% reliable failover today, with guaranteed no data loss, > > is to use a system that commits all the data on two machines at the > > same time in the same transaction. I haven't seen any argument so > > far that there is any such system "out of the box", although with two > > phase commit support available, it would seem that some systems could > > be extended in that direction. > > > > The other answer for all of this is to do it with hardware, but > > that's a shared-disk system, so if your disk blows up, you have a > > problem. Or, if you're using the operating system of people who > > don't know how fsck works. I don't know anyone who has that problem; > > certainly not any vendors whose name starts with 'I' and ends with > > 'M'. > > > > > 1) It might slow down the process a bit. as confirmation happens after > > > > > transaction gets comitted to all the nodes. > > > > Anyone who tells you that you can have completely reliable data > > replication with no performance hit is trying to sell you a bridge in > > Brooklyn. If you want reliable data replication that guarantees you > > can have automatic failover, you are going to pay for it somehow; the > > question is which compromise you want to make. That seems to be > > something you'll need to decide. > > > > > 2) Its difficult to convince, as it is an external project and if > > support > > > for the same stops or future versions of postgres does not work, it > > might be > > > a problem. > > > > If you have this problem, probably free software isn't for you. > > PostgreSQL is a modular system, and people use different components > > together in deployed systems. This happens to be true of commercial > > offerings too (if not, you could buy the cheapest version of, say, > > Oracle and get RAC in the bargain), but they _sell_ it to you as > > though it were one big package. To the extent your managers don't > > understand this, you're always going to have a problem using free > > software. > > > > A > > -- > > Andrew Sullivan | ajs@crankycanuck.ca > > In the future this spectacle of the middle classes shocking the avant- > > garde will probably become the textbook definition of Postmodernism. > > --Brad Holland > > > > ---------------------------(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 > > > > ------=_Part_3716_3599128.1162287728751 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Shoaib,

This sounds really like what i need, but again 8.2 is in beta.

Just one question, i dunno if i am thinking in right direction, but is there anyway of finding out the END XID, transaction id and OID of last archived WAL log applied to the database. I was thinking if i can get that value then i can reset XLOGs using pg_resetxlog to that point and then start applying the new WAL logs. Am i thinking it correctly or there is some flaw here.

Also if i am thinking it right, then how can i find the details i asked above.

Regards,
Moiz Kothari

On 10/30/06, Shoaib Mir < shoaibmir@gmail.com> wrote:
Hi Moiz,

This might help you :) --> http://developer.postgresql.org/pgdocs/postgres/warm-standby.html

Thanks,
-------
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

On 10/30/06, Andrew Sullivan < ajs@crankycanuck.ca> wrote:
On Mon, Oct 30, 2006 at 06:41:54PM +0530, Moiz Kothari wrote:
> I agree that PGCluster might be a better option, i dont want to go with
> Slony because of primary key constraints.

I don't know what the "primary key constraints" issue you have is,
but Slony would be inappropriate for a "100% failover" system anyway:
you can't know you haven't trapped data on the origin.  This is in
fact true for the WAL shipping you suggested, also.  The only way to
achieve 100% reliable failover today, with guaranteed no data loss,
is to use a system that commits all the data on two machines at the
same time in the same transaction.  I haven't seen any argument so
far that there is any such system "out of the box", although with two
phase commit support available, it would seem that some systems could
be extended in that direction.

The other answer for all of this is to do it with hardware, but
that's a shared-disk system, so if your disk blows up, you have a
problem.  Or, if you're using the operating system of people who
don't know how fsck works.  I don't know anyone who has that problem;
certainly not any vendors whose name starts with 'I' and ends with
'M'.

> 1) It might slow down the process a bit. as confirmation happens after
> transaction gets comitted to all the nodes.

Anyone who tells you that you can have completely reliable data
replication with no performance hit is trying to sell you a bridge in
Brooklyn.  If you want reliable data replication that guarantees you
can have automatic failover, you are going to pay for it somehow; the
question is which compromise you want to make.  That seems to be
something you'll need to decide.

> 2) Its difficult to convince, as it is an external project and if support
> for the same stops or future versions of postgres does not work, it might be
> a problem.

If you have this problem, probably free software isn't for you.
PostgreSQL is a modular system, and people use different components
together in deployed systems.  This happens to be true of commercial
offerings too (if not, you could buy the cheapest version of, say,
Oracle and get RAC in the bargain), but they _sell_ it to you as
though it were one big package.  To the extent your managers don't
understand this, you're always going to have a problem using free
software.

A
--
Andrew Sullivan  | ajs@crankycanuck.ca
In the future this spectacle of the middle classes shocking the avant-
garde will probably become the textbook definition of Postmodernism.
                --Brad Holland

---------------------------(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


------=_Part_3716_3599128.1162287728751--