Pooler issue after upgrade to 1.5. With "error: unexpected response from login query".

[JazzShapka]

Hi All!
I ran into the following problem. I worked with operator version 1.4. I created 2 postgres clusters ("dev" and "staging") with identical configurations and databases names (in different namespace k8s), then I restored sql-backup of the database from "dev" to the "staging" database, while replication and backups on s3 continued to work correctly. After that, I updated the operator to version 1.5 and tried to configure the connection through pooler. For the base from the "dev" cluster, everything worked correctly. And when connecting to the pooler for the "staging" cluster, I encountered the error "error: unexpected response from login query". Then I noticed that pooler schemes were not created automatically for the "staging" database, I think this is due to the fact that I restored in the "staging" base from the sql-backup and the operator didn’t touch upon them
when updating. This is perceived as a mistake. :(

Now I try understand what is missing for the base on "staging" to use pooler.

Any thoughts?

UPD. Yes. This is the problem of the lack of a "pooler" scheme for the required database. After adding the "pooler" scheme and assigning rights for the roles pooler (u) and postgres (uc), the connection through pgbouncer works without errors.

[timmy59100]

I've got the same problem.
@JazzShapka Could you please show how you added the pooler scheme?

[mboutet]

Same problem here. Sometimes the pooler scheme doesn't always get created for some databases.

[mboutet]

@timmy59100, the query can be found in

postgres-operator/pkg/cluster/database.go

Line 60 in 3b6dc4f

CREATE SCHEMA IF NOT EXISTS {{.pooler_schema}};

You'll have to replace the variable by their actual values.

[FxKu]

@mboutet would be very curious to see the operator logs to know what fails here.

[mboutet]

@FxKu, next time I deploy my stack from scratch and it happens, I will share the operator's logs here. For now, I implemented a Job that gets created each time I run helm install or helm upgrade which runs the query found in the code. I took a look at the code and could not find anything that could cause the problem. However, I'm not familiar with Go. What's weird is that I have three databases and usually only one or two of the three databases (at random) do not get the pooler schema created. Even if I wait for the resync/repair cycle to kick in, it never gets created.

[JannikZed]

seems like I'm hitting this issue now as well. I added a new DB and user via the cluster manifest. I can check with psql that the new DB is created successfully. I just can't connect to it via the pgbouncer: psql: error: ERROR: unexpected response from login query
Using operator 1.6.0

[ingvarch]

@JazzShapka

UPD. Yes. This is the problem of the lack of a "pooler" scheme for the required database. After adding the "pooler" scheme and assigning rights for the roles pooler (u) and postgres (uc), the connection through pgbouncer works without errors.

Could you send the commands which you use to fix it ?

[JannikZed]

this issue still exists with version 1.6.3.
@FxKu @Jan-M

[ingvarch]

Hi there,

I found solution for me. Dirty hack.
In Postgresql manifest we have parameter - enableConnectionPooler
When I add new user or new database, I switch enableConnectionPooler: false, wait when pgbouncer pods going down and after that I switch enableConnectionPooler: true again.

Beware when you do it in production. Plan maintenance before doing this hack.

I hope it going help developers to solve this problem 🙏

[telepenin]

UPD. Yes. This is the problem of the lack of a "pooler" scheme for the required database. After adding the "pooler" scheme and assigning rights for the roles pooler (u) and postgres (uc), the connection through pgbouncer works without errors.

Could anyone explain why need the pooler schema?
I tried to find Dockerfile for registry.opensource.zalan.do/acid/pgbouncer but no luck. Where is it located?