#5322 postgresql server modul don't show database after upgrade 11.5 to 12

[Tobi H]

I used PostgreSQL 11.x (11.5 was the latest) before i upgraded my Server to PostgreSQL 12.

I used su - postgres for login to my server
after that i saved my db to pg_dumpall > Sicherung
after that i listed my clusters pg_lsclusters
then stopping 12 with sudo pg_dropcluster 12 main --stop
...and upgraded to 12 with sudo pg_upgradecluster 11 main

During prozess i've got many OK messages and after finish i checked
if my server app using postgres was working fine -> all OK

Then i dropped my old cluster
sudo pg_dropcluster 11 main

Back to webmin 1930 i switched to server -> postgres db server and saw this

1 -> 13445 -> 13446 -> 16384 instead of the db names of the db's
The databases are still there because when i list postgres i get the real names of my databases

su - postgres
psql -l

in webmin clicking on one of the four "db entries" above delivers

Diese Datenbank akzeptiert zur Zeit keine Verbindungen. Deshalb können keine Aktionen in ihr durchgeführt werden.

(no access because of db does not accept connections)

??confused?? because my login to postgres is possible and my app is still working with postgres username + password as before.

So i assume a webmin bug not working propperly with postgres 12

[Ilia]

If you want us to let you know more, please share the database with us or at least an empty database that fails to work. What is the exact database name? What is the username that your are using to connect to it? What are your system locale settings set to?

[Tobi H]

As far as i wrote in first post - my PostgreSQL Version is 12.1 under ubuntu server 18.04.3

I upgraded from 11.5 to 12.0 - now we allready have the update to 12.1 without any changes to webmin.

My Problem is not that i can't connect to my databases or have any problem to do so.
My command sudo -u postgres psql leads me properly to my database and i can connect to it
\c DB_NAME

Also my server application matrix-synapse (using psycopg2) is able to connect properly to my database on PostgreSQL Server 12.1 with username = postgres and password = my.password

i can list my existing databases by using command \l under psql

All fine so far. So everything is working except webmin!! What i did was (for reproduce it)

1) make install of an PostgreSQL 11.x DB (now we are at 11.6 i think)
2) log in to your Postgres sudo -u postgres psql
3) create a test db by CREATE_DATABASE test_db
4) if you have old data you may be willing to import that by pg_restore -d test_db /path/to/your/dump.file -c -U postgres
5) upgrade your postgresql instance with sudo -u postgres -i followed up by pg_upgradecluster 11.x main
6) check if your new cluster is working properly (12.1 main e.g.)
7) if your server app (in my case matrix-synapse) is connecting as well as before then
8) pg_dropcluster 11.x main
9) apt-get remove --purge postgresql-11*
10) apt-get autoremove -y
11) apt-get autoclean
12) reboot server
13) re-login to webmin and check waht happend

What i did more was
uninstall webmin and delete all folders (purged)
re-install webmin with new source 1.930 e.g. after postgresql is clean installed - means no rest of older versions 9.x / 10.x / 11.x

To me the same error

postgresql module is not able to connect to db
but i can administrate the Postgre Users / Groups ???

There must be something definitely wrong with the webmin module since >12.x support

It will not help you to give you a database - i could give you my template1 e.g. but that is the same as you yours. So please reproduce my steps above - it will be easier to see localy what happened.

[Ron OHara]

See the solution at the end of this

[Alex Sant'Anna]

I've the same problem, but I've two servers, in one server migrate the 11 cluster to 12 and dropped de 11 cluster, in this server I've the above bug. In my other server I havent dropped the 11 cluster yet and webmin works fine, it shows Postgres 12, but the databases are still on version 11.

Maybe something related to this ? https://www.postgresql.org/message-id/3A344FC1-C695-478A-BD1A-6ADFA50C6227%40yesql.se

[Tobi H]

interesting that variant !!

So webmin developers - please give us a hint how to solve those problems.
As i wrote above, my postgresql database is working as expected after the upgrade. Only the webui administration by webmin is leaking ....

What could be the difference between a fresh install or a "first-install on a fresh installed linux" and the upgraded versions from 9/10/11 -> 12 - allthough the upgraded databases are properly reachable by other clients / terminal login??

[Ron OHara]

(Solution hint from Ilja Rostotsev earlier)

SOLVED

What has happened is that 11.x returns 'datname' as the [0] element of the select. 12.1 return a number as [0] element, and 'datname' as [1] element.

you open /usr/share/webmin/postgresql/postgresql-lib.pl and find/replace:
(you may have installed from source into /opt/webmin/postgresql/postgresql-lib.pl - if so edit that)

So you change line 149: from

return sort { lc($a) cmp lc($b) } map { $_->[0] } @{$t->{'data'}};

TO

return sort { lc($a) cmp lc($b) } map { $_->[1] } @{$t->{'data'}};

EDIT

This should probably be DB version sensitive, because it will be wrong if you connect to a version 11 DB ... and correct if you connect to a version 12 DB ... so treat this as a 12.x work around. I dont know where to find the connected DB version.

A better SOLUTION

EDIT 2 ... DB version independent solution

Leave the 'return' line untouched. Changes the select statement from

local $t = &execute_sql_safe($config{'basedb'}, 'select * from pg_database
order by datname');

TO

local $t = &execute_sql_safe($config{'basedb'}, 'select datname from pg_database
order by datname');

That places 'datname' in the [0] element for all versions of the DB
Then the unchanged return works fine.

return sort { lc($a) cmp lc($b) } map { $_->[0] } @{$t->{'data'}};

[Tobi H]

Cherio Mrs. Sophie :-)

Like this solution!! A very short but very effective one. It should become urgently default in the next release of the postgre modul for webmin.

I edited the line as "EDIT 2 ...."
Thanks a lot.

[Tobi H]

@all : how can I edit the header "#5322 postgresql server modul don't show database after upgrade 11.5 to 12"

like this

"[SOLVED]#5322 postgresql server modul don't show database after upgrade 11.5 to 12"

I did not see any pencil to edit my posted Header??

[Jamie Cameron]

Thanks - the next release of Webmin will include this fix to be not dependent on the column order : https://github.com/webmin/webmin/commit/e5dbd8e46cd2dee0293f1fb3ff31bd6bfa2147da