Hacker News new | ask | show | jobs
by CAP_NET_ADMIN 524 days ago
Does anyone know a battle-tested tool that would help with (almost)online migrations of postgresql servers to other hosts? I know it can be done by manually, but I'd like to avoid that
3 comments
andruby 524 days ago
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

link
dikei 524 days ago
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.

link
nijave 523 days ago
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

link
hans_castorp 524 days ago
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.

link