Re: Database Mirroring Solution - Mailing list pgsql-general

 >>> We basicaly need to run a database servers in 2 different
 >>> towns. Now there will be update's and selects and both need
 >>> to be in sync with each other. Aswell as if / when database in
 >>> town 1 goes down ... we need to be able to switch to the database
 >>> in town 2 for emergency purposes. We cannot use just one master
 >>> as the connectivity between the two towns isn't fast enough for
 >>> the amount of users that will be viewing data through the connection.

There are two ways to have the replication happen -

1. Have a change made at site1 then replicate it at site2 before
committing the change and allowing the user to carry on with something else.

2. Have a change made at site1 and commit it so the user can move on to
something else, then duplicate the change at site2 in the background.

If you want the quicker user response from the second way then you will
need to separate the data in a way that eliminates update conflicts as
well as sequence number conflicts.

The first way will get delays (increasing as traffic increases) in
committing as the change is replicated in the other office, larger
delays will cause rollbacks as I would expect failures in the
replication when the delays are too long.

If your current connection is not fast enough to have both offices
connect to the one database then replicating both ways in real time will
only produce double the traffic. Which means you will want to look at
synchronising in the background and have varying delays between changes
in site1 showing up in site2.

example:-
Lets say you have 5 users in each office, currently if the database is
in one office then 5 users will connect to it through the local network
(no issues there) and 5 will connect through your slow external network.

If you want all changes to reflect in both offices then the changes made
  in both offices will be sent to the other office, so you will
effectively have 10 users working through your slow external connection.

If your external network is insufficient for the 5 users from the other
office then synchronising both ways will double the traffic and not get
the desired result.



Excess other traffic can also interfere so you may want to look into
Quality Of Service between the 2 sites to ensure the database traffic
always gets priority over any other traffic.


 >
 > Thanks for the advice. I will keep it in mind. I also just wanted to
 > make sure
 > that I havent missed something or some solution that is already out
 > there to
 > cater for my needs.

PGCluster is a multi master replication system, but I don't think it
will offer a better solution for you.


--

Shane Ambler
pgSQL@007Marketing.com

Get Sheeky @ http://Sheeky.Biz