I have a local application running on a LAN. Sometimes a database gets shutdown. In such a scenario how can I keep the application working? Can I have two concurrent databases so that one picks up the work when the other becomes unavailable?
migrated from stackoverflow.com Jun 10 at 19:21
This question came from our site for professional and enthusiast programmers.
closed as not a real question by Mark Storey-Smith, Mat, Paul White, RolandoMySQLDBA, dezso Jun 12 at 11:54
It's difficult to tell what is being asked here. This question is ambiguous, vague, incomplete, overly broad, or rhetorical and cannot be reasonably answered in its current form. For help clarifying this question so that it can be reopened, visit the help center.If this question can be reworded to fit the rules in the help center, please edit the question.
First, "sometimes one of them gets shut down" isn't a good approach. You need controlled infrastructure or you won't get anything reliable.
You might want to look in to PgPool-II's statement-based replication, but it's got a lot of limitations.
A better solution for most applications is to use master/standby fail-over with heartbeat and HAProxy or similar. See the manual for more info. This wiki page might also be interesting, though it's more of a link catalogue.
repmgr can be a useful part of a HA setup, and the new version is being enhanced with some more HA-specific features.
Finally, the team I'm a minor part of is working on bi-directional logical replication for PostgreSQL, which would provide multi-master replication (not multi-master "clustering" since there's no global transaction manager, global lock manager, etc).