How To Upgrade PostgreSQL installed from YUM

I have a bit different case with other tutorials as I use Postgres YUM repo to get extra extensions installed. My case here is to upgrade from version 16 to 17.

First, the usual installation:

PG=17
dnf -y install postgresql$PG-{server,contrib}
# Install extra extensions
dnf -y install {postgis35,pgrouting,pgvector,pg_uuidv7,timescaledb}_$PG postgresql$PG-devel
for ext in "postgis" "postgis_raster" "postgis_sfcgal" "postgis_tiger_geocoders" "postgis_topology" "earthdistance" "address_standardizer" "address_standardizer_data_us" "pgrouting" "pg_uuidv7" "vector"; do
  # this lets non admin enable the extensions
  echo "trusted = true" >> "/usr/pgsql-$PG/share/extension/$ext.control"
done

While this repo doesn’t have pg_lsclusters, it does have some bash script to aid in upgrading. We’re going to use postgresql-17-setup. This script aids in initdb and upgrade from one previous version (in this case it’s 16). Note that if you want to upgrade two step major versions (like from 15 to 17), you better to do upgrade in version 16 first.

Now we need to init the db and check upgrade:

$> /usr/pgsql-17/bin/postgresql-17-setup initdb
Initializing database ... OK

$> /usr/pgsql-17/bin/postgresql-17-setup check_upgrade
Performing upgrade check: OK

See /var/lib/pgsql/17/pgupgrade.log for details.

Then perform upgrade

pssh -t 0 -Iih ~/hosts <<'EOC'
# copy old config as backup
/bin/cp -f /var/lib/pgsql/16/data/postgresql.conf /var/lib/pgsql/16/data/postgresql.old.conf
/bin/cp -f /var/lib/pgsql/16/data/pg_hba.conf /var/lib/pgsql/16/data/pg_hba.old.conf
# disable daemon and perform upgrade
systemctl disable postgresql-16 --now
/usr/pgsql-17/bin/postgresql-17-setup upgrade
# in my setup, I symlink postgresql service, let's replace it
ln -fs /usr/lib/systemd/system/postgresql-17.service /usr/lib/systemd/system/postgresql.service
systemctl daemon-reload
# restore config
/bin/cp -f /var/lib/pgsql/16/data/postgresql.old.conf /var/lib/pgsql/17/data/postgresql.conf
/bin/cp -f /var/lib/pgsql/16/data/pg_hba.old.conf /var/lib/pgsql/17/data/pg_hba.conf

systemctl enable postgresql-17 --now
EOC

After installation you should check if it is running correctly.