Red Hat Bugzilla – Bug 1462411
Upgrade leaves postgreSQL unusable, with no warning and no sane recovery path
Last modified: 2017-06-19 09:02:11 EDT
Description of problem:
On upgrading from Fedora 23 to 25, the PostgreSQL service doesn't start.
The problem is that the database format has changed, in this case from 9.1 to 9.4.
/var/log/messages helpfully says:
Jun 17 05:03:03 overkiller systemd: Starting PostgreSQL database server...
Jun 17 05:03:04 overkiller systemd: Started D-Bus User Message Bus.
Jun 17 05:03:04 overkiller postgresql-check-db-dir: An old version '9.1' of the database format was found.
Jun 17 05:03:04 overkiller postgresql-check-db-dir: You need to dump and reload before using PostgreSQL 9.5.7.
Jun 17 05:03:04 overkiller postgresql-check-db-dir: See /usr/share/doc/postgresql/README.rpm-dist for more information.
Jun 17 05:03:04 overkiller systemd: postgresql.service: Control process exited, code=exited status=1
If you are upgrading across more than one major release of PostgreSQL (for
example, from 8.3.x to 9.0.x), you will need to follow the "traditional"
dump and reload process to bring your data into the new version. That is:
>>> *before* upgrading, run pg_dumpall to extract all your data into a SQL
file. Shut down the old postmaster, upgrade to the new version RPMs,
perform initdb, and run the dump file through psql to restore your data.
Of the 5,000 or so packages involved in the upgrade, how was I supposed to find out about this BEFORE launching the upgrade? I'm not lazy, but I didn't read all 5000 READMEs.
OK, well, there's :
4. as root, run "postgresql-setup --upgrade [--upgrade-from ID]"
No help. It only supports upgrading from 9.4:
postgresql - Upgrade data from system PostgreSQL version (PostgreSQL 9.4)
ERROR: Cannot upgrade because the database in /var/lib/pgsql/data is of
version 9.1 but it should be 9.4
So one is left with very unpleasant alternatives:
Restore the machine from backups, dump the SQL database, and redo the fedora upgrade. The upgrade ran overnight on this machine; restore from backups is at least as long. So this will take a day or two...
Try to install PSQL 9.1 from (someplace) to dump the existing database.
Discard the databases and start over.
Neither the fedora upgrade instructions https://fedoraproject.org /wiki/DNF_system_upgrade nor the release notes https://docs.fedoraproject.org/en-US/Fedora/25/html-single/Release_Notes/index.html say anything about this trap.
PSQL is a service used by applications in the background - I didn't even realize that it was active on this machine. There needs to be a warning mechanism.
The upgrade should not proceed if a package reports an upgrade prerequesite - apparently PostgreSQL didn't.
There needs to be a way out for people who end up in this situation; I wouldn't mind upgrading the DB version one step at a time, but there doesn't seem to be any pointer to an upgrade utility list. (E.g. postgresql-setup 9.2 -> 9.3 -> 9.4 -> 9.5.
And how hard would it be for the PSQL upgrade to do the database dump/upgrade/restore that it requires? I don't see what human judgement is required - the RPM spec file ought to be able to look at the existing databases & decide whether it can do a fast upgrade, a dump and restore - and just do it. This seems to be quite avoidable...
Hi, I feel your pain ;) I have been there and done the psql dumps and restores. Technically, it might be possible for the postgresql upgrade scriptlets to run something during package update, but it'd be very hard to get it to work properly for all cases, in particular for very big databases.
Anyway, this is something for postgresql folks to handle, dnf-system-upgrade-plugin is just a dumb messenger here.
(In reply to tlhackque from comment #0)
> Description of problem:
> On upgrading from Fedora 23 to 25, the PostgreSQL service doesn't start.
> The problem is that the database format has changed, in this case from 9.1
> to 9.4.
This doesn't seem to be truth, F23 has 9.4.10, F25 has 9.5.7. So perhaps
you were running non-default version of postgresql server, and now you
plan to run non-default postgresql version again; this is not supported
by Fedora and I would suggest you to contact your PostgreSQL provider.
If you plan to migrate to the default 9.5 version, I would suggest you to
dump & restore the database, you can have a look at pgrpms  which could
help you with that. The postgresql-setup script works from Fedora version
N to N+2 at most.