#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

[Tobi H]

...and this one

[Ron OHara]

Solution is shown at the end of this bug report.

[654th]

Same here with Debian 10 buster, PostgreSQL Version 12.0 and Webmin 1.930

[Ilia]

This is odd, as I installed PostgreSQL 12 on my Debian 10, and without any issues could list databases, with the names identical to psql -l command.

Is it possible that it's specific to your system/locale issue?

Did you check PostgreSQL 12 changelog to possibly get any close idea of why it might be happening on your side?

Maybe Jamie could fix it without being able to reproduce it, while I don't think I could.

Did you try changing global Webmin language to English (UTF8)?

[Ilia]

For me it works with any system language.

[654th]

This is a clean install without any local changes.

[Ilia]

How did you install version 12 of PGSQL?

[654th]

Same way as I did often before:

sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt/ buster-pgdg main" >> /etc/apt/sources.list.d/pgdg.list'

sudo apt-get update
sudo apt-get install postgresql postgresql-contrib

[Ilia]

I did simply this:

wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc |  apt-key add -
echo "deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main" | tee  /etc/apt/sources.list.d/pgdg.list
apt update
apt -y install postgresql-12 postgresql-client-12

It worked for me.

While this is clean install did you import db using Webmin or somehow else?

[654th]

And yes - of course I added the key first.

There are fresh databases also, but from the beginning there are numbers only instead of table names.

[Ilia]

Is there a way I could get one of those databases which produce broken output?

[Ilia]

Besides, what is the content of postgresql.conf config file?

[654th]

Is there a way I could get one of those databases which produce broken output?

This issue exist from the beginnig wit standard databases

Besides, what is the content of postgresql.conf config file?

Well, this is the standard content, created by the installer - more than 700 rows.

[Ilia]

This is odd, as I just installed Debian 10 and PostgreSQL 12 (using repo above) and it just works flawlessly.

What is the output of:

psql -V
which psql
whereis psql

[654th]

psql -V
psql (PostgreSQL) 12.0 (Debian 12.0-2.pgdg100+1)
which psql
/usr/bin/psql
whereis psql
psql: /usr/bin/psql /usr/share/man/man1/psql.1.gz

[Ilia]

The same on mine.

What is the output of:

/etc/webmin/postgresql/config

The one on the server I installed just now is:

root@debug-debian10:~# cat /etc/webmin/postgresql/config
nodbi=0
blob_mode=0
port=
pid_file=/var/run/postmaster.pid
perpage=25
webmin_subs=0
sync_create=1
hba_conf=/etc/postgresql/*/main/pg_hba.conf
host=
simple_sched=0
dump_cmd=/usr/bin/pg_dump
sync_modify=1
access=*: *
basedb=template1
sameunix=1
date_subs=0
start_cmd=/etc/init.d/postgresql* start
psql=/usr/bin/psql
max_dbs=50
login=postgres
max_text=1000
pass=123
stop_cmd=/etc/init.d/postgresql* stop
access_own=0
repository=
sync_delete=1
plib=
style=0
setup_cmd=
encoding=
rstr_cmd=/usr/bin/pg_restore
add_mode=1

[654th]

ameunix=1
nodbi=0
dump_cmd=/usr/bin/pg_dump
psql=/usr/bin/psql
blob_mode=0
add_mode=1
date_subs=0
simple_sched=0
access=*: *
rstr_cmd=/usr/bin/pg_restore
pid_file=/var/run/postmaster.pid
start_cmd=/etc/init.d/postgresql* start
webmin_subs=0
max_dbs=50
perpage=25
style=0
login=postgres
basedb=template1
access_own=0
stop_cmd=/etc/init.d/postgresql* stop
plib=
hba_conf=/etc/postgresql/*/main/pg_hba.conf
pass=
max_text=1000

[Ilia]

Login to db server and run:

SHOW SERVER_ENCODING;

and

SHOW CLIENT_ENCODING;

What is the output?

[654th]

server and client encoding are both UTF8

[Ilia]

Okay, what if you open /usr/share/webmin/postgresql/postgresql-lib.pl and find/replace:

Line 149:

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

to:

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

It's just a guess though, as I can't reproduce it.

Give me the exact names of your databases.

[Ron OHara]

Thanks for the hint... not quite right. (Solution from Ilja Rostotsev)

What has happened is that 11.x returns 'datanam' 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:

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'}};

[Ilia]

I am just curious, what if you add to index.cgi file, before require the following:

use utf8;

Will it work with standard sorting?

[Ilia]

Any news?

[Tobi H]

So, i've opened the Bug and i have now the question, how can we update the postgresql module for webmin to get rid of the problem?

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

Means "this database does not accept any connections at the moment"

But that's not true because i can connect my db very well by matrix-synapse server and localy by entering the postgresql admin user credentials.

[Tobi H]

What will happen if i delete the complete module from webmin config and also delete the dependent users and reset all postgres settinghs by webmin?

I don't want to "barbeque" my server setup so - if i reinstall after that - will webmin reissue my postgresql user as well?