pgBouncer Setup, Extended Architecture, Use-cases and leveraging SO_REUSEPORT

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!
• 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

1. 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!

2. 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.

3. 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!

4. 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.

5. 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)

Now I’ll create some database definitions in my pgBouncer .ini files

 

bouncer1.ini

[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

bouncer2.ini

[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

Some points to note, here. The round-robin connectivity can not be provided by libpq, which allows multiple endpoints in a single connection string, even though pgBouncer uses libpq to connect. The connection attempt just hangs when you attempt to connect to pgBouncer if you try to set this up.

 

However, because we are using multiple instances of pgBouncer, listening on the same port, just pointing at different machines, the connections can now go round-robin between them. And, because we know how many of each kind of connection types we are spinning up, we also have more control over how that load is balanced. So, you can have more pgBouncer instances pointing at standby2 than at standby1, for example, if you want to divert more load to standby2.

 

Be careful, though: if one of your standbys goes down and you are using this kind of load balancing, you are going to unpredictably lose a proportion of your read traffic. To avoid this you can run a service that pings the databases and shuts down the appropriate pgBouncer services if they don’t respond, or add hooks to your failover manager to shut down pgBouncer as databases go down.

 

But, the big question, does it work?

 

First, I’ll send some read-write traffic to pgbench_rw (the primary, on localhost 127.0.0.1).

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)

So that worked, which is good news, as it means that both reads and writes are being sent to the pgbench_rw (our read-write, primary) database in pgBouncer, and all the transactions completed successfully.

 

Just to see what happens, let’s try sending that same workload to pgbench_ro (our read-only, secondary) databases.

 

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

A lot of errors complaining that the servers are in recovery, and “number of transactions actually processed: 0”. This is actually good news, as we don’t want anything other than read-only transactions going to the standbys.

 

Let’s see what happens with a read only workload.

 

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)

Perfect. All 10,000 transactions were completed for all 4 clients.

 

And, just to prove that a read-only workload is only being processed on the standbys, on both of the standby servers (shows pgbench activity coming from our pgbench session):

 

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)

And on the primary (there is no pgbench activity):

    postgres=# select application_name, client_addr from pg_stat_activity where application_name = 'pgbench';
 application_name | client_addr 
------------------+-------------
(0 rows)

Failover

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 this.

 

You don’t even need SO_REUSEPORT, although you can use the two in combination, just make sure all your read-write databases are pointing to the VIP and not a fixed address!

 

How to setup pgBouncer in hostile environments

When traffic loads become too high and you want to prioritise certain traffic over others, or when the machine pgBouncer is installed on fails, what then?

Here are some useful ideas for setting up pgBouncer in a hostile environment.

 

Heading off DoS attacks and badly behaved applications

As a general rule, applications connecting to your databases are well behaved. The OLTP side works well in the daytime connecting to your primary server, the batch jobs run overnight, and reporting is spread neatly across multiple standbys also during the day. All well and good.

 

But then, there’s that one application… Maybe it’s public facing, in the DMZ, and at risk from high traffic or denial of service attacks, or maybe it’s just badly designed. Regardless of why, you can protect the rest of the database traffic from this one relatively untrustworthy source.

 

By using distinct port numbers on pgBouncer you can isolate incoming traffic, and provide database connections which deal with sudden bursts of work in very different ways, which can help prevent the database from becoming swamped during high activity periods from specific applications. This can be useful for providing security on high risk applications, or even when your applications are just sometimes “badly behaved”.

 

So, on our test system, let’s add another pgBouncer instance to deal with high bursts of incoming traffic from a specific application, while letting the “well behaved” traffic do its job.

 

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

Some points to note here:

• 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

So then we just set up our unit script and start the service.

 

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.

And we’re ready to go.

 

Let’s start some heavy read-write traffic on pgbench-rw.

 

phil@pgbouncer:~$ /usr/bin/pgbench -h localhost -p 6432 -U bounce -C -c 20 -T 600 pgbench_rw

And some heavy read-only traffic on pgbench-ro.

 

phil@pgbouncer:~$ /usr/bin/pgbench -h localhost -p 6432 -U bounce -C -c 20 -T 600 --select-only --no-vacuum pgbench_ro

So far so good.

 

What happens if we start twenty clients on port 6433, database pgbench_dos?

phil@pgbouncer:~$ /usr/bin/pgbench -h localhost -p 6433 -U bounce -C -c 20 -T 600 pgbench_dos
starting vacuum...end.

Which is working fine. htop is showing some heavy load, but that’s to be expected.

 

What happens when we throw a huge amount of clients at pgbench_dos, simulating sudden heavy load?

 

Well, we get a lot of these:

 

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)

We’re actually hitting the limit on the number of database connections that can be made, even though pgBouncer accepts double what the database can spin up. The important part is that the two pgBouncer instances that are listening on port 6432 are unaffected, and carry on as usual.

 

So, our - admittedly basic - pgBouncer service we have spun up is protecting the other pgBouncer instances from running out of database connections.

 

This is great news for everyone who doesn’t want to stop doing their job, just because another application entirely isn’t doing theirs.

 

A useful pgBouncer architecture for resiliency

 

But what happens when the machine itself crashes?

 

Consider the usual architecture for pgBouncer:

 

pgBouncer sits on a machine, or machines, between the application and the databases. You can add whatever you like to this architecture, and all your bets are covered. Wrong. If the machine running pgBouncer goes down, all you have added is another point of failure. If pgBouncer is unavailable, you have potentially lost all access to the database, and your application is still running, and now throwing ugly connectivity errors too.

 

You can add a proxy between pgBouncer and the application, checking that pgBouncer (and the databases) are still available, but then all you have done is add yet another potential point of failure to the architecture. What happens if the proxy goes down, or fails to register the availability (or not) of pgBouncer? And so it goes on.

 

However, there is an elegant and simple solution to this problem, named the “Laetitia Avrot” architecture, thanks to my colleague and Postgres expert Laetitia Avrot who first identified it.

 

Simply put, you place pgBouncer at the “highest point of failure”, or to look at it another way, you place pgBouncer at a level in the hierarchy where its failure becomes irrelevant.

 

In the following diagram we see pgBouncer installed on the same machine as the application itself, or perhaps the webserver (or both). If the machine the application relies on goes down, then so does pgBouncer, however if your application itself is unavailable then you have bigger problems than pgBouncer being down. You just have to make sure they are both available at the same time.

 

Another advantage of the Laetitia Avrot architecture is that you don’t need additional resources to run pgBouncer in the first place. Because it is lightweight and easy to manage, you can usually install it on the application server or webserver without too much impact.

 

And anything that can save you time, effort and money on deploying a connection pooler, that has to be worth the investment.