After upgrading postgresql-databases are not accessible any more

[Thomas Schweikle]

- Install postgresql on 11.04, configure applications to access postgres
- Upgrade from 11.04 to 11.10
- none of the postgresql-databases are accessible any more.

Here is one error message as given by a WEB application:
Fatal error: Uncaught exception 'Zend_Exception' with message 'Fatal error: Can't connect to SQL server' in /opt/webacula/html/index.php:178
Stack trace:
#0 {main}
  thrown in /opt/webacula/html/index.php on line 178

It looks like the postgresql-database is lost after upgrading. This is fatal. Not for a test sytem, but for a productive one!
There are no instructions what to do before upgrading or after upgrading. The only Instructions found are those on the PostgreSQL web site. There are no warnings, no nothing. Absolutely bad. I suppose this a critical error and showstopper!

ProblemType: Bug
DistroRelease: Ubuntu 11.10
Package: postgresql 9.1+122
ProcVersionSignature: Ubuntu 3.0.0-12.20-virtual 3.0.4
Uname: Linux 3.0.0-12-virtual x86_64
ApportVersion: 1.23-0ubuntu3
Architecture: amd64
Date: Mon Oct 10 18:38:48 2011
InstallationMedia: Ubuntu-Server 10.04.1 LTS "Lucid Lynx" - Release amd64 (20100816.2)
PackageArchitecture: all
ProcEnviron:
 PATH=(custom, user)
 LANG=en_US.UTF-8
 SHELL=/bin/bash
SourcePackage: postgresql-common
UpgradeStatus: Upgraded to oneiric on 2011-10-10 (0 days ago)

[Martin Pitt (pitti)]

The provided error messages are a little too vague. Can you please give me the output of "pg_lsclusters"? what happens if you try

  sudo -u postgres psql template1

Does this get you into the postgresql console? If not, what's the output?

[Dries Cox (driescox)]

Hello,
I've got the same problem.
If i do a sudo -u postgres psql template1 i get

"psql: could not connect to server: Bestand of map bestaat niet
 Is the server running locally and accepting
 connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?"

It looks like the server is not running, but I don't succeed in making it run :-(

Thanks in advance for all the help

[Dries Cox (driescox)]

I downgraded to 8.4 again

sudo apt-get remove postgresql

and installed 8.4

sudo apt-get install postgresql-8.4

Now it works perfect again.

[Thomas Schweikle (tps)]

The problem is quite simple: while upgrading, postgres-8.4 is replaced by postgres-9.1. The newly installed server ist then started, but without migrating the databases from 8.4 to 9.1

The migration described on the postgres project site are not working for Ubuntu: pg_upgrade is there, but pg_config is missing. The way described it impossible.

The only available:
- dump the whole database, while the old server is running
- stop the old server, start the new one
- import the database again

[Martin Pitt (pitti)]

> The problem is quite simple: while upgrading, postgres-8.4 is replaced by postgres-9.1.

No, it's not. 9.1 gets installed in addition.

> The migration described on the postgres project site are not working for Ubuntu:

On upgrade you should have gotten a debconf note that describes how to do the upgrade. Please have a look at /usr/share/doc/postgresql-common/README.Debian.gz.

If you want to use pg_upgrade, you can install libpq-dev to get pg_config.

I suppose everything is alright on your system, but to verify I still need the answers to my questions in comment 2. Thanks!

[Migs de Villa (jc-devilla)]

I experienced the same thing and freaked when I couldn't access the databases. Same thing, 11.04 upgrade to 11.10. Postgres 8.4 and postgres 9.1 got installed side-by-side.

However, if you check the config at /etc/postgresql/9.1/main/postgresql.conf, it listens on port 5433 and postgres8.4 would be on 5432.

Of course, you have to dump/restore the data from the 8.4 instance to the 9.1 instance. Once you verify that the data is fully transferred, you can blow away the 8.4 instance, and update its config to use port 5432 as normal.

[Thomas Schweikle (tps)]

From comment #2
> The provided error messages are a little too vague.
> Can you please give me the output of "pg_lsclusters"?
> what happens if you try
>
> sudo -u postgres psql template1
>
> Does this get you into the postgresql console?

Since I could find what was going wrong, I managed to migrate data and the postgres-server is now running fine.

While upgrading postgres-8.4 was removed, but config files where held. I just had to reinstall postgres-server-8.4 to have access to my old database. Wasn't a real problem. Now I have both installed and only postgres-9.1 running (they have the same configuration, thus sharing the same port --- I'll remove the old version as soon as I am sure all is running again as expected).

> If not, what's the output?

[Thomas Schweikle (tps)]

From comment #6
>> The problem is quite simple: while upgrading, postgres-8.4 is replaced by postgres-9.1.
>
> No, it's not. 9.1 gets installed in addition.

Sometimes yes, sometimes no. In case of the server in question postgres-8.4 was removed, only the configuration files and databases remained. On an other system postgres-8.4 stood in place, but wasn't started after rebooting. On my third system after upgrading both servers ran. Postgres-8.4 on the standard port, Postgres-9.1 on a port one higher than the standard.

>> The migration described on the postgres project site are not working for Ubuntu:
>
> On upgrade you should have gotten a debconf note that
> describes how to do the upgrade. Please have a look at
> /usr/share/doc/postgresql-common/README.Debian.gz.

