/* custom_monitor.c */
#include “postgres.h”
#include “fmgr.h”
#include “miscadmin.h”
#include “postmaster/bgworker.h”
#include “storage/ipc.h”
#include “storage/latch.h”
#include “utils/guc.h”
#include “utils/ps_status.h”

PG_MODULE_MAGIC;

static bool got_sigterm = false;

/* 信号处理函数 */
static void handle_sigterm(SIGNAL_ARGS)
{
int save_errno = errno;
got_sigterm = true;
if (MyLatch)
SetLatch(MyLatch);
errno = save_errno;
}

/* 执行监控任务 */
static void perform_monitoring(void)
{
PGconn *conn;
PGresult *res;

/* 连接到数据库 */
conn = PQconnectdb(“fgedudb=fgedudb fgedu=fgedu”);
if (PQstatus(conn) != CONNECTION_OK)
{
elog(WARNING, “Connection to fgedudb failed: %s”, PQerrorMessage(conn));
PQfinish(conn);
return;
}

/* 监控连接数 */
res = PQexec(conn, “SELECT count(*) FROM pg_stat_activity”);
if (PQresultStatus(res) == PGRES_TUPLES_OK)
{
int count = atoi(PQgetvalue(res, 0, 0));
elog(INFO, “Current connections: %d”, count);
}
PQclear(res);

/* 监控表空间使用情况 */
res = PQexec(conn, “SELECT spcname, pg_size_pretty(pg_fgedutbs_size(oid)) FROM pg_fgedutbs”);
if (PQresultStatus(res) == PGRES_TUPLES_OK)
{
int nrows = PQntuples(res);
for (int i = 0; i < nrows; i++) { char *spcname = PQgetvalue(res, i, 0); char *size = PQgetvalue(res, i, 1); elog(INFO, "Tablespace %s size: %s", spcname, size); } } PQclear(res); /* 关闭连接 */ PQfinish(conn); } /* 清理资源 */ static void cleanup_resources(void) { /* 清理代码 */ } /* 后台工作进程入口函数 */ void custom_monitor_worker_main(Datum main_arg) { /* 初始化工作进程 */ BackgroundWorkerInitializeConnection("fgedudb", "fgedu", 0); /* 设置进程标题 */ ps_status_set("custom monitor worker"); /* 注册信号处理 */ pqsignal(SIGTERM, handle_sigterm); pqsignal(SIGINT, handle_sigterm); /* 主循环 */ while (!got_sigterm) { /* 执行监控任务 */ perform_monitoring(); /* 睡眠一段时间 */ pg_usleep(10000000); /* 10秒 */ } /* 清理资源 */ cleanup_resources(); } /* 模块初始化函数 */ void _PG_init(void) { BackgroundWorker worker; /* 初始化工作进程结构 */ memset(&worker, 0, sizeof(worker)); worker.bgw_name = "Custom Monitor Worker"; worker.bgw_type = "monitor"; worker.bgw_flags = BGWORKER_SHMEM_ACCESS | BGWORKER_BACKEND_DATABASE_CONNECTION; worker.bgw_start_time = BgWorkerStart_RecoveryFinished; worker.bgw_restart_time = BGW_NEVER_RESTART; worker.bgw_main = custom_monitor_worker_main; worker.bgw_main_arg = 0; worker.bgw_notify_pid = 0; /* 注册工作进程 */ RegisterBackgroundWorker(&worker); }

#!/bin/bash
# monitor_bgworker.sh
# from:www.itpux.com.qq113257174.wx:itpux-com
# from:www.itpux.com.qq113257174.wx:itpux-com
# from:www.itpux.com.qq113257174.wx:itpux-com
# from:www.itpux.com.qq113257174.wx:itpux-com
# web: `http://www.fgedu.net.cn`
# web: `http://www.fgedu.net.cn`
# web: `http://www.fgedu.net.cn`
# web: `http://www.fgedu.net.cn`

# 检查后台工作进程状态
check_bgworker() {
echo “Checking background worker status…”
ps aux | grep “custom monitor worker” | grep -v grep

if [ $? -eq 0 ]; then
echo “Custom monitor worker is running.”
else
echo “Custom monitor worker is not running.”
return 1
fi
}

# 检查日志
check_logs() {
echo “Checking logs…”
tail -n 50 /var/log/postgresql/postgresql-18-main.log | grep “Custom Monitor Worker”
}

# 主函数
main() {
echo “PostgreSQL Background Worker Monitor”
echo “==================================”

check_bgworker
echo “”
check_logs
echo “”
echo “Monitor completed.”
}

# 执行主函数
main

# 1. 安装Prometheus和Grafana
$ sudo apt-get install prometheus grafana

# 2. 配置Prometheus
$ cat > /etc/prometheus/prometheus.yml << EOF global: scrape_interval: 15s scrape_configs: - job_name: 'postgresql' static_configs: - targets: ['localfgedu.net.cn:9187'] EOF # 3. 安装PostgreSQL exporter $ wget https://github.com/prometheus-community/postgres_exporter/releases/download/v0.15.0/postgres_exporter-0.15.0.linux-amd64.tar.gz $ tar -xzf postgres_exporter-0.15.0.linux-amd64.tar.gz $ sudo mv postgres_exporter-0.15.0.linux-amd64/postgres_exporter /usr/local/bin/ # 4. 创建服务文件 $ cat > /etc/systemd/system/postgres_exporter.service << EOF [Unit] Description=PostgreSQL Exporter After=network.target [Service] User=pgsql Environment=DATA_SOURCE_NAME=postgresql://fgedu@localfgedu.net.cn:5432/fgedudb?sslmode=disable ExecStart=/usr/local/bin/postgres_exporter [Install] WantedBy=multi-fgedu.target EOF # 5. 启动服务 $ sudo systemctl daemon-reload $ sudo systemctl start postgres_exporter $ sudo systemctl enable postgres_exporter # 6. 配置Grafana # 访问 http://localfgedu.net.cn:3000，添加Prometheus数据源，导入PostgreSQL dashboard