一、PostgreSQL日常运维概述

PostgreSQL作为企业级开源数据库，其日常应用涵盖了数据库的例行维护、性能监控、备份恢复以及高可用管理等多个方面。与一些商业数据库相比，PostgreSQL的维护量相对较低，但对这些任务的适当关注将有助于确保系统长期稳定高效运行。

日常运维的核心任务包括：

• 定期清理：通过VACUUM回收存储空间

• 统计信息更新：确保查询优化器获得准确的统计信息

• 备份管理：定期备份并验证恢复流程

• 日志监控：跟踪错误日志和慢查询

• 性能优化：识别并解决性能瓶颈

这些任务具有重复性特点，可以使用cron脚本或Windows任务计划程序轻松实现自动化，数据库管理员只需负责设置适当的脚本并检查执行结果。

二、数据库连接与基础管理

2.1 连接数据库

bash

# 基本连接语法
psql -U 用户名 -d 数据库名 -h 主机地址 -p 端口号

# 常用连接示例
psql -U postgres -d mydb -h localhost -p 5432

# 连接后执行SQL并退出
psql -U postgres -d mydb -c "SELECT * FROM users;"

# 退出psql
\q

2.2 数据库对象查看

sql

-- 查看所有数据库
\l

-- 切换数据库
\c database_name

-- 查看当前数据库所有表
\dt

-- 查看表结构
\d table_name

-- 查看所有模式
\dn

-- 查看所有角色/用户
\du

-- 查看当前连接信息
\conninfo

2.3 数据库管理操作

sql

-- 创建数据库
CREATE DATABASE mydb WITH ENCODING='UTF8' OWNER=myuser;

-- 删除数据库
DROP DATABASE IF EXISTS mydb;

-- 创建模式
CREATE SCHEMA myschema;

-- 删除模式（级联删除其下所有对象）
DROP SCHEMA myschema CASCADE;

三、用户与权限管理

3.1 用户管理

sql

-- 创建用户（可登录的角色）
CREATE USER app_user WITH PASSWORD 'StrongPassword123';

-- 创建角色（无登录权限）
CREATE ROLE read_only;

-- 创建超级用户
CREATE ROLE admin WITH SUPERUSER LOGIN PASSWORD 'admin123';

-- 修改用户密码
ALTER USER app_user WITH PASSWORD 'NewPassword456';

-- 修改用户权限属性
ALTER USER app_user WITH CREATEDB;    -- 允许创建数据库
ALTER USER app_user WITH NOCREATEDB;  -- 禁止创建数据库

-- 删除用户
DROP USER IF EXISTS app_user;

3.2 权限授予

sql

-- 授予数据库权限
GRANT CONNECT ON DATABASE mydb TO app_user;
GRANT CREATE ON DATABASE mydb TO app_user;

-- 授予模式权限
GRANT USAGE ON SCHEMA public TO app_user;
GRANT CREATE ON SCHEMA public TO app_user;

-- 授予表权限
GRANT SELECT, INSERT, UPDATE ON ALL TABLES IN SCHEMA public TO app_user;

-- 授予特定表的所有权限
GRANT ALL PRIVILEGES ON TABLE users TO app_user;

-- 设置默认权限（新创建的表自动授权）
ALTER DEFAULT PRIVILEGES IN SCHEMA public 
GRANT SELECT ON TABLES TO read_only;

3.3 权限查看与撤销

sql

-- 查看角色列表
SELECT rolname, rolsuper, rolcreatedb FROM pg_roles;

-- 查看用户权限
\du username

-- 撤销权限
REVOKE INSERT ON users FROM app_user;
REVOKE ALL ON DATABASE mydb FROM app_user;

四、日常维护任务

4.1 VACUUM与ANALYZE

PostgreSQL的MVCC机制会产生"死元组"，需要定期清理以回收存储空间并更新统计信息。

sql

