PostgreSQL high-availability/load-balancing strategy

[ustuehler]

Looking at the matrix in PostgreSQL: Documentation: 9.3: Comparison of Different Solutions, I think that Transaction Log Shipping comes out on top for our PuppetDB scenario. Second best might be Trigger-Based Master-Standby Replication.

My criteria was:

• There shoudn't be a need for conflict resolution strategies, ever. This ruled out pgpool-II and Bucardo.
• Standby should be able to serve read-only queries. This ruled out shared storage: Gluster, NAS, DRBD.
• Write delay is acceptable (writes are asynchronous from Puppet's point of view, anyway), while data loss on master failure is not. This ruled out Slony, or Trigger-Based Master-Standby Replication.

Those criteria only leave us with Streaming Replication (synchronous) for Transaction Log Shipping and proprietary synchronous multi-master replication.

[ustuehler]

PostgreSQL documentation 25.2. Log-Shipping Standby Servers says the following:

No changes to the database tables are required to enable this capability, so it offers low administration overhead compared to some other replication solutions.

Low administration overhead is also what we want, since we want our attention to focus on Puppet and not so much on database administration.

[ustuehler]

See output.txt for a successful run of vagrant up with two load-balancing instances of gluster, puppetdb and puppet. Remaining single-point of failure is postgres. :)