Installing and Configuring PgB

What we are configuring and why

We will be installing and configuring PgBouncer – a lightweight proxy server that sits between your client applications and the PostgreSQL server. Its main task is connection pooling (grouping) to the database. In a typical architecture, each client application (e.g., a web server) opens its own connection to PostgreSQL. With a large number of clients or frequent connections/disconnections, this leads to a significant load on the database server, as creating and closing each connection is a resource-intensive operation. PostgreSQL allocates memory and CPU time for each active connection, which can quickly lead to resource exhaustion and performance degradation.

Ultimately, the reader will get an optimized and more stable database system. PgBouncer will maintain a fixed number of "hot" connections to PostgreSQL, and client applications will connect to PgBouncer. This reduces the overhead of creating connections, decreases memory consumption on the PostgreSQL server, and allows the database to handle more queries with lower latencies. Your application will run faster, and the PostgreSQL server will be more resilient to peak loads.

Alternatives exist, such as cloud-managed databases (e.g., Amazon RDS, Google Cloud SQL, Azure Database for PostgreSQL), which often include built-in connection pooling solutions or offer on-demand resource scaling. However, choosing a self-hosted solution on a VPS has its advantages. Firstly, it provides full control over configuration and optimization, which is critical for specific workloads. Secondly, it is often significantly cheaper in the long run, especially with stable or predictable loads. Finally, a self-hosted solution offers flexibility in integrating with other services on your VPS and does not tie you to a specific cloud provider, which is important for minimizing vendor lock-in.

What VPS configuration is needed for this task

The choice of VPS configuration for PgBouncer and PostgreSQL depends on the expected load, the number of concurrent connections, and the data volume. PgBouncer itself is very lightweight and consumes minimal resources, but it acts as a proxy for PostgreSQL, which can be quite demanding.

Minimum requirements for small and medium projects (up to 50-100 concurrent connections to PgBouncer)

• CPU: 2 vCPU. PostgreSQL actively uses the processor, especially for complex queries.
• RAM: 4 GB. PostgreSQL can consume a lot of RAM for data caching and buffers. PgBouncer, on the other hand, requires very little memory – tens of megabytes.
• Disk: 80 GB NVMe SSD. Disk subsystem speed is extremely important for a database. NVMe provides significantly higher performance compared to regular SSDs or HDDs. The volume depends on the size of your database and its growth rate.
• Network: 100 Mbps or 1 Gbps. For most tasks, 100 Mbps is sufficient, but for high-load applications with a large volume of data transferred between the application and the database, 1 Gbps is better.

Recommended VPS plan for most tasks

For more serious projects, where 200-300 concurrent connections through PgBouncer and active database operations are expected, consider the following characteristics:

• CPU: 4 vCPU
• RAM: 8-16 GB
• Disk: 160-320 GB NVMe SSD
• Network: 1 Gbps

For example, you can choose a VPS with the specified characteristics, which will provide sufficient performance and stability for most web applications and services.

When a dedicated server is needed, not a VPS

A dedicated server becomes necessary when:

• Maximum performance and isolation are required: For very large databases, high-load OLTP systems, or analytical platforms where every millisecond of response time is critical, and you do not want to share resources with other users.
• High I/O requirements: When the VPS disk subsystem cannot handle the volume of input/output operations, especially when using RAID arrays on a dedicated server.
• Specific security and compliance requirements: Some regulatory requirements may mandate the use of physically isolated hardware.
• Scaling: If the resources of the largest VPS plan are no longer sufficient, and further horizontal or vertical scaling is required, a dedicated server provides more options.

Location: what it affects

The choice of VPS location directly impacts the latency between your application and the database. Ideally, your VPS with PgBouncer and PostgreSQL should be in the same data center or geographical region as your client application servers. This minimizes ping and ensures the best data exchange speed.

• Proximity to users: If your primary users are in a specific region, locating the server there will improve their experience.
• Proximity to applications: If your web server is in one region and the database in another, this will add latency to every database query.
• Legislation: Some countries have strict data storage laws, which may require data to be hosted in a specific jurisdiction.

Server preparation

After provisioning a new VPS, it is necessary to perform a series of basic configurations to ensure security and ease of management. We will be using Ubuntu Server 24.04 LTS, as it is a current and stable version for 2026.

1. System update

First, let's update the package list and installed packages to the latest versions.

sudo apt update && sudo apt upgrade -y # Update package list and install updates

2. Creating a new user with sudo privileges

