Re: pgsql Replication Proxy (was Re: Replication for a - Mailing list pgsql-sql

LOL!  Depending on how much FUD I can throw at the guys higher up in the food 
chain at my office, I might be able to get some budget space to develop 
something like this.  There are some significant technical hurdles I have to 
overcome, but I think it's doable.  The analogy I came up with is SCSI RAID 
for databases.  You can rip a database server out, and the overall system 
will still function...toss it back in, and updates will still happen.  I 
would also like to be able to throw a fresh database in place and have it 
mirror the existing database servers in the background so you don't have to 
go through the complicated procedure of dumping/restoring the database 
servers by hand.

Re: FIFO, yeah, I realized that after I sent the message.

Does anyone have any ideas for me on this?  I think it might make sense to use 
PostgreSQL as the storage mechanism for the proxy server, but that sort of 
defeats the purpose of having a replication system.  Maybe spread can be used 
to distribute the messages to different servers, but I'm not too familiar 
with it.

Also, one final note, I'm a Perl programmer, so anything I build will be 
written in that.  If anyone has objections, let me know and maybe we could 
work together on something.

On Tuesday 06 May 2003 09:28 am, Diehl, Jeffrey wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly