Bug 1462411 - Upgrade leaves postgreSQL unusable, with no warning and no sane recovery path
Upgrade leaves postgreSQL unusable, with no warning and no sane recovery path
Status: CLOSED NOTABUG
Product: Fedora
Classification: Fedora
Component: postgresql (Show other bugs)
25
Unspecified Unspecified
unspecified Severity high
: ---
: ---
Assigned To: Pavel Raiskup
Fedora Extras Quality Assurance
:
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2017-06-17 07:43 EDT by tlhackque
Modified: 2017-06-19 09:02 EDT (History)
10 users (show)

See Also:
Fixed In Version:
Doc Type: If docs needed, set a value
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2017-06-19 09:02:11 EDT
Type: Bug
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)

  None (edit)
Description tlhackque 2017-06-17 07:43:55 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
Ju

/usr/share/doc/postgresql/README.rpn-dist says:

   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-setup --upgrade-ids
postgresql - Upgrade data from system PostgreSQL version (PostgreSQL 9.4)

 postgresql-setup --upgrade
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.

Issues:

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...
Comment 1 Zbigniew Jędrzejewski-Szmek 2017-06-17 19:50:35 EDT
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.
Comment 2 Pavel Raiskup 2017-06-19 09:02:11 EDT
(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 [1] which could
help you with that.  The postgresql-setup script works from Fedora version
N to N+2 at most.

[1] https://yum.postgresql.org/

Note You need to log in before you can comment on or make changes to this bug.