undefined | Better HN

0 pointsSymbiote6y ago0 comments

I upgraded from 9.3 → 11.2 a few months ago using pg_upgrade[1], on a master+slave database with 150GB of data. I did a fair amount of testing, but the final procedure was very fast and smooth.

1. Test the upgrade: set up an additional secondary (9.3), break the replication link (promote it to a master). Test the upgrade on that. It was really fast, under 30 seconds to shut down the old DB, run the in-place upgrade, and start up the new DB.

2a. In production: set up an additional secondary (9.3). Make the primary read-only. Promote the new secondary to a master. Shut down, upgrade to 11.2, restart. Point applications at it.

2b. Backout plan: leave the applications pointing at the original database server, make it read-write.

There are other options, including with only seconds of downtime, but <1 minute with pg_upgrade was simple and very acceptable for us.

[1] https://www.postgresql.org/docs/current/pgupgrade.html

[2] https://www.postgresql.org/docs/current/upgrading.html

0 comments

devereaux6y ago

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.

SymbioteOP6y ago

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.)

jbverschoor6y ago

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.

j / k navigate · click thread line to collapse