PGBOUNCER: Lightweight Connection Pooler

Overview

PGBOUNCER is available in our Linux distribution. We recommend its use in most every serious producion environment. Take a look at the documentation to learn more.

Tutorial

Make sure your PostgreSQL server is running. The below example shows starting PostgreSQL for the first time using our Developers Sandox.

$ ./pgc start pg95
 
## Initializing pg95 #######################
 
Superuser Password [password]: 
Giving current user permission to data dir
 
Initializing Postgres DB at:
   -D "~/bigsql/data/pg95"
 
Using PostgreSQL Port 5432
 
Password securely remembered in the file: /home/userx/.pgpass
 
to load this postgres into your environment, source the env file: 
    ~/bigsql/pg95/pg95.env
 
pg95 starting on port 5432

Next we install pgBouncer.


$ ./pgc install pgbouncer17
  ['pgbouncer17']
Get:1 http://localhost:8000 pgbouncer17-1.7.2-1-linux64
  Unpacking pgbouncer17-1.7.2-1-linux64.tar.bz2

Ensure that the password in the "users.txt" file matches the postgres superuser password set for our database.


$ cat pgbouncer17/users.txt
"postgres" "password"

Now we confirm that pgbouncer is installed and not yet running.


$ ./pgc status
pg95 running on port 5432
pgbouncer17 stopped on port 6543

Next we startup pgbouncer and confirm that it is running.


$ ./pgc start pgbouncer17
 
pgbouncer17 starting...

$ ./pgc status
pg95 running on port 5432
pgbouncer17 running on port 6543

Now we set some environment variables to make it easier to run psql


$ cd pg95
$ source pg95.env
$ cd ..

We connect with psql to pgbouncer (running on port 6543) and pgbouncer connects us thru to the underlying database running on port 5432.


$ psql -U postgres -p 6543
Password for user postgres: 
psql (9.5.2)
Type "help" for help.

postgres=#