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