2ndQuadrant is now part of EDB

Bringing together some of the world's top PostgreSQL experts.

2ndQuadrant | PostgreSQL
Mission Critical Databases
  • Contact us
  • EN
    • FR
    • IT
    • ES
    • DE
    • PT
  • Support & Services
  • Products
  • Downloads
    • Installers
      • Postgres Installer
      • 2UDA – Unified Data Analytics
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
  • Postgres Learning Center
    • Webinars
      • Upcoming Webinars
      • Webinar Library
    • Whitepapers
      • Business Case for PostgreSQL Support
      • Security Best Practices for PostgreSQL
    • Blog
    • Training
      • Course Catalogue
    • Case Studies
      • Performance Tuning
        • BenchPrep
        • tastyworks
      • Distributed Clusters
        • ClickUp
        • European Space Agency (ESA)
        • Telefónica del Sur
        • Animal Logic
      • Database Administration
        • Agilis Systems
      • Professional Training
        • Met Office
        • London & Partners
      • Database Upgrades
        • Alfred Wegener Institute (AWI)
      • Database Migration
        • International Game Technology (IGT)
        • Healthcare Software Solutions (HSS)
        • Navionics
    • Books
      • PostgreSQL 11 Administration Cookbook
      • PostgreSQL 10 Administration Cookbook
      • PostgreSQL High Availability Cookbook – 2nd Edition
      • PostgreSQL 9 Administration Cookbook – 3rd Edition
      • PostgreSQL Server Programming Cookbook – 2nd Edition
      • PostgreSQL 9 Cookbook – Chinese Edition
    • Videos
    • Events
    • PostgreSQL
      • PostgreSQL – History
      • Who uses PostgreSQL?
      • PostgreSQL FAQ
      • PostgreSQL vs MySQL
      • The Business Case for PostgreSQL
      • Security Information
      • Documentation
  • About Us
    • About 2ndQuadrant
    • 2ndQuadrant’s Passion for PostgreSQL
    • News
    • Careers
    • Team Profile
  • Blog
  • Menu Menu
You are here: Home1 / Blog2 / Eisentraut's PlanetPostgreSQL3 / Running multiple PgBouncer instances with systemd
Running multiple PgBouncer instances with systemd
Peter Eisentraut

Running multiple PgBouncer instances with systemd

August 13, 2020/2 Comments/in Eisentraut's PlanetPostgreSQL /by Peter Eisentraut

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

(Remember to daemon-reload at appropriate times. I’m not going to repeat that every time.)

Then use

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:

  1. A TCP/IP socket on port 6432 for normal use. (You could also use 5432.)
  2. Per-instance TCP/IP and Unix-domain sockets, for administration and monitoring.

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

Then change [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]

or

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.

Tags: PgBouncer, systemd
Share this entry
  • Share on Facebook
  • Share on Twitter
  • Share on WhatsApp
  • Share on LinkedIn
2 replies
  1. Andrey Borodin
    Andrey Borodin says:
    August 17, 2020 at 3:55 pm

    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.

    Reply
  2. Peter Eisentraut
    Peter Eisentraut says:
    April 2, 2021 at 11:22 am

    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.

    Reply

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Search

Get in touch with us!

Recent Posts

  • Random Data December 3, 2020
  • Webinar: COMMIT Without Fear – The Beauty of CAMO [Follow Up] November 13, 2020
  • Full-text search since PostgreSQL 8.3 November 5, 2020
  • Random numbers November 3, 2020
  • Webinar: Best Practices for Bulk Data Loading in PostgreSQL [Follow Up] November 2, 2020

Featured External Blogs

Tomas Vondra's Blog

Our Bloggers

  • Simon Riggs
  • Alvaro Herrera
  • Andrew Dunstan
  • Craig Ringer
  • Francesco Canovai
  • Gabriele Bartolini
  • Giulio Calacoci
  • Ian Barwick
  • Marco Nenciarini
  • Mark Wong
  • Pavan Deolasee
  • Petr Jelinek
  • Shaun Thomas
  • Tomas Vondra
  • Umair Shahid

PostgreSQL Cloud

2QLovesPG 2UDA 9.6 backup Barman BDR Business Continuity community conference database DBA development devops disaster recovery greenplum Hot Standby JSON JSONB logical replication monitoring OmniDB open source Orange performance PG12 pgbarman pglogical PG Phriday postgres Postgres-BDR postgres-xl PostgreSQL PostgreSQL 9.6 PostgreSQL10 PostgreSQL11 PostgreSQL 11 PostgreSQL 11 New Features postgresql repmgr Recovery replication security sql wal webinar webinars

Support & Services

24/7 Production Support

Developer Support

Remote DBA for PostgreSQL

PostgreSQL Database Monitoring

PostgreSQL Health Check

PostgreSQL Performance Tuning

Database Security Audit

Upgrade PostgreSQL

PostgreSQL Migration Assessment

Migrate from Oracle to PostgreSQL

Products

HA Postgres Clusters

Postgres-BDR®

2ndQPostgres

pglogical

repmgr

Barman

Postgres Cloud Manager

SQL Firewall

Postgres-XL

OmniDB

Postgres Installer

2UDA

Postgres Learning Center

Introducing Postgres

Blog

Webinars

Books

Videos

Training

Case Studies

Events

About Us

About 2ndQuadrant

What does 2ndQuadrant Mean?

News

Careers 

Team Profile

© 2ndQuadrant Ltd. All rights reserved. | Privacy Policy
  • Twitter
  • LinkedIn
  • Facebook
  • Youtube
  • Mail
Webinar: Being Committed – A Review of Transaction Control Statements... Webinar: PostgreSQL on Windows [Follow Up]
Scroll to top
×