[BUG] "No Such Database" by pgbouncer

[spencerswagger]

Describe the bug Postgresql created database successfully but "No Such Database" by pgbouncer It works well when i connect postgresql directly.

To Reproduce Steps to reproduce the behavior:

1. Deploy a postgresql replication cluster
2. Connect to postgresql through pgbouncer, and create a new database
3. Swicth to the new database

Expected behavior same as connecting directly

[spencerswagger]

I notice that there is "Manage user accounts" part in kbcli. Should i manage databases(or schema and tables) by kbcli?

[weicao]

How do you connect to pgbouncer and then connect directly to pg? Are you using kbcli?

[spencerswagger]

I create and expose postgresql cluster by kbcli. and connect pgbouncer by psql and jdbc driver

[weicao]

I just have a try. I create two connections to pgbouncer and pg.

The first connects to pg:

$ kubectl port-forward service/cherry18-postgresql 5432:5432
$ psql -h127.0.0.1 -p 5432 -U postgres postgres

The second connects to pgbouncer:

$ kubectl port-forward service/cherry18-postgresql 6432:6432
$ psql -h127.0.0.1 -p 6432 -U postgres postgres

After I creates a database in the session to pg, I can see it in the session to pgbouncer:

$ psql -h127.0.0.1 -p 5432 -U postgres postgres
# create database test1;

$ psql -h127.0.0.1 -p 6432 -U postgres postgres
postgres=# \l
...
 test1     | postgres | UTF8     | en_US.utf-8 | en_US.utf-8 |

And vice versa.

 $ psql -h127.0.0.1 -p 6432 -U postgres postgres
postgres=# create database test2;

$ psql -h127.0.0.1 -p 5432 -U postgres postgres
postgres=# \l
...
 test2     | postgres | UTF8     | en_US.utf-8 | en_US.utf-8 |

Could you check it again, and post your steps and outputs here. Thanks !

[spencerswagger]

Sorry for late reply. I did same operations as your example. But when i try to connect another database. Success on 5432

$ psql -h127.0.0.1 -p 5432 -U postgres anotherdb
anotherdb=#

Fail on 6432

$ psql -h127.0.0.1 -p 6432 -U postgres anotherdb
psql: error: connection to server at "127.0.0.1", port 6432 failed: FATAL:  no such database: anotherdb

[weicao]

I see, the reason is that the new created database doesn't synced to pgbouncer's config file. It is fixed in above pr and we'll publish a new versioned postgres kb addon later.

A quick fix is:

1. edit configmap

   $ kubectl edit configmap cherry18-postgresql-pgbouncer-configuration # replace the cluster to yours

2. Add following content to pgbouncer.ini

   [databases]
   * = host=localhost port=5432

   save and quit.

3. Restart the cluster to reload pgbouncer config.

   $ kbcli cluster restart cherry18

4. Wait until the restart ops finishes.

   $ kbcli cluster list-ops

Then it works.

[spencerswagger]

It works, thanks!