Working as the root user is insecure. Let's create a new user and grant them sudo privileges.

sudo adduser username # Create a new user (replace 'username' with the desired name)
sudo usermod -aG sudo username # Add the user to the 'sudo' group

Now, log out of the root session and log in as the new user.

3. Configuring SSH key authentication

This is a more secure authentication method than a password. If you don't already have an SSH key pair, generate one on your local machine (ssh-keygen).

mkdir -p ~/.ssh # Create the directory for keys if it doesn't exist
chmod 700 ~/.ssh # Set correct permissions for the directory
nano ~/.ssh/authorized_keys # Open the file to add the public key

Paste your public SSH key (starting with ssh-rsa AAAA... or ssh-ed25519 AAAA...) into this file and save (Ctrl+X, Y, Enter). Then set the correct permissions:

chmod 600 ~/.ssh/authorized_keys # Set correct permissions for the key file

After verifying login with the key, disable password login for root and all users in /etc/ssh/sshd_config:

sudo nano /etc/ssh/sshd_config # Open SSH server configuration

Find and change (or add) the following lines:

PermitRootLogin no
PasswordAuthentication no
ChallengeResponseAuthentication no
UsePAM no

Save changes and restart the SSH service:

sudo systemctl restart sshd # Restart SSH server to apply changes

4. Installing and configuring Fail2Ban

Fail2Ban scans logs and temporarily blocks IP addresses that show signs of malicious attacks (e.g., multiple failed SSH login attempts).

sudo apt install fail2ban -y # Install Fail2Ban (version 0.12.0+ for Ubuntu 24.04)
sudo cp /etc/fail2ban/jail.conf /etc/fail2ban/jail.local # Copy config for local changes
sudo nano /etc/fail2ban/jail.local # Edit local config

In the jail.local file, ensure the [sshd] section is enabled (enabled = true) and configure parameters as desired (bantime, findtime, maxretry). For example:

[sshd]
enabled = true
port = ssh
logpath = %(sshd_log)s
backend = systemd
bantime = 1h # IP ban time (1 hour)
findtime = 10m # Time window for attempts (10 minutes)
maxretry = 3 # Maximum number of attempts before banning

sudo systemctl enable fail2ban # Enable Fail2Ban autostart on system boot
sudo systemctl start fail2ban # Start Fail2Ban service

5. Firewall configuration (UFW)

UFW (Uncomplicated Firewall) is a simple interface for configuring iptables rules. By default, we will only allow SSH, HTTP(S), and ports for PostgreSQL/PgBouncer.

sudo apt install ufw -y # Install UFW
sudo ufw allow ssh # Allow SSH (port 22)
sudo ufw allow http # Allow HTTP (port 80)
sudo ufw allow https # Allow HTTPS (port 443)
sudo ufw allow 5432/tcp # Allow standard PostgreSQL port
sudo ufw allow 6432/tcp # Allow standard PgBouncer port (we will use it)
sudo ufw enable # Enable firewall (confirm 'y')
sudo ufw status # Check firewall status

Software installation — step-by-step

Software Installation — Step-by-Step

Now that the server is prepared, let's proceed with installing PostgreSQL and PgBouncer. We will use the latest versions available in Ubuntu 24.04 LTS repositories or from official sources.

1. PostgreSQL Installation

By 2026, PostgreSQL 16.x or 17.x will most likely be available in Ubuntu 24.04 LTS through standard repositories, but for maximum currency and to use new features, we will target PostgreSQL 18.0+. To do this, we will add the official PostgreSQL repository.

sudo apt install curl gnupg2 -y # Install utilities for working with keys and repositories
curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo gpg --dearmor -o /etc/apt/trusted.gpg.d/postgresql.gpg # Add PostgreSQL GPG key
echo "deb http://apt.postgresql.org/pub/repos/apt/ noble-pgdg main" | sudo tee /etc/apt/sources.list.d/pgdg.list > /dev/null # Add PostgreSQL repository for Ubuntu 24.04 (Noble Numbat)
sudo apt update # Update package list after adding the repository
sudo apt install postgresql-18 -y # Install PostgreSQL 18 (current version for 2026)

Check PostgreSQL status:

sudo systemctl status postgresql # Check that PostgreSQL is running

By default, PostgreSQL creates a postgres user and a postgres database. For security and convenience, let's create a separate user and database for your application.

sudo -i -u postgres # Switch to postgres user
psql # Start psql console

In the psql console:

