Package: postgresql-common
Version: 177.pgdg80+1
Followup-For: Bug #838812
Dear Maintainer,
I can confirm the problem; the attached patch fixes it.
A brief explanation: In the query to select databases which need upgrading,
pg_database is joined onto pg_user, which is a view of pg_shadow, which in turn
is a view of pg_authid, containing only roles with login privileges. Thus any
databases owned by roles without login privileges are not included in the list.
The fix is to join on pg_authid instead.
See https://www.postgresql.org/docs/9.6/static/view-pg-user.html and
https://www.postgresql.org/docs/9.6/static/view-pg-shadow.html .
Regards, Til
PS #614374 is a duplicate of this bug.
-- System Information:
Debian Release: 8.6
APT prefers stable-updates
APT policy: (500, 'stable-updates'), (500, 'stable')
Architecture: amd64 (x86_64)
Foreign Architectures: i386
Kernel: Linux 3.16.0-4-amd64 (SMP w/4 CPU cores)
Locale: LANG=en_US.UTF-8, LC_CTYPE=en_US.UTF-8 (charmap=UTF-8)
Shell: /bin/sh linked to /bin/dash
Init: systemd (via /run/systemd/system)
Versions of packages postgresql-common depends on:
ii adduser 3.113+nmu3
ii debconf [debconf-2.0] 1.5.56
ii init-system-helpers 1.22
ii lsb-base 4.1+Debian13+nmu1
ii postgresql-client-common 177.pgdg80+1
ii procps 2:3.3.9-9
ii ssl-cert 1.0.35
ii ucf 3.0030
Versions of packages postgresql-common recommends:
ii logrotate 3.8.7-1+b1
postgresql-common suggests no packages.
--- /usr/bin/pg_upgradecluster.orig 2016-10-31 13:38:45.942422918 +0100
+++ /usr/bin/pg_upgradecluster 2016-10-31 13:40:34.561540476 +0100
@@ -439,7 +439,7 @@
my %databases;
open F, '-|', $oldpsql, '-h', $oldsocket, '-p', $info{'port'},
'-F|', '-d', 'template1', '-Atc',
- 'SELECT datname, datallowconn, pg_catalog.pg_encoding_to_char(encoding), usename FROM pg_database, pg_user WHERE datdba = usesysid' or
+ 'SELECT datname, datallowconn, pg_catalog.pg_encoding_to_char(encoding), rolname FROM pg_database, pg_roles WHERE datdba = pg_roles.oid' or
error 'Could not get pg_database list';
while (<F>) {
chomp;
