4

Question:

I'm struggling to connect pgbouncer 1.7.2 to an operational postgresql 9.6.5 database (Django/Python web app, Ubuntu 14.04 OS).

Can you help me troubleshoot this?

Background:

PostgreSQL is working perfectly without pgbouncer. I'm using the canonical guide to setup pgbouncer. The DB was formerly restored from another machine.

Everything is on one machine. I'm trying a unix socket connection. Haven't tried TCP (but open to it).

The database is called mydb. My Django project is setup to connect to it via the user ubuntu.

What I've tried:

When I try psql -d mydb -p 6432 ubuntu (as the user ubuntu), I get: psql: ERROR: pgbouncer cannot connect to server

At the same time, pgbouncer.log shows:

01:39:56.428 78472 LOG C-0xfa51e0: mydb/ubuntu@unix(120837):6432 login attempt: db=mydb user=ubuntu tls=no
01:39:56.428 78472 LOG C-0xfa51e0: mydb/ubuntu@unix(120837):6432 closing because: pgbouncer cannot connect to server (age=0)
01:39:56.428 78472 WARNING C-0xfa51e0: mydb/ubuntu@unix(120837):6432 Pooler Error: pgbouncer cannot connect to server
01:40:11.428 78472 LOG S-0xfa0530: mydb/ubuntu@11.65.119.381:5432 closing because: connect failed (age=15)

Note that psql -d mydb -p 5432 ubuntu successfully logs me into mydb (without needing a password). Is the password creating the problem here?

Next if I do pgbouncer -d pgbouncer.ini (as the user ubuntu), I get a permission denied error:

2017-10-15 23:34:14.325 17606 FATAL Cannot open logfile: '/var/log/postgresql/pgbouncer.log': Permission denied

There are no corresponding log lines generated in pgbouncer.log. File perms are set as follows:

ubuntu@ip-xxx-xx-xx-xx:/var/log/postgresql$ ls -lh
total 59M
-rw-r--r-- 1 postgres postgres 1.8M Oct 15 23:35 pgbouncer.log
-rw-r----- 1 postgres adm       57M Oct 15 23:07 postgresql-9.6-main.log

What I've configured:

For the record, here's what I have in my Django app's settings.py file:

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

If I uncomment 'PORT': '6432', it still doesn't work.

Pgbouncer's pgbouncer.ini contains the following:

[databases]
mydb= host=11.65.119.381 port=5432 user=ubuntu dbname=mydb

logfile = /var/log/postgresql/pgbouncer.log
pidfile = /var/run/postgresql/pgbouncer.pid

; ip address or * which means all ip-s
listen_addr = *
listen_port = 6432

; unix socket is also used for -R.
; On debian it should be /var/run/postgresql
;unix_socket_mode = 0777
;unix_socket_group =
unix_socket_dir = /var/run/postgresql

auth_type = trust
auth_file = /etc/pgbouncer/userlist.txt
admin_users = myuser, postgres, root
stats_users = myuser, postgres, root

Setting auth_type to any didn't work either.

/etc/pgbouncer/userlist.txt contains:

"ubuntu" "md565j6e98u1z098oiuyt7543poi4561yh3"

where I got the password string via SELECT usename, passwd FROM pg_shadow WHERE usename='ubuntu';. Note that this is different from DB_PASSWORD referenced in Django's settings.py (I've unsuccessfully tried that in userlist.txt too).

pg_hba.conf contains:

local   all             postgres                                peer

# "local" is for Unix domain socket connections only
local   all             all                                     trust
# IPv4 local connections:
host    all             all             127.0.0.1/32            trust
# IPv6 local connections:
host    all             all             ::1/128                 md5

postgresql.conf contains:

listen_addresses = '*'
port = 5432 
unix_socket_directories = '/var/run/postgresql'
#unix_socket_group = ''                 # (change requires restart)
#unix_socket_permissions = 0777         # begin with 0 to use octal notation

File perms:

/var/run/postgresql/ contains:

