Re: A Replication Idea

That would be "Application Aware" category of HA.
The challenge is can you make it "Transparent". That would be
Distributed.

Vic Cekvenich wrote:

> Just for caching in Java we use PoolMan from CodeStudio.com.
>
> The way I would do replicaton is have 1 RW server and many RO servers.
> When you select, you select from "local" RO DB server. When you update,
> you update the RW server. If you must have latest data, you go to the 1
> RW server.
> The RW server's tables have a flag, that indicates it needs to
> replicated to local.
> ANd there is a lazy, delayed process that reads from RW row by row, and
> after each "local" RO server commits, it updates the flag. On to the
> next row.
>
> VIc
>
> Peter Darley wrote:
>
> > Orion,
> > How would it handle functions, which could potentially modify data, even
> > from a select statement?
> > Thanks,
> > Peter Darley
> >
> > -----Original Message-----
> > From: pgsql-general-owner(at)postgresql(dot)org
> > [mailto:pgsql-general-owner(at)postgresql(dot)org]On Behalf Of Orion Henry
> > Sent: Tuesday, February 19, 2002 10:12 AM
> > To: pgsql-general(at)postgresql(dot)org
> > Subject: [GENERAL] A Replication Idea
> >
> >
> >
> > I've been thinking about replication and wanted to throw out an idea to see
> > how fast it gets torn apart. I'm sure the problem can't be this easy but I
> > can't think of why.
> >
> > Ok... Let's say you have two fresh databases, both empty. You set up a
> > postgres proxy for them. The proxy works like this:
> >
> > It listens on port 5432.
> > It pools connections to both real databases.
> > It is very simple just forwarding requests and responses back and
> > forth between client and server. A client can connect to
> > the proxy and not be able to tell that it is not an actual
> > postgres database.
> > When connections are made to it, it proxys connections to both
> > back-end databases.
> > If an insert/update/delete/DDL command comes, it forwards it to both
> > machines.
> > If a query comes down the line it forwards it to one machine or the
> > other.
> > If one of the machines goes offline or is not responding the proxy
> > queues up all update transactions intended for it and stops
> > forwarding queries to it until it comes back online and all
> > queued transactions have been committed.
> > A new machine can be inserted to the cluster. When the proxy is
> > alerted to this, it's first communication would be to
> > pgdumpall() one of the functional databases and pipe it to
> > the new one. At that moment, it is considered an
> > unreachable database and all update transactions are queued
> > for when the dump/rebuild is complete.
> > If a machine dies in catastrophic failure it can be removed from the
> > cluster, and once the machine is fixed, re-inserted as per
> > above.
> > If there were some SQL command for determining the load a machine
> > is experiencing the proxy could intelligently balance the
> > load to the machines in the cluster that can handle it.
> > If the proxy were to fail, clients could safely connect to one of
> > the back end databases in read-only mode until the proxy
> > came back up.
> > The proxy would store a log of incomplete transactions in some kind
> > of presistant storage for all the databases it's connected
> > to, so should it die, it can resume right where it left off
> > assuming the log is intact.
> >
> > With the proxy set up like this you could connect to it as though it were a
> > database, upload your current data and schema and get most all the benifits
> > of clustering.
> >
> > With this setup could achieve load balancing, fail-over, master-master
> > replication, master-slave replication, hot swap servers, dynamic addition
> > and removal of servers and HA-like clustering. The only thing it does not
> > do is partition data across servers. The only assumption I am aware of
> > that I am making is that two identical databases, given the same set of
> > arbitrary transactions will end up being the same. The only single point
> > of failure in this system would be the proxy itself. A modification to the
> > postgres client software could allow automatically fail-over to read-only
> > connections with one of the back-end databases. Also, the proxy could be
> > run on a router or other diskless system. I haven't really thought about
> > it, but it may even be possible to use current HA technology and run a pool
> > of failover proxy's.
> >
> > If the proxy ended up NOT slowing the performance of a standalone,
> > single-system server, it could become the default connection method to
> > PostgreSQL such that a person could do an out-of-the-box install of the
> > database and a year later realize they really wanted a cluster, they could
> > hot-add a server without even restarting the database.
> >
> > So, long story short, I'd like to get people's comments on this. If it
> > won't/can't work or has been tried before, I want to hear about it before I
> > start coding. I find it hard to believe that a replication/clusterings
> > solution could be this easy to implement but I can't think of why this
> > would not work.
> >
> > Orion
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 6: Have you searched our list archives?
> >
> > http://archives.postgresql.org
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> > http://www.postgresql.org/users-lounge/docs/faq.html
> >
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

--
-------------------------------------------------------------------------
Medi Montaseri medi(at)CyberShell(dot)com
Unix Distributed Systems Engineer HTTP://www.CyberShell.com
CyberShell Engineering
-------------------------------------------------------------------------