I haven’t done this myself, but I use PostgreSQL a lot, so I’ve studied it a bit. The key is that PostgreSQL doesn’t have any master-master mode, and cannot just swap master and slave roles. I would set up (at least) three servers: master, slave #1 (to master), and slave #2 (to slave #1).
Failure recovery would be as follows:
- Slave #2 fails: repair or replace, re-sync with Slave #1.
- Slave #1 fails: promote Slave #2 to slave of master. Repair or replace original slave #1 to be slave #2. If repaired, rewind it to last good checkpoint before resync.
- Master fails: promote Slave #1 to master. Slave #2 becomes Slave #1. Repair or replace original master to be slave #2. If repaired, rewind as above before resync.
Consider using synchronous replication from master to slave #1, ensuring transactions commit in two servers before returning success to the client. You can load balance by allowing Select queries in one or both slaves.