Connection Pooling with Pgbouncer on PostgreSQL 9.0

Posted on by Raghav
Connection pooling, Why we go for connection pooling in PostgreSQL, When your application demands for very good number of concurrent connection hits then you need to approach it, because Connection pool sits between your application and the database.

Idea behind connection pool is that you have enough connections to use of all the available resources and any incoming requests are re-used without dropping the database connection and keeping ready for a new connection to use.

pgbouncer is lightweight connection pooler. pgBouncer runs as a single process, not spawning a process per connection, which relies on library named libevent for connection pooling.

pgbouncer setup on PostgreSQL 9.0 is very simple, however there is small change with the latest version you need to create manual pg_auth file. pgbouncer uses pg_auth file for user authentication. Earlier verion of PostgreSQL 9.0, you can find the pg_auth file under $PGDATA/global/pg_auth, now in the latest version that file has been removed and placed in pg_catalog as table ‘pg_auth’.

pgbouncer Setup:

1. First, download libevent library for pgbouncer.
Download link for libevent:
http://www.monkey.org/~provos/libevent-2.0.12-stable.tar.gz

tar -xvf libevent-2.0.12-stable.tar.gz

cd libevent-2.0.12-stable

./configure 

make

make install

2. Download the latest pgbouncer tar and configure to your PostgreSQL 9.0.
http://pgfoundry.org/frs/download.php/2912/pgbouncer-1.4.tgz

tar -xvf pgbouncer-1.4

cd pgbouncer-1.4

./configure --prefix=/opt/PostgreSQL/9.0/bin

make

make install

3. Create a libevent-i386.conf file in /etc/ld.so.conf.d directory

vi /etc/ld.so.conf.d/libevent-i386.conf

/usr/local/lib

:wq!

4. Run the ldconfig to apply new changes.

#ldconfig

5. Change the ownership of pgbouncer utility in PostgreSQL binary to postgres user.

chown -R postgres:postgres /opt/PostgreSQL/9.0/bin/bin/pgbouncer

6. Create the pgbouncer_auth file for users authentication.

7. Create pgbouncer.ini file with postgres user permission under /etc directory.

8. Start pgbouncer

-bash-4.1$ ./pgbouncer -d /etc/pgbouncer.ini

2011-08-14 11:42:00.925 1949 LOG File descriptor limit: 1024 (H:1024), max_client_conn: 1000, max fds possible: 1010

9. Connect to the databases using pgbouncer

10. Getting help: Connect to pgbouncer database and get helped.

$ psql -p 6432 -U postgres pgbouncer

pgbouncer=# show help;

For better understanding on pg_auth you can find in below link by ‘depesz’.
http://www.depesz.com/index.php/2010/12/04/auto-refreshing-password-file-for-pgbouncer/

Do post your comments which are highly appreciated.

–Raghav

32 Replies to “Connection Pooling with Pgbouncer on PostgreSQL 9.0”

  2. Dear Raghavendra,Thanks for the information. it is really helpful my project. Could I ask a little question? because I need to compare with pgbouncer and pg_pconnect these two tool performance with php5. Do you know where I can get more information about it. I am looking for internet, but i didn&#39t see any good evaluation web pages. Hope you can give me some direction. Thanks.Louis

  6. @Lorgio,1. How to be an architecture to handle 3 million transactions a day?2. What do I have to configure parameters in postgresql stop supporting these transactions?I never tested though, but proper tuning on both the ends (PG/pgbouncer) will be easy handled this scenario. 3. How much memory used by each connection to the database?Again, each connection will occupy some space in the Shared memory of PostgreSQL, hence tuning the connection also depends on the available memory on the server. Final point, always deploy pgbouncer at the application end instead of at the database end. This way connections will be load balanced if you have multiple application connecting to the same database.Another point to consider, always allow ulimit to maximum number for postgres user on the system where pgbouncer deployed.

  8. hi,In my project , several Client uses ODBC interface to access the postgres DB .in the above case how we can use pgbouncer to setup transition pooling.

  10. Thanks,Small correction, while creating pgbouncer_auth file I missed to disable heading and bottoms. Fix is to, use t before copying to file and delete the bottom line editing.

  11. Hi Raghavendra, above all very good tutorial, regarding the connection pool I have some questions:Environment:- Postgresql 9.0- Java 6- Glassfish 3.11. How to be an architecture to handle 3 million transactions a day?2. What do I have to configure parameters in postgresql stop supporting these transactions?3. How much memory used by each connection to the database?Thank you very much for your supportregards

  12. Hello,I have two PostgreSQL instances (7777 and 7778) and I&#39m using the pgbouncer on port 7779.Accessing by pgboucer ( psql -p 7779 postgres). If I execute the command: SHOW port; The answer is 7778. Why I never connect on port 7777?

  14. Very helpful, thanks.I had to delete the lines in the auth file that weren&#39t actually for a user to get past the error "ERROR broken auth file".

  16. am getting this error…psql: could not connect to server: No such file or directory Is the server running locally and accepting connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.6432"?

  19. Hello,

    I have two PostgreSQL instances (7777 and 7778) and I'm using the pgbouncer on port 7779.

    Accessing by pgboucer ( psql -p 7779 postgres). If I execute the command: SHOW port; The answer is 7778. Why I never connect on port 7777?

  21. Very helpful, thanks.

    I had to delete the lines in the auth file that weren't actually for a user to get past the error "ERROR broken auth file".

  22. am getting this error…

    psql: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.6432"?

  23. @Louis
    Not sure on pg_connect. If am not wrong, I guess its PHP native call function for connectivity.
    http://php.net/manual/en/function.pg-connect.php

    Pgbouncer, its just a pooler sitting in between your database and the application to handle the concurrent connection without lossing any connection request's raised by the users. In Php, you will be giving pgbouncer port 6432 to access the database.

  24. Dear Raghavendra,

    Thanks for the information. it is really helpful my project. Could I ask a little question? because I need to compare with pgbouncer and pg_pconnect these two tool performance with php5. Do you know where I can get more information about it. I am looking for internet, but i didn't see any good evaluation web pages. Hope you can give me some direction. Thanks.

    Louis

  25. @Lorgio,
    1. How to be an architecture to handle 3 million transactions a day?
    2. What do I have to configure parameters in postgresql stop supporting these transactions?

    I never tested though, but proper tuning on both the ends (PG/pgbouncer) will be easy handled this scenario.

    3. How much memory used by each connection to the database?

    Again, each connection will occupy some space in the Shared memory of PostgreSQL, hence tuning the connection also depends on the available memory on the server.

    Final point, always deploy pgbouncer at the application end instead of at the database end. This way connections will be load balanced if you have multiple application connecting to the same database.

    Another point to consider, always allow ulimit to maximum number for postgres user on the system where pgbouncer deployed.

  26. hi,

    In my project , several Client uses ODBC interface to access the postgres DB .

    in the above case how we can use pgbouncer to setup transition pooling.

  27. Hi Raghavendra, above all very good tutorial, regarding the connection pool I have some questions:

    Environment:

    – Postgresql 9.0
    – Java 6
    – Glassfish 3.1

    1. How to be an architecture to handle 3 million transactions a day?
    2. What do I have to configure parameters in postgresql stop supporting these transactions?
    3. How much memory used by each connection to the database?

    Thank you very much for your support

    regards

  29. Thanks,
    Small correction, while creating pgbouncer_auth file I missed to disable heading and bottoms. Fix is to, use t before copying to file and delete the bottom line editing.

Comments are closed.