PostgreSQL Database in High Availability
PostgreSQL offers various ways (external link) to archive and replicate the primary database for backup, high availability, and load balancing scenarios.
BriefCam recommends implementing a master-standby replication.
A master-standby replication setup includes one master database server that permits read/write operations. The master server asynchronously sends data changes to one or more standby servers.
If the master database server fails, the system can fail over to one of the standby servers, which makes that standby server the active primary server.
This solution is currently only offered on Linux Servers.
To deploy this configuration BriefCam provides two scripts:
A setup script that configures the data replication between the two database servers.
A failover script that can be executed when the master database fails.
Automated Failover
A more advanced deployment can include a cluster manager (such as HA Proxy) that identifies when the master database is not available and triggers an automated failover.