1

I'm looking into providing a method of having multiple hot spare PostgreSQL instances to our website.

The majority of implementations that I have found require a single point of failure with PgPool II or PGBouncer proxying the connection for every app server to the correct database.

Example:

App Server 1 \              Spare
App Server 2 -  PGPool II - Master
App Server 3 /              Spare

I'm looking to try a method of automatic fail-over, where each app server will fail-over automatically to a new master DB quickly and correctly. It can still use PGPool or PGBouncer, but each app server should be separate and autonomous with no message/state passing from app server to app server.

Example:

App Server 1 -  PGPool II \ Master 1
App Server 2 -  PGPool II - Master 2
App Server 3 -  PGPool II / Master 3

A maximum time for switch over should be 1-2 seconds.

  1. Does this make sense and is it possible?
  2. Is this an easy to implement via some tool/configuration? If so, how? If not, why?
3
  • pgpool has pgpool-ha. So it need not be a single point of failure. Are you planning to have pgpool replicate writes, or use in-built replication? Having multiple masters at the same time needs quite a bit of thought. What if 2 sessions try to change the same record in 2 masters? Which one should be given precedence?
    – Jayadevan
    Nov 4 '14 at 13:53
  • @Jayadevan Does PgPool-HA support scaling past 2 instances? because I would need to run it locally to the App Servers to abstract the database architecture from them. Also in our situation, last write always wins, and everything happens inside a transaction. Nov 6 '14 at 10:03
  • I haven't tried that. I suggest trying pgpool mailing list.
    – Jayadevan
    Nov 7 '14 at 3:32
1

I can recommend using Slony for PostgreSQL replication from my experience with it.

I can't respond better then the Slony docs section on failover so I'll quote it:

If some more serious problem occurs on the "origin" server, it may be necessary to SLONIK FAILOVER to a backup server. This is a highly undesirable circumstance, as transactions "committed" on the origin, but not applied to the subscribers, will be lost. You may have reported these transactions as "successful" to outside users. As a result, failover should be considered a last resort. If the "injured" origin server can be brought up to the point where it can limp along long enough to do a controlled switchover, that is greatly preferable.

Slony-I does not provide any automatic detection for failed systems. Abandoning committed transactions is a business decision that cannot be made by a database system. If someone wants to put the commands below into a script executed automatically from the network monitoring system, well ... it's your data, and it's your failover policy.

To your question: does it make sense?, personally, I would say, no. I agree with the Slony docs that it's better to have a human understand why there was a failure and then manually (and quickly) failover.

Having managed PostgreSQL for more than a decade, unless there's a hardware failure, it's difficult to make it seriously fail. And I recommend involving a human to confirm hardware failure and check the state of the system before failing over.

If you are getting started with a high-availability PostgreSQL, I would first look at the hardware, tuning and RAID configuration behind your PostgreSQL database before considering automatic failover.

3
  • I'm sorry, this was a good write up, however I forgot to amend my diagram above to show that all the postgres servers became masters. The premise is to have each app server do the job of sending to multiple servers via the install of PGPool or PGbouncer. I want to buy myself time to repair the service without causing downtime, which is what your system seems to cause in the event of a failure. Nov 3 '14 at 17:02
  • For us at the end of the day, if the hardware fails then we want to just rebuild with a replacement server and push it back into the cluster without much care for the root cause. Nov 3 '14 at 17:04
  • When you say "all the servers became masters", it's not clear if you mean that they are doing master-master replication between themselves, of if intend that replication has been dropped, and they are all receiving updates from different sources and drifting out of sync. I don't have experience with master-master replication. Bucardo does it but only between two masters. Adding more masters to sync would involve exponential communication overhead to stay in sync. Nov 5 '14 at 13:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.