Steps to install and setup PgBouncer connection pooling proxy with Azure DB for PostgreSQL

In my previous blog post on Azure blogs, I explain why it is important to leverage connection pooling proxy with PostgreSQL and how using a connection pooling proxy like PgBouncer against Azure Database for PostgreSQL can provide a significant performance boost, assuming connection pooling is not used at the application layer. To give some estimates of the performance improvement when using PgBouncer for connection pooling with Azure Database for PostgreSQL, we ran a simple performance benchmark test with pgbench. pgbench provides a configuration setting to create new connection for every transaction so we leveraged that to measure the impact of connection latency on throughput of the application. The following are the observations with A/B testing comparing throughput with standard pgbench benchmark testing with and without PgBouncer. In the test, we ran pgbench with scale factor of 5 against Azure Database for PostgreSQL running on general purpose tier with 2 vCores (GP_Gen5_2). The only variable during the tests was PgBouncer. With PgBouncer, the throughput improved 4x times as shown below while connection latency was reduced by 40 percent.

 

 

In this blog post, we share the steps to install and setup PgBouncer connection pooling proxy between application and Azure DB for PostgreSQL to reap the performance and resiliency benefits it offers. In addition, we also provide you with a QuickStart template at the end of the blog post which can enable you to quickly deploy and setup a PgBouncer in a VM and get started. 

 

PgBouncer connection proxy is setup between the application and database layer as shown in the image below. As Azure DB for PostgreSQL is a fully managed platform service, you won’t have access to install any external component on database server. In this case, if you are running your application on an Azure VM, you can setup PgBouncer on the same VM running the application. If the application runs on a managed service like Azure App Services or Azure Functions, you may have to provision a separate Ubuntu VM to run PgBouncer proxy service. However, if you are running your application on Azure Kubernetes Services (AKS), you can leverage PgBouncer sidecar to run it as a Kubernetes sidecar to your application. In this blog post, we will focus on steps to install and run PgBouncer on Ubuntu VM while steps to setup PgBouncer sidecar can be a topic for a follow-up blog post.

 

Steps to setup PgBouncer on Ubuntu VM

All the steps mentioned below uses Azure CLI to provision and deploy resources.

1. Provision an Ubuntu VM. If your application is already running on Azure VM and you have a VM provisioned, you can skip this step and move to step 2.

          az group create --name myResourceGroup --location eastus

          az vm create \

          --resource-group myResourceGroup \

          --name PgBouncerPoolVM \

          --image UbuntuLTS \

          --admin-username pgadminuser \

          --generate-ssh-keys

 

2. Open port 5432 on Network Security Group (NSG) on Ubuntu VM so PgBouncer can listen on the port for incoming connections from application.

          az vm open-port --port 5432 --resource-group myResourceGroup –name PgBouncerPoolVM

 

Important Note:  The above command will create an NSG for the VM and open port 5432 on the VM for Any Source IP. This is not recommended from security perspective. Ideally, the VM should be configured in a VNet and the Source IP range must be defined and whitelisted in NSG using az network nsg to restrict access to PgBouncer VM to client application VMs only.

 

3. Ssh into the VM and install PgBouncer. You can leverage just in time secure access to login to VM to configure PgBouncer.

         ssh pgadminuser@<PublicIPEndpoint>

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

 

4. Download the certificate file from the Certificate Authority to connect to Azure DB for PostgreSQL using SSL connection.

         sudo wget https://www.digicert.com/CACerts/BaltimoreCyberTrustRoot.crt

         sudo openssl x509 -inform DER -in BaltimoreCyberTrustRoot.crt -text -out /etc/root.crt

 

5. Modify the Pgbouncer.ini configuration file located in /etc/pgbouncer/pgBouncer.ini using the following settings

         [databases]

         * = host=<servername>.postgres.database.azure.com  port=5432

   

        [pgbouncer]

         # Do not change these settings:

         listen_addr = 0.0.0.0

         auth_file = /etc/pgbouncer/userlist.txt

         auth_type = trust

         server_tls_sslmode = verify-ca

         server_tls_ca_file = /etc/root.crt

             

         # These are defaults and can be configured

         # please leave them as defaults if you are

         # uncertain.

         listen_port = 5432

         unix_socket_dir =

         user = postgres

         pool_mode = session

         max_client_conn = 100

         ignore_startup_parameters = extra_float_digits

         admin_users = postgres

 

6. Modify the authentication file located in /etc/pgbouncer/userlist.txt to specify the username/password pairs that clients may use to access PgBouncer service. Each line should be in format below

         "username@hostname" "password"

         

         Each entry is simply new line separated for example:

         "sa@mypgserver" "P@ssword1234"

         "test@mypgserver" "Test@#1234"

 

Note: Azure Database for PostgreSQL usernames are always in the format username@hostname

 

7. Start the PgBouncer service and verify there are no errors in the log

          sudo service pgbouncer start

          more /var/log/postgresql/pgbouncer.log

 

8. If postgresql-client is not installed on the VM, install postgresql-client and use psql to validate the connectivity to PgBouncer service which in-turn would connect to backend Azure DB for PostgreSQL service.

        sudo apt-get update

        sudo apt-get install postgresql-client

        psql -h 127.0.0.1 -p 5432 -U sa@mypgserver -d postgres

 

9. From external application VM or workstation connect to PgBouncer service to make sure your connection is successful.

        psql -h <PublicIPEndpoint> -p 5432 -U sa@mypgserver -d postgres

 

You are now all setup to leverage PgBouncer connection pooling proxy to connect to Azure DB for PostgreSQL service.

 

Deploying Pgbouncer using QuickStart ARM template

If you would like to skip the above steps and want to quickly setup and test PgBouncer with Azure DB for PostgreSQL, you can click on Deploy button below. It will take you to the template and once you provide the parameter values, it will provision an Ubuntu VM with PgBouncer installed, setup and running connected to Azure Database for PostgreSQL. I would encourage you to give it a try. The template is available in our GitHub repository

 

 

Once you have deployed the ARM Template **successfully without any errors**, you will be able to see the hostname, sshCommand and psqlCommand as part of Deployment Outputs  

 

 

hostname is the Public DNS for the Ubuntu VM hosting pgbouncer

sshCommand provides the ssh command to connect to the Ubuntu VM hosting pgbouncer

psqlCommand provides the psql command to connect the PostgreSQL Server  

 

psql "host={dnsLabelPrefix}.{region}.cloudapp.azure.com port=5432 dbname={your_database} user={your_username}" 

For Example : psql "host=pgubuntu.westus.cloudapp.azure.com port=5432 dbname=postgres user=pguser@pgbouncerserver"  

 

Hope this helps !!!

 

Ramkumar Chandrasekaran

Senior Software Engineer, Microsoft

 

Parikshit Savjani

Senior Program Manager, Microsoft