Bug 665532 – dev-db/postgresql-11_beta1 and 11_beta3 databases are incompatible ( FATAL, cluster was initialized with CATALOG_VERSION_NO 201804191, but the server was compiled with CATALOG_VERSION_NO 201806231 )

• Status: RESOLVED FIXED
• Product: Gentoo Linux

[Description Kent Fredric (IRC: kent\n) (RETIRED) 2018-09-09 02:39:15 UTC]

2018-09-09 13:48:42.904 NZST [30371] FATAL:  database files are incompatible with server
2018-09-09 13:48:42.904 NZST [30371] DETAIL:  The database cluster was initialized with CATALOG_VERSION_NO 201804191, but the server was compiled with CATALOG_VERSION_NO 201806231.
2018-09-09 13:48:42.904 NZST [30371] HINT:  It looks like you need to initdb.

This is now on my list of reasons why beta versions of postgresql should be package.mask'ed with a warning to this effect.

https://wiki.postgresql.org/wiki/Apt/FAQ#I_want_to_try_the_beta_version_of_the_next_PostgreSQL_release

Because basically, after portage automatically upgraded me to 11_beta3, I was no longer able to start postgres, and was no longer able to spawn my irssi client either ...

Fortunately, I could downgrade to beta1, however, the point remains there is still no process ( at least, not a documented one: https://wiki.gentoo.org/wiki/PostgreSQL/QuickStart ) for migrating between subversions of postgres.

Because naturally, they can't be co-installed, and the documentation as stated requires them co-installed for an upgrade to happen.

This is very bad for me, because now I'm not even sure if I'll be able to use postgres-11 final without throwing away my entire database....

In summary, beta postgres releases should be p.masked with a warning indicating that beta versions may have databases that are fatally incompatible with each other, with no clear migration path, and as a result, are entirely unfit for an end user to install beyond research purposes.

[Comment 1 Kent Fredric (IRC: kent\n) (RETIRED) 2018-09-09 02:40:29 UTC]

- irssi client 
+ irc client

Its quassel.

[Comment 2 Michael Orlitzky 2018-09-09 13:01:37 UTC]

(In reply to Kent Fredric (IRC: kent\n) from comment #0)
> 
> Fortunately, I could downgrade to beta1, however, the point remains there is
> still no process ( at least, not a documented one:
> https://wiki.gentoo.org/wiki/PostgreSQL/QuickStart ) for migrating between
> subversions of postgres.
> 

The best way that I've found is to pg_dumpall the whole thing, eselect the new version, initialize an empty DB, edit postgres.conf and pg_hba.conf, and then load it all back with pg_restore.

[Comment 3 Michael Orlitzky 2018-09-09 13:12:26 UTC]

(In reply to Michael Orlitzky from comment #2)
> 
> The best way that I've found is to pg_dumpall the whole thing...

This process is complicated when the versions overlap. You'd have to dump the databases, upgrade-in-place to the new version, delete (or move) the data files from /var/lib, initialize a new DB, and then restore the dump. Having to create the dump before the upgrade is especially tricky though.

These upgrades should only be required for major versions, but the format of v11 is still in flux until it's released.

[Comment 4 Aaron W. Swenson 2018-09-09 15:42:36 UTC]

This is a bit of a coincidence. I've been thinking over the past few days that I/we need to mask the beta packages along with the related USE_EXPAND flag.

Unfortunately, there isn't a way to prevent this from happening, or a workaround other than dumping the DB with pg_dumpall and starting from scratch.

I'm going to work on masking this now.

[Comment 5 Aaron W. Swenson 2018-09-09 19:07:04 UTC]

commit 6ce9b6a5ad3e33c7bda9cefcd05f79eaf7e86ce9 (HEAD -> master, origin/master, origin/HEAD)
Author: Aaron W. Swenson <[e-mail]>
Date:   Sun Sep 9 15:00:50 2018 -0400

    base/use.mask and package.mask: >=postgresql-11

    Mask dev-db/postgresql-beta*, and mask postgres_targets_postgres{11,12}.

    While beta and rc are feature frozen, they’re not generally safe for production
    use.