Yes, the note is printed. But on a window 132x43 it takes half a second and it is scrolled out of sight. Looking through the apt-logs doesn't really help. It is to easy to overlook such a note. The best I bet would be to pipe notes like that to an extra file, displayed after the upgrade terminates, not forcing one to sit and watch the screen awaiting something important scrolling by.

> If you want to use pg_upgrade, you can install libpq-dev to get pg_config.
>
> I suppose everything is alright on your system, but to verify
> I still need the answers to my questions in comment 2. Thanks!

The only thing not OK at now is duplicate client entries warned about by bacula.

[Martin Pitt (pitti)]

Having postgresql-8.4 removed IS a problem indeed, that's not intended to work. It seems it was dropped from update-manager's ./DistUpgrade/removal_blacklist.cfg. Michael, can we please put that back and SRU it quickly?

[Martin Pitt (pitti)]

> Yes, the note is printed. But on a window 132x43 it takes half a second and it is scrolled out of sight.

That also sounds weird. It is supposed to be a debconf note which stays on the screen forever until you press Enter. How exactly did you upgrade? With the graphical update program, apt-get, aptitude, do-release-upgrade? Under Ubuntu (GNOME) or Kubuntu (KDE)?

[Martin Pitt (pitti)]

Hello Thomas, or anyone else affected,

Accepted update-manager into oneiric-proposed, the package will build now and be available in a few hours. Please test and give feedback here. See https://wiki.ubuntu.com/Testing/EnableProposed for documentation how to enable and use -proposed. Thank you in advance!

[Launchpad Janitor (janitor)]

This bug was fixed in the package update-manager - 1:0.152.25.4

---------------
update-manager (1:0.152.25.4) oneiric-proposed; urgency=low

  * DistUpgrade/removal_blacklist.cfg:
    - ensure that postgresql does not get removed (LP: #871893)

update-manager (1:0.152.25.3) oneiric-proposed; urgency=low

  * DistUpgrade/DistUpgradeCache.py:
    - fix crash when packages needs downgrading
  * UpdateManager/Core/utils.py:
    - do not crash if iptables does not exist, thanks to Daniel
      Holbach for reporting the issue (LP: #877514)
  * DistUpgrade/DistUpgradeQuirks.py:
    - keep poking the screensaver to ensure that it really won't
      activate during the upgrade (thanks to Jonathan Davies for
      the report)
  * tests/test_sources_list.py:
    - fix test (archive.ubuntu.com no longer listens to ftp)
  * po/*:
    - refresh again to fix regression (LP: #877461)
 -- Michael Vogt <email address hidden> Wed, 19 Oct 2011 09:52:08 +0200

[Jean-Baptiste Lallement (jibel)]

SRU verification for Oneiric:
I have reproduced the problem with update-manager 1:0.152.25 in oneiric and have verified that the version of update-manager 1:0.152.25.4 in -proposed fixes the issue.

Marking as verification-done

[Dmitry Shachnev (mitya57)]

Fixed in 1:0.154.

[KK (krzyklo)]

Kubuntu 32 bit, upgrade from Natty to Oneirc through do-upgrade.
In contrary to previous posts it seems that I have lost 2 databases during upgrade from Natty to Oneiric.

After upgrade folder /usr/lib/postgresql/9.1/main/base contains only 3 databases - template0, template1, postgres.
There are no /usr/lib/postgresql/8.4/main/base folder where I had 2 important databases (one with personal finance data (Gnucash) ).
The original folder /usr/lib/postgresql/8.4/main/base contained 5 databases.

I didn't remember any clear warning or option to choose regarding what will happen with postgresql during upgrade process..

I do Kubuntu upgrades earlier, and never lost databases, therefore didn't backuped myself..
You can call me careless (yes I will move database folder to home partition and start from now on using Cron+rsync to do database backups), but I still coudn't believe that developers allowed to loss Postgresql databases during standard upgrade ???????????????????????????
Really bitter lesson...

2 weeks ago I have moved to new laptop, and the old one still have Natty, it saved me from the total disaster... but I spent 2 days trying to figure out what am I do wrong during upgrade??? and still don't know..

I probably leave this unresolved and agree to lost 2 weeks and reproduce from old laptop databases at new Oneiric.

BTW. I must express my great dissapointment about Nepomuk/Strigi behaviour after upgrade.. I must switch them off because they couldn't end indexing process and calm down during 72 hours of usage ~100% of CPU..

[Martin Pitt (pitti)]

H H [2011-10-30 13:12 -0000]:
> After upgrade folder /usr/lib/postgresql/9.1/main/base contains only 3 databases - template0, template1, postgres.

Note that the actual databases are in /var/lib/postgresql/. They
shouldn't have been lost during the upgrade, if you reinstall
postgresql-8.4 it should work just fine.

[KK (krzyklo)]

@Martin Pitt
>Note that the actual databases are in /var/lib/postgresql/. They
>shouldn't have been lost during the upgrade, if you reinstall
>postgresql-8.4 it should work just fine.

My typo, I had looked for my lost databases in folder /var/lib/postgresql of course, not in /usr/lib/postgresql.