"The database cluster was initialized with CATALOG_VERSION_NO X, but the server was compiled with CATALOG_VERSION_NO Y." after aptitude upgrade

Question

Today I upgraded all software on Ubuntu server:

sudo aptitude upgrade

Upgrading performed not well and postgresql didn't start, with error:

alex@db:~$ sudo service postgresql start
* Starting PostgreSQL 9.5 database server[fail]
* The PostgreSQL server failed to start. Please check the log output:
2015-10-30 12:42:09 MSK FATAL:  database files are incompatible with server
2015-10-30 12:42:09 MSK DETAIL:  The database cluster was initialized with CATALOG_VERSION_NO 201507281, but the server was compiled with CATALOG_VERSION_NO 201510051.
2015-10-30 12:42:09 MSK HINT:  It looks like you need to initdb.

I don't remember, which version of postgresql was on server before upgrading, but as result of upgrading I saw only one data dir and one cluster:

alex@db:~$ ls /var/lib/postgresql/
9.5  Maildir

alex@db:~$ pg_lsclusters 
Ver Cluster Port Status Owner    Data directory               Log file
9.5 main    5432 down   postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log

So it looks like it was a minor upgrade.

After copying data dir somewhere, running initdb, and aptitude upgrade again I noticed a 9.4 cluster was created:

alex@db:~$ sudo cp /var/lib/postgresql/9.5/main /var/lib/postgresql/9.5/main.old
alex@db:~$ sudo /usr/lib/postgresql/9.5/bin/initdb -D /var/lib/postgresql/9.5/main/
alex@db:~$ sudo aptitude upgrade
alex@db:~$ ls /var/lib/postgresql/
9.4  9.5  Maildir
alex@db:~$ pg_lsclusters 
Ver Cluster Port Status Owner    Data directory               Log file
9.4 main    5433 down   postgres /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log
9.5 main    5432 down   postgres /var/lib/postgresql/9.5/main /var/log/postgresql/postgresql-9.5-main.log

Now it works fine, but how to restore saved data from /var/lib/postgresql/9.5/main.old? pg_upgrade doesn't work. And... yes, I didn't create a backup before upgrading.

UPDATE: Contents of /etc/apt/sources.list

#######################################################################################
# Hetzner APT-Mirror
#
deb http://mirror.hetzner.de/ubuntu/packages trusty main restricted universe multiverse
deb http://mirror.hetzner.de/ubuntu/packages trusty-backports main restricted universe multiverse
deb http://mirror.hetzner.de/ubuntu/packages trusty-updates main restricted universe multiverse
deb http://mirror.hetzner.de/ubuntu/security trusty-security main restricted universe multiverse

# See http://help.ubuntu.com/community/UpgradeNotes for how to upgrade to
# newer versions of the distribution.
deb http://de.archive.ubuntu.com/ubuntu/ trusty main restricted
deb-src http://de.archive.ubuntu.com/ubuntu/ trusty main restricted

## Major bug fix updates produced after the final release of the
## distribution.
deb http://de.archive.ubuntu.com/ubuntu/ trusty-updates main restricted
deb-src http://de.archive.ubuntu.com/ubuntu/ trusty-updates main restricted

## N.B. software from this repository is ENTIRELY UNSUPPORTED by the Ubuntu
## team. Also, please note that software in universe WILL NOT receive any
## review or updates from the Ubuntu security team.
deb http://de.archive.ubuntu.com/ubuntu/ trusty universe
deb-src http://de.archive.ubuntu.com/ubuntu/ trusty universe
deb http://de.archive.ubuntu.com/ubuntu/ trusty-updates universe
deb-src http://de.archive.ubuntu.com/ubuntu/ trusty-updates universe

