PostgreSQL 系统管理函数详解
PostgreSQL 提供了一系列强大的系统管理函数，用于数据库维护、监控和配置。这些函数可分为多个类别，以下是主要功能的详细说明：
一、数据库配置函数
1. 参数管理函数
-- 查看所有配置参数
SELECT name, setting, unit, context FROM pg_settings;
-- 动态修改参数（无需重启）
SELECT set_config('log_min_duration_statement', '1000', false);
-- 重置参数为默认值
SELECT reset_config('work_mem');
2. 配置文件操作
-- 重载配置文件（等效于pg_ctl reload）
SELECT pg_reload_conf();
-- 查看配置文件位置
SELECT pg_current_logfile(), pg_conf_load_time();
二、维护与清理函数
1. VACUUM 相关函数
-- 执行VACUUM（非FULL模式）
SELECT pg_stat_reset();
VACUUM (VERBOSE, ANALYZE) customers;
-- 仅收集统计信息（不清理死元组）
VACUUM (ANALYZE, SKIP_LOCKED) orders;
-- 查看需要VACUUM的表
SELECT schemaname, relname, n_dead_tup
FROM pg_stat_user_tables
WHERE n_dead_tup > 0
ORDER BY n_dead_tup DESC;
2. 冻结事务ID管理
-- 检查事务ID年龄
SELECT datname, age(datfrozenxid) FROM pg_database;
-- 手动推进冻结事务ID
VACUUM FREEZE customers;
-- 紧急冻结（避免事务ID回卷）
SELECT pg_emergency_freeze('my_table');
三、备份与恢复函数
1. 基础备份控制
-- 开始备份模式
SELECT pg_start_backup('nightly_backup', true, false);
-- 结束备份模式
SELECT pg_stop_backup(false, true);
-- 检查备份进度
SELECT * FROM pg_stat_progress_basebackup;
2. 时间点恢复(PITR)
-- 创建还原点
SELECT pg_create_restore_point('before_major_update');
-- 查看WAL信息
SELECT pg_current_wal_lsn(), pg_walfile_name_offset(pg_current_wal_lsn());
-- 强制切换WAL文件
SELECT pg_switch_wal();
四、监控与统计函数
1. 统计信息收集
-- 重置统计计数器
SELECT pg_stat_reset();
SELECT pg_stat_reset_shared('bgwriter');
-- 获取后台写入器统计
SELECT * FROM pg_stat_get_bgwriter_stats();
-- 用户自定义统计
SELECT pg_stat_get_activity(pg_backend_pid());
2. 会话管理
-- 查看活动会话
SELECT pid, usename, application_name, state
FROM pg_stat_activity;
-- 终止会话
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE application_name = 'problem_app';
-- 取消长时间运行的查询
SELECT pg_cancel_backend(pid)
FROM pg_stat_activity
WHERE state = 'active' AND now() - query_start > interval '5 minutes';
五、扩展与插件管理
1. 扩展操作函数
-- 创建扩展
SELECT pg_available_extensions();
CREATE EXTENSION pg_stat_statements;
-- 更新扩展
ALTER EXTENSION pg_partman UPDATE;
-- 查看扩展依赖
SELECT * FROM pg_extension_dependencies();
2. 插件控制
-- 加载共享库（需超级用户）
LOAD 'auto_explain';
-- 查看已加载库
SELECT * FROM pg_loaded_libraries();
六、存储与文件管理
1. 表空间操作
-- 创建表空间
SELECT pg_tablespace_location(oid), * FROM pg_tablespace;
-- 检查表空间使用情况
SELECT pg_tablespace_size('fast_ssd');
2. 大对象管理
-- 创建大对象
SELECT lo_create(0);
-- 导入文件为大对象
SELECT lo_import('/path/to/file.pdf');
-- 导出大对象到文件
SELECT lo_export(oid, '/path/to/output.pdf') FROM pg_largeobject_metadata;
七、复制与高可用
1. 流复制控制
-- 查看复制状态
SELECT * FROM pg_stat_replication;
-- 提升备库为主库
SELECT pg_promote(true, 60);
-- 创建物理复制槽
SELECT * FROM pg_create_physical_replication_slot('standby1_slot');
2. 逻辑复制
-- 创建逻辑复制槽
SELECT * FROM pg_create_logical_replication_slot(
'inventory_slot',
'pgoutput'
);
-- 解码WAL变更
SELECT * FROM pg_logical_slot_get_changes(
'inventory_slot',
NULL,
NULL
);
八、安全相关函数
1. 加密函数
-- 密码哈希
SELECT crypt('mypassword', gen_salt('bf', 8));
-- 数据加密
SELECT pgp_sym_encrypt('secret data', 'aes_key');
-- 查看密码哈希算法
SELECT * FROM pg_password_hash_algorithms();
2. 权限检查
-- 检查表权限
SELECT has_table_privilege('user1', 'customers', 'INSERT');
-- 查看角色权限
SELECT * FROM pg_roles WHERE rolname = 'admin';
-- 授予默认权限
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO readonly_role;
九、实用工具函数
1. 系统信息
-- 获取PostgreSQL版本
SELECT version();
-- 查看编译选项
SELECT pg_config();
-- 服务器启动时间
SELECT pg_postmaster_start_time();
2. 性能诊断
-- 查看锁等待
SELECT * FROM pg_lock_waits();
-- 获取当前执行的查询计划
SELECT pg_stat_get_backend_activity(pid)
FROM pg_stat_activity
WHERE state = 'active';
-- 生成随机数据
SELECT generate_series(1,100), md5(random()::text);
十、最佳实践示例
1. 自动化维护脚本
-- 每周维护脚本
DO $$
BEGIN
-- 分析所有用户表
EXECUTE format('ANALYZE %I',
string_agg(table_schema||'.'||table_name, ', '))
FROM information_schema.tables
WHERE table_schema NOT IN ('pg_catalog', 'information_schema');
-- 清理碎片严重的表
PERFORM vacuum_full_ratio(
schemaname,
relname,
0.3 -- 碎片率阈值
) FROM pg_stat_user_tables
WHERE n_dead_tup > 1000;
-- 记录维护日志
INSERT INTO maintenance_log
VALUES (now(), 'Weekly maintenance completed');
END $$;
2. 紧急情况处理
-- 事务ID回卷紧急处理
DO $$
DECLARE
db record;
BEGIN
FOR db IN SELECT datname FROM pg_database WHERE datname NOT IN ('template0', 'template1')
LOOP
EXECUTE format('VACUUM FREEZE %I', db.datname);
RAISE NOTICE 'Frozen database: %', db.datname;
END LOOP;
END $$;
PostgreSQL 的系统管理函数提供了对数据库各个方面的精细控制，合理使用这些函数可以显著简化管理工作。对于生产环境，建议：
- 将常用管理操作封装为PL/pgSQL函数
- 通过pgAgent或cron设置定期维护任务
- 在执行破坏性操作前创建还原点
- 结合监控系统使用统计函数进行性能分析