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
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.