Re: A Replication Idea - Mailing list pgsql-general

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@postgresql.org
> [mailto:pgsql-general-owner@postgresql.org]On Behalf Of Orion Henry
> Sent: Tuesday, February 19, 2002 10:12 AM
> To: pgsql-general@postgresql.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
>