How to upgrade PostgreSQL (and others)
|Reported by:||thomas||Owned by:||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
- Do we care about upgrading existing installations at all or is a pointer to pgsql's documentation enough
- Is the way mentioned above the correct way to provide upgradable program/library installation as a prerequisite to use
- 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)
- How to handle upgrades on other DBs like MariaDB