Re: pgsql Replication Proxy (was Re: Replication for a

I love this idea.  The proxy could return immediately instead of making my
program block on update.

One note, though.  Instead of a stack, you need a FIFO.  For example:

delete from sometable where field=value;
insert into sometable (field) values (value1);
insert into sometable (field) values (value2);
....


This code breaks in a stack and only works in a fifo.  Minor point, though.

So do we have a volunteer to write such a tool?  <grin>

Mike Diehl.

-----Original Message-----
From: Michael A Nachbaur [mailto:mike@nachbaur.com]
Sent: Monday, May 05, 2003 1:57 PM
To: pgsql-sql@postgresql.org
Subject: pgsql Replication Proxy (was Re: [SQL] Replication for a large
database)


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.


---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org