Upgrade to postgresql 9.6 fails

I upgraded already four UCS installations from 4.4-8 to 5.0-0. For this I needed to upgrade the Postgresql installation from version 9.4 to 9.6. On the fifth installation this upgrade fails:

pg_dropcluster 9.6 main --stop

gives the message: specified cluster does not exist
and

chown -R postgres:postgres /var/lib/postgresql/9.6

gives the message: file or directory not found.

I suspect that somehow the installation of 9.6 did not complete correctly.

What can I do to force the installation of Postgresql-9.6 so that I am finally able to update the UCS ?

Thanks for any thoughts - Martin

Okay, here some more information. We tried the update initially on a slave machine which was originally installed as version 4.1-0 and updated to 4.4-8. Both master and slave do not have any apps installed but simply run as ADC and file-server.

This was our update process:

root@dcs1:~# [ -f /usr/sbin/univention-pkgdb-scan ] && chmod -x /usr/sbin/univention-pkgdb-scan
root@dcs1:~# service postgresql stop
root@dcs1:~# rm -rf /etc/postgresql/9.6
root@dcs1:~# apt-get install --reinstall postgresql-9.6
Paketlisten werden gelesen... Fertig
Abhängigkeitsbaum wird aufgebaut.
Statusinformationen werden eingelesen.... Fertig
Die folgenden Pakete wurden automatisch installiert und werden nicht mehr benötigt:
  linux-image-4.9.0-14-amd64 linux-image-4.9.0-14-amd64-signed
Verwenden Sie »apt autoremove«, um sie zu entfernen.
The following additional packages will be installed:
  postgresql-contrib-9.6 sysstat
Vorgeschlagene Pakete:
  locales-all libdbd-pg-perl isag
Die folgenden NEUEN Pakete werden installiert:
  postgresql-9.6 postgresql-contrib-9.6 sysstat
0 aktualisiert, 3 neu installiert, 0 zu entfernen und 0 nicht aktualisiert.
Es müssen 5.192 kB an Archiven heruntergeladen werden.
Nach dieser Operation werden 25,3 MB Plattenplatz zusätzlich benutzt.
Möchten Sie fortfahren? [J/n] j
Holen:1 https://updates.software-univention.de/4.4/maintained/component 4.4-8-errata/amd64/ postgresql-9.6 9.6.24-0+deb9u1 [4.327 kB]
Holen:2 https://updates.software-univention.de/4.4/maintained/component 4.4-8-errata/amd64/ postgresql-contrib-9.6 9.6.24-0+deb9u1 [504 kB]
Holen:3 https://updates.software-univention.de/4.3/maintained 4.3-0/amd64/ sysstat 11.4.3-2 [360 kB]
Es wurden 5.192 kB in 5 s geholt (937 kB/s).
Vorkonfiguration der Pakete ...
Vormals nicht ausgewähltes Paket postgresql-9.6 wird gewählt.
(Lese Datenbank ... 114393 Dateien und Verzeichnisse sind derzeit installiert.)
Vorbereitung zum Entpacken von .../postgresql-9.6_9.6.24-0+deb9u1_amd64.deb ...
Entpacken von postgresql-9.6 (9.6.24-0+deb9u1) ...
Vormals nicht ausgewähltes Paket postgresql-contrib-9.6 wird gewählt.
Vorbereitung zum Entpacken von .../postgresql-contrib-9.6_9.6.24-0+deb9u1_amd64.deb ...
Entpacken von postgresql-contrib-9.6 (9.6.24-0+deb9u1) ...
Vormals nicht ausgewähltes Paket sysstat wird gewählt.
Vorbereitung zum Entpacken von .../sysstat_11.4.3-2_amd64.deb ...
Entpacken von sysstat (11.4.3-2) ...
sysstat (11.4.3-2) wird eingerichtet ...

Creating config file /etc/default/sysstat with new version
update-alternatives: /usr/bin/sar.sysstat wird verwendet, um /usr/bin/sar (sar) im automatischen Modus bereitzustellen
insserv: warning: script 'K01univention-system-setup-boot' missing LSB tags and overrides
insserv: warning: script 'univention-system-setup-boot' missing LSB tags and overrides
insserv: warning: script 'univention-system-setup-boot-prepare' missing LSB tags and overrides
postgresql-9.6 (9.6.24-0+deb9u1) wird eingerichtet ...
Creating new cluster 9.6/main ...
  config /etc/postgresql/9.6/main
  data   /var/lib/postgresql/9.6/main
  locale de_DE.UTF-8
  socket /var/run/postgresql
  port   5433