## N.B. software from this repository is ENTIRELY UNSUPPORTED by the Ubuntu 
## team, and may not be under a free licence. Please satisfy yourself as to 
## your rights to use the software. Also, please note that software in 
## multiverse WILL NOT receive any review or updates from the Ubuntu
## security team.
deb http://de.archive.ubuntu.com/ubuntu/ trusty multiverse
deb-src http://de.archive.ubuntu.com/ubuntu/ trusty multiverse
deb http://de.archive.ubuntu.com/ubuntu/ trusty-updates multiverse
deb-src http://de.archive.ubuntu.com/ubuntu/ trusty-updates multiverse

## N.B. software from this repository may not have been tested as
## extensively as that contained in the main release, although it includes
## newer versions of some applications which may provide useful features.
## Also, please note that software in backports WILL NOT receive any review
## or updates from the Ubuntu security team.
deb http://de.archive.ubuntu.com/ubuntu/ trusty-backports main restricted universe multiverse
deb-src http://de.archive.ubuntu.com/ubuntu/ trusty-backports main restricted universe multiverse

deb http://security.ubuntu.com/ubuntu trusty-security main restricted
deb-src http://security.ubuntu.com/ubuntu trusty-security main restricted
deb http://security.ubuntu.com/ubuntu trusty-security universe
deb-src http://security.ubuntu.com/ubuntu trusty-security universe
deb http://security.ubuntu.com/ubuntu trusty-security multiverse
deb-src http://security.ubuntu.com/ubuntu trusty-security multiverse

## Uncomment the following two lines to add software from Canonical's
## 'partner' repository.
## This software is not part of Ubuntu, but is offered by Canonical and the
## respective vendors as a service to Ubuntu users.
# deb http://archive.canonical.com/ubuntu trusty partner
# deb-src http://archive.canonical.com/ubuntu trusty partner

## Uncomment the following two lines to add software from Ubuntu's
## 'extras' repository.
## This software is not part of Ubuntu, but is offered by third-party
## developers who want to ship their latest software.
# deb http://extras.ubuntu.com/ubuntu trusty main
# deb-src http://extras.ubuntu.com/ubuntu trusty main

deb http://packages.elasticsearch.org/elasticsearch/1.4/debian stable main

UPDATE2:

alex@db:~$ sudo dpkg -l postgresql\*
Desired=Unknown/Install/Remove/Purge/Hold
| Status=Not/Inst/Conf-files/Unpacked/halF-conf/Half-inst/trig-aWait/Trig-pend
|/ Err?=(none)/Reinst-required (Status,Err: uppercase=bad)
||/ Name                                                      Version                           Architecture                      Description
+++-=========================================================-=================================-=================================-=======================================================================================================================
ii  postgresql                                                9.4+170.pgdg14.04+1               all                               object-relational SQL database (supported version)
un  postgresql-7.4                                            <none>                            <none>                            (no description available)
un  postgresql-8.0                                            <none>                            <none>                            (no description available)
un  postgresql-9.1                                            <none>                            <none>                            (no description available)
ii  postgresql-9.3                                            9.3.10-1.pgdg14.04+1              amd64                             object-relational SQL database, version 9.3 server
ii  postgresql-9.4                                            9.4.5-1.pgdg14.04+1               amd64                             object-relational SQL database, version 9.4 server
ii  postgresql-9.5                                            9.5~beta1-1.pgdg14.04+1           amd64                             object-relational SQL database, version 9.5 server
un  postgresql-client                                         <none>                            <none>                            (no description available)
ii  postgresql-client-9.3                                     9.3.10-1.pgdg14.04+1              amd64                             front-end programs for PostgreSQL 9.3
ii  postgresql-client-9.4                                     9.4.5-1.pgdg14.04+1               amd64                             front-end programs for PostgreSQL 9.4
ii  postgresql-client-9.5                                     9.5~beta1-1.pgdg14.04+1           amd64                             front-end programs for PostgreSQL 9.5
ii  postgresql-client-common                                  170.pgdg14.04+1                   all                               manager for multiple PostgreSQL client versions
ii  postgresql-common                                         170.pgdg14.04+1                   all                               PostgreSQL database-cluster manager
ii  postgresql-contrib                                        9.4+170.pgdg14.04+1               all                               additional facilities for PostgreSQL (supported version)
ii  postgresql-contrib-9.3                                    9.3.10-1.pgdg14.04+1              amd64                             additional facilities for PostgreSQL
ii  postgresql-contrib-9.4                                    9.4.5-1.pgdg14.04+1               amd64                             additional facilities for PostgreSQL
ii  postgresql-contrib-9.5                                    9.5~beta1-1.pgdg14.04+1           amd64                             additional facilities for PostgreSQL
ii  postgresql-doc                                            9.4+170.pgdg14.04+1               all                               documentation for the PostgreSQL database management system
ii  postgresql-doc-9.3                                        9.3.10-1.pgdg14.04+1              all                               documentation for the PostgreSQL database management system
ii  postgresql-doc-9.4                                        9.4.5-1.pgdg14.04+1               all                               documentation for the PostgreSQL database management system
un  postgresql-doc-9.5                                        <none>                            <none>                            (no description available)
ii  postgresql-server-dev-9.3                                 9.3.10-1.pgdg14.04+1              amd64                             development files for PostgreSQL 9.3 server-side programming


