Accelerating Postgres connections with PgBouncer

PgBouncer is a lightweight connection pooler for PostgreSQL, connection pooling makes Postgres connection much faster, which is important in Web applications.

Here I will explain the steps I used to configure it under Ubuntu 14.04 LTS.

Step 1: We would configure users allowed to connect to PgBouncer:

$ sudo vi /etc/pgbouncer/userlist.txt
"rayed"  "pgbouncer_password_for_rayed"

Step 2: We configure databases PgBouncer will pool for, and how PgBouncer will authenticate user:

$ sudo vi /etc/pgbouncer/pgbouncer.ini
rayed = host=localhost user=rayed password=postgres_password_for_rayed
auth_type = md5
;auth_file = /8.0/main/global/pg_auth
auth_file = /etc/pgbouncer/userlist.txt

The default value for “auth_type” is “trust” which means a system user “rayed” will be allowed to connect to Postgres user “rayed”, I change to “md5” to force a password checking from the file “/etc/pgbouncer/userlist.txt”.

Step 3: We will allow PgBouncer to start:

$ sudo vi /etc/default/pgbouncer 

The default value is “0” which means don’t start PgBouncer ever, it is a way to make sure you know what you are doing 🙂

Step 4: Starting pgBouncer:

$ sudo service pgbouncer start

Step 5: Test the connection, by default “psql” connect using port “5432”, and pgBouncer use “6432”, so to test a pgBouncer connection we would use the following command:

$ psql -p 6432 

If you get “Auth failed” error make, make sure the password you entered is the one you typed in step 1, if the command doesn’t ask for password try it with “-W” option, e.g. “psql -p 6432 -W”.

PostgreSQL Replication

We will assume we have two servers:

  • Master IP
  • Slave IP


Create replication user “repl”:

sudo -u postgres createuser -P repl
sudo -u postgres psql

Allow “repl” user to connect from the slave:

$ vi /etc/postgresql/9.1/main/pg_hba.conf
host    replication     repl          md5

Setup replication parameters:

vi /etc/postgresql/9.1/main/postgresql.conf
listen_addresses = '*'
wal_level = hot_standby
max_wal_senders = 1
wal_keep_segments = 5

Create a copy of the database and copy to the slave:

service postgresql stop
cd /var/lib/postgresql/9.1
tar -czf main.tar.gz main/
service postgresql start
scp main.tar.gz rayed@slave:


Restore data from master:

service postgresql stop
cd /var/lib/postgresql/9.1
tar -xzvf ~rayed/main.tar.gz
rm /var/lib/postgresql/9.1/main/

Create recovery.conf file in data directory:

vi /var/lib/postgresql/9.1/main/recovery.conf
standby_mode = 'on'
primary_conninfo = 'host= port=5432 user=repl password=repl_password

Edit postgresql.conf:

vi /etc/postgresql/9.1/main/postgresql.conf
hot_standby = on

Start the slave server again, and check the log file

service postgresql start
tail -f /var/log/postgresql/postgresql-9.1-main.log 

For more info:

Login to PostgreSQL without typing a Password

If you to connect to PostgreSQL without typing a password, you can do it by having your password in the file “.pgpass”.

The file reside in your home directory and must be readable to you only:

touch ~/.pgpass
chmod 600 ~/.pgpass

The format is simple:


For example:


The “*” means any host using any port.

This of course will work for “psql” and other tools to like “pg_dump”.

PostgreSQL: New Project Setup

For each new project that need PostgreSQL you should create its own user and its own DB, the following are the steps need for that.


$ sudo aptitude install postgresql
$ sudo aptitude install python-psycopg2  # For Django access

Require a Password

$ sudo vi /etc/postgresql/9.1/main/pg_hba.conf
#local   all             all                      peer
local    all             all                      md5
$ sudo service postgresql restart    

Create Postgres User

$ sudo -u postgres createuser -P my_user
Enter password for new role: 
Enter it again: 
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n

Create DB

$ sudo -u postgres createdb my_db -O my_user

Test It

$ psql -U my_user my_db   

After login you issue any SQL statement, you should also try the following commands:

  • \d list tables.
  • \l list databases.
  • \du list users.