update-alternatives: /usr/share/postgresql/9.6/man/man1/postmaster.1.gz wird verwendet, um /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) im automatischen Modus bereitzustellen
Trigger für systemd (232-25+deb9u13A~4.4.8.202107221144) werden verarbeitet ...
Trigger für postgresql-common (181+deb9u3A~4.4.0.201911181236) werden verarbeitet ...
Building PostgreSQL dictionaries from installed myspell/hunspell packages...
Removing obsolete dictionary files:
Trigger für man-db (2.7.6.1-2) werden verarbeitet ...
postgresql-contrib-9.6 (9.6.24-0+deb9u1) wird eingerichtet ...
root@dcs1:~# pg_dropcluster 9.6 main --stop
Redirecting stop request to systemctl
root@dcs1:~# service postgresql start
root@dcs1:~# test -e /var/lib/postgresql/9.6/main && mv /var/lib/postgresql/9.6/main
root@dcs1:~# test -e /var/lib/postgresql/9.6/main && mv /var/lib/postgresql/9.6/main /var/lib/postgresql/9.6/main.old
root@dcs1:~# pg_upgradecluster 9.4 main
pg_controldata: could not open file "/var/lib/postgresql/9.4/main/global/pg_control" for reading: No such file or directory
Use of uninitialized value $info{"owneruid"} in getpwuid at /usr/bin/pg_upgradecluster line 323.
Disabling connections to the old cluster during upgrade...
Use of uninitialized value in subroutine entry at /usr/bin/pg_upgradecluster line 205.
Restarting old cluster with restricted connections...
Error: /var/lib/postgresql/9.4/main is not accessible or does not exist
Error: Could not restart old cluster
root@dcs1:~# ucr commit /etc/postgresql/9.6/main/*
root@dcs1:~# chown -R postgres:postgres /var/lib/postgresql/9.6
chown: Zugriff auf '/var/lib/postgresql/9.6' nicht möglich: Datei oder Verzeichnis nicht gefunden
root@dcs1:~# service postgresql restart
root@dcs1:~# [ -f /usr/sbin/univention-pkgdb-scan ] && chmod +x /usr/sbin/univention-pkgdb-scan
root@dcs1:~# univention-install --yes univention-postgresql-9.6
Paketlisten werden gelesen...
E: Konnte Sperre /var/lib/apt/lists/lock nicht bekommen - open (11: Die Ressource ist zur Zeit nicht verfügbar)
E: Das Verzeichnis /var/lib/apt/lists/ kann nicht gesperrt werden.
root@dcs1:~#

Actually I don’t know what postgresql is used for. Any ideas on how to proceed are welcome.

Thanks - Martin

Before you continue please execute pg_lsclusters to get a list of databases from your host. This should print something like this:

Ver Cluster Port Status Owner    Data directory              Log file
11  main    5432 online postgres /var/lib/postgresql/11/main /var/log/postgresql/postgresql-11-main.log

In my case I’am already on UCS 5.0-1 and only have PostgreSQL 11 installed. Your output will be different as you are still on an older version of PostrgreSQL 8.x. Depending on which version you have you have to plan your update:

  • if you don’t have any database at all you don’t have to do anything
  • otherwise you have to use pg_upgradecluster $old $name to upgrade each database to the current version. What you have to insert instead of $old and $name heavily depends on your setup.

For both servers the output looks like this:

Ver Cluster Port Status Owner     Data directory               Log file
9.4 main    5432 down   <unknown> /var/lib/postgresql/9.4/main /var/log/postgresql/postgresql-9.4-main.log

I then tried to open the logfile but the directory /var/log/postgresql is empty on both machines. I assume that both servers do not use postgresql so I can remove postgresql-9.4 safely.

Your 2nd post already had

Error: /var/lib/postgresql/9.4/main is not accessible or does not exist

The missing directory /var/log/postgresql/ does not look good as it should be there.
So next please check if that directory (still) exists on your server: ls -lh /var/lib/postgresql/9.4 /etc/postgresql/*

My guess is that some configuration file is already removed by the upgrade so your old PostgreSQL 9.4 is now missing some files. A healthy PostrgreSQL 11 should look like this:

# ls -l /var/lib/postgresql/11/main/
insgesamt 84
drwx------ 5 postgres postgres 4096 Dez 16 09:53 base
drwx------ 2 postgres postgres 4096 Dez 16 09:54 global
drwx------ 2 postgres postgres 4096 Dez 16 09:53 pg_commit_ts
drwx------ 2 postgres postgres 4096 Dez 16 09:53 pg_dynshmem
drwx------ 4 postgres postgres 4096 Dez 16 09:59 pg_logical
drwx------ 4 postgres postgres 4096 Dez 16 09:53 pg_multixact
drwx------ 2 postgres postgres 4096 Dez 16 09:54 pg_notify
drwx------ 2 postgres postgres 4096 Dez 16 09:53 pg_replslot
drwx------ 2 postgres postgres 4096 Dez 16 09:53 pg_serial
drwx------ 2 postgres postgres 4096 Dez 16 09:53 pg_snapshots
drwx------ 2 postgres postgres 4096 Dez 16 09:54 pg_stat
drwx------ 2 postgres postgres 4096 Dez 16 09:53 pg_stat_tmp
drwx------ 2 postgres postgres 4096 Dez 16 09:53 pg_subtrans
drwx------ 2 postgres postgres 4096 Dez 16 09:53 pg_tblspc
drwx------ 2 postgres postgres 4096 Dez 16 09:53 pg_twophase
-rw------- 1 postgres postgres    3 Dez 16 09:53 PG_VERSION
drwx------ 3 postgres postgres 4096 Dez 16 09:53 pg_wal
drwx------ 2 postgres postgres 4096 Dez 16 09:53 pg_xact
-rw------- 1 postgres postgres   88 Dez 16 09:53 postgresql.auto.conf
-rw------- 1 postgres postgres  130 Dez 16 09:54 postmaster.opts
-rw------- 1 postgres postgres  100 Dez 16 09:54 postmaster.pid

same for 9.4 - if it is vastly different some files may already have been removed by your pg_dropcluster … from your 2nd posing.

If this DB is unused you should be able to just drop the database by using pg-dropcluster 9.4 main

Oooops, I finally removed PostgreSQL-9.4 from the servers as it probably did not work anyway.

I will now try to upgrade the servers to UCS 5.0 during a maintenance window. The upgrade check did not complain anymore (except the slave about the master not being updated first).

Mastodon