alex@db:~$ sudo apt-cache policy postgresql-9.5
postgresql-9.5:
  Installed: 9.5~beta1-1.pgdg14.04+1
  Candidate: 9.5~beta1-1.pgdg14.04+1
  Version table:
 *** 9.5~beta1-1.pgdg14.04+1 0
        500 http://apt.postgresql.org/pub/repos/apt/ trusty-pgdg/main amd64 Packages
        100 /var/lib/dpkg/status

Hm, ok. So you didn't explicitly enable any testing repositories. You must've manually run `apt-get install postgresql-9.5` as it's not installed automatically when you ask for PostgreSQL, but even so, I think it should not be that easy to accidentally install an alpha release. I've emailed the apt packaging team; mail at http://www.postgresql.org/message-id/CAMsr+YGNDGe2GoKOc1ovg15_XeTb0cAJ+JEnJGOtcJu2fj24cw@mail.gmail.com — Craig Ringer, Oct 30 '15 at 11:47
So, do you have the alpha packages in your `/var/cache/apt/archives` still? — Craig Ringer, Oct 30 '15 at 11:48
Yep: postgresql-9.5_9.5~alpha2-1.pgdg14.04+1_amd64.deb postgresql-client-9.5_9.5~alpha2-1.pgdg14.04+1_amd64.deb postgresql-contrib-9.5_9.5~alpha2-1.pgdg14.04+1_amd64.deb @CraigRinger — Alexander Matveev, Oct 30 '15 at 11:52
P.S. I didn't run `apt-get install postgresql-9.5`, only `aptitude upgrade` =( — Alexander Matveev, Oct 30 '15 at 11:53
Before doing anything else, if you don't have a recent,vaild backup, *at least* make a (recursive) copy of the old data directory and store it in a safe place. — joop, Oct 30 '15 at 12:03
@AlexanderMatveev Most recently, sure, but you must've explicitly installed PostgreSQL 9.5 to start with. — Craig Ringer, Oct 30 '15 at 12:08
@AlexanderMatveev OK. Copy those ~alpha2 files somewhere safe, like your home directory, *now*. Then back up your 9.5beta1, per my answer, uninstall 9.5beta2, install 9.5alpha1, copy your old database back into place, start the server, and use `pg_dump` to dump the databse. — Craig Ringer, Oct 30 '15 at 12:12
Dis you check the contents? (your original cp was **not** recursive) — joop, Oct 30 '15 at 12:17
@joop in the original it was recursive, but thanks for this =) — Alexander Matveev, Oct 30 '15 at 12:41

