PgBouncer Ubuntu setup and testing

PgBouncer is a proxy server that allows you to manage a connection pool to a PostgreSQL database. In this article, we will look at how to set up and test PgBouncer on Ubuntu.

PgBouncer can greatly improve performance using the PostgreSQL database in php and python projects, allows you to manage the connection pool and reduce the load to the server. However, to achieve maximum performance, PgBouncer must be properly configured. and perform performance testing using the pgbench utility, as well as optimize your code and database queries.

Installing PgBouncer

To install PgBouncer on Ubuntu, run the following commands:

sudo apt-get update
sudo apt-get install -y pgbouncer

Configuring PgBouncer

After installing PgBouncer, edit the file configuration /etc/pgbouncer/pgbouncer.ini to customize it for your environment. You will need specify the connection settings for your PostgreSQL database, as well as configure the connection pool settings.

Example of configuring pgbouncer.ini file:

[databases]
mydb=host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = *
listen_port = 6432
auth_type=md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = session
max_client_conn = 100
default_pool_size = 20

Setting up users

PgBouncer uses the file userlist.txt to determine which users can connect to the server. Create this file and add to it users and passwords in the format username:password.

Example file userlist.txt:

"pgbouncer" "password"
"myuser" "mypassword"

Launch PgBouncer

To start PgBouncer, run the following command:

sudo service pgbouncer start

Testing PgBouncer

You can test PgBouncer using the PgBouncer utility pgbench that comes with PostgreSQL. For this you need to create a test database and run test:

createdb testdb
pgbench -i -s 10 testdb
pgbench -c 10 -T 60 -U myuser -p 6432 localhost/testdb

In this example, we have created a test database testdb, populated it with data using pgbench -i and then ran the test with 10 concurrent clients (-c 10) for 60 seconds (-T 60) via PgBouncer (-p 6432). We also specified username (-U myuser) and database (localhost/testdb).

Conclusion

Now you know how to set up and test PgBouncer on Ubuntu. PgBouncer can greatly improve the performance of your PostgreSQL database by allowing manage the connection pool and reduce the load on the server.

#DevOps #Technologies #Linux #Ubuntu