CREATE USER myappuser WITH PASSWORD 'your_strong_password'; -- Create user for the application
CREATE DATABASE myappdb OWNER myappuser; -- Create database for the application, owned by myappuser
GRANT ALL PRIVILEGES ON DATABASE myappdb TO myappuser; -- Grant all privileges on the database
\q # Exit psql
exit # Exit postgres user

2. PgBouncer Installation

PgBouncer is also available through standard Ubuntu repositories. Version 1.23.0 or higher is expected by 2026.

sudo apt install pgbouncer -y # Install PgBouncer

Check that PgBouncer is installed and its version:

pgbouncer --version # Check PgBouncer version (1.23.0+ expected)

By default, PgBouncer is not running or is configured with minimal parameters. We will configure it in the next section.

3. Configuring PostgreSQL for PgBouncer

PgBouncer will connect to PostgreSQL as a regular client. You need to ensure that PostgreSQL allows connections from the local host. Edit the pg_hba.conf file.

sudo nano /etc/postgresql/18/main/pg_hba.conf # Open host access configuration file

Add the following lines to the end of the file to allow PgBouncer to connect from the local host:

# TYPE  DATABASE        USER            ADDRESS                 METHOD
host    all             all             127.0.0.1/32            md5
host    all             all             ::1/128                 md5

Also, ensure that PostgreSQL is listening on the local interface. Edit postgresql.conf:

sudo nano /etc/postgresql/18/main/postgresql.conf # Open the main PostgreSQL configuration file

Find the listen_addresses line and ensure it includes localhost:

listen_addresses = 'localhost' # PostgreSQL will listen only on the local interface

Save changes and restart PostgreSQL:

sudo systemctl restart postgresql # Restart PostgreSQL to apply changes

Configuration

Now we will proceed with detailed PgBouncer configuration. The main configuration file is located at /etc/pgbouncer/pgbouncer.ini.

1. Main PgBouncer Configuration File (pgbouncer.ini)

Open the file for editing:

sudo nano /etc/pgbouncer/pgbouncer.ini # Open PgBouncer configuration file

Here is an example configuration with important comments:

[databases]
# Database name as seen by PgBouncer clients
# Format:  = host= port= dbname= user=
# In our case, PgBouncer will connect to local PostgreSQL
myappdb = host=127.0.0.1 port=5432 dbname=myappdb user=pgbouncer_admin

[pgbouncer]
listen_addr = 0.0.0.0 # PgBouncer will listen on all network interfaces
listen_port = 6432 # Port on which PgBouncer will accept connections from clients

auth_type = md5 # Authentication type for clients connecting to PgBouncer (md5, plain, hba, cert, trust, any)
auth_file = /etc/pgbouncer/userlist.txt # File with list of users and their passwords for PgBouncer

admin_users = pgbouncer_admin # Users who can connect to the pseudo-DB "pgbouncer" for management
stats_users = pgbouncer_admin # Users who can view statistics

pool_mode = session # Pooling mode: session, transaction, statement
# session: connection is returned to the pool only after client disconnection. Simplest, but least efficient.
# transaction: connection is returned to the pool after each transaction. Recommended for most web applications.
# statement: connection is returned to the pool after each query (statement). Most aggressive, but can break application logic using session variables.

default_pool_size = 20 # Number of connections PgBouncer will maintain with each DB by default
min_pool_size = 5 # Minimum number of connections PgBouncer will keep open
max_client_conn = 1000 # Maximum number of clients that can connect to PgBouncer
max_db_connections = 0 # Maximum number of PgBouncer connections to a single DB (0 = no limit)
max_user_connections = 0 # Maximum number of connections for a single user (0 = no limit)

# Timeouts
server_lifetime = 3600 # Connection to the PostgreSQL server will be recreated every 3600 seconds (1 hour)
server_idle_timeout = 600 # Close idle connections to PostgreSQL after 600 seconds
client_idle_timeout = 300 # Close idle PgBouncer client connections after 300 seconds

# Logging
logfile = /var/log/pgbouncer/pgbouncer.log
pidfile = /var/run/pgbouncer/pgbouncer.pid
log_connections = 1
log_disconnections = 1
log_pooler_errors = 1

# TLS/SSL settings for connections between PgBouncer and PostgreSQL
# If your PostgreSQL is configured to use TLS, PgBouncer must use it
server_tls_sslmode = prefer # SSL mode for PgBouncer -> PostgreSQL (disable, allow, prefer, require, verify-ca, verify-full)
server_tls_ca_file = /etc/ssl/certs/ca-certificates.crt # CA certificate for verifying PostgreSQL server
server_tls_key_file = # If PgBouncer needs a client certificate for PostgreSQL
server_tls_cert_file = # If PgBouncer needs a client certificate for PostgreSQL