-- 清理指定表并更新统计信息
VACUUM ANALYZE table_name;

-- 仅清理（不更新统计信息）
VACUUM table_name;

-- 仅更新统计信息
ANALYZE table_name;

-- 全库清理（生产环境建议使用autovacuum）
VACUUM;

-- 更激进的清理（获取排他锁，会阻塞读写）
VACUUM FULL table_name;

-- 查看表膨胀情况
SELECT schemaname, tablename, 
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
       n_dead_tup
FROM pg_stat_user_tables 
WHERE n_dead_tup > 1000;

重要提示：PostgreSQL默认开启了autovacuum，它会自动清理死元组。但建议监控其工作状态，并在大表批量更新后手动执行VACUUM。

4.2 统计信息维护

查询优化器依赖统计信息来生成高效的执行计划，定期执行ANALYZE至关重要。

sql

-- 查看统计信息最后更新时间
SELECT schemaname, tablename, last_analyze, last_autoanalyze
FROM pg_stat_user_tables;

-- 手动触发分析
ANALYZE VERBOSE table_name;

4.3 索引维护

sql

-- 重建索引（减少膨胀，提高查询效率）
REINDEX INDEX index_name;
REINDEX TABLE table_name;

-- 查看索引使用情况
SELECT schemaname, tablename, indexname, idx_scan, idx_tup_read, idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

-- 查找未使用的索引
SELECT schemaname, tablename, indexname, idx_scan
FROM pg_stat_user_indexes
WHERE idx_scan = 0;

五、监控与性能诊断

5.1 系统视图监控

PostgreSQL内置了多个系统视图用于性能监控：

sql

-- 1. 查看当前活动连接
SELECT pid, usename, application_name, client_addr, state, 
       query, query_start, state_change
FROM pg_stat_activity
WHERE state = 'active'
ORDER BY query_start;

-- 2. 查看长时间运行的查询
SELECT pid, usename, query, 
       EXTRACT(EPOCH FROM (NOW() - query_start)) AS duration_seconds
FROM pg_stat_activity
WHERE state = 'active' 
  AND query NOT LIKE '%pg_stat_activity%'
ORDER BY duration_seconds DESC
LIMIT 10;

-- 3. 查看数据库级别统计
SELECT datname, numbackends, xact_commit, xact_rollback, 
       blks_read, blks_hit, 
       ROUND(100.0 * blks_hit / NULLIF(blks_hit + blks_read, 0), 2) AS cache_hit_ratio
FROM pg_stat_database;

-- 4. 查看表级别的I/O统计
SELECT schemaname, tablename, 
       seq_scan, seq_tup_read,
       idx_scan, idx_tup_fetch,
       n_tup_ins, n_tup_upd, n_tup_del
FROM pg_stat_user_tables
ORDER BY seq_scan DESC
LIMIT 20;

5.2 pg_stat_statements扩展

此扩展是性能调优的核心工具，可统计SQL执行情况。

启用步骤：

sql

-- 1. 修改postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
pg_stat_statements.track = all
pg_stat_statements.max = 10000

-- 2. 重启数据库后创建扩展
CREATE EXTENSION pg_stat_statements;

-- 3. 查询慢SQL
SELECT query, calls, total_time, mean_time, rows
FROM pg_stat_statements
ORDER BY mean_time DESC
LIMIT 20;

5.3 EXPLAIN执行计划分析

sql

-- 查看执行计划（不实际执行）
EXPLAIN SELECT * FROM users WHERE age > 18;

-- 实际执行并显示详细耗时
EXPLAIN (ANALYZE, BUFFERS, TIMING) 
SELECT u.*, o.order_total 
FROM users u 
JOIN orders o ON u.id = o.user_id 
WHERE u.created_at > '2024-01-01';

5.4 数据库大小监控

sql

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

