Re: Synchronising/mirroring databases

On Wed, Nov 06, 2002 at 06:30:23PM +0100, Anand Buddhdev wrote:
> I want to be able to keep 3 geographically separate PGSQL databases
> synchronised. I looked at rserv, which is distributed with the later
> versions, and it seems to work well. However, unless I read the
> documentation wrong, it will only work with one master and one slave.

Right. If you want multi-slave, you need to buy the commercial
version from PostgreSQL, Inc.

> I am also not sure how resilient rserv is to network outages in the middle
> of a synchronisation process. I attempted to interrupt one rserv sync
> process, and noticed that the remote slave database was not modified,
> and later, when the network was available again, it synced correctly. But
> one such test is no guarantee about its resilience. Could anyone shed
> more light on rserv's reliability?

The commercial version is pretty reliable. We use it. We get quite
good sync times, and can recover from several hours' interruption if
we need to (we have a largish volume of data we handle in that time).

> I was thinking of alternative ideas, and I came up with a simple solution
> myself, but I don't know if it's the right way to go about it. My
> solution involves generating insert/delete/update queries and writing
> them out to files each time the master server is changed. And then have
> a queue runner process collect these queries, and execute them remotely
> on each slave database using the psql tool. Thus the slaves would be

This is more or less what rserv itself does. The data is kept inside
the database itself. The advantage of this approach is that, in a
_really bad_ case, you can actually move the master. (I have,
happily, never had to do that.)

A
--
----
Andrew Sullivan 204-4141 Yonge Street
Liberty RMS Toronto, Ontario Canada
<andrew(at)libertyrms(dot)info> M2P 2A8
+1 416 646 3304 x110