# TLS/SSL settings for connections between clients and PgBouncer
# Clients can connect to PgBouncer via TLS
client_tls_sslmode = disable # SSL mode for Client -> PgBouncer (disable, allow, prefer, require, verify-ca, verify-full)
client_tls_key_file = /etc/ssl/private/pgbouncer-key.pem # Private key for TLS
client_tls_cert_file = /etc/ssl/certs/pgbouncer-cert.pem # Certificate for TLS

Save changes.

2. PgBouncer User File (userlist.txt)

PgBouncer requires a separate file with a list of users and their passwords for client authentication. We will also create a pgbouncer_admin user, which PgBouncer will use to connect to PostgreSQL and for administering PgBouncer itself.

sudo nano /etc/pgbouncer/userlist.txt # Open file for editing

Add the following lines, replacing your_pgbouncer_admin_password with a strong password. For the client user myappuser, use the same password as when creating the user in PostgreSQL.

"pgbouncer_admin" "your_pgbouncer_admin_password"
"myappuser" "your_strong_password"

Important: Passwords are stored in plain text here. Ensure that the permissions for userlist.txt are very strict.

sudo chmod 600 /etc/pgbouncer/userlist.txt # Set strict permissions

Let's create the pgbouncer_admin user in PostgreSQL, which PgBouncer will use to connect to the database.

sudo -i -u postgres # Switch to postgres user
psql # Start psql console

In the psql console:

CREATE USER pgbouncer_admin WITH PASSWORD 'your_pgbouncer_admin_password'; -- Create PgBouncer user
GRANT CONNECT ON DATABASE myappdb TO pgbouncer_admin; -- Grant connect privileges to the database
\q # Exit psql
exit # Exit postgres user

3. Configuring TLS/SSL for PgBouncer

For secure connections between the client and PgBouncer, as well as between PgBouncer and PostgreSQL, it is recommended to use TLS. If you are using Certbot for a web server, you can reuse its certificates, or generate self-signed ones for internal use. For public access, use Certbot/Caddy.

Suppose you want to use Certbot to obtain certificates. If you already have a domain configured on your VPS and are using Caddy or Nginx, you can obtain a certificate for your domain (e.g., pg.yourdomain.com).

sudo apt install certbot -y # Install Certbot
sudo certbot certonly --standalone -d pg.yourdomain.com # Obtain certificate for the domain (replace with your own)

After obtaining certificates, Certbot will place them in /etc/letsencrypt/live/pg.yourdomain.com/. Copy them to a location accessible by PgBouncer and set the correct permissions:

sudo mkdir -p /etc/ssl/pgbouncer # Create directory for PgBouncer certificates
sudo cp /etc/letsencrypt/live/pg.yourdomain.com/fullchain.pem /etc/ssl/pgbouncer/pgbouncer-cert.pem
sudo cp /etc/letsencrypt/live/pg.yourdomain.com/privkey.pem /etc/ssl/pgbouncer/pgbouncer-key.pem
sudo chmod 600 /etc/ssl/pgbouncer/pgbouncer-key.pem # Private key must be accessible only by PgBouncer
sudo chown pgbouncer:pgbouncer /etc/ssl/pgbouncer/ # Change file owner to pgbouncer user

client_tls_sslmode = require # Require TLS from clients
client_tls_key_file = /etc/ssl/pgbouncer/pgbouncer-key.pem
client_tls_cert_file = /etc/ssl/pgbouncer/pgbouncer-cert.pem

sudo systemctl restart pgbouncer # Restart PgBouncer
sudo systemctl enable pgbouncer # Enable PgBouncer autostart
sudo systemctl status pgbouncer # Check PgBouncer status

psql -h 127.0.0.1 -p 6432 -U myappuser -d myappdb # Connect to PgBouncer

SELECT 1;
\conninfo # Shows that you are connected to PgBouncer, not directly to PostgreSQL
\q

psql -h 127.0.0.1 -p 6432 -U pgbouncer_admin -d pgbouncer # Connect to PgBouncer admin interface

SHOW STATS; # Show general pooling statistics
SHOW POOLS; # Show connection pool status
SHOW CLIENTS; # Show active clients
SHOW SERVERS; # Show active connections to PostgreSQL
\q