[devereaux]

This is a nice way to do that, but you have a low volume of data, and you think 30 seconds is fast and 1 minute of downtime is acceptable. I question these assumptions.

Consider the situation when you're adding thousands of new records per seconds, and the database is being used every second (quite literally: to compute per seconds statistics).

A better solution is to have triggers on the old master, to do the same inserts on the new master (after copying the data/promoting a replica/whatever), and have similar triggers on the new master when the IP is not the old master (to be able to backout to the old server)

Then both the new and the old master run "in parallel", with the same data, and you can have the apps use the new server (on a new domain name, new ip, new port, whatever) when you want - on a app by app basis if you want. You can keep both until you decide to decommission the old master.

[Symbiote]

That's a good plan for someone with a larger or busier database, but my approach was the correct solution for us. You can be satisfied that your taxes aren't wasted by us implementing anything more complicated :-)

(Most of the writes to our PostgreSQL server are batch processes, decoupled with a message broker, and the message consumers were easily paused during the upgrade.)

[jbverschoor]

Not everyone is google, and hec. even google an their services are down sometimes.

If there's not too much data, it seems like the safest and fastest option.