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.
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.
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.
- Does this make sense and is it possible?
- Is this an easy to implement via some tool/configuration? If so, how? If not, why?