total 8.0K
drwxr-s--- 2 postgres postgres 120 Oct 16 00:06 9.6-main.pg_stat_tmp
-rw-r--r-- 1 postgres postgres   6 Oct 15 13:23 9.6-main.pid
-rw-r--r-- 1 postgres postgres   6 Oct 15 23:23 pgbouncer.pid
Hassan Baig
  • 15,055
  • 27
  • 102
  • 205
  • You have provided a lot of information! Please set `log_connections` to `on` in PostgreSQL and see what is in the PostgreSQL server log after a failed connection attempt. – Laurenz Albe Oct 18 '17 at 06:33
  • Turned `log_connections on`. It's a busy server so it immediately starts overflowing with `2017-10-18 06:55:28.130 UTC [75440] [unknown]@[unknown] LOG: connection received: host=[local] 2017-10-18 06:55:28.130 UTC [75440] ubuntu@mydb LOG: connection authorized: user=ubuntu database=mydb`. Next I tried `psql -d mydb -p 6432 ubuntu`. Nothing new appears in the postgresql log, exactly these same strings keep repeating. While on the terminal, I get the error `psql: ERROR: pgbouncer cannot connect to server`. – Hassan Baig Oct 18 '17 at 06:58
  • @LaurenzAlbe: So failed connections on port `5432` **do show up** in postgresql's log after one turns on `log_connections`. However, the failure to connect via port `6432` does not show up in the same postgresql log - I just confirmed this. Does this imply something that can be used to diagnose the issue? – Hassan Baig Oct 19 '17 at 00:57
  • That implies that pgbouncer never reaches the PostgreSQL server, so something must be wrong in its configuration. – Laurenz Albe Oct 19 '17 at 03:56
  • @LaurenzAlbe: theoretically speaking, could it be a port/firewall issue? – Hassan Baig Oct 19 '17 at 06:30
  • Yes. Unfortunately I don't know a lot about firewalls or pgBouncer. You could `strace` the pgBouncer process to see where it tries to connect to. – Laurenz Albe Oct 19 '17 at 07:20
  • @laurenzalbe: Yikes, `strace` is not an intuitive read. But good suggestion - I removed the verbose `pgbouncer.log` error I had included in the question and instead added `strace` output. – Hassan Baig Oct 19 '17 at 09:20
  • You want to look for lines that start with `connect`, that's when a network connection is started. – Laurenz Albe Oct 19 '17 at 11:39

1 Answers1

1

If you are successfully connecting to Postgres proper over a Unix socket, you can tell pgbouncer to do the same, by specifying the host as the directory where your socket files are (typically /tmp if you've compiled it yourself) though distros put it in various places.

So try something like this in your pgbouncer.ini:

[databases]
mydb= host=/tmp dbname=mydb

You probably don't need to set the username when pgbouncer is running as the user ubuntu (in your case) and you're using the default port number, so you don't need to set it explicitly.

In Postgres, do

show unix_socket_directories;

to see where your actual socket directory is.

Johann Oskarsson
  • 776
  • 6
  • 15
  • In my scenario, both `pgbouncer.pid` and `9.6-main.pid` are located at `/var/run/postgresql` (moreover, `show unix_socket_directories` points to this as well). So in my case, you're suggesting I should set `host=/var/run/postgresql` in pgbouncer.ini and reload pgbouncer via `sudo service pgbouncer reload`? – Hassan Baig Oct 19 '17 at 13:50
  • Yes. As long as the service is configured to use the same user as your psql tests, you should be fine. And you might have to restart, rather than just reload. – Johann Oskarsson Oct 19 '17 at 13:53
  • Great. This change allowed me to log in via `psql -d mddb -p 6432 ubuntu`. Pgbouncer still hasn't become operational yet. Can you quickly look at the Django conf I posted in the ques - especially the port I've commented? Is that something I need to uncomment to go forth? I'm in a production environment, so just want to be extra careful before I do this. – Hassan Baig Oct 19 '17 at 23:28
  • The port you've commented is the port number (file name) the pgbouncer is listening for connections, on the unix socket it created. You can see these files with `ls -a /var/run/postgresql`. Without it, it'll try to connect to the postgres server proper. That said, I don't know how to configure Django -- the parameters look correct as long as they're sent properly to psychopg2. – Johann Oskarsson Oct 20 '17 at 03:16