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.
