Major PostgeSQL update

I had a mild panic attack when I couldn't open PostgreSQL databases on my PC after I had upgraded to Fedora 32. The postgresql service was down and wouldn't restart. The solution was easy-peasy, but I thought I should write it down. It adds some much needed content to this blog, and when the same thing happens in 9 or 18 months time I'll know what do do straight away.

Identifying the issue was just a matter of running systemctl status postgresql:

# systemctl status postgresql
‚óŹ postgresql.service - PostgreSQL database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: disabled)
     Active: failed (Result: exit-code) since Sun 2020-05-10 12:26:54 BST; 2min 3s ago
    Process: 1860 ExecStartPre=/usr/libexec/postgresql-check-db-dir postgresql (code=exited, status=1/FAILURE)
        CPU: 19ms

May 10 12:26:52 fbox systemd[1]: Starting PostgreSQL database server...
May 10 12:26:53 fbox postgresql-check-db-dir[1860]: An old version of the database format was found.
May 10 12:26:53 fbox postgresql-check-db-dir[1860]: Use 'postgresql-setup --upgrade' to upgrade to version '12'
May 10 12:26:53 fbox postgresql-check-db-dir[1860]: See /usr/share/doc/postgresql/README.rpm-dist for more information
May 10 12:26:54 fbox systemd[1]: postgresql.service: Control process exited, code=exited, status=1/FAILURE
May 10 12:26:54 fbox systemd[1]: postgresql.service: Failed with result 'exit-code'.
May 10 12:26:54 fbox systemd[1]: Failed to start PostgreSQL database server.

The issue here is that Fedora 32 comes with PostgreSQL 12, whereas Fedora 31 shipped with version 11. Major PostgreSQL version upgrades require you to run postgresql-setup --upgrade. The postgresql-setup utility can be installed via DNF (it wasn't installed on my PC).

# postgresql-setup --upgrade
 * Upgrading database.
 * Upgraded OK.
WARNING: The configuration files were replaced by default configuration.
WARNING: The previous configuration and data are stored in folder
WARNING: /var/lib/pgsql/data-old.
WARNING: If you've just upgraded your database from a previous major version of
Fedora or RHEL, please run reindexdb against your databases.  Core library
collation data may have changed and this will invalidate database indexes.  For
example, in Fedora 28 and RHEL 8 there have been extensive changes in glibc
collations to support ISO 14651:2016 (Unicode 9.0.0 data) and your indexes may
be affected:
 * See /var/lib/pgsql/upgrade_postgresql.log for details.

As shown in the output, I finally needed to run run reindexdb against my databases. I think it is best to run the command as the user postgres as (in my set-up at least) that user has access to my databases as well as the postgres and template1 databases.

# su postgres
bash-5.0$ reindexdb -all

And that was it. Panic over.