Thread: Database Mirroring Solution
Hi I have done some research and it seems there are no active mirroring solutions for postgresql 8 and above. I did see Slony but I need Master to Master functionality and not only Master to Slave/'s. Is anyone aware of any mirroring solutions for postgres ? TIA Gideon
Gideon wrote: > Hi > > I have done some research and it seems there are no active mirroring > solutions for postgresql 8 and above. I did see Slony but I need > Master to Master functionality and not only Master to Slave/'s. That's not "mirroring", it's known as "multi-master replication". Mirroring is generally considered single-master => single-slave. Or duplicate queries perhaps. >Is > anyone aware of any mirroring solutions for postgres ? I don't know of any plug-and-play system either. Some support for two-phase commit was added recently and I believe Slony might be supporting multi-master in its next version. Having said that, I'm not aware of any generic solution (for any RDBMS) that can handle all the permutations of peoples requirements. It might be that you can assemble something that meets your needs - can you share any details about how/why you intend to use this? -- Richard Huxton Archonet Ltd
On Fri, 10 Nov 2006 13:24:18 +0200 Gideon <gideondebian@isogo.co.za> wrote: > Is anyone aware of any mirroring > solutions for > postgres ? Have yoy tried HeartBeat + DRBD for an HA solution? Enrico -- If Bill Gates had a penny for everytime Windows crashed,he'd be a multi-billionaire by now .......oh look, he already is!!!! scotty@linuxtime.it - Skype:sscotty71 http://www.linuxtime.it/enricopirozzi
Thanks for the quick reply. 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. (The fastest affordable connection here for this purpose is round about 256k.) Regards Gideon Richard Huxton wrote: > Gideon wrote: >> Hi >> >> I have done some research and it seems there are no active mirroring >> solutions for postgresql 8 and above. I did see Slony but I need >> Master to Master functionality and not only Master to Slave/'s. > > That's not "mirroring", it's known as "multi-master replication". > Mirroring is generally considered single-master => single-slave. Or > duplicate queries perhaps. > > >Is > > anyone aware of any mirroring solutions for postgres ? > > I don't know of any plug-and-play system either. Some support for > two-phase commit was added recently and I believe Slony might be > supporting multi-master in its next version. > > Having said that, I'm not aware of any generic solution (for any > RDBMS) that can handle all the permutations of peoples requirements. > It might be that you can assemble something that meets your needs - > can you share any details about how/why you intend to use this? >
On Fri, 10 Nov 2006 13:42:14 +0200 Gideon <gideondebian@isogo.co.za> wrote: > We basicaly need to run a database servers in 2 different > towns. I suppose you can't use an HA solution. :( Regards Enrico -- If Bill Gates had a penny for everytime Windows crashed,he'd be a multi-billionaire by now .......oh look, he already is!!!! scotty@linuxtime.it - Skype:sscotty71 http://www.linuxtime.it/enricopirozzi
Gideon wrote: > Thanks for the quick reply. No problem, but don't forget to cc: the list. > 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. > > (The fastest affordable connection here for this purpose is round about > 256k.) A slow link is going to cause problems with most replication solutions anyway. Well, there are two options that I can think of: Option 1: Run Slony replicating from town1 to town2. Run PgPool connection pooling at town2 and route any update queries to town1. Cope with the delay on updates propagating. Option 2: Use table partitioning to split e.g. customers by town. Use slony to replicate customers_town1 to town2 and customers_town2 to town1. Users can't update data "owned" by the other town - enforce this with suitable GRANT/REVOKE. Either of those any use? -- Richard Huxton Archonet Ltd
Richard Huxton wrote: > Gideon wrote: >> Thanks for the quick reply. > > No problem, but don't forget to cc: the list. > >> 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. >> >> (The fastest affordable connection here for this purpose is round about >> 256k.) > > A slow link is going to cause problems with most replication solutions > anyway. Well, there are two options that I can think of: > > Option 1: > Run Slony replicating from town1 to town2. > Run PgPool connection pooling at town2 and route any update queries to > town1. > Cope with the delay on updates propagating. > Option 1 Seems like a possibility I do some more research into this... Thanks a lot. > Option 2: > Use table partitioning to split e.g. customers by town. > Use slony to replicate customers_town1 to town2 and customers_town2 to > town1. > Users can't update data "owned" by the other town - enforce this with > suitable GRANT/REVOKE. > Option 2: Doesnt quite seem like a possibility as users in both towns must be able to work on the same records and update the same information. > Either of those any use? 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.
>>> 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
gideondebian@isogo.co.za (Gideon) wrote:
> Thanks for the quick reply.
>
> 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.
>
> (The fastest affordable connection here for this purpose is round about
> 256k.)
There was a "Slony-II" project that intended to provide multimaster
support; there were a number of performance and functionality
pathologies that led to most of the efforts being dropped. It's worth
noting that nodes would have been required to be on a very fast local
LAN; you would NOT be running multimaster across a slow link.
Multimaster replication is not likely to work over a slow link, unless
you are willing to take on considerable risks of:
a) Loss of data integrity and
b) Potential for introduction of conflicting updates.
Multimaster replication tends to lead to a pretty big "performance
hit."
If on a fast network, the "moral slowdown" might be on the order of
the difference between CPU speed and network speed, namely that peak
performance falls by a factor of, say, 80 (that being the ratio
between 1 GHz, a common CPU speed, and 100 mbits/s).
If you can only get 256Kbits/s, then you could expect a further
400-fold slowdown. (Which amounts to being ~30,000 times as slow as a
single node...)
That's "back of the napkin" estimation, but it's the sort of slowdown
that attempts to apply general purpose multimaster replication leads
to...
--
output = ("cbbrowne" "@" "gmail.com")
http://cbbrowne.com/info/lsf.html
Howe's Law:
Everyone has a scheme that will not work.
If the delay between sites is unacceptable, then you most certainly do not want a synchronous replication system, because then everybody goes slow for all updates. So you'll need to figure out how you plan to deal with conflicts when each site updates the same row and the conflict is discovered after commit but during the asynchronous merging. I don't know what your usage model is, but 256k isn't all that bad for a lot of stuff, if the latency is low. Then again, it *is* pretty bad for a lot of other stuff. :) On Nov 10, 2006, at 3:42 AM, Gideon wrote: > Thanks for the quick reply. > > 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. > > (The fastest affordable connection here for this purpose is round > about > 256k.) > > Regards > Gideon > > Richard Huxton wrote: >> Gideon wrote: >>> Hi >>> >>> I have done some research and it seems there are no active mirroring >>> solutions for postgresql 8 and above. I did see Slony but I need >>> Master to Master functionality and not only Master to Slave/'s. >> >> That's not "mirroring", it's known as "multi-master replication". >> Mirroring is generally considered single-master => single-slave. >> Or duplicate queries perhaps. >> >> >Is >> > anyone aware of any mirroring solutions for postgres ? >> >> I don't know of any plug-and-play system either. Some support for >> two-phase commit was added recently and I believe Slony might be >> supporting multi-master in its next version. >> >> Having said that, I'm not aware of any generic solution (for any >> RDBMS) that can handle all the permutations of peoples >> requirements. It might be that you can assemble something that >> meets your needs - can you share any details about how/why you >> intend to use this? >> > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that > your > message can get through to the mailing list cleanly