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

On Tuesday 06 May 2003 12:10 pm, Wei Weng wrote:
> Michael:
>
> 1: It will probably need a messaging system that can properly
> register/unregister a backend database system. a SIP like syntax comes
> into mind. (or a XML message, for the ease of future expansion)

I'm thinking Spread would be a good idea here.  To tell you the truth, I 
haven't had to deal too much with high-speed guarenteed messaging in a looong 
time, so I'm out of touch with this.

> 2: What do you mean by "per-database basis"? Database as the concept of
> database schema? or seperate physical database server?

Per database schema.  This is primarily to meet my requirements, but it makes 
sense for other scenarios.  Essentially, I have a production database server 
and a development database server.  Both have similar capabilities, and are 
always on (redundant power supplies, UPS, etc).  My development machine will 
have a replicated copy of the production database schema, but I'd also like 
to have a "private" database schema on the same machine that I can do 
development work on, where I can break it without affecting production.

I'd like to eventually do something similar to mod_backhand, where "select" 
statements are sent to a server with the least amount of load, so slower 
machines can still take part in a proxy setup without reducing performance 
(or hammering the poor little box).  One could also give a server a handicap 
so it has more capacity for other uses (e.g. my development server).

> 3: How does the proxy server know that a backend server failed in commit?

I'm thinking of having a queue of statements that the proxy server maintains.  
Each server will have it's own pointer that refers to the location in the 
queue they are at.  Each statement is then sent to the database server 
sequentially, and if a statement fails on a given server, its removed from 
the list list of valid servers, and it's index remains at the statement that 
failed.

This is really starting to sound complicated...

> Wei
>
> Michael A Nachbaur wrote:
> >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.