Database creation issue with fr_FR encoding in a PostgreSQL cluster

[oavril]

Hello,

i use everest 1.3.0

I am encountering a difficulty when creating a database.

If I do this:

CREATE DATABASE "test1" 
\c test1

it works.

But as soon as I use the template0 to modify the collation:

CREATE DATABASE "test2" WITH OWNER "postgres" ENCODING 'UTF8' LC_COLLATE = 'fr_FR.UTF-8' LC_CTYPE = 'fr_FR.UTF-8' TEMPLATE template0; 
\c test2

I get this message:

The connection to the server on "xxx.xxx.xxx.xxx", port 5432 failed: FATAL: bouncer config error 
Previous connection kept.

the displayed IP corresponds to the external IP of the cluster

Can you tell me what is wrong or if there is an action I need to perform beforehand?

[oavril]

Hello, In addition,

If I connect to pgBouncer with the command: psql "sslmode=require" -U postgres -h xxx.xxx.xxx.xxx -p 5432

• if I go through pgbouncer, the problem occurs.
  

• if I use the same command in the pod of one of the postgres instances (i.e., without going through pgbouncer), everything works.
  

the log of the pgbouncer pod provides me with the following information:

for "\c test1":

2025-01-07 09:42:38.572 UTC [7] LOG S-0x564589741070: postgres/_crunchypgbouncer@xxx.xxx.xxx.xxx:5432 new connection to server (from xxx.xxx.xxx.xxx:57626)
2025-01-07 09:42:38.577 UTC [7] LOG S-0x564589741070: postgres/_crunchypgbouncer@xxx.xxx.xxx.xxx:5432 SSL established: TLSv1.3/TLS_AES_256_GCM_SHA384/ECDH=prime256v1
2025-01-07 09:42:38.591 UTC [7] LOG C-0x564589727120: postgres/postgres@xxx.xxx.xxx.xxx:21835 login attempt: db=postgres user=postgres tls=TLSv1.3/TLS_AES_256_GCM_SHA384
2025-01-07 09:42:43.292 UTC [7] LOG C-0x564589727120: postgres/postgres@xxx.xxx.xxx.xxx:2137 login attempt: db=postgres user=postgres tls=TLSv1.3/TLS_AES_256_GCM_SHA384
2025-01-07 09:42:46.798 UTC [7] LOG S-0x564589741320: test1/_crunchypgbouncer@xxx.xxx.xxx.xxx:5432 new connection to server (from xxx.xxx.xxx.xxx:39122)
2025-01-07 09:42:46.805 UTC [7] LOG S-0x564589741320: test1/_crunchypgbouncer@xxx.xxx.xxx.xxx:5432 SSL established: TLSv1.3/TLS_AES_256_GCM_SHA384/ECDH=prime256v1
2025-01-07 09:42:46.820 UTC [7] LOG C-0x5645897273d0: test1/postgres@xxx.xxx.xxx.xxx:2851 login attempt: db=test1 user=postgres tls=TLSv1.3/TLS_AES_256_GCM_SHA384
2025-01-07 09:42:46.831 UTC [7] LOG C-0x564589727120: postgres/postgres@xxx.xxx.xxx.xxx:2137 closing because: client close request (age=3s)

for "\c test2":

2025-01-07 09:43:29.556 UTC [7] LOG S-0x5645897415d0: test2/_crunchypgbouncer@xxx.xxx.xxx.xxx:5432 new connection to server (from xxx.xxx.xxx.xxx:36744)
2025-01-07 09:43:29.562 UTC [7] LOG S-0x5645897415d0: test2/_crunchypgbouncer@xxx.xxx.xxx.xxx:5432 SSL established: TLSv1.3/TLS_AES_256_GCM_SHA384/ECDH=prime256v1
2025-01-07 09:43:29.576 UTC [7] LOG S-0x5645897415d0: test2/_crunchypgbouncer@xxx.xxx.xxx.xxx:5432 closing because: error response from auth_query (age=0s)
2025-01-07 09:43:29.576 UTC [7] LOG C-0x564589727120: test2/(nouser)@xxx.xxx.xxx.xxx:58351 closing because: bouncer config error (age=0s)
2025-01-07 09:43:29.576 UTC [7] WARNING C-0x564589727120: test2/(nouser)@xxx.xxx.xxx.xxx:58351 pooler error: bouncer config error

test2/(nouser)@xxx.xxx.xxx.xxx seems to indicate that despite being connected with the postgres .

Still no one to help?

[oavril]

Hello,

The solution is still in the qualification phase within our IT department.
The issue is still ongoing after a clean reinstallation with Everest 1.4.0 with Helm.

Still no assistance?

Kind regards,
Olivier

[oksana-grishchenko]

Hi @oavril! Thank you for reporting the issue.

The issue is under investigation. Until a proper fix is delivered, the following command can be used as a workaround:

 kubectl -n YOUR_NAMESPACE patch pg/YOUR_CLUSTER_NAME  -p '{"spec":{"proxy":{"pgBouncer":{"exposeSuperusers":false}}}}' --type=merge

The setting will be immediately reconciled back to the true value, however the patch triggers an update process that makes the new DBs accessible. The patch won't cause any restarts (downtime) of the DB cluster itself. Each time you create a new DB from template0 you would need to run the command again to access the new DB.