CODEWITHSUNDEEP
postgresqlpgpgbouncer
Vincent COFFIN
Vincent COFFIN

Reputation: 31

PGBouncer : Cant connect on the right db

I'm actually facing an issue. I've installed pgbouncer on a production server, on which i've a Odoo instance and postgresql as well.

Perhaps :

In my logs, i'm having this : 

2018-09-10 16:39:16.389 10123 WARNING C-0x1eb5478: 
(nodb)/(nouser)@unix(18272):6432 pooler error: no such database: postgres
2018-09-10 16:39:16.389 10123 LOG C-0x1eb5478: (nodb)/(nouser)@unix(18272):6432 login failed: db=postgres user=oerppreprod

Here is the actual conf of pgbouncer : 

pgbouncer_archive = host=127.0.0.1 port=5432 dbname=archive
admin_users = postgres
ignore_startup_parameters = extra_float_digits

With aswell, the default config (i've only added/edited this).

Why is he trying to connect on the postgres database ?

When i go back on the previous conf (without PGBouncer, just swapping from port 6432 to 5432), everything is working ....

Any idea ?

Thanks in advance !

Upvotes: 3

Views: 6586

Answers (1)

Alexey Shabramov
Alexey Shabramov

Reputation: 788

I had the same issue, and in my situation. Maybe it will be usefull to somebody:

I have solved this by a few steps:

  1. At the beginning of every request - your Framework or PDO (or else) running the initial query to check if database you asking is exists in the postgres data to process you request.

  2. I have replaced the part of line "user=project_user password=mytestpassword" from the database section of pgbouncer.ini file. As I tested, if you replace this part - then the pgbouncer will use your userlist.txt file (or your selected auth), in my case, it was the userlist.txt.

  3. Added the line "postgres = host=127.0.0.1 port=5432 dbname=postgres"

    [databases]

    postgres = host=127.0.0.1 port=5432 dbname=postgres

    my_database = host=127.0.0.1 port=5432 dbname=my_database

  4. My userlist.txt file looks like this (I am using auth_type = md5, so my password was in md5):

    "my_user" "md5passwordandsoelse"

  5. I have added my admin users to my pgbouncer.ini file:

    admin_users = postgres, my_user

After all manipulations I advise you to check from which user u are running queries, by usin this simple query:

select current_user;

At the end, with this query you must to receive you selected username (in my case it was - my_user) p.s. also I must to mention, that I was using 127.0.0.1 - because my pgbouncer is installed on the same server with postgres.

Upvotes: 1

Related Questions