Hacker News Reader

PG's internal wal-level replication? primary to a read-replica, and then switch the read to become the primary. You'll have a bit of downtime while you stop connections on the original server, switch the new server to primary, and update your app config to connect to the new server.

I believe that's a pretty standard way to provide "HA" postgres. (We use Patroni for our HA setup)

https://github.com/patroni/patroni

We use the same setup, though we use PGBouncer so after switching primary we just force reconnect all clients from PGBouncer instead.

The clients will have to retry on-going transactions, but that's a basic fault tolerant requirement anyway.

In my experience, everyone's setup is slightly different so it's hard to find a generic solution. pgcopydb is pretty good

I can't remember the name but I saw a Ruby based tool on Hacker News a few months ago that'd automate logical rep setup and failover for you

pglogical can do that (or at least minimize the manual steps as much as possible)

I am not entirely sure, but I think CloudNativePG (a Kubernetes operator) can also be used for that.