Thread: Synchronising/mirroring databases
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. 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? Is anyone doing database replication using other means? How do they achieve it? 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 updated asynchronously, and that's not a problem, as long as they do get updated within a few minutes of the master. In case the network was not available when the queue runner was processing the requests, then it can just try later. I would appreciate ideas and suggestions. Thanks, -- Anand Buddhdev http://anand.org
Your description of the solution sounds very much like rserv, except that rsev keeps the changes in a table in the database ... are you concerned about the data being inside PG for replication? We are looking for an HA solution that can handle synchronization of the failover and have been looking at rserv and its commercial cousin erserver. I am also looking for feedback from users on either approach. Let me know if you hear anything :-) Charlie 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. > >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? > >Is anyone doing database replication using other means? How do they >achieve it? > >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 >updated asynchronously, and that's not a problem, as long as they do >get updated within a few minutes of the master. In case the network >was not available when the queue runner was processing the requests, >then it can just try later. > >I would appreciate ideas and suggestions. > >Thanks, > > > -- Charles H. Woloszynski ClearMetrix, Inc. 115 Research Drive Bethlehem, PA 18015 tel: 610-419-2210 x400 fax: 240-371-3256 web: www.clearmetrix.com
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@libertyrms.info> M2P 2A8
+1 416 646 3304 x110