PostgreSQL DBA 核心职责

日常运维职责

系统监控

DBA 的首要职责是确保数据库系统的稳定运行。日常监控应当覆盖以下方面：

bash

#!/bin/bash
# daily_monitoring.sh - 日常监控脚本示例

# PostgreSQL 服务状态
echo "=== PostgreSQL 服务状态 ==="
systemctl status postgresql --no-pager | head -10

# 连接数监控
echo "=== 连接数统计 ==="
psql -c "SELECT count(*) as total, 
    sum(case when state='active' then 1 else 0 end) as active,
    sum(case when state='idle' then 1 else 0 end) as idle
FROM pg_stat_activity;"

# 复制状态
echo "=== 复制状态 ==="
psql -c "SELECT application_name, client_addr, state, 
    pg_wal_lsn_diff(sent_lsn, write_lsn) as pending,
    pg_wal_lsn_diff(write_lsn, replay_lsn) as replay_lag
FROM pg_stat_replication;"

# 磁盘使用
echo "=== 磁盘使用 ==="
df -h /var/lib/postgresql

# 慢查询统计
echo "=== 今日慢查询 TOP 5 ==="
psql -c "SELECT query, calls, round(mean_time::numeric,2) as avg_ms
FROM pg_stat_statements 
ORDER BY mean_time DESC LIMIT 5;"

维护任务

DBA 应当建立定期维护任务清单：

任务	频率	负责人员	备注
VACUUM ANALYZE	每日	自动化	根据表膨胀情况调整
日志轮转	每日	自动化	logrotate
备份验证	每周	DBA	恢复测试
性能基线	每周	DBA	更新性能基准
磁盘空间检查	每日	自动化	超过 80% 告警
复制状态检查	每小时	自动化	延迟超过阈值告警
慢查询分析	每日	DBA	优化建议
安全审计	每月	DBA	权限检查

变更管理

数据库变更管理是 DBA 的重要职责：

markdown

## 数据库变更申请

### 变更信息
- 申请人：
- 申请时间：
- 变更类型：DDL/DML/配置/权限
- 影响范围：

### 变更内容
```sql
-- 变更前
SELECT * FROM orders LIMIT 1;

-- 变更后
ALTER TABLE orders ADD COLUMN new_field VARCHAR(100);

影响评估

执行时间预估：
锁类型：
对业务影响：

回滚方案

sql

ALTER TABLE orders DROP COLUMN new_field;

审批

DBA 审批：
业务负责人审批：


## 性能优化职责

### 性能诊断

DBA 应当能够快速诊断和解决性能问题：

```sql
-- 性能诊断查询集合

-- 1. 找出最耗时的查询
SELECT 
    substring(query, 1, 80) AS query_preview,
    calls,
    round(total_time::numeric, 2) AS total_ms,
    round(mean_time::numeric, 2) AS avg_ms,
    rows,
    round(100.0 * shared_blks_hit / NULLIF(shared_blks_hit + shared_blks_read, 0), 2) AS cache_hit
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 10;

-- 2. 找出扫描次数最多的表
SELECT
    schemaname,
    relname AS table_name,
    seq_scan,
    seq_tup_read,
    idx_scan,
    n_live_tup
FROM pg_stat_user_tables
ORDER BY seq_scan DESC
LIMIT 10;

-- 3. 找出未使用或低效的索引
SELECT
    schemaname,
    relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE idx_scan = 0
AND schemaname = 'public'
ORDER BY pg_relation_size(indexrelid) DESC;

-- 4. 锁等待分析
SELECT
    blocked_activity.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocked_activity.query AS blocked_query,
    blocking_activity.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocking_activity.query AS blocking_query
FROM pg_catalog.pg_locks blocked_locks
JOIN pg_catalog.pg_stat_activity blocked_activity 
    ON blocked_locks.pid = blocked_activity.pid
JOIN pg_catalog.pg_locks blocking_locks 
    ON blocked_locks.locktype = blocking_locks.locktype
JOIN pg_catalog.pg_stat_activity blocking_activity 
    ON blocking_locks.pid = blocking_activity.pid
WHERE NOT blocked_locks.granted;

性能报告

DBA 应当定期输出性能报告：

markdown

# PostgreSQL 性能周报

## 周报期间
2025-01-13 至 2025-01-19

## 一、数据库健康状况

### 1.1 可用性
- 服务运行时间：99.99%
- 故障次数：0
- 平均响应时间：< 50ms

### 1.2 连接统计
- 平均连接数：150
- 峰值连接数：280
- 最大连接限制：500

### 1.3 复制状态
- 复制延迟平均值：< 1MB
- 复制状态：正常

## 二、性能指标

### 2.1 TPS 统计
- 平均 TPS：1200
- 峰值 TPS：3500
- 读写比例：3:1

### 2.2 慢查询统计
- 慢查询数量（> 1s）：23
- 慢查询数量（> 10s）：2
- 最慢查询 TOP 3 已优化

