Compile pgbouncer 1.7.2

19 Jun 2017

A log of how I custom-compiled the latest pgbouncer on Ubuntu 16.04.

You'll see I did a lot of this as the root user.

$ su -
# cd /usr/local/src
# wget https://pgbouncer.github.io/downloads/files/1.7.2/pgbouncer-1.7.2.tar.gz
# wget https://pgbouncer.github.io/downloads/files/1.7.2/pgbouncer-1.7.2.tar.gz.sha256
# sha256sum pgbouncer-1.7.2.tar.gz > actual.sha256
# diff actual.sha256 pgbouncer-1.7.2.tar.gz.sha256
# rm actual.sha256 pgbouncer-1.7.2.tar.gz.sha256
# tar -xzvf pgbouncer-1.7.2.tar.gz 
# cd pgbouncer-1.7.2
# apt install libevent-dev
# ./configure --prefix=/usr/local/pgbouncer-1.7.2
# make
# make install

With the compile and install done, let's set up a directory structure where we can keep logs, store pgbouncer's pid file, etc, etc.

# mkdir /usr/local/pgbouncer-1.7.2/etc
# mkdir /usr/local/pgbouncer-1.7.2/log
# mkdir /usr/local/pgbouncer-1.7.2/run

I'm being lazy here by keeping the dirs owned as root even though I'm going to run pgbouncer as user nobody. Feel free to do something more sophisticated.

# chmod 777 /usr/local/pgbouncer-1.7.2/log
# chmod 777 /usr/local/pgbouncer-1.7.2/run
# vim /usr/local/pgbouncer-1.7.2/etc/pgbouncer.ini

In my environment, I had a PostgreSQL running on port 5433 (the standard PostgreSQL is port 5432) that I wanted to talk to. You'll note too that I allow connecting to the postgresql database, which is the standard database that tools connect to.

[databases]

postgres = host=127.0.0.1 port=5433 user=postgres dbname=postgres
fd_db_test = host=127.0.0.1 port=5433 user=some_user_test dbname=some_db_test

[pgbouncer]

logfile = /usr/local/pgbouncer-1.7.2/log/pgbouncer.log
log_connections = 0
log_disconnections = 0

pidfile = /usr/local/pgbouncer-1.7.2/run/pgbouncer.pid

user = nobody

listen_addr = *
listen_port = 6432

auth_type = trust
auth_file = /usr/local/pgbouncer-1.7.2/etc/userlist.txt

pool_mode = session

server_reset_query = DISCARD ALL

max_client_conn = 100

default_pool_size = 20

Time to create the userlist config file:

# vim /usr/local/pgbouncer-1.7.2/etc/userlist.txt

"postgres" "postgres"
"some_user_test" "some_user_test"

Here's how to start pgbouncer as a daemon:

# /usr/local/pgbouncer-1.7.2/bin/pgbouncer \
    --daemon \
    --quiet \
    /usr/local/pgbouncer-1.7.2/etc/pgbouncer.ini

Here's how we kill pgbouncer, given that we know the location of its pid file:

# kill $(cat /usr/local/pgbouncer-1.7.2/run/pgbouncer.pid)