Re: Feature Request for 7.5

>> I have been looking into how to ensure that synchronous replication, etc.
>> could best be implimented. To date, I see only two options: incorporate
>> the replication code into the database backend or have a separate "proxy"
>> which handles the replication.
>
> There are many problems with a "proxy" solution. One is that you really
> don't know if a statement does modify the database or not. A SELECT for
> example can call a user defined function somewhere and that can do
> whatever the programmer likes it to do. So you would have to "replicate"
> all that too. Granted, you can exclude this type of database usage from
> your supported list.
>
> Next you don't have control over sequence allocation. Every application
> that uses sequence allocated ID's is in danger, because they are not
> blocking, you cannot force the order of assignments and they don't roll
> back either.
>
> And you get into deadlock problems if you don't guarantee that your
> proxy uses the same order to access all databases. And you cannot
> guarantee that if your proxy tries to do it parallel. So it has to do
> the queries against all databases one by one, that doesn't scale well.
>
> The last thing (I mention for now) is that I cannot imagine any way that
> such proxy code allows for a new member to join without stopping the
> whole application, creating an identical copy of one member
> (dump+restore) and continue. So it is impossible to build 24*7 support
> that way.
>
> No, separate proxy code doesn't strike me as the superior solution.

I incorporate code (triggers/plpgsql) into the backend that creates a
serialized audit trail of changes, then have an external proxy running which
queries the audit trail and applies the changes. I don't have enough
experience with the subject to debate it much, but I took this approach
because:

- It's much easier for the backend to keep straight the changes, for all the
reasons mentioned above.

- It seemed to me to be much easier to write the actual replication stuff as
a C++ app on top of libpq than to try to stuff that into the backend.

Note that I only need master/slave (take that LA County Commission)
replication, and I don't really need to worry about scalability because I'll
only have one or two slaves.

--
Scott Ribe
scott_ribe(at)killerbytes(dot)com
http://www.killerbytes.com/
(303) 665-7007 voice