### 2.3 缓冲区命中率
- 共享缓冲区命中率：99.2%
- 索引缓冲区命中率：99.8%

## 三、优化建议

### 3.1 本周优化完成
1. 优化订单查询，响应时间从 2s 降至 50ms
2. 新增 customer_id 索引
3. 清理未使用索引 3 个

### 3.2 待优化项
1. 部分历史表膨胀严重，建议 VACUUM FULL
2. 建议增加连接池配置
3. 建议拆分超大分区表

## 四、下周计划
1. 执行 VACUUM FULL 清理膨胀表
2. 上线 PgBouncer 连接池
3. 完成分区表改造评估

故障处理职责

故障响应

DBA 应当建立故障响应机制：

bash

#!/bin/bash
# incident_response.sh - 故障响应脚本

# 故障级别定义
# P0: 服务完全不可用
# P1: 严重性能下降，部分功能不可用
# P2: 一般问题，不影响核心功能
# P3: 轻微问题，优化建议

INCIDENT_LEVEL=$1
INCIDENT_DESC=$2

if [ -z "$INCIDENT_LEVEL" ] || [ -z "$INCIDENT_DESC" ]; then
    echo "Usage: $0 <level> <description>"
    echo "Example: $0 P1 '主从复制延迟超过 1GB'"
    exit 1
fi

echo "========== 故障响应 =========="
echo "故障级别: $INCIDENT_LEVEL"
echo "故障描述: $INCIDENT_DESC"
echo "响应时间: $(date '+%Y-%m-%d %H:%M:%S')"
echo "响应人员: $(whoami)"

# 收集诊断信息
echo ""
echo "收集诊断信息..."

# 收集 PostgreSQL 状态
psql -c "SELECT version();" > /tmp/incident_$(date +%Y%m%d_%H%M%S).txt
psql -c "SELECT * FROM pg_stat_activity;" >> /tmp/incident_*.txt

# 收集系统信息
vmstat 1 5 >> /tmp/incident_*.txt
iostat -x 1 5 >> /tmp/incident_*.txt

echo "诊断信息已保存到 /tmp/incident_*.txt"

# 发送告警
# ./send_alert.sh "$INCIDENT_LEVEL" "$INCIDENT_DESC"

故障复盘

故障处理完成后，DBA 应当组织复盘会议并输出故障报告：

markdown

# 故障复盘报告

## 故障信息
- 故障时间：2025-01-20 14:30
- 故障级别：P1
- 影响范围：订单系统写入延迟，15% 订单创建失败
- 恢复时间：14:45（15 分钟）
- 总计影响时长：15 分钟

## 故障经过
1. 14:30 监控告警：主从复制延迟超过 1GB
2. 14:32 DBA 响应，开始排查
3. 14:35 确认主库产生大量 WAL（批量数据导入）
4. 14:38 停止批量导入任务
5. 14:42 复制延迟开始下降
6. 14:45 复制延迟恢复正常

## 根因分析
根本原因：业务系统执行批量数据导入，产生大量 WAL，主从复制跟不上。

触发因素：
1. 批量导入未在业务低峰期执行
2. 批量导入前未通知 DBA
3. 未使用批量导入优化参数

## 改进措施

### 立即执行（本周完成）
1. 建立批量任务审批流程
2. 优化批量导入参数（COPY、并行）
3. 增加复制延迟告警阈值

### 短期改进（本月完成）
1. 实施批量任务时间段限制
2. 增加从库硬件资源
3. 优化 WAL 写入参数

### 长期改进（季度完成）
1. 评估异步转同步复制的可行性
2. 建立业务变更联动机制
3. 增加自动化限流机制

## 经验教训
1. 业务变更需要与 DBA 联动
2. 关键操作需要时间窗口管理
3. 监控告警阈值需要动态调整

安全管理职责

安全审计

DBA 应当定期进行安全审计：

sql

-- 安全审计查询

-- 1. 检查超级用户
SELECT usename FROM pg_user WHERE usesuper = true;

-- 2. 检查具有 CREATE ROLE 权限的用户
SELECT usename FROM pg_user WHERE rolcreaterole = true;

-- 3. 检查密码过期时间
SELECT usename, valuntil FROM pg_user 
WHERE valuntil IS NOT NULL AND valuntil < NOW();

-- 4. 检查未加密连接
SELECT client_addr, usename, database 
FROM pg_stat_activity 
WHERE ssl = false;

-- 5. 检查宽泛的访问权限
SELECT * FROM pg_hba.conf 
WHERE address = '0.0.0.0/0' OR address = '::/0';

安全加固

DBA 应当执行安全加固：

bash

# 安全加固清单

# 1. 配置 SSL/TLS
# postgresql.conf
ssl = on
ssl_cert_file = '/etc/ssl/certs/postgresql.crt'
ssl_key_file = '/etc/ssl/private/postgresql.key'

