[Pkg-postgresql-public] Bug#838812: pg_upgradecluster skips databases owned by a role who is not a user

Jean-Christophe Boggio cat at thefreecat.org
Sun Sep 25 10:38:26 UTC 2016


Package: postgresql-common
Version: 173
Severity: critical

When doing pg_upgradecluster, all databases owned by a role (a group of 
users) and not a user are silently skipped. Even worse, these databases 
are recreated EMPTY ! If you don't take a close look before dropping the 
old cluster, you'd better have a good backup.

Problem probably comes from the query on line 438 :

SELECT datname, datallowconn, pg_catalog.pg_encoding_to_char(encoding), 
usename FROM pg_database, pg_user WHERE datdba = usesysid

This should rather be written :

SELECT d.datname, 
datallowconn,pg_catalog.pg_encoding_to_char(encoding),pg_catalog.pg_get_userbyid(d.datdba) 
as usename FROM pg_catalog.pg_database d;


I haven't used this fix to solve my problem. I was in a hurry so I gave 
all databases to postgres, did the pg_upgradecluster and gave back the 
databases to their respective owners afterwards.

What makes me think this query is the good one is that the only 
correctly converted databases were the ones shown by this query (maybe 
there's another similar query in the script).



More information about the Pkg-postgresql-public mailing list