-- 查看当前数据库每个表的大小
SELECT schemaname, tablename,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total_size,
       pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table_size,
       pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename) - 
                      pg_relation_size(schemaname||'.'||tablename)) AS index_size
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 20;

5.5 关键日志配置

生产环境建议启用以下日志设置：

ini

# postgresql.conf 配置
log_statement = 'ddl'                              # 记录DDL操作
log_lock_waits = on                                 # 记录锁等待
log_temp_files = 0                                  # 记录临时文件使用
log_autovacuum_min_duration = 0                     # 记录autovacuum执行
log_min_duration_statement = 1000                   # 记录超过1秒的慢查询
logging_collector = on                              # 启用日志收集器
log_directory = 'pg_log'                            # 日志目录
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'    # 日志文件名格式

六、备份与恢复

6.1 逻辑备份

使用pg_dump和pg_dumpall进行逻辑备份：

bash

# 备份单个数据库（文本格式）
pg_dump -U postgres -d mydb -f mydb_backup.sql

# 备份单个数据库（自定义格式，支持压缩和并行恢复）
pg_dump -U postgres -d mydb -F c -f mydb_backup.dump

# 备份指定表
pg_dump -U postgres -d mydb -t users -t orders -f users_orders.sql

# 备份整个集群（所有数据库和角色）
pg_dumpall -U postgres -f full_cluster_backup.sql

# 备份并压缩
pg_dump -U postgres -d mydb | gzip > mydb_backup.sql.gz

# 压缩自定义格式备份
pg_dump -U postgres -d mydb -F c -Z 6 -f mydb_backup.dump

6.2 逻辑恢复

bash

# 恢复SQL文本格式备份
# 先创建空数据库
createdb -U postgres -T template0 mydb_new
# 执行恢复
psql -U postgres -d mydb_new -f mydb_backup.sql

# 恢复自定义格式备份
pg_restore -U postgres -d mydb_new -v mydb_backup.dump

# 恢复压缩备份
gunzip -c mydb_backup.sql.gz | psql -U postgres -d mydb_new

6.3 物理备份

bash

# 使用pg_basebackup进行物理备份
pg_basebackup -U postgres -D /backup/base_backup -F t -z -P

# 参数说明：
# -D：备份目标目录
# -F t：输出tar格式
# -z：压缩输出
# -P：显示进度

6.4 连续归档与PITR

启用WAL归档可实现时间点恢复（Point-In-Time Recovery）：

ini

# postgresql.conf 配置
wal_level = replica
archive_mode = on
archive_command = 'cp %p /backup/archive/%f'

恢复步骤：

bash

# 1. 停止数据库
sudo systemctl stop postgresql

# 2. 恢复基础备份
cp -r /backup/base_backup/* /var/lib/postgresql/14/main/

# 3. 创建恢复配置文件
cat > /var/lib/postgresql/14/main/recovery.conf << EOF
restore_command = 'cp /backup/archive/%f %p'
recovery_target_time = '2024-12-24 15:30:00'
recovery_target_timeline = 'latest'
EOF

# 4. 启动数据库
sudo systemctl start postgresql

6.5 自动化备份脚本

bash

#!/bin/bash
# PostgreSQL自动备份脚本

BACKUP_DIR="/backup/postgresql"
DATE=$(date +%Y%m%d_%H%M%S)
DB_USER="postgres"
DB_NAME="mydb"
RETENTION_DAYS=30

# 创建备份目录
mkdir -p $BACKUP_DIR

# 执行备份
pg_dump -U $DB_USER -d $DB_NAME -F c -f $BACKUP_DIR/${DB_NAME}_${DATE}.dump

# 验证备份
if [ $? -eq 0 ]; then
    echo "Backup successful: ${DB_NAME}_${DATE}.dump"
    # 删除过期备份
    find $BACKUP_DIR -name "${DB_NAME}_*.dump" -mtime +$RETENTION_DAYS -delete
else
    echo "Backup failed!"
    exit 1
fi