# 2. 配置密码策略
# postgresql.conf
password_encryption = scram-sha-256

# 3. 启用审计日志
# postgresql.conf
log_connections = on
log_disconnections = on
log_statement = 'ddl'
log_min_duration_statement = 1000

# 4. 配置网络访问控制
# pg_hba.conf
# TYPE  DATABASE  USER  ADDRESS        METHOD
host    all       all   10.0.0.0/8     scram-sha-256
host    all       all   192.168.0.0/16 scram-sha-256

# 5. 定期轮换密钥
# 建议每季度更换 SSL 证书
# 建议每年更换数据库用户密码

容量规划职责

容量监控

DBA 应当持续监控容量使用趋势：

sql

-- 容量监控查询

-- 1. 数据库增长趋势
SELECT
    DATE_TRUNC('week', stat_reset_time) AS week,
    pg_database_size('mydb') AS size_bytes
FROM pg_stat_database
WHERE datname = 'mydb'
ORDER BY week;

-- 2. 表空间使用趋势
SELECT
    schemaname,
    relname,
    pg_relation_size(relid) AS size_bytes,
    n_live_tup AS rows
FROM pg_stat_user_tables
ORDER BY pg_relation_size(relid) DESC
LIMIT 20;

-- 3. 索引增长趋势
SELECT
    schemaname,
    relname,
    indexrelname,
    pg_relation_size(indexrelid) AS size_bytes
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC
LIMIT 20;

-- 4. 预测未来容量
SELECT
    '表大小' AS metric,
    current_size,
    growth_rate,
    days_until_full,
    CONCAT('需要扩容') AS recommendation
FROM (
    SELECT
        pg_database_size('mydb') AS current_size,
        (pg_database_size('mydb') - 
            pg_database_size('mydb') - 1000000000) / 7 AS daily_growth  -- 假设
) t;

容量规划报告

markdown

# PostgreSQL 容量规划报告

## 报告期间
2025年1月

## 一、当前容量状态

### 1.1 存储容量
- 当前数据库大小：500GB
- 存储总容量：1TB
- 使用率：50%
- 月增长率：50GB

### 1.2 预计耗尽时间
- 按当前增长率：预计 10 个月后耗尽
- 预留缓冲：建议 6 个月后启动扩容

### 1.3 计算资源
- CPU 使用率均值：45%
- 内存使用率：60%
- 峰值连接数：280/500

## 二、扩容建议

### 2.1 短期建议（3 个月内）
1. 清理历史数据（归档 6 个月前的数据）
2. 优化表结构（去除冗余字段）
3. 增加监控频率

### 2.2 中期建议（6 个月内）
1. 存储扩容至 2TB
2. 评估分区表改造
3. 引入只读从库分散查询压力

### 2.3 长期建议（12 个月内）
1. 评估分布式数据库方案
2. 实施数据归档策略
3. 建立容量预警机制

## 三、行动计划
| 任务 | 负责人 | 完成时间 |
|------|--------|---------|
| 归档历史数据 | DBA | 2025-02-15 |
| 分区表改造评估 | DBA | 2025-03-01 |
| 存储扩容申请 | DBA | 2025-03-15 |

常见问题（FAQ）

Q: DBA 最重要的是什么技能？

A: DBA 核心技能包括：1) 故障排查能力（快速定位和解决问题）2) 性能优化能力（SQL 调优、参数调优）3) 安全管理能力（权限控制、审计合规）4) 自动化能力（脚本编写、工具使用）5) 沟通协调能力（与开发、业务沟通）。

Q: 如何衡量 DBA 的工作成效？

A: 衡量指标包括：1) 系统可用性（目标 99.99%）2) 故障恢复时间（目标 < 30 分钟）3) 性能改进效果（查询响应时间下降百分比）4) 自动化程度（人工操作减少比例）5) 安全合规性（审计通过率）。

Q: DBA 如何与开发团队协作？

A: 协作建议：1) 建立开发规范（SQL 编写规范、索引规范）2) 参与需求评审（评估技术方案）3) 代码审查（数据库相关代码）4) 培训分享（定期技术分享）5) 建立沟通渠道（即时通讯群组）。

PostgreSQL DBA 核心职责 ​

日常运维职责 ​

系统监控 ​

维护任务 ​

变更管理 ​

影响评估 ​

回滚方案 ​

审批 ​

性能报告 ​

故障处理职责 ​

故障响应 ​

故障复盘 ​

安全管理职责 ​

安全审计 ​

安全加固 ​

容量规划职责 ​

容量监控 ​

容量规划报告 ​

常见问题（FAQ） ​

PostgreSQL DBA 核心职责

日常运维职责

系统监控

维护任务

变更管理

影响评估

回滚方案

审批

性能报告

故障处理职责

故障响应

故障复盘

安全管理职责

安全审计

安全加固

容量规划职责

容量监控

容量规划报告

常见问题（FAQ）