How To Simplify pgBouncer Monitoring with pgbouncer_fdw

For connection pooling in PostgreSQL, one of the best and most popular tools out there is PgBouncer. However, monitoring PgBouncer can be challenging due to its use of SHOW commands, which are only available via a special database connection as opposed to making its statistics available via a standard table or view.

In order to more easily monitor PgBouncer, the team at Crunchy Data developed an open source PgBouncer Foreign Data Wrapper (pgbouncer_fdw). This blog post describes why monitoring PgBouncer is important, and how you can do this easily using pgMonitor and pgbouncer_fdw.

Background

pgMonitor is an open source monitoring utility for PostgreSQL that we've been developing here at CrunchyData. It is a suite of tools used to facilitate the collection and visualization of metrics that you should be aware of in your PostgreSQL database and host environments. It is reasonable to include all the statistics that are available via PgBouncer as well so we can know whether connection pooling is providing a benefit and working as efficiently as possible. However, there was not an easy way to monitor PgBouncer due to the use of SHOW commands from a special database connection to provide those statistics.

$ psql -h localhost -U ccp_monitoring -p 5432 pgbouncer
Password for user ccp_monitoring:
psql (11.6, server 1.12.0/bouncer)
Type "help" for help.

pgbouncer=# \x
Expanded display is on.
pgbouncer=# SHOW pools;
-[ RECORD 1 ]---------
database | pgbouncer
user | pgbouncer
cl_active | 1
cl_waiting | 0
sv_active | 0
sv_idle | 0
sv_used | 0
sv_tested | 0
sv_login | 0
maxwait | 0
maxwait_us | 0
pool_mode | statement
-[ RECORD 2 ]---------
[...]

Without being able to use standard SQL to gather these statistics, it's hard to perform more detailed analysis and collect them using typical database monitoring tools.

I recalled seeing someone using the new Foreign Data Wrapper (FDW) feature introduced way back in PG9.1 to make monitoring PgBouncer easier. A foreign data wrapper in PostgreSQL allows a direct connection to many different external data sources outside the database itself (Oracle, MySQL, other PG databases, files, etc). It makes those connections appear as normal tables that can be queried via standard SQL, depending on the features supported by that specific FDW. Some Googling around found this Gist by Fabrízio de Royes Mello.

This method worked great, but I figured I could make this even easier to manage by turning it into another feature also introduced in PG9.1: Extensions. An extension allows easier installation and management of specific versions of code within the database. As an example, all of the contrib modules available from PostgreSQL core are now extensions as well. This led to the creation of PgBouncer Foreign Data Wrapper (pgbouncer_fdw).

While pgbouncer_fdw isn't technically a FDW itself, it does take advantage of the dblink_fdw feature under the hood, so the name is still fitting.

Using pgbouncer_fdw

After following the typical setup of an FDW, setting up the SERVER and USER MAPPING, pgbouncer_fdw exposes most of the SHOW commands as standard views that can then be queried using standard SQL from any location where the extension is installed.

CREATE EXTENSION dblink;

CREATE SERVER pgbouncer FOREIGN DATA WRAPPER dblink_fdw OPTIONS (host 'localhost',
                                                                 port '6432',
                                                                 dbname 'pgbouncer');

CREATE USER MAPPING FOR PUBLIC SERVER pgbouncer OPTIONS (user 'ccp_monitoring', password 'mypassword');

CREATE EXTENSION pgbouncer_fdw;

GRANT USAGE ON FOREIGN SERVER pgbouncer TO ccp_monitoring;

GRANT SELECT ON pgbouncer_clients TO ccp_monitoring;
GRANT SELECT ON pgbouncer_config TO ccp_monitoring;
GRANT SELECT ON pgbouncer_databases TO ccp_monitoring;
GRANT SELECT ON pgbouncer_dns_hosts TO ccp_monitoring;
GRANT SELECT ON pgbouncer_dns_zones TO ccp_monitoring;
GRANT SELECT ON pgbouncer_lists TO ccp_monitoring;
GRANT SELECT ON pgbouncer_pools TO ccp_monitoring;
GRANT SELECT ON pgbouncer_servers TO ccp_monitoring;
GRANT SELECT ON pgbouncer_sockets TO ccp_monitoring;
GRANT SELECT ON pgbouncer_stats TO ccp_monitoring;
GRANT SELECT ON pgbouncer_users TO ccp_monitoring;

Access to these views can now be controlled via the standard GRANT system as seen above. The GRANT statements show the objects that the pgbouncer_fdw extension creates.

All views correspond to those provided by the similarly named SHOW command from PgBouncer itself., although not all are included, either due to just duplicating data in another view (STATS_TOTALS, STATS_AVERAGES, etc), or following the advice from the source project (FDS).

With pgbouncer_fdw set up, viewing the PgBouncer statistics at any time is now much easier using a simple SELECT statement:

postgres=> SELECT * FROM pgbouncer_pools;
-[ RECORD 1 ]---------
database   | pgbouncer
user       | pgbouncer
cl_active  | 1
cl_waiting | 0
sv_active  | 0
sv_idle    | 0
sv_used    | 0
sv_tested  | 0
sv_login   | 0
maxwait    | 0
maxwait_us | 0
pool_mode  | statement
-[ RECORD 2 ]---------
database   | postgres
user       | postgres
cl_active  | 1
cl_waiting | 0
sv_active  | 1
sv_idle    | 0
sv_used    | 0
sv_tested  | 0
sv_login   | 0
maxwait    | 0
maxwait_us | 0
pool_mode  | session

This extension is fully open source under the PostgreSQL License and available anytime from the Crunchy Data GitHub repository. We hope that it provides what we felt was a missing piece to the monitoring stack of one of the most critical third-party tools to many enterprise installations of PostgreSQL.