pgbouncer_fdw
Extension for querying PgBouncer stats from normal SQL views & running pgbouncer commands from normal SQL functions
Repository
CrunchyData/pgbouncer_fdw
https://github.com/CrunchyData/pgbouncer_fdw
Source
pgbouncer_fdw-1.4.0.tar.gz
pgbouncer_fdw-1.4.0.tar.gz
Overview
| Package | Version | Category | License | Language |
|---|---|---|---|---|
pgbouncer_fdw | 1.4.0 | FDW | PostgreSQL | SQL |
| ID | Extension | Bin | Lib | Load | Create | Trust | Reloc | Schema |
|---|---|---|---|---|---|---|---|---|
| 8650 | pgbouncer_fdw | No | No | No | Yes | No | No | - |
| Related | dblink dblink postgres_fdw pg_stat_monitor pg_stat_statements wrappers multicorn odbc_fdw jdbc_fdw |
|---|
Requires dblink and PgBouncer >= 1.17; live queries require a configured PgBouncer admin console.
Version
| Type | Repo | Version | PG Ver | Package | Deps |
|---|---|---|---|---|---|
| EXT | MIXED | 1.4.0 | 1817161514 | pgbouncer_fdw | dblink |
| RPM | PGDG | 1.4.0 | 1817161514 | pgbouncer_fdw_$v | - |
| DEB | PIGSTY | 1.4.0 | 1817161514 | postgresql-$v-pgbouncer-fdw | - |
Build
You can build the DEB packages for pgbouncer_fdw using pig build:
pig build pkg pgbouncer_fdw # build DEB packages
Install
You can install pgbouncer_fdw directly. First, make sure the PGDG and PIGSTY repositories are added and enabled:
pig repo add pgsql -u # Add repo and update cache
Install the extension using pig or apt/yum/dnf:
pig install pgbouncer_fdw; # Install for current active PG version
pig ext install -y pgbouncer_fdw -v 18 # PG 18
pig ext install -y pgbouncer_fdw -v 17 # PG 17
pig ext install -y pgbouncer_fdw -v 16 # PG 16
pig ext install -y pgbouncer_fdw -v 15 # PG 15
pig ext install -y pgbouncer_fdw -v 14 # PG 14
dnf install -y pgbouncer_fdw_18 # PG 18
dnf install -y pgbouncer_fdw_17 # PG 17
dnf install -y pgbouncer_fdw_16 # PG 16
dnf install -y pgbouncer_fdw_15 # PG 15
dnf install -y pgbouncer_fdw_14 # PG 14
apt install -y postgresql-18-pgbouncer-fdw # PG 18
apt install -y postgresql-17-pgbouncer-fdw # PG 17
apt install -y postgresql-16-pgbouncer-fdw # PG 16
apt install -y postgresql-15-pgbouncer-fdw # PG 15
apt install -y postgresql-14-pgbouncer-fdw # PG 14
Create Extension:
CREATE EXTENSION pgbouncer_fdw CASCADE; -- requires: dblink
Usage
Create Server
CREATE EXTENSION pgbouncer_fdw;
CREATE SERVER pgbouncer FOREIGN DATA WRAPPER dblink_fdw
OPTIONS (host 'localhost', port '6432', dbname 'pgbouncer');
For multiple PgBouncer instances:
CREATE SERVER pgbouncer1 FOREIGN DATA WRAPPER dblink_fdw
OPTIONS (host '192.168.1.10', port '6432', dbname 'pgbouncer');
CREATE SERVER pgbouncer2 FOREIGN DATA WRAPPER dblink_fdw
OPTIONS (host '192.168.1.11', port '6432', dbname 'pgbouncer');
INSERT INTO pgbouncer_fdw_targets (target_host) VALUES ('pgbouncer1'), ('pgbouncer2');
UPDATE pgbouncer_fdw_targets SET active = false WHERE target_host = 'pgbouncer';
Create User Mapping
CREATE USER MAPPING FOR PUBLIC SERVER pgbouncer
OPTIONS (user 'ccp_monitoring', password 'mypassword');
Available Views
| View | Description |
|---|---|
pgbouncer_clients | Client connection details |
pgbouncer_pools | Connection pool statistics |
pgbouncer_servers | Backend server status |
pgbouncer_stats | Statistics summary |
pgbouncer_databases | Database definitions |
pgbouncer_config | Configuration parameters |
pgbouncer_lists | Internal lists |
pgbouncer_dns_hosts | DNS host cache |
pgbouncer_dns_zones | DNS zone cache |
pgbouncer_sockets | Socket information |
pgbouncer_users | User configuration |
SELECT * FROM pgbouncer_pools;
SELECT * FROM pgbouncer_stats;
SELECT database, cl_active, cl_waiting, sv_active FROM pgbouncer_pools;
When monitoring multiple instances, each row includes a pgbouncer_target_host column identifying the source.
Command Functions
Administrative functions (require explicit GRANT EXECUTE):
SELECT pgbouncer_command_reload(); -- Reload configuration
SELECT pgbouncer_command_pause('mydb'); -- Pause a database
SELECT pgbouncer_command_resume('mydb'); -- Resume a database
SELECT pgbouncer_command_kill('mydb'); -- Kill connections
SELECT pgbouncer_command_disable('mydb'); -- Disable a database
SELECT pgbouncer_command_enable('mydb'); -- Enable a database
SELECT pgbouncer_command_reconnect('mydb'); -- Reconnect to backend
SELECT pgbouncer_command_set('key', 'value'); -- Set a parameter
SELECT pgbouncer_command_shutdown(); -- Shutdown PgBouncer
SELECT pgbouncer_command_suspend(); -- Suspend operations
SELECT pgbouncer_command_wait_close('mydb'); -- Wait for connections to close
Permissions
GRANT USAGE ON FOREIGN SERVER pgbouncer TO monitoring_user;
GRANT SELECT ON pgbouncer_pools TO monitoring_user;
GRANT SELECT ON pgbouncer_stats TO monitoring_user;
GRANT EXECUTE ON FUNCTION pgbouncer_command_reload() TO admin_user;
Feedback
Was this page helpful?
Thanks for the feedback! Please let us know how we can improve.
Sorry to hear that. Please let us know how we can improve.