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

If someone hasn't mentioned it already, look at:
http://www.objectweb.org/c-jdbc/index.html

From their description:
C-JDBC provides a flexible architecture that allows you to achieve
scalability, high availability and failover with your database tiers. C-JDBC
instantiates the concept of RAIDb : Redundant Array of Inexpensive Databases.
The database is distributed and replicated among several nodes and C-JDBC
load balance the queries between these nodes.

Yes, it's open source.

Meanwhile I'll check out DBI::Multiplex

Cheers,
Steve


On Tuesday 06 May 2003 10:45 am, Diehl, Jeffrey wrote:
> You are considering something much more complex/useful than I first
> thought. Cool!
>
> You should really look at DBI::Multiplex.  It has many of the features you
> are looking for.
> I think you could expand upon it though.
>
> I'm also a perl programmer.  If you need any help, I may be able to find
> some time....
>
> Mike
>
> -----Original Message-----
> From: Michael A Nachbaur [mailto:mike@nachbaur.com]
> Sent: Tuesday, May 06, 2003 11:40 AM
> To: Diehl, Jeffrey; pgsql-sql@postgresql.org
> Subject: Re: pgsql Replication Proxy (was Re: [SQL] Replication for a
>
>
> 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
>
> ---------------------------(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