Update Postgresql in Ubuntu - pg_upgrade vs pg_upgradecluster

Question

I would like to switch from postgres 9.6 to version 14 which runs on Ubuntu 21.04. I have a cluster with 3 databases. I would like to know what is the difference between upgrading with pg_upgrade and pg_upgradecluster? Which one is faster and safer?

Answer 1

pg_upgrade is a tool from Postgresql itself that will operate on a single database (folder).

pg_upgradecluster however is a wrapper from your operating system (= Ubuntu) to pg_upgrade or pg_dump/pg_restore. In addition to very conveniently upgrading your database, it will also do some housekeeping like moving the config files to the correct folder in /etc/postgres/ .

So, if you have set up your database by pg_createcluster and it is hence listed by pg_lsclusters, I'd strongly recommend using pg_upgradecluster to upgrade it.

In terms of "faster vs. safer", be sure to read about the various options on the manpage.

If you can take a reliable backup (e.g. snapshot), you can safely use the -m upgrade --link option which will be fastest and allow for a very short downtime (depending on database size and resources, but I've recently upgraded a 700GB database in ~25 seconds).

The safest option of course is not using pg_upgrade, but the default pg_dump/pg_restore method, which will shut down your original database and copy the data to a new database in a new location (= it will use at approx. twice the space, at least temporarily until you decide to delete the original folder).