How to upgrade PostgreSQL (and others)

[thomas]

The recent upgrade of PostgreSQL was a major version change. According to the documentation, major version upgrades might introduce incompatibilities - even in the binary format of the data files.

Simply overwriting the installed programs and libraries of PostgreSQL might end up in a unusable database. PostgreSQL has a documentation about upgrading on their website, it is referred to in the blfs page since v14.0.

We had a few comments in the v14.0-ticket (#15631) which is closed in the meanwhile as the version 14 has been worked in:

Replying to Douglas R. Reno:

It looks like anyone that uses this will need to run a pg_upgrade prior to restarting postgresql.

which is, afaik, recommended on every major version change anyhow. Our current instructions are very basic in this regard. To me, it looks like the easiest way to support surviving a format change of the DB is when we do not merge postgres to /usr but install it in /opt/postgresql-x.y.z (and have a symlink /opt/postgresql to it used in $PATH). When installing the new version of postgres, the two versions are available on system and after having run the pg_upgrade and everything works, the previous version in /opt can be removed and the symlink changed to the new version.

Same for /srv/pgsql/data which than should be /srv/pgsql/data-x.y.z.

This ticket is to continue the discussion on how to perform a a upgrade of existing installations. The question is

1. Do we care about upgrading existing installations at all or is a pointer to pgsql's documentation enough
2. Is the way mentioned above the correct way to provide upgradable program/library installation as a prerequisite to use pg_upgrade
3. is there a way to use the compiled but not yet installed binaries to upgrade the old data files (in which case the installation path of Postgres could remain being /usr)
4. How to handle upgrades on other DBs like MariaDB

[Douglas R. Reno]

renodr [ /sources/postgresql-13.4 ]$ sudo systemctl status postgresql
× postgresql.service - PostgreSQL database server
     Loaded: loaded (/usr/lib/systemd/system/postgresql.service; enabled; vendor preset: enabled)
     Active: failed (Result: exit-code) since Tue 2021-10-26 17:11:53 CDT; 2min 49s ago
    Process: 17336 ExecStart=/usr/bin/pg_ctl -s -D ${PGROOT}/data start -w -t 120 (code=exited, status=1/FAILURE)
        CPU: 7ms

Oct 26 17:11:53 ISENGARD systemd[1]: Starting PostgreSQL database server...
Oct 26 17:11:53 ISENGARD postgres[17338]: 2021-10-26 17:11:53.420 CDT [17338] FATAL:  database files are incompatible with server
Oct 26 17:11:53 ISENGARD postgres[17338]: 2021-10-26 17:11:53.420 CDT [17338] DETAIL:  The data directory was initialized by PostgreSQL version 13, which is not compatible with this version 14.0.
Oct 26 17:11:53 ISENGARD postgres[17336]: pg_ctl: could not start server
Oct 26 17:11:53 ISENGARD postgres[17336]: Examine the log output.
Oct 26 17:11:53 ISENGARD systemd[1]: postgresql.service: Control process exited, code=exited, status=1/FAILURE
Oct 26 17:11:53 ISENGARD systemd[1]: postgresql.service: Failed with result 'exit-code'.
Oct 26 17:11:53 ISENGARD systemd[1]: Failed to start PostgreSQL database server.

An example of why this is needed

[thomas]

I've created a branch named upgradedb. Changes in that branch are so far:

• Adding a new page to Chap.22 to talk about how to upgrade DBMS in general (and animate people to do backups :-) )
• add a note in postgresql.xml to use the compiled yet not installed new binaries to upgrade the data files

Please add comments here about the structure in general and feel free to modify the changes directly!

[thomas]

Added with [2bda7ac4]