How to Install pgBouncer from Source
[phil@localhost ~]$ pgbouncer -V
PgBouncer 1.14.0
libevent 2.0.21-stable
adns: libc-2.17
tls: OpenSSL 1.0.2k-fips 26 Jan 2017
[phil@pgbouncer ~]$ pgbouncer -V
PgBouncer 1.15.0
libevent 2.1.8-stable
adns: evdns2
tls: OpenSSL 1.1.1d 10 Sep 2019
systemd: yes
At this point we have 2 options:
- Compile pgBouncer with systemd support using the --with-systemd flag, and fix the OpenSSL version along with it.
- Use multiple systemd scripts and ini files as a workaround, and live with the version of OpenSSL we have.
How to install pgBouncer with a full compilation from Source
phil@pgbouncer:~$ apt info openssl
Package: openssl
Version: 1.1.1d-0+deb10u5
Priority: optional
Section: utils
Maintainer: Debian OpenSSL Team <pkg-openssl-devel@lists.alioth.debian.org>
phil@pgbouncer:~$ sudo apt install libevent-dev libsystemd-dev libssl-dev libc-ares-dev pkg-config libc-ares2 curl
phil@pgbouncer:~$ curl -o pgbouncer-1.15.0.tar.gz https://www.pgbouncer.org/downloads/files/1.15.0/pgbouncer-1.15.0.tar.gz
phil@pgbouncer:~$ tar xf pgbouncer-1.15.0.tar.gz
phil@pgbouncer:~$ cd pgbouncer-1.15.0/
phil@pgbouncer:~/pgbouncer-1.15.0$ ./configure --prefix=/usr/local --with-systemd --with-cares
[..a lot of checks..]
Results:
adns = c-ares
pam = no
systemd = yes
tls = yes
A few points to note at this stage:
- As mentioned before, you don’t have to install curl, or download the file directly. If you have an air-gapped server (with no internet connection) you can download the tarball anywhere and transfer it onto the target machine as best you can. If so, omit the curl install and the curl call entirely.
- Use of the c-ares DNS name resolution package is optional. Earlier versions (below 1.10) are buggy with IPv6 addresses, however more recent (and less buggy) versions of c-ares have additional capabilities compared to the default libevent 2.x evdns component, which you may want to bake in as part of the build. If c-ares is available, pgBouncer will include it in the build by default. In the example above I have installed it using libc-ares2 and the development headers package libc-ares-dev, and forced it with the --with-cares flag, and it shows up in the Results summary as “adns = c-ares”.
- You may want to build with PAM (pluggable authentication module) support. In this case you will need to install the appropriate PAM libraries and use the --with-pam flag to include them and allow the “pam” authentication type in pgBouncer.
phil@pgbouncer:~/pgbouncer-1.15.0$ make
[..verbose output..]
CCLD pgbouncer
phil@pgbouncer:~/pgbouncer-1.15.0$ sudo make install
INSTALL pgbouncer /usr/local/bin
INSTALL README.md /usr/local/share/doc/pgbouncer
INSTALL NEWS.md /usr/local/share/doc/pgbouncer
INSTALL etc/pgbouncer.ini /usr/local/share/doc/pgbouncer
INSTALL etc/userlist.txt /usr/local/share/doc/pgbouncer
INSTALL doc/pgbouncer.1 /usr/local/share/man/man1
INSTALL doc/pgbouncer.5 /usr/local/share/man/man5
phil@pgbouncer:~$ which pgbouncer
/usr/local/bin/pgbouncer
phil@pgbouncer:~$ pgbouncer -V
PgBouncer 1.15.0
libevent 2.1.8-stable
adns: c-ares 1.14.0
tls: OpenSSL 1.1.1d 10 Sep 2019
systemd: yes
Cases where pgBouncer cannot be compiled from source and what to do
The only caveats to using pgBouncer with SO_REUSEPORT are that:
- The server kernel version supports it (i.e has SO_REUSEPORT support in the kernel)
- Note: that this was added in the Linux kernel version 3.9, so if you get:
$ uname -r 3.9.something...or higher
- You should be good to go!
- Note: that this was added in the Linux kernel version 3.9, so if you get:
- The version of pgBouncer is v1.12 or higher. SO_REUSEPORT support was added in v1.12 of pgBouncer, so a lower version isn’t going to even give you the option of using it.
root@pgbouncer:~# pgbouncer -V
PgBouncer 1.15.0
libevent 2.1.8-stable
adns: c-ares 1.14.0
tls: OpenSSL 1.1.1d 10 Sep 2019
root@pgbouncer:~# cat /etc/pgbouncer/bouncer1.ini
;; bouncer1
;; Includes the default pgbouncer.ini file
%include /etc/pgbouncer/pgbouncer.ini
;; Overrides
[databases]
pgbench = dbname=pgbench host=127.0.0.1 user=pgbench password=foo
[users]
bounce = pool_mode=transaction max_user_connections=25
[pgbouncer]
listen_addr = *
logfile = /var/log/postgresql/bouncer1.log
pidfile = /var/run/postgresql/bouncer1.pid
auth_type = trust
so_reuseport = 1
unix_socket_dir = /var/run/postgresql/bouncer1_sock
root@pgbouncer:~# cat /etc/pgbouncer/bouncer2.ini
;; bouncer2
;; Includes the default pgbouncer.ini file
%include /etc/pgbouncer/pgbouncer.ini
;; Overrides
[databases]
pgbench = dbname=pgbench host=127.0.0.1 user=pgbench password=foo
[users]
bounce = pool_mode=transaction max_user_connections=25
[pgbouncer]
listen_addr = *
logfile = /var/log/postgresql/bouncer2.log
pidfile = /var/run/postgresql/bouncer2.pid
auth_type = trust
so_reuseport = 1
unix_socket_dir = /var/run/postgresql/bouncer2_sock
postgres@pgbouncer:~$ mkdir /var/run/postgresql/bouncer1_sock
postgres@pgbouncer:~$ mkdir /var/run/postgresql/bouncer2_sock
root@pgbouncer:~# cat /etc/systemd/system/bouncer1.service
[Unit]
Description=pgBouncer connection pooling for PostgreSQL: Instance 1
After=postgresql.service
[Service]
Type=forking
User=postgres
Group=postgres
ExecStart=/usr/sbin/pgbouncer -d /etc/pgbouncer/bouncer1.ini
ExecReload=/bin/kill -SIGHUP $MAINPID
PIDFile=/var/run/postgresql/bouncer1.pid
[Install]
WantedBy=multi-user.target
root@pgbouncer:~# cat /etc/systemd/system/bouncer2.service
[Unit]
Description=pgBouncer connection pooling for PostgreSQL: Instance 2
After=postgresql.service
[Service]
Type=forking
User=postgres
Group=postgres
ExecStart=/usr/sbin/pgbouncer -d /etc/pgbouncer/bouncer2.ini
ExecReload=/bin/kill -SIGHUP $MAINPID
PIDFile=/var/run/postgresql/bouncer2.pid
[Install]
WantedBy=multi-user.target
root@pgbouncer:~# systemctl daemon-reload
root@pgbouncer:~# systemctl start bouncer1
root@pgbouncer:~# systemctl status bouncer1
● bouncer1.service - pgBouncer connection pooling for PostgreSQL: Instance 1
Loaded: loaded (/etc/systemd/system/bouncer1.service; disabled; vendor preset: enabled)
Active: active (running) since Tue 2021-03-16 10:30:29 CET; 6s ago
Process: 27322 ExecStart=/usr/sbin/pgbouncer -d /etc/pgbouncer/bouncer1.ini (code=exited, status=0/SUCCESS)
Main PID: 27324 (pgbouncer)
Tasks: 2 (limit: 2318)
Memory: 1.2M
CGroup: /system.slice/bouncer1.service
└─27324 /usr/sbin/pgbouncer -d /etc/pgbouncer/bouncer1.ini
Mar 16 10:30:29 pgbouncer systemd[1]: Starting pgBouncer connection pooling for PostgreSQL: Instance 1...
Mar 16 10:30:29 pgbouncer systemd[1]: bouncer1.service: Can't open PID file /run/postgresql/bouncer1.pid (yet?) after start: No such file or directory
Mar 16 10:30:29 pgbouncer systemd[1]: Started pgBouncer connection pooling for PostgreSQL: Instance 1.
root@pgbouncer:~# systemctl start bouncer2
root@pgbouncer:~# systemctl status bouncer2
● bouncer2.service - pgBouncer connection pooling for PostgreSQL: Instance 2
Loaded: loaded (/etc/systemd/system/bouncer2.service; disabled; vendor preset: enabled)
Active: active (running) since Tue 2021-03-16 10:30:41 CET; 5s ago
Process: 27330 ExecStart=/usr/sbin/pgbouncer -d /etc/pgbouncer/bouncer2.ini (code=exited, status=0/SUCCESS)
Main PID: 27332 (pgbouncer)
Tasks: 2 (limit: 2318)
Memory: 1.2M
CGroup: /system.slice/bouncer2.service
└─27332 /usr/sbin/pgbouncer -d /etc/pgbouncer/bouncer2.ini
Mar 16 10:30:41 pgbouncer systemd[1]: Starting pgBouncer connection pooling for PostgreSQL: Instance 2...
Mar 16 10:30:41 pgbouncer systemd[1]: bouncer2.service: Can't open PID file /run/postgresql/bouncer2.pid (yet?) after start: No such file or directory
Mar 16 10:30:41 pgbouncer systemd[1]: Started pgBouncer connection pooling for PostgreSQL: Instance 2.
root@pgbouncer:~# /usr/bin/pgbench -h localhost -p 6432 -U bounce -i pgbench
dropping old tables...
NOTICE: table "pgbench_accounts" does not exist, skipping
NOTICE: table "pgbench_branches" does not exist, skipping
NOTICE: table "pgbench_history" does not exist, skipping
NOTICE: table "pgbench_tellers" does not exist, skipping
creating tables...
generating data...
100000 of 100000 tuples (100%) done (elapsed 0.04 s, remaining 0.00 s)
vacuuming...
creating primary keys...
done.
root@pgbouncer:~# psql -h localhost -p 6432 -U bounce pgbench
psql (11.10 (Debian 11.10-0+deb10u1))
Type "help" for help.
pgbench=# \dt
List of relations
Schema | Name | Type | Owner
--------+------------------+-------+---------
public | pgbench_accounts | table | pgbench
public | pgbench_branches | table | pgbench
public | pgbench_history | table | pgbench
public | pgbench_tellers | table | pgbench
(4 rows)
CPU[| 0.7%] Tasks: 92, 126 thr; 1 running
Mem[||||||||||||||||||||||||||||||||||||||||579M/1.92G] Load average: 0.28 1.14 0.69
Swp[| 7.29M/975M] Uptime: 03:00:23
PID USER PRI NI VIRT RES SHR S CPU% MEM% TIME+ Command
27524 postgres 20 0 19944 3304 2368 S 0.0 0.2 0:00.00 /usr/sbin/pgbouncer -d /etc/pgbouncer/bouncer1.ini
27523 postgres 20 0 19944 3304 2368 S 0.0 0.2 0:39.43 /usr/sbin/pgbouncer -d /etc/pgbouncer/bouncer1.ini
27542 postgres 20 0 19944 3456 2536 S 0.0 0.2 0:00.00 /usr/sbin/pgbouncer -d /etc/pgbouncer/bouncer2.ini
27541 postgres 20 0 19944 3456 2536 S 0.0 0.2 0:13.99 /usr/sbin/pgbouncer -d /etc/pgbouncer/bouncer2.ini
root@pgbouncer:~# /usr/bin/pgbench -h localhost -p 6432 -U bounce -c 4 -T 300 pgbench
CPU[|||||||||||||||||||||||||||||||||||||||||||||97.7%] Tasks: 93, 124 thr; 1 running
Mem[||||||||||||||||||||||||||||||||||||||||579M/1.92G] Load average: 0.52 0.68 0.58
Swp[| 7.29M/975M] Uptime: 03:03:44
PID USER PRI NI VIRT RES SHR S CPU% MEM% TIME+ Command
27541 postgres 20 0 19944 3456 2536 S 10.9 0.2 0:15.24 /usr/sbin/pgbouncer -d /etc/pgbouncer/bouncer2.ini
27523 postgres 20 0 19944 3304 2368 S 10.2 0.2 0:40.67 /usr/sbin/pgbouncer -d /etc/pgbouncer/bouncer1.ini
27524 postgres 20 0 19944 3304 2368 S 0.0 0.2 0:00.00 /usr/sbin/pgbouncer -d /etc/pgbouncer/bouncer1.ini
27542 postgres 20 0 19944 3456 2536 S 0.0 0.2 0:00.00 /usr/sbin/pgbouncer -d /etc/pgbouncer/bouncer2.ini
Why Use SO_REUSEPORT
-
Scaling pgBouncer
a. pgBouncer is single threaded. On a multi-threaded and/or multi-CPU machine, a single instance of pgBouncer isn’t going to do you any good, as it only works on a single thread
b. By adding more pgBouncer instances, you can use more than one thread - easy scaling! -
Provide load balancing
a. As shown in the previous section, when using SO_REUSEPORT, you effectively get load-balancing for free, at the port level of incoming traffic. We’ll discuss this further in.
-
Provide rw/ro routing
a. Using different pgBouncer databases you can
i. Route read-write traffic to the database currently the primary
ii. Round-robin route read-only traffic to a number of standby databases
iii. Weight the load on a given standby by ratios you set! -
Provide failover
a. As shown in the previous section you can perform silent failover during promotion of a new primary, although you’ll need a VIP (Virtual IP address, always pointing at the primary) to do it.
-
And even DoS prevention!
a. By using distinct port numbers you can provide database connections which deal with sudden bursts of incoming traffic in very different ways, which can help prevent the database from becoming swamped during high activity periods
b. This can be useful even when your applications are just sometimes “badly behaved”
Scaling
Load balancing
Provide rw/ro routing
First, I have set up two standbys, like so:
postgres=# select application_name, client_addr, sync_state from pg_stat_replication;
application_name | client_addr | sync_state
------------------+-----------------+------------
standby1 | 192.168.152.129 | async
standby2 | 192.168.152.130 | async
(2 rows)
[databases]
pgbench_rw = dbname=pgbench host=127.0.0.1 user=pgbench password=foo
pgbench_ro = dbname=pgbench host=192.168.152.129 port=5432 user=pgbench password=foo
[databases]
pgbench_rw = dbname=pgbench host=127.0.0.1 user=pgbench password=foo
pgbench_ro = dbname=pgbench host=192.168.152.130 port=5432 user=pgbench password=foo
root@pgbouncer:~# /usr/bin/pgbench -h localhost -p 6432 -U bounce -c 4 -T 30 pgbench_rw
starting vacuum...end.
transaction type:
scaling factor: 1
query mode: simple
number of clients: 4
number of threads: 1
duration: 30 s
number of transactions actually processed: 19051
latency average = 6.300 ms
tps = 634.954188 (including connections establishing)
tps = 634.971881 (excluding connections establishing)
root@pgbouncer:~# /usr/bin/pgbench -h localhost -p 6432 -U bounce -c 4 -T 5 pgbench_ro
starting vacuum...ERROR: cannot execute VACUUM during recovery
(ignoring this error and continuing anyway)
ERROR: cannot execute VACUUM during recovery
(ignoring this error and continuing anyway)
ERROR: cannot execute TRUNCATE TABLE in a read-only transaction
(ignoring this error and continuing anyway)
end.
client 0 aborted in command 5 (SQL) of script 0; ERROR: cannot execute UPDATE in a read-only transaction
client 1 aborted in command 5 (SQL) of script 0; ERROR: cannot execute UPDATE in a read-only transaction
client 3 aborted in command 5 (SQL) of script 0; ERROR: cannot execute UPDATE in a read-only transaction
client 2 aborted in command 5 (SQL) of script 0; ERROR: cannot execute UPDATE in a read-only transaction
transaction type:
scaling factor: 1
query mode: simple
number of clients: 4
number of threads: 1
duration: 5 s
number of transactions actually processed: 0
root@pgbouncer:~# /usr/bin/pgbench -h localhost -p 6432 -U bounce -c 4 --transactions=10000 --select-only --no-vacuum pgbench_ro
transaction type:
scaling factor: 1
query mode: simple
number of clients: 4
number of threads: 1
number of transactions per client: 10000
number of transactions actually processed: 40000/40000
latency average = 0.761 ms
tps = 5253.376928 (including connections establishing)
tps = 5253.479261 (excluding connections establishing)
postgres=# select application_name, client_addr from pg_stat_activity where application_name = 'pgbench';
application_name | client_addr
------------------+-----------------
pgbench | 192.168.152.128
(1 row)
postgres=# select application_name, client_addr from pg_stat_activity where application_name = 'pgbench';
application_name | client_addr
------------------+-------------
(0 rows)
Failover
How to setup pgBouncer in hostile environments
Heading off DoS attacks and badly behaved applications
root@pgbouncer:~# cat /etc/pgbouncer/bouncer3.ini
;; bouncer1
;; Includes the default pgbouncer.ini file
%include /etc/pgbouncer/pgbouncer.ini
;; Overrides
[databases]
pgbench_dos = dbname=pgbench host=127.0.0.1 user=pgbench password=foo
[users]
bounce = pool_mode=transaction max_user_connections=200
[pgbouncer]
listen_addr = *
listen_port = 6433
logfile = /var/log/postgresql/bouncer3.log
pidfile = /var/run/postgresql/bouncer3.pid
auth_type = trust
unix_socket_dir = /var/run/postgresql/bouncer3_sock
so_reuseport = 0
max_db_connections = 10
- We are listening on port 6433 and using database pgbench_dos to connect. This is deliberate to keep incoming traffic isolated for this workload, even though it ultimately connects to the same database - the primary
- We have increased max_user_connections to 200, so 200 client connections can be made at any given time, but we have also dropped max_db_connections to 10, so that there will never be more than 10 database connections allowed at any given time, any more incoming traffic than can be handled by this will be buffered, or eventually simply time out, or fail altogether. There are many other settings that can be used to throttle this traffic, but this is simple and neat for a demonstration.
- I’ve turned SO_REUSEPORT off, but you can use the two in tandem.
- As before, the /var/run/postgresql/bouncer3_sock directory needs to be created by postgres
root@pgbouncer:~# cat /etc/systemd/system/bouncer3.service
[Unit]
Description=pgBouncer connection pooling for PostgreSQL: Instance 3
After=postgresql.service
[Service]
Type=forking
User=postgres
Group=postgres
ExecStart=/usr/sbin/pgbouncer -d /etc/pgbouncer/bouncer3.ini
ExecReload=/bin/kill -SIGHUP $MAINPID
PIDFile=/var/run/postgresql/bouncer3.pid
[Install]
WantedBy=multi-user.target
root@pgbouncer:~# systemctl status bouncer3.service
● bouncer3.service - pgBouncer connection pooling for PostgreSQL: Instance 3
Loaded: loaded (/etc/systemd/system/bouncer3.service; disabled; vendor preset: enabled)
Active: active (running) since Wed 2021-03-17 12:49:55 CET; 41s ago
Process: 34068 ExecStart=/usr/sbin/pgbouncer -d /etc/pgbouncer/bouncer3.ini (code=exited, status=0/SUCCESS)
Main PID: 34070 (pgbouncer)
Tasks: 2 (limit: 2318)
Memory: 1.2M
CGroup: /system.slice/bouncer3.service
└─34070 /usr/sbin/pgbouncer -d /etc/pgbouncer/bouncer3.ini
Mar 17 12:49:55 pgbouncer systemd[1]: Starting pgBouncer connection pooling for PostgreSQL: Instance 3...
Mar 17 12:49:55 pgbouncer systemd[1]: bouncer3.service: Can't open PID file /run/postgresql/bouncer3.pid (yet?) after start: No such file or directory
Mar 17 12:49:55 pgbouncer systemd[1]: Started pgBouncer connection pooling for PostgreSQL: Instance 3.
phil@pgbouncer:~$ /usr/bin/pgbench -h localhost -p 6432 -U bounce -C -c 20 -T 600 pgbench_rw
phil@pgbouncer:~$ /usr/bin/pgbench -h localhost -p 6432 -U bounce -C -c 20 -T 600 --select-only --no-vacuum pgbench_ro
phil@pgbouncer:~$ /usr/bin/pgbench -h localhost -p 6433 -U bounce -C -c 20 -T 600 pgbench_dos
starting vacuum...end.
phil@pgbouncer:~$ /usr/bin/pgbench -h localhost -p 6433 -U bounce -C -c 200 -T 600 pgbench_dos
starting vacuum...end.
connection to database "pgbench_dos" failed:
ERROR: no more connections allowed (max_client_conn)
client 100 aborted while establishing connection
connection to database "pgbench_dos" failed:
ERROR: no more connections allowed (max_client_conn)
client 101 aborted while establishing connection
connection to database "pgbench_dos" failed:
ERROR: no more connections allowed (max_client_conn)
client 102 aborted while establishing connection
connection to database "pgbench_dos" failed:
ERROR: no more connections allowed (max_client_conn)
A useful pgBouncer architecture for resiliency
