PostgreSQL 服务器信号函数

PostgreSQL 服务器信号函数

PostgreSQL 提供了一组服务器信号函数（Server Signaling Functions），允许数据库管理员向 PostgreSQL 服务器进程发送特定信号以控制服务器行为。这些函数提供了对数据库服务器的精细控制能力。

一、核心信号函数列表

二、函数详解与使用示例

1. 进程管理函数

取消长时间运行的查询：

-- 查找运行时间超过1小时的查询
SELECT pid, now() - query_start AS duration, query 
FROM pg_stat_activity 
WHERE state = 'active' AND now() - query_start > interval '1 hour';

-- 取消指定进程(pid=12345)的查询
SELECT pg_cancel_backend(12345);

强制终止问题进程：

-- 终止所有空闲事务
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity 
WHERE state = 'idle in transaction' 
AND now() - state_change > interval '10 minutes';

2. 配置管理函数

动态重载配置：

-- 修改postgresql.conf后无需重启
SELECT pg_reload_conf();

-- 验证配置是否已加载
SELECT name, setting, source, applied 
FROM pg_settings 
WHERE name = 'max_connections';

3. 日志管理函数

日志轮换：

-- 手动触发日志轮换
SELECT pg_rotate_logfile();

-- 验证日志文件(需要访问服务器文件系统)
\! ls -l $PGDATA/pg_log | tail -n 5

三、高级应用场景

1. 自动化查询终止

-- 创建函数自动终止长时间运行的查询
CREATE OR REPLACE FUNCTION terminate_long_queries(max_minutes int) RETURNS void AS $$
BEGIN
  PERFORM pg_terminate_backend(pid)
  FROM pg_stat_activity
  WHERE state = 'active'
  AND now() - query_start > (max_minutes * interval '1 minute')
  AND pid <> pg_backend_pid(); -- 不终止自己
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

-- 使用函数终止运行超过30分钟的查询
SELECT terminate_long_queries(30);

2. 安全连接终止

-- 终止特定用户的连接(谨慎使用)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'problem_user'
AND pid <> pg_backend_pid();

四、重要注意事项

1. 权限控制：

   • pg_terminate_backend() 仅限超级用户
   • pg_cancel_backend() 允许进程所有者执行

2. 影响范围：

   • 终止后端进程可能导致事务回滚
   • 取消查询可能使应用层出现错误

3. 替代方案：

   • 考虑使用 statement_timeout 参数限制查询执行时间
   • 使用连接池工具管理连接生命周期

4. 日志轮换：

   • 需要正确配置 logging_collector
   • 日志文件名格式由 log_filename 参数控制

五、相关系统视图

-- 查看活动进程
SELECT * FROM pg_stat_activity;

-- 查看锁信息
SELECT * FROM pg_locks;

-- 查看等待事件
SELECT * FROM pg_stat_activity WHERE wait_event_type IS NOT NULL;

PostgreSQL 的信号函数为数据库管理员提供了强大的服务器管理能力，合理使用这些函数可以实现精细化的数据库运维管理。