1

I'm trying to do python manage.py syncdb on a Django installation, but I keep getting OperationalError: ERROR: pgbouncer cannot connect to server. pgbouncer.log contains lines such as:

2017-09-19 19:44:15.107 1128 LOG C-0x8a9930: mydb/myuser@unix:6432 closing because: pgbouncer cannot connect to server (age=0)
2017-09-19 19:44:15.107 1128 WARNING C-0x8a9930: mydb/myuser@unix:6432 Pooler Error: pgbouncer cannot connect to server
2017-09-19 19:44:15.107 1128 LOG S-0x8c72e0: mydb/myuser@35.154.149.188:5432 new connection to server
2017-09-19 19:44:15.107 1128 LOG C-0x8a9930: mydb/myuser@unix:6432 login failed: db=mydb user=myuser
2017-09-19 19:44:30.108 1128 LOG S-0x8c72e0: mydb/myuser@35.154.149.188:5432 closing because: connect failed (age=15)

In case needed, ps -aef | grep pgbouncer yields:

postgres  1128     1  0 18:38 ?        00:00:00 /usr/sbin/pgbouncer -d /etc/pgbouncer/pgbouncer.ini
myuser    1919  1533  0 19:45 pts/0    00:00:00 grep --color=auto pgbouncer

Moreover, grep port /etc/pgbouncer/pgbouncer.ini results in:

;;   dbname= host= port= user= password=
mydb = host=xx.xxx.xxx.xxx port=5432 dbname=mydb
;forcedb = host=127.0.0.1 port=300 user=baz password=foo client_encoding=UNICODE datestyle=ISO connect_query='SELECT 1'
listen_port = 6432

Lastly, the relevant parts of settings.py contain:

DATABASES = {
    'default': {
        'ENGINE': 'django.db.backends.postgresql_psycopg2',
        'NAME': 'mydb',
        'USER': 'myuser',
        'PASSWORD': 'mypassword',
        'HOST': '/var/run/postgresql',
        'PORT': '6432',
    }

I turned log_connections to on in postgresql.conf, restarted PG and tried again. Here's the relevant lines:

2017-09-20 07:50:59 UTC LOG:  database system is ready to accept connections
2017-09-20 07:50:59 UTC LOG:  autovacuum launcher started
2017-09-20 07:51:00 UTC LOG:  connection received: host=[local]
2017-09-20 07:51:00 UTC LOG:  incomplete startup packet
2017-09-20 07:51:00 UTC LOG:  connection received: host=[local]
2017-09-20 07:51:00 UTC LOG:  connection authorized: user=postgres database=postgres
2017-09-20 07:51:01 UTC LOG:  connection received: host=[local]
2017-09-20 07:51:01 UTC LOG:  connection authorized: user=postgres database=postgres
2017-09-20 07:51:01 UTC LOG:  connection received: host=[local]
2017-09-20 07:51:01 UTC LOG:  connection authorized: user=postgres database=postgres

It seems the connection is going through, but the user and database name is postgres. Those credentials aren't what I supplied in pgbouncer.ini.

However, explicitly adding myuser in the connection string described in pgbouncer.ini leads to: 

2017-09-20 09:37:37 UTC FATAL:  Peer authentication failed for user "myuser"
2017-09-20 09:37:37 UTC DETAIL:  Connection matched pg_hba.conf line 90: "local   all             all                                     peer"

Totally stumped.

Hassan Baig
  • 15,055
  • 27
  • 102
  • 205
  • What do the PostgreSQL logs say about that? Is PostgreSQL running at the destination? – Laurenz Albe Sep 20 '17 at 07:34
  • You didn't read the first part of my comment. Turn `log_connections` to `on` and check the PostgreSQL log. – Laurenz Albe Sep 20 '17 at 07:41
  • settings.py has socket directory defined as host, yet you have localhost in `/etc/pgbouncer/pgbouncer.ini` - change `/var/run/postgresql` to `127.0.0.1` please in `settings.py` – Vao Tsun Sep 20 '17 at 08:48
  • @VaoTsun: ah no, that didn't work. For the record, I've set `unix_socket_dir = /var/run/postgresql` in pgbouncer.ini, so socket should work. Maybe `pgbouncer.ini` isn't loading correctly when I do `sudo service pgbouncer start`? Either that, or there's a permissions issue here. What do you feel? – Hassan Baig Sep 20 '17 at 08:55
  • `ps -aef | grep pgbouncer` shows right config. what do you have in `auth_hba_file`?.. – Vao Tsun Sep 20 '17 at 08:58
  • @VaoTsun: Well I don't use that file. My auth file is set to `/etc/pgbouncer/userlist.txt` in `pgbouncer.ini` (with `auth_type=md5`). userlist.txt contains `"myuser" "mypassword"` (matches `settings.py`). – Hassan Baig Sep 20 '17 at 09:10
  • there are no connections for myuser in postgres logs - did you switch `log_connections` on postgres.conf as Laurenz asked? – Vao Tsun Sep 20 '17 at 09:14
  • Yes I did. Notice the logs I pasted at the end of the question. There are connections being generated for `postgres` instead. That's what comes up when I try to use pgbouncer right now. Thing is, I'm not trying to connect to DB `postgres` via user `posgres`. I'm trying to connect to db `mydb` via user `myuser`. There's some kind of misconfiguration - which is leading me to believe my pgbouncer.ini is either incomplete or loading incorrectly. Maybe I should explicitly add `user=myuser` to the connection string in `pgbouncer.ini`? – Hassan Baig Sep 20 '17 at 09:18
  • Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/154871/discussion-between-hassan-baig-and-vao-tsun). – Hassan Baig Sep 20 '17 at 09:28
  • @VaoTsun: it worked. And you know what I changed in the end? I was setting `'PORT' : '6432'` in `settings.py` (also shown in my question). I simply commented out that line. It's working now. I'm not sure why it's working however. Perhaps some kind of collision of ports going on in the test server? – Hassan Baig Sep 20 '17 at 13:03

1 Answers1

3

It seems the mis-configuration emanated from this line in settings.py: 

'PORT': '6432',

I commented it and pgbouncer started working.

Though I'm not sure 'why'.

Maybe there's a collision on this port; Pgbouncer and PG coexist on a single server in my case. I've set them up over different VMs in the past without a hitch (and without needing to comment 'PORT': '6432',)

Hassan Baig
  • 15,055
  • 27
  • 102
  • 205
  • 2
    That's very strange. I suspect that with commented PORT you connected directly to postgresql – Alex Yu Sep 20 '21 at 10:53
  • You can check your pgbouncer config for port with `sudo cat /etc/pgbouncer/pgbouncer.ini | grep listen_port`. That is where you want to connect. – smido Jul 30 '22 at 02:40