The Firm chose PostgreSQL as its primary RDBMS. It wanted PostgreSQL to power it’s webapp by being performant, resistant to failure, fast, and to scale to huge load.
The Firm wanted PostgreSQL to do the following:
a.) automatically failover to a replicated database to keep our web-app running
b.) failover with limited loss of database transactions within 5 minutes
c.) Point In Time Recovery (‘PITR’) with 30 minutes of last transaction, or better
d.) Regularly daily recovery of PITR to verify processes
e.) Health monitoring of the previous goals
We show our path to solve The Firm’s PostgreSQL usage goals via systematic research and prototyping to choose PostgreSQL automated replication and failover, Point In Time Recovery (‘PITR’) backup, and health monitoring management tools.
The Firm now employs Hot Standby Streaming Replication with Repmgrd Automatic Failover teamed with PGBouncer, PGBarman for automated Point In Time Recovery, and nagios-like health monitoring of all these services.