Running multiple PgBouncer instances with systemd
Since PgBouncer runs as a single process, it is not straightforward to make use of multiple CPUs on a host. When you are running on real hardware, this is wasteful, since single-CPU server machines don’t exist anymore, as far as I can tell. When using virtualization, you could just provision a single CPU per virtual host and run multiple virtual hosts with a single PgBouncer instance each. But there are also ways to run multiple instances of PgBouncer on a single host, so let’s explore that. Since version 1.12, PgBouncer supports the
SO_REUSEPORT socket option, which allows running multiple instances of PgBouncer on the same host on the same port and they will share the connections.
First, let’s set up the basics of how to run a single instance of PgBouncer via systemd. PgBouncer ships with a sample service unit configuration file at
etc/pgbouncer.service in the source tree that you can use to get started. Copy that file to
/etc/systemd/system/pgbouncer.service and run
sudo systemctl daemon-reload . Then you can run
sudo systemctl start pgbouncer .
This is the unit file I’m using for this example. It is a slightly adjusted and abbreviated version of the file in the source tree.
[Unit] Description=connection pooler for PostgreSQL After=network.target [Service] Type=notify User=postgres ExecStart=/usr/local/bin/pgbouncer /etc/pgbouncer/pgbouncer.ini ExecReload=/bin/kill -HUP $MAINPID KillSignal=SIGINT [Install] WantedBy=multi-user.target
And this is the PgBouncer configuration file I’m using:
[databases] ;; add yours here [pgbouncer] listen_addr = localhost listen_port = 6432 unix_socket_dir = /tmp so_reuseport = 1
Now if you want to run multiple instances, you can obviously just make copies of these files and run them as entirely separate services. But here we want to make use of the systemd template system. So create a file
/etc/systemd/system/[email protected] that looks like this:
[Unit] Description=connection pooler for PostgreSQL (%i) After=network.target [Service] Type=notify User=postgres ExecStart=/usr/local/bin/pgbouncer /etc/pgbouncer/pgbouncer.ini ExecReload=/bin/kill -HUP $MAINPID KillSignal=SIGINT [Install] WantedBy=multi-user.target
daemon-reload at appropriate times. I’m not going to repeat that every time.)
systemctl start [email protected] systemctl start [email protected] # etc.
and you can start as many as you want. The specific numbers here are irrelevant, they just serve as identifiers. (They don’t even have to be numbers.)
The problem with that setup is that while the TCP/IP listen sockets will happily share between all instances, per the
so_reuseport setting, this does not apply to the Unix-domain sockets. So you would either have to turn off Unix-domain sockets, or create separate configuration files for each instance with different directories for the Unix-domain sockets, which would remove some of the elegance of this approach.
I recommend a new approach using socket activation, which is supported as of PgBouncer 1.14.
Create a file
/etc/systemd/system/[email protected] :
[Unit] Description=sockets for PgBouncer [Socket] ListenStream=6432 ListenStream=%i ListenStream=/tmp/.s.PGSQL.%i ReusePort=true [Install] WantedBy=sockets.target
This gives us:
- A TCP/IP socket on port 6432 for normal use. (You could also use 5432.)
- Per-instance TCP/IP and Unix-domain sockets, for administration and monitoring.
ReusePort setting corresponds to the
so_reuseport setting in
pgbouncer.ini. (When you use systemd socket activation, the listen socket settings in
pgbouncer.ini are ignored.)
[email protected] like this:
[Unit] Description=connection pooler for PostgreSQL (%i) After=network.target [email protected]%i.socket [Service] Type=notify User=postgres ExecStart=/usr/local/bin/pgbouncer /etc/pgbouncer/pgbouncer.ini ExecReload=/bin/kill -HUP $MAINPID KillSignal=SIGINT [Install] WantedBy=multi-user.target
and then you can start
systemctl start [email protected] systemctl start [email protected] systemctl start [email protected] systemctl start [email protected]
systemctl enable [email protected] # etc.
to enable them for system start.
Here, the 50001+ numbers are the administration port numbers. You can use any numbers you want that are not used by anything else.
Now, for production traffic, you can connect like this as normal:
psql -h somehost -p 6432 -d yourdb ...
(must be TCP/IP). For monitoring and administration, you connect to each instance like this:
psql -p 50001 -d pgbouncer -U pgbouncer -c 'SHOW ...'
As alluded to at the beginning, there are many ways to set up and deploy PgBouncer. It depends on available hardware, deployment tools, and architecture requirements. If you have other ideas, please leave a comment or join the discussion on Gitter.
As an alternative to multiple PgBouncers you can just deploy one Odyssey https://github.com/yandex/odyssey and set up number of CPU workers in config file.
Note to everyone who is trying to set up this last recipe: The ReusePort option in systemd does not work correctly for this purpose in versions before 222. (See this commit, which fixes it, for details: https://github.com/systemd/systemd/commit/54255c64e6d223deb7d3863e426e78c443fda37c) RHEL 7 and clones use version 219, so this unfortunately will not work there.