Craig Ringer · Accepted Answer · 2015-11-02T01:43:20.283

You are/were using an alpha release of the database, which is explicitly documented as not remaining format-stable. It looks like it might've been a bit too easy to install without realising that, though; I've mailed the apt packaging team about that.

Update: you must've explicitly done an aptitude install postgresql-9.5 and either used --force when originally installing or added 9.5 to your sources.list entry manually. The 9.5 packages are not installed by default, and take extra steps to install. See:

I updated the wiki with a warning note, in case you followed those instructions without realising about the format change. Though I'd think using a prerelease database with data you care about would be something that's obviously not a great idea, really.

If you need to read the old data, you'll need to download the particular version you were running then, install that, and use it to dump the database(s). Then you can restore them to the new copy.

I strongly advise you to dump your data, then restore it to PostgreSQL 9.4, and keep using that until 9.5.0 comes out ~~or at least until a beta is released~~. After beta the PostgreSQL team tries hard to avoid changing the format.

Update: You're running 9.5~beta1-1.pgdg14.04+1 now, so I'm pretty sure it's the 9.5alpha2 to 9.5beta1 update that broke things. Which makes sense.

So what we need to do is find the 9.5alpha1 version, then force a downgrade. Note that this will stop you from being able to access your 9.5beta2 database until you upgrade again, so I suggest dumping it first.

From the version it's clear it's a PGDG release, i.e. a release from apt.postgresql.org (run by the PostgreSQL Development Group). Examining http://apt.postgresql.org/pub/repos/apt/dists/trusty-pgdg/9.5/binary-amd64/Packages shows us the current package-list, and the alpha is not in it. That's to be expected, since it's no longer a current release, but it means you can't download it from that repo directly. However, it's also not in trusty-pgdg-testing, and it's not in the package pool at http://apt.postgresql.org/pub/repos/apt/pool/main/p/postgresql-9.5/ either.

In other words, the alpha has been removed from the repository completely, and is no longer available for download from apt.postgresql.org.

First, check if you still have the alpha in your /var/cache/apt/archives. If you do, copy everything with alpha2 in its name to a separate location now.

If you don't, your options are to compile it yourself, or ask on the mailing list if anyone still has the packages.

Nitpicking: there **is** a beta release of 9.5 available: http://www.enterprisedb.com/products-services-training/pgdownload — , Oct 30 '15 at 10:41
Thanks for the answer. A have two more questions: 1) Why postgres upgraded to non-stable release with `aptitude upgrade`? There were no warnings, no confirmations, nothing. 2) I can't make a dump, because I can't start neither 9.5, nor 9.4 cluster with this data. 9.4 cluster says that this folder was created with 9.5 and won't start. How to restore my data? — Alexander Matveev, Oct 30 '15 at 10:43
@a_horse_with_no_name Good point - the alpha2 to beta1 upgrade likely being what broke the user's system. — Craig Ringer, Oct 30 '15 at 10:50
@CraigRinger Edited question with two update sections. I would be grateful if you could help me. — Alexander Matveev, Oct 30 '15 at 11:02
@CraigRinger after all discussions I made all by your advices. You are absolutely right about using alpha and beta versions. Now I can't remember, why I installed this version, but looks like I needed some features. Now I downgraded to 9.5 alpha and I have all my data. I'll stay on this version or maybe I'll downgrade to 9.4. Marking your answer as right. Many thanks! — Alexander Matveev, Oct 30 '15 at 12:45
Do *not* stay on 9.5 alpha. Dump your data now - use `pg_dumpall`, or use `pg_dumpall --globals-only` then `pg_dump -Fc` for each database. Then either downgrade to 9.4, or upgrade to 9.5beta1. — Craig Ringer, Oct 30 '15 at 12:51

"The database cluster was initialized with CATALOG_VERSION_NO X, but the server was compiled with CATALOG_VERSION_NO Y." after aptitude upgrade

1 Answers1