[hitpointdrew]

The best stack I found for this is:

Keepalived -> pgbouncer -> postgresql

Then repmgr for managing replication and barman for backups.

The stack is nice because keepalived gives you a virtual ip that you point your apps to, then you can promote a standby to primary (or have one auto promote on a failure) and the VIP will flip to the new primary. All in all you get like 5-10 seconds of “down” time when it flips (depending on how aggressive or conservative you want to be with the rise and fall settings).

Edit: caveat you won’t get keepalived to work if you are using AWS and spread your Postgres servers across AZ’s, they would have to be in the same AZ.

Edit 2: You can simplify the setup if you don’t need connection pooling, in that case skip pgbouncer.