6 ms·

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, bu

• andruby 2y 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 https://github.com/patroni/patroni
  • dikei 2y 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.
• hans_castorp 2y 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.
• nijave 2y 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