Обсуждение: PostgreSQL and 2-node failover cluster solutions
Hi, we have 2 PostgreSQL servers running multiple instances and would like to automate failover between them. We store db data on NFS storage on dedicated Netapps. We can currently perform manual failover by dropping the instance on one server and starting in on the other (the init scripts handle bringing virtual IP up/down and the NFS volume is automounted as needed). My question to you: are there any simple, reliable and free cluster/fail-over (whatever you'd like to call it) solutions that you know of to automate the above? I'm prepared to give a bit on the simple and free if necessary, although reliability is pretty important. I have been looking at Kimberlite, but it doesn't look like it supports NFS shared storage, only locally attached. Otherwise it is almost perfect. Many other offerings floating around are either expensive commercial, or overly complex requiring kernel patching and complicated setups. Any suggestions appreciated. Especially if you've already solved this! Regards, Chris
On Fri, Oct 04, 2002 at 12:59:22PM -0700, Chad R. Larson wrote: > At 11:05 AM 10/4/02 , Chris Miles wrote: > >I have been looking at Kimberlite, but it doesn't look like it supports > >NFS shared storage, only locally attached. Otherwise it is almost perfect. > > Their web site shows both NFS and Fibre attached storage being supported. Kimberlite supports failing over NFS mounted filesystems, but it requires cluster quorum disks to be direct attached. I am talking to the developers about alternatives, but am also seeking any other clustering options. Regards, Chris.
On Fri, Oct 04, 2002 at 07:05:28PM +0100, Chris Miles wrote: > Hi, we have 2 PostgreSQL servers running multiple instances and would like > to automate failover between them. We store db data on NFS storage on > dedicated Netapps. We can currently perform manual failover by dropping > the instance on one server and starting in on the other (the init scripts > handle bringing virtual IP up/down and the NFS volume is automounted as > needed). Uh; why would you want to do that? Databases work much better with local storage than over NFS. > My question to you: are there any simple, reliable and free cluster/fail-over > (whatever you'd like to call it) solutions that you know of to automate > the above? I'm prepared to give a bit on the simple and free if necessary, > although reliability is pretty important. http://www.linux-ha.org/ (heartbeat) > Many other offerings floating around are either expensive commercial, or > overly complex requiring kernel patching and complicated setups. What OS? There are a very large number of linux failover software packages; commercial and free. They all have their pros and cons - I prefer heartbeat because of it's simplicity. (the main drawback of heartbeat is that it does not yet implement service-monitoring - but you can do that outside heartbeat if you want to) -- Ragnar Kjørstad
On Sun, Oct 06, 2002 at 12:47:55AM +0200, Ragnar Kjørstad wrote: > On Fri, Oct 04, 2002 at 07:05:28PM +0100, Chris Miles wrote: > > Hi, we have 2 PostgreSQL servers running multiple instances and would like > > to automate failover between them. We store db data on NFS storage on > > dedicated Netapps. We can currently perform manual failover by dropping > > the instance on one server and starting in on the other (the init scripts > > handle bringing virtual IP up/down and the NFS volume is automounted as > > needed). > > Uh; why would you want to do that? > Databases work much better with local storage than over NFS. They also work very well, if not as good, with dedicated NFS storage, when setup properly. I have built both a large Oracle server on Sun E6500 using dedicated Netapp NFS storage (connected directly with dual Gigabit fibre) and a medium sized pair of PostgreSQL servers using Netapp NFS again. The advantage in the latter case is the ease of database failover between servers. The same could be done with multi-homed scsi disk or SAN but in this case Netapps were available. The speed is more than sufficient. > http://www.linux-ha.org/ (heartbeat) > > What OS? Linux. > There are a very large number of linux failover software packages; > commercial and free. They all have their pros and cons - I prefer > heartbeat because of it's simplicity. (the main drawback of heartbeat is > that it does not yet implement service-monitoring - but you can do that > outside heartbeat if you want to) I have not had a close look at Heartbeat yet. Simplicity is one of my requirements, so I am interested. Could you describe your setup? I presume you are using it to failover PostgreSQL servers? Regards, Chris.
On Sun, Oct 06, 2002 at 02:06:49PM +0100, Chris Miles wrote: > > Uh; why would you want to do that? > > Databases work much better with local storage than over NFS. > > They also work very well, if not as good, with dedicated NFS storage, when > setup properly. I have built both a large Oracle server on Sun E6500 using > dedicated Netapp NFS storage (connected directly with dual Gigabit fibre) > and a medium sized pair of PostgreSQL servers using Netapp NFS again. > > The advantage in the latter case is the ease of database failover between > servers. The same could be done with multi-homed scsi disk or SAN but > in this case Netapps were available. The speed is more than sufficient. Well; if you have a single NetApp then you still have a single point of failure (avoiding that is the whole purpose of failover, right?), and if you have two of them then it's one pretty damn expensive postgresql-server :) Anyway..... > > There are a very large number of linux failover software packages; > > commercial and free. They all have their pros and cons - I prefer > > heartbeat because of it's simplicity. (the main drawback of heartbeat is > > that it does not yet implement service-monitoring - but you can do that > > outside heartbeat if you want to) > > I have not had a close look at Heartbeat yet. Simplicity is one of > my requirements, so I am interested. Could you describe your setup? > I presume you are using it to failover PostgreSQL servers? No, currently we're only using it for failover lvs- and file-servers. The principle is the same though: Heartbeat runs on two servers connected with multiple "heartbeat-channels" (ethernet, serial....). They constantly monitor each other, and if the secondary server looses contact with the primary it will start the "services" locally. In your case the services would be an IP-address and the postgresql-server. You also need some "fencing" to make sure that the two servers don't both start postgresql by accident. This is done with "stonith" (Shoot the other node in the head) - before the secondary server starts postgresql it will cut the power to the primary server to make sure it's not up. That's pretty much it. Be awere that failover doesn't solve any problem in the world though: If postgresql corrupts it's files, both servers are screwed. Also, failover will complicate the setup, and in general a more complicated setup means more downtime (operator-error) - so, make sure you read the documentation and understand how it works (or pay someone to do it for you). -- Ragnar Kjørstad
On Sun, Oct 06, 2002 at 03:21:14PM +0200, Ragnar Kjørstad wrote: > Well; if you have a single NetApp then you still have a single point of > failure (avoiding that is the whole purpose of failover, right?), and if > you have two of them then it's one pretty damn expensive > postgresql-server :) Indeed we run our Netapps as a clustered pair. If you already have the facilities available or can get them for a good price second hand, then I am happy to let the group know that running PostgreSQL servers on Linux and Netapps is not only possible, it is a fast, reliable and stable platform. > Anyway..... > > I presume you are using it to failover PostgreSQL servers? > > No, currently we're only using it for failover lvs- and file-servers. > The principle is the same though: Heartbeat runs on two servers > connected with multiple "heartbeat-channels" (ethernet, serial....). > They constantly monitor each other, and if the secondary server looses > contact with the primary it will start the "services" locally. In your > case the services would be an IP-address and the postgresql-server. > > You also need some "fencing" to make sure that the two servers don't > both start postgresql by accident. This is done with "stonith" (Shoot > the other node in the head) - before the secondary server starts > postgresql it will cut the power to the primary server to make sure it's > not up. > > That's pretty much it. Great, sounds very similar to Kimberlite, and also to our requirements. I will give it a test. > Be awere that failover doesn't solve any problem in the world though: If > postgresql corrupts it's files, both servers are screwed. Also, failover > will complicate the setup, and in general a more complicated setup means > more downtime (operator-error) - so, make sure you read the > documentation and understand how it works (or pay someone to do it for > you). Naturally, that's why we are after as simple a solution as possible. That is also why we went for a simple, free and open database like PostgreSQL rather than a more complex system like Oracle (of course, cost was a factor also :). Thanks for the cluster info. If anyone wants to discuss HA PostgreSQL setups more, drop me a line. Cheers, Chris.