PostgreSQL数据库日常运维必备——从安装到健康检查

1. 引言

PostgreSQL作为功能强大的开源关系型数据库，在企业级应用中越来越广泛。本文总结了日常运维中最常用的操作和检查方法。

2. 安装与基础配置

CentOS/RHEL安装示例：

# 安装官方仓库
yum install -y https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm

# 安装PostgreSQL 15
yum install -y postgresql15-server postgresql15-contrib

# 初始化数据库
/usr/pgsql-15/bin/postgresql-15-setup initdb

# 启动服务
systemctl enable postgresql-15
systemctl start postgresql-15

关键配置参数（postgresql.conf）：

# 连接设置
listen_addresses = '*'          # 监听所有接口
max_connections = 200           # 最大连接数

# 内存设置（假设服务器32GB内存）
shared_buffers = 8GB            # 推荐物理内存25%
effective_cache_size = 24GB     # 推荐物理内存75%
work_mem = 64MB                 # 排序/哈希操作内存
maintenance_work_mem = 1GB      # VACUUM/CREATE INDEX内存

# 写入性能
wal_buffers = 16MB
checkpoint_completion_target = 0.9

3. 连接管理

-- 查看当前连接数
SELECT count(*) FROM pg_stat_activity;

-- 查看连接详情（含执行语句）
SELECT pid, usename, application_name, client_addr, state, 
       now() - query_start AS duration, query
FROM pg_stat_activity 
WHERE state != 'idle' 
ORDER BY duration DESC;

-- 终止异常连接
SELECT pg_terminate_backend(pid) 
FROM pg_stat_activity 
WHERE state = 'idle' AND state_change < now() - interval '1 hour';

4. 日常健康检查清单

4.1 数据库状态检查

-- 检查数据库大小
SELECT datname, 
       pg_size_pretty(pg_database_size(datname)) AS size
FROM pg_database 
ORDER BY pg_database_size(datname) DESC;

-- 检查表大小Top10
SELECT schemaname, tablename, 
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size
FROM pg_tables 
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC 
LIMIT 10;

4.2 死锁检测

SELECT pid, locktype, mode, granted, 
       relation::regclass AS locked_table
FROM pg_locks 
WHERE NOT granted;

4.3 长事务监控

SELECT pid, usename, xact_start, query_start, 
       now() - xact_start AS tx_duration, state, query
FROM pg_stat_activity 
WHERE xact_start IS NOT NULL 
  AND now() - xact_start > interval '5 minutes'
ORDER BY xact_start;

5. VACUUM与ANALYZE策略

-- 查看表膨胀率
SELECT schemaname, tablename, n_dead_tup, n_live_tup,
       round(100 * n_dead_tup / (n_live_tup + n_dead_tup), 2) AS dead_ratio
FROM pg_stat_user_tables 
WHERE n_dead_tup > 1000
ORDER BY dead_ratio DESC;

-- 手动执行VACUUM
VACUUM (VERBOSE, ANALYZE) your_table;

-- 全表VACUUM（维护窗口执行）
VACUUM FULL VERBOSE;

6. 备份与恢复

物理备份（pg_basebackup）：

# 全量备份
pg_basebackup -D /backup/base_$(date +%Y%m%d) -Ft -X fetch -P -U repl_user

# 启用归档（postgresql.conf）
archive_mode = on
archive_command = 'cp %p /archive/%f'

逻辑备份：

# 单库备份
pg_dump -U postgres mydb > /backup/mydb_$(date +%Y%m%d).sql

# 压缩并行备份
pg_dump -U postgres -Fd -j 4 -f /backup/mydb_dump mydb

# 恢复
psql -U postgres -d mydb < /backup/mydb.sql

7. 日常巡检脚本

#!/bin/bash
# daily_check.sh

PGPASSWORD='yourpass' psql -U postgres -h localhost -d postgres <<EOF
\timing off
\echo '=== 数据库状态 ==='
SELECT datname, pg_size_pretty(pg_database_size(datname)) FROM pg_database;

\echo '=== 连接数 ==='
SELECT count(*) FROM pg_stat_activity;

\echo '=== 未使用的索引 ==='
SELECT schemaname, indexname 
FROM pg_stat_user_indexes 
WHERE idx_scan = 0;

\echo '=== 长时间运行的查询 ==='
SELECT pid, now()-query_start AS duration, query 
FROM pg_stat_activity 
WHERE state='active' AND now()-query_start > interval '1 min';
EOF