pgbouncer_fdw

pig build pkg pgbouncer_fdw         # 构建 DEB 包

pig repo add pgsql -u          # 添加仓库并更新缓存

CREATE EXTENSION pgbouncer_fdw CASCADE;  -- 依赖: dblink

CREATE EXTENSION pgbouncer_fdw;

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

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 FOR PUBLIC SERVER pgbouncer
  OPTIONS (user 'ccp_monitoring', password 'mypassword');

SELECT * FROM pgbouncer_pools;
SELECT * FROM pgbouncer_stats;
SELECT database, cl_active, cl_waiting, sv_active FROM pgbouncer_pools;

SELECT pgbouncer_command_reload();              -- 重载配置
SELECT pgbouncer_command_pause('mydb');          -- 暂停数据库
SELECT pgbouncer_command_resume('mydb');         -- 恢复数据库
SELECT pgbouncer_command_kill('mydb');           -- 终止连接
SELECT pgbouncer_command_disable('mydb');        -- 禁用数据库
SELECT pgbouncer_command_enable('mydb');         -- 启用数据库
SELECT pgbouncer_command_reconnect('mydb');      -- 重新连接后端
SELECT pgbouncer_command_set('key', 'value');    -- 设置参数
SELECT pgbouncer_command_shutdown();             -- 关闭 PgBouncer
SELECT pgbouncer_command_suspend();              -- 暂停操作
SELECT pgbouncer_command_wait_close('mydb');     -- 等待连接关闭

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;