X-Original-To: pgsql-sql@postgresql.org Received: from spampd.localdomain (postgresql.org [64.49.215.8]) by postgresql.org (Postfix) with ESMTP id 36C8C47633A for ; Mon, 5 May 2003 15:57:16 -0400 (EDT) Received: from dbdevel.sunwave.net (h64-141-16-15.sunwave.net [64.141.16.15]) by postgresql.org (Postfix) with ESMTP id 733EE475E82 for ; Mon, 5 May 2003 15:57:15 -0400 (EDT) Received: from localhost (localhost.localdomain [127.0.0.1]) by dbdevel.sunwave.net (Postfix) with ESMTP id 8A159178002 for ; Mon, 5 May 2003 15:57:16 -0400 (EDT) From: Michael A Nachbaur To: Subject: pgsql Replication Proxy (was Re: Replication for a large database) Date: Mon, 5 May 2003 12:57:16 -0700 User-Agent: KMail/1.5 References: <10976.65.102.128.233.1052151419.squirrel@fordparts.com> <200305051226.35780.mike@nachbaur.com> In-Reply-To: <200305051226.35780.mike@nachbaur.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200305051257.16393.mike@nachbaur.com> X-Spam-Status: No, hits=-31.7 required=5.0 tests=BAYES_10,EMAIL_ATTRIBUTION,IN_REP_TO,QUOTED_EMAIL_TEXT, REFERENCES,REPLY_WITH_QUOTES,USER_AGENT autolearn=ham version=2.50 X-Spam-Level: X-Spam-Checker-Version: SpamAssassin 2.50 (1.173-2003-02-20-exp) X-Archive-Number: 200305/66 X-Sequence-Number: 13231 I've thought some more about this, and I want to pass this idea past you guys. What do you think about a replication proxy, essentially a daemon that sits between a PostgreSQL client and server. Every single SQL query, transaction statement, etc that the proxy recieves it repeats back to all the database servers. In this way, if a back-end database server goes down queries will continue unabated (except the downed server won't recieve updates). Basically, the proxy server could intercept these queries and place them in a stack (on a per-database basis) and when every server in the queue acknowledges the update, the query is removed from the stack. Each database server can have their own position in the stack, so if servers A and B successfully run a query, but C doesn't (e.g. it requires human intervention), C is removed from the list of acceptable servers but A and B can keep moving through the queue. What do you think? Also, should this discussion be moved to another mailing list? On Monday 05 May 2003 12:26 pm, Michael A Nachbaur wrote: > I have thought about this. The problem I come into is data consistancy. I > have at least 8 different processes that harvest data, and an intranet > website that can also manipulate the database (to assign customers to > different packages, re-assign modems to different customers, etc). Trying > to maintain consistancy across the entire application would be such a > nightmare, I don't want to think about it. > > If I go with a centralized middleware server that manages all database > access, then I could perhaps do that in there...and then I could use > transactions on both databases, and if either transaction fails then I'll > roll back the other. But this would make my entire framework very rigid.