PostgreSQL数据库技术第 7 篇：数据库管理与维护

摘要

本文全面介绍PostgreSQL数据库的管理与维护工作。数据库管理是确保数据库系统稳定、高效、安全运行的关键环节。文章将系统讲解角色与权限管理、数据库连接配置、服务器参数优化、日常维护任务、备份恢复策略、监控与日志分析以及磁盘空间管理等核心内容。通过本文学习，读者将掌握PostgreSQL数据库的日常运维技能，能够制定和执行完整的维护计划，确保数据库系统的高可用性和数据安全性。

学习目标

掌握PostgreSQL角色体系结构和权限管理方法
理解并能够配置pg_hba.conf连接控制
了解postgresql.conf关键参数及其调优方法
掌握VACUUM、ANALYZE、REINDEX等日常维护操作
理解备份恢复策略，能够制定和执行备份计划
掌握时间点恢复（PITR）原理和实现方法
了解数据库监控工具和日志分析方法
掌握磁盘空间管理和清理策略

一、角色与权限管理

1.1 角色体系概述

PostgreSQL使用角色（Role）概念统一管理用户和组。角色可以拥有登录权限成为用户，也可以作为组来管理其他角色。这种统一的设计简化了权限管理的复杂性。

角色有几种基本类型：

登录角色（Login Role）：可以登录数据库的用户角色
组角色（Group Role）：用于权限管理，不能直接登录
超级用户（Superuser）：拥有所有权限，可以绕过所有访问检查
数据库所有者：对特定数据库拥有所有权限

1.2 角色创建与管理

-- 创建登录角色（用户）
CREATE USER app_user WITH PASSWORD 'secure_password';
CREATE ROLE admin WITH LOGIN PASSWORD 'admin_password' SUPERUSER;
 
-- 创建组角色
CREATE ROLE read_only;
CREATE ROLE developers;
 
-- 将成员添加到组
GRANT read_only TO app_user;
GRANT developers TO app_user;
 
-- 创建带过期期限的角色
CREATE ROLE temp_user WITH LOGIN PASSWORD 'temp_pass' VALID UNTIL '2024-12-31';
 
-- 创建带连接限制的角色
CREATE ROLE limited_user WITH LOGIN PASSWORD 'limit_pass'
    CONNECTION LIMIT 10;
 
-- 修改角色属性
ALTER ROLE app_user WITH PASSWORD 'new_password';
ALTER ROLE app_user VALID UNTIL 'infinity';
ALTER ROLE app_user CONNECTION LIMIT -1;  -- 无限制
 
-- 删除角色
DROP ROLE temp_user;
-- 如果角色拥有对象，需要使用CASCADE
DROP ROLE temp_user CASCADE;

1.3 权限管理

PostgreSQL的权限系统非常精细，涵盖了数据库、模式、表、列、函数等多个层次。

-- 数据库级别权限
GRANT ALL PRIVILEGES ON DATABASE mydb TO app_user;
GRANT CONNECT ON DATABASE mydb TO read_only;
GRANT TEMPORARY ON DATABASE mydb TO app_user;
REVOKE CONNECT ON DATABASE mydb FROM public;
 
-- 模式级别权限
GRANT ALL ON SCHEMA public TO app_user;
GRANT USAGE ON SCHEMA public TO read_only;
REVOKE CREATE ON SCHEMA public FROM public;
 
-- 表级别权限
GRANT SELECT ON TABLE products TO read_only;
GRANT SELECT, INSERT, UPDATE ON TABLE orders TO app_user;
GRANT ALL ON TABLE customers TO admin;
REVOKE DELETE ON TABLE products FROM app_user;
 
-- 列级别权限
GRANT SELECT(id, name) ON products TO read_only;
GRANT UPDATE(last_login) ON users TO app_user;
 
-- 函数权限
GRANT EXECUTE ON FUNCTION get_customer_orders(INTEGER) TO app_user;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO read_only;
 
-- 序列权限
GRANT USAGE, SELECT ON SEQUENCE orders_id_seq TO app_user;
 
-- 默认权限（适用于未来创建的对象）
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT SELECT ON TABLES TO read_only;
ALTER DEFAULT PRIVILEGES IN SCHEMA public
    GRANT USAGE ON SEQUENCES TO app_user;

1.4 权限层级与继承

flowchart TD
    A[PostgreSQL权限体系] --> B[数据库级别]
    A --> C[模式级别]
    A --> D[对象级别]

    B --> B1[CONNECT<br/>CREATE<br/>TEMPORARY<br/>ALL PRIVILEGES]

    C --> C1[USAGE<br/>CREATE<br/>ALL PRIVILEGES]

    D --> D1[表权限]
    D --> D2[序列权限]
    D --> D3[函数权限]
    D --> D4[其他对象]

    D1 --> D1A[SELECT<br/>INSERT<br/>UPDATE<br/>DELETE<br/>TRUNCATE<br/>TRIGGER<br/>REFERENCES]
    D2 --> D2A[USAGE<br/>SELECT<br/>UPDATE]
    D3 --> D3A[EXECUTE]

    B1 --> E[权限检查顺序]
    C1 --> E
    D1A --> E

    E --> F[1. 超级用户绕过检查]
    E --> G[2. 对象所有者拥有全部权限]
    E --> H[3. 检查授予的权限]
    E --> I[4. 检查角色继承的权限]

图表说明：此流程图展示了PostgreSQL的多层次权限体系。权限分为数据库级别、模式级别和对象级别三个层次。每个层次都有特定的权限类型，如数据库级别的CONNECT、CREATE、TEMPORARY，表级别的SELECT、INSERT、UPDATE等。权限检查遵循特定顺序：首先检查是否是超级用户（绕过所有检查），然后检查是否是对象所有者（拥有全部权限），接着检查直接授予的权限，最后检查通过角色继承获得的权限。这种多层次的权限设计提供了灵活而精细的访问控制能力。

二、数据库连接配置

2.1 pg_hba.conf文件详解

pg_hba.conf（Host-Based Authentication）文件控制PostgreSQL的客户端访问认证。该文件定义了哪些主机可以使用哪种认证方法连接到数据库。

配置文件格式：

# TYPE  DATABASE  USER  ADDRESS  METHOD  OPTIONS

# 连接类型 | 数据库名 | 用户名 | 客户端地址 | 认证方法 | 选项
local    all       all                     peer
host     all       all   127.0.0.1/32      scram-sha-256
host     all       all   ::1/128           scram-sha-256

2.2 认证方法类型

连接类型（TYPE）：

local：本地Unix域套接字连接
host：TCP/IP连接
hostssl：要求SSL/TLS的TCP/IP连接
hostnossl：禁止SSL/TLS的TCP/IP连接

认证方法（METHOD）：

认证方法	描述	安全性	适用场景
trust	无条件信任	低	开发环境
reject	拒绝连接	-	禁止特定连接
scram-sha-256	密码加密传输	高	推荐使用
md5	MD5加密	中	兼容旧客户端
password	明文密码	低	不推荐
peer	操作系统用户映射	高	本地连接
cert	SSL证书认证	最高	高安全要求
ldap	LDAP认证	高	企业环境
pam	PAM认证	高	集中认证

2.3 配置示例

# pg_hba.conf配置示例
 
# 允许本地连接使用peer认证
local   all             postgres                                peer
 
# 允许本地连接使用scram-sha-256
local   all             all                                     scram-sha-256
 
# 允许IPv4本地连接
host    all             all             127.0.0.1/32            scram-sha-256
 
# 允许IPv6本地连接
host    all             all             ::1/128                 scram-sha-256
 
# 允许特定IP段连接（生产环境）
host    production_db   app_user        192.168.1.0/24         scram-sha-256
 
# 要求SSL的远程连接
hostssl all             all             0.0.0.0/0               cert
 
# 拒绝特定用户连接
host    all             bad_user        0.0.0.0/0              reject
 
# LDAP认证（企业环境）
host    all             all             10.0.0.0/8             ldap ldapserver=ldap.example.com ldapbasedn="dc=example,dc=com"
 
# 修改配置后需要重载
pg_ctl reload -D /path/to/data

2.4 连接限制与资源控制

-- 配置最大连接数（需要修改postgresql.conf并重启）
max_connections = 200
 
-- 为特定用户设置连接限制
ALTER ROLE app_user CONNECTION LIMIT 5;
 
-- 为特定数据库设置连接限制
ALTER DATABASE production_db CONNECTION LIMIT 100;
 
-- 查看当前连接数
SELECT count(*) FROM pg_stat_activity;
 
-- 查看每个用户的连接数
SELECT usename, count(*)
FROM pg_stat_activity
GROUP BY usename
ORDER BY count(*) DESC;
 
-- 终止特定连接
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE usename = 'bad_user' AND pid != pg_backend_pid();
 
-- 终止特定数据库的所有连接
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'maintenance_db' AND pid != pg_backend_pid();

三、服务器参数优化

3.1 postgresql.conf关键参数

postgresql.conf是PostgreSQL的主配置文件，包含大量影响数据库行为和性能的参数。

内存相关参数：

# 共享缓冲区（最重要的参数）
shared_buffers = 4GB              # 通常设置为系统内存的25%
 
# 工作内存（每个排序/哈希操作）
work_mem = 64MB                   # 根据并发查询数调整
 
# 维护工作内存
maintenance_work_mem = 512MB      # 用于VACUUM、CREATE INDEX等
 
# 自动清理工作内存
autovacuum_work_mem = -1          # -1表示使用maintenance_work_mem
 
# 事务日志内存
wal_buffers = 16MB                # 通常为shared_buffers的3%左右

连接相关参数：

# 最大连接数
max_connections = 200
 
# 超级用户保留连接数
superuser_reserved_connections = 3
 
# TCP keepalives
tcp_keepalives_idle = 60          # 发送keepalive前空闲秒数
tcp_keepalives_interval = 10      # keepalive重试间隔
tcp_keepalives_count = 6          # keepalive重试次数

WAL相关参数：

# WAL级别
wal_level = replica               # minimal, replica, 或 logical
 
# WAL文件大小
min_wal_size = 1GB
max_wal_size = 4GB
 
# WAL写入方法
wal_sync_method = open_datasync   # fdatasync, open_sync, open_datasync
 
# 检查点
checkpoint_timeout = 15min
checkpoint_completion_target = 0.9
max_wal_size = 4GB
min_wal_size = 1GB
 
# 同步提交
synchronous_commit = on           # on, off, local, remote_write

查询规划器参数：

# 默认统计信息目标
default_statistics_target = 100   # 增加可改善规划质量
 
# 随机页面代价
random_page_cost = 1.1            # SSD可设为1.1
 
# 有效缓存大小
effective_cache_size = 12GB       # 通常为系统内存的50-75%
 
# 并行查询
max_parallel_workers_per_gather = 2
max_parallel_workers = 8
parallel_tuple_cost = 0.01
parallel_setup_cost = 1000.0
 
# 连接池
shared_preload_libraries = 'pg_stat_statements'

3.2 参数调优决策图

flowchart TD
    A[PostgreSQL参数调优] --> B{工作负载类型}
    B --> C[OLTP<br/>事务处理]
    B --> D[OLAP<br/>数据分析]
    B --> E[Web应用<br/>混合负载]

    C --> C1[优化目标<br/>• 快速事务<br/>• 高并发<br/>• 低延迟]

    D --> D1[优化目标<br/>• 批量处理<br/>• 复杂查询<br/>• 高吞吐]

    E --> E1[优化目标<br/>• 平衡性能<br/>• 响应时间<br/>• 资源利用]

    C1 --> F[关键参数<br/>shared_buffers: 25%<br/>work_mem: 较小<br/>random_page_cost: 1.1<br/>max_connections: 较高<br/>checkpoint: 频繁]

    D1 --> G[关键参数<br/>shared_buffers: 25%<br/>work_mem: 较大<br/>maintenance_work_mem: 大<br/>parallel_workers: 多<br/>max_connections: 较少]

    E1 --> H[关键参数<br/>shared_buffers: 25%<br/>work_mem: 中等<br/>effective_cache_size: 50%<br/>checkpoint: 适中<br/>synchronous_commit: 可调整]

    F --> I[监控验证<br/>• pg_stat_activity<br/>• pg_stat_statements<br/>• 慢查询日志]
    G --> I
    H --> I

图表说明：此决策图展示了如何根据不同的工作负载类型优化PostgreSQL参数。OLTP系统需要快速事务处理和高并发，应配置较小的work_mem（避免单个查询占用过多内存）和较高的max_connections。OLAP系统处理大量数据和复杂查询，应配置较大的work_mem和maintenance_work_mem，启用更多并行工作进程。Web应用需要平衡各种因素，参数设置较为适中。参数调整后需要通过监控工具验证效果，包括pg_stat_activity查看活动连接、pg_stat_statements分析SQL性能、慢查询日志识别问题查询。

四、日常维护任务

4.1 VACUUM机制

PostgreSQL的MVCC机制在更新或删除数据时，不会立即物理删除旧版本数据，而是将其标记为”死元组”。这些死元组占用磁盘空间并可能影响查询性能。VACUUM机制负责清理这些死元组。

-- 手动执行VACUUM
VACUUM;
 
-- VACUUM特定表
VACUUM products;
 
-- VACUUM ANALYZE（同时更新统计信息）
VACUUM ANALYZE products;
 
-- 完全VACUUM（需要排他锁，慎用）
VACUUM FULL products;
 
-- 查看表的膨胀情况
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS 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 toast_and_indexes
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

4.2 自动清理配置

PostgreSQL的autovacuum守护进程自动执行VACUUM和ANALYZE操作。

# postgresql.conf配置
 
# 启用自动清理
autovacuum = on
 
# 自动清理工作进程数
autovacuum_max_workers = 3
 
# 清理触发阈值（表死元组比例）
autovacuum_vacuum_threshold = 50        # 最小死元组数
autovacuum_vacuum_scale_factor = 0.2    # 死元组比例
 
# ANALYZE触发阈值
autovacuum_analyze_threshold = 50
autovacuum_analyze_scale_factor = 0.1
 
# 清理频率限制
autovacuum_naptime = 1min               # 检查间隔
autovacuum_vacuum_cost_delay = 10ms
autovacuum_vacuum_cost_limit = 200
 
# 表级别配置（覆盖全局设置）
ALTER TABLE products SET (
    autovacuum_vacuum_threshold = 100,
    autovacuum_vacuum_scale_factor = 0.1,
    autovacuum_analyze_threshold = 50,
    autovacuum_analyze_scale_factor = 0.05
);

4.3 ANALYZE统计信息

ANALYZE命令收集表内容的统计信息，查询规划器使用这些信息生成最优执行计划。

-- 更新所有表的统计信息
ANALYZE;
 
-- 更新特定表的统计信息
ANALYZE products;
 
-- 更新特定列的统计信息
ANALYZE products (name, price);
 
-- 查看统计信息
SELECT
    schemaname,
    tablename,
    attname,
    n_distinct,
    correlation,
    most_common_vals
FROM pg_stats
WHERE tablename = 'products';
 
-- 查看最后自动分析时间
SELECT
    relname,
    last_autovacuum,
    last_autoanalyze,
    autovacuum_count,
    autoanalyze_count
FROM pg_stat_user_tables;

4.4 REINDEX重建索引

索引可能因为更新、删除操作而膨胀，影响查询性能。REINDEX重建索引可以恢复其效率。

-- 重建特定索引
REINDEX INDEX idx_products_name;
 
-- 重建表的所有索引
REINDEX TABLE products;
 
-- 重建数据库的所有索引
REINDEX DATABASE mydb;
 
-- 并发重建索引（PostgreSQL 12+，不阻塞读写）
REINDEX INDEX CONCURRENTLY idx_products_name;
 
-- 查看索引膨胀情况
SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

五、备份与恢复

5.1 备份策略概述

PostgreSQL提供多种备份方法，各有优缺点：

备份类型	工具	优点	缺点	恢复速度
SQL转储	pg_dump	跨平台、可编辑、可选择性备份	慢、需要恢复时间	慢
文件系统备份	rsync/cp	快、简单	需要停机或一致性快照	快
连续归档	WAL归档	支持时间点恢复、最小数据丢失	复杂、需要更多存储	中等

5.2 SQL转储备份

# 转储单个数据库（自定义格式）
pg_dump -Fc -f mydb.backup mydb
 
# 转储单个数据库（目录格式，支持并行）
pg_dump -j 4 -Fd -f mydb_dir/ mydb
 
# 转储所有数据库
pg_dumpall -f all_databases.sql
 
# 只转储模式（不包含数据）
pg_dump -s -f schema.sql mydb
 
# 只转储数据（不包含模式）
pg_dump -a -f data.sql mydb
 
# 转储特定表
pg_dump -t products -t orders -f tables.sql mydb
 
# 排除特定表
pg_dump -T 'logs_*' -f backup.dump mydb
 
# 并行转储大数据库
pg_dump -j 8 -Fc -f large_db.backup large_db

5.3 SQL转储恢复

# 恢复自定义格式备份
pg_restore -d mydb_new mydb.backup
 
# 恢复时清理现有数据库
pg_restore -c -d mydb mydb.backup
 
# 并行恢复
pg_restore -j 8 -d mydb mydb.backup
 
# 只恢复特定表
pg_restore -t products -d mydb mydb.backup
 
# 恢复SQL转储文件
psql -d mydb < all_databases.sql
 
# 恢复前创建数据库
createdb mydb_new
pg_restore -d mydb_new mydb.backup

5.4 连续归档备份

连续归档（也称为PITR - Point-In-Time Recovery）结合文件系统备份和WAL归档，实现时间点恢复能力。

# postgresql.conf配置
wal_level = replica
archive_mode = on
archive_command = 'cp %p /backup/wal/%f'
max_wal_senders = 3
wal_keep_size = 1GB
 
# 基础备份（使用pg_basebackup）
pg_basebackup -D /backup/base -Ft -z -P
 
# 流式备份（压缩）
pg_basebackup -D - -Ft -z > backup.tar
 
# 增量备份配置
# 1. 完成基础备份
# 2. 连续归档WAL文件
# 3. 保留足够的历史WAL
 
# 恢复步骤
# 1. 停止数据库
pg_ctl stop -D /data
 
# 2. 清空数据目录
rm -rf /data/*
 
# 3. 恢复基础备份
tar -xzf backup.tar -C /data
 
# 4. 创建recovery.signal
touch /data/recovery.signal
 
# 5. 配置恢复参数
cat >> /data/postgresql.auto.conf <<EOF
restore_command = 'cp /backup/wal/%f %p'
recovery_target_time = '2024-03-01 10:00:00'
EOF
 
# 6. 启动数据库（开始恢复）
pg_ctl start -D /data

5.5 备份恢复流程图

sequenceDiagram
    autonumber
    participant Admin as 管理员
    participant Backup as 备份系统
    participant DB as PostgreSQL
    participant WAL as WAL归档

    Note over Admin,WAL: 日常备份阶段

    Admin->>Backup: 1. 执行基础备份<br/>pg_basebackup
    Backup->>DB: 2. 复制数据文件
    DB-->>Backup: 3. 基础备份完成<br/>(backup.tar)

    loop 连续归档
        DB->>WAL: 4. WAL填满<br/>archive_command触发
        WAL->>Backup: 5. 归档WAL文件<br/>/backup/wal/0000...
    end

    Note over Admin,WAL: 灾难恢复阶段

    Admin->>DB: 6. 检测到故障<br/>停止数据库
    Admin->>Backup: 7. 准备恢复
    Backup->>DB: 8. 恢复基础备份
    Backup->>DB: 9. 配置recovery.signal
    Backup->>DB: 10. 指定恢复目标<br/>recovery_target_time

    DB->>WAL: 11. 读取归档WAL
    WAL-->>DB: 12. 重放WAL记录

    DB->>DB: 13. 达到恢复目标
    DB-->>Admin: 14. 恢复完成<br/>数据库可用

图表说明：此序列图展示了PostgreSQL连续归档备份和恢复的完整流程。备份阶段包括执行基础备份（使用pg_basebackup）和连续归档WAL文件。恢复阶段包括停止数据库、恢复基础备份、配置恢复参数、重放WAL到指定时间点。这种备份策略提供了完整的时间点恢复能力，可以将数据库恢复到任意指定时间点的状态，最大限度减少数据丢失。

六、监控与日志分析

6.1 统计信息视图

PostgreSQL提供丰富的统计信息视图，用于监控数据库运行状态。

-- 数据库活动统计
SELECT
    datname,
    numbackends,
    xact_commit,
    xact_rollback,
    blks_read,
    blks_hit,
    tup_returned,
    tup_fetched,
    tup_inserted,
    tup_updated,
    tup_deleted
FROM pg_stat_database
WHERE datname = 'mydb';
 
-- 表访问统计
SELECT
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    idx_tup_fetch,
    n_tup_ins,
    n_tup_upd,
    n_tup_del,
    n_tup_hot_upd,
    n_live_tup,
    n_dead_tup,
    last_vacuum,
    last_autovacuum,
    last_analyze,
    last_autoanalyze
FROM pg_stat_user_tables
ORDER BY seq_scan + idx_scan DESC;
 
-- 索引使用统计
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY idx_scan DESC;
 
-- 查找未使用的索引
SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
    AND indexrelname NOT LIKE 'pg_toast%';

6.2 活动会话监控

-- 查看当前活动会话
SELECT
    pid,
    usename,
    application_name,
    client_addr,
    state,
    query_start,
    state_change,
    waiting,
    query
FROM pg_stat_activity
WHERE state != 'idle';
 
-- 查看长时间运行的查询
SELECT
    pid,
    now() - query_start AS duration,
    usename,
    query
FROM pg_stat_activity
WHERE state = 'active'
    AND now() - query_start > interval '5 minutes'
ORDER BY duration DESC;
 
-- 查看阻塞情况
SELECT
    blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_statement,
    blocking_activity.query AS blocking_statement
FROM pg_catalog.pg_locks blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks blocking_locks ON blocking_locks.locktype = blocked_locks.locktype
    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
WHERE NOT blocked_locks.granted;

6.3 慢查询日志

# postgresql.conf配置
 
# 启用慢查询日志
log_min_duration_statement = 1000    # 记录执行时间超过1秒的查询
 
# 日志格式
log_line_prefix = '%t [%p]: [%l-1] user=%u,db=%d,app=%a,client=%h '
log_timezone = 'UTC'
 
# 日志输出
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_rotation_age = 1d
log_rotation_size = 100MB
 
# 分析慢查询日志
pgBadger
pgFouine
pg_stat_statements扩展

6.4 pg_stat_statements扩展

-- 启用pg_stat_statements
-- 1. 修改postgresql.conf
shared_preload_libraries = 'pg_stat_statements'
 
-- 2. 重启数据库
pg_ctl restart -D /data
 
-- 3. 创建扩展
CREATE EXTENSION pg_stat_statements;
 
-- 查看SQL统计信息
SELECT
    calls,
    total_exec_time / 1000 AS total_seconds,
    mean_exec_time / 1000 AS avg_seconds,
    stddev_exec_time / 1000 AS stddev_seconds,
    rows,
    100.0 * shared_blks_hit /
        nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent,
    query
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 20;
 
-- 重置统计信息
SELECT pg_stat_statements_reset();
 
-- 查找I/O密集型查询
SELECT
    query,
    calls,
    shared_blks_read,
    shared_blks_written,
    local_blks_read,
    local_blks_written
FROM pg_stat_statements
WHERE shared_blks_read > 0 OR local_blks_read > 0
ORDER BY shared_blks_read DESC
LIMIT 10;

七、磁盘空间管理

7.1 空间使用分析

-- 数据库大小
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 indexes_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;
 
-- 最大表（包括TOAST）
SELECT
    nspname AS schema,
    relname AS table,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS size
FROM pg_class
JOIN pg_namespace ON pg_class.relnamespace = pg_namespace.oid
WHERE relkind = 'r'
    AND nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_total_relation_size(oid) DESC
LIMIT 20;
 
-- 索引大小
SELECT
    schemaname,
    tablename,
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS index_size
FROM pg_stat_user_indexes
ORDER BY pg_relation_size(indexrelid) DESC;

7.2 空间回收策略

-- 清理死元组并回收空间
VACUUM FULL products;      -- 重写表，回收空间（需要排他锁）
VACUUM products;           -- 普通清理，空间可重用但不归还OS
 
-- 清理并分析
VACUUM ANALYZE products;
 
-- 重建索引以回收空间
REINDEX TABLE products;
 
-- 清理特定schema
VACUUM ANALYZE;
 
-- 查看膨胀情况
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) AS total,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS table,
    (pg_total_relation_size(schemaname||'.'||tablename) - pg_relation_size(schemaname||'.'||tablename))::bigint /
        NULLIF(pg_relation_size(schemaname||'.'||tablename), 0) AS bloat_ratio
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY bloat_ratio DESC;

八、数据库管理知识总结

维护任务周期表

任务	频率	命令/工具	目的
VACUUM	自动	autovacuum	清理死元组
ANALYZE	自动	autovacuum	更新统计信息
全量备份	每日	pg_basebackup	灾难恢复
WAL归档	连续	archive_command	时间点恢复
索引重建	按需	REINDEX	优化索引性能
统计分析	每周	pg_stat_statements	性能优化
空间检查	每周	pg_*_size函数	容量规划
配置审查	每月	检查配置文件	性能调优

权限管理最佳实践

实践	说明	示例
最小权限原则	只授予必要的最小权限	只授予SELECT而非ALL
使用角色组	通过角色组管理权限	GRANT role TO user
定期审计	定期检查和清理权限	查询pg_user和pg_roles
使用REVOKE	明确拒绝不必要的权限	REVOKE DELETE ON table
监控特权用户	监控超级用户活动	审计日志
默认拒绝	从public回收默认权限	REVOKE ALL ON schema

九、常见问题解答

Q1：VACUUM和VACUUM FULL有什么区别？

答：普通VACUUM标记死元组为可重用，但不会将空间归还给操作系统，表大小不会减小。

VACUUM FULL重写整个表，将活元组紧凑排列，将空间归还给操作系统，表大小会明显减小。

但VACUUM FULL需要排他锁，阻塞所有访问，不适合生产环境频繁使用。对于大多数情况，普通VACUUM配合autovacuum已经足够。如果确实需要回收空间，可以考虑在维护窗口执行VACUUM FULL，或者使用pg_repack扩展进行在线表重建。

Q2：如何选择备份策略？

答：备份策略选择取决于数据量、可容忍的数据丢失程度和恢复时间目标（RTO）。

SQL转储适合小型数据库和跨平台迁移，但恢复时间较长。连续归档（PITR）适合大型数据库，提供时间点恢复能力，是最完整的备份方案。

对于关键业务系统，建议采用混合策略：每日基础备份（pg_basebackup）加上连续WAL归档，可以在较短时间内恢复到任意时间点。备份文件应存储在独立的存储系统上，最好使用异地备份防止单点故障。

Q3：autovacuum参数如何调优？

答：autovacuum调优需要平衡清理频率和系统负载。

默认设置适合大多数场景，但对于特定情况需要调整：高更新频率的表应降低触发阈值（减小autovacuum_vacuum_scale_factor），大表应增加autovacuum_vacuum_threshold避免积累过多死元组。

监控pg_stat_user_tables的last_autovacuum和autovacuum_count列，判断autovacuum是否正常工作。如果发现表经常膨胀，需要降低该表的autovacuum触发阈值。对于有大量批量操作的场景，可以在批量操作后手动执行VACUUM ANALYZE。

Q4：如何监控数据库性能？

答：PostgreSQL提供了多层监控机制。

1）系统视图：pg_stat_activity查看活动会话，pg_stat_database查看数据库统计，pg_stat_user_tables查看表访问统计；2）慢查询日志：设置log_min_duration_statement记录慢查询，使用pgBadger等工具分析；3）pg_stat_statements扩展：跟踪SQL执行统计，找出性能瓶颈；4）操作系统监控：使用top、vmstat、iostat等监控CPU、内存、磁盘I/O。

综合使用这些工具可以全面了解数据库性能状况，及时发现和解决问题。

Q5：数据库连接数设置多少合适？

答：连接数设置需要平衡并发需求和系统资源。

每个连接占用内存（工作内存、通信缓冲区等），过多连接会导致内存耗尽和上下文切换开销。

一般经验公式：max_connections = (总内存 - shared_buffers) / (work_mem * 2 + 其他开销)。对于Web应用，通常使用连接池（如PgBouncer）来减少实际数据库连接数。连接池维护少量持久连接，应用层的短连接复用这些持久连接，可以支持大量并发请求而不增加数据库连接压力。监控pg_stat_activity的连接使用情况，根据实际需求调整max_connections。

十、总结与下一篇预告

本文系统讲解了PostgreSQL数据库的管理与维护工作，包括角色与权限管理、连接配置、服务器参数调优、日常维护任务、备份恢复策略、监控分析和磁盘空间管理。这些内容构成了数据库管理员的核心技能体系，是确保数据库系统稳定、高效、安全运行的基础。

角色与权限管理提供了灵活而精细的访问控制机制。pg_hba.conf连接控制确保只有授权客户端可以访问数据库。服务器参数优化直接影响数据库性能表现。日常维护任务（VACUUM、ANALYZE、REINDEX）保持数据库健康状态。备份恢复策略是数据安全的最后一道防线。监控分析工具帮助及时发现和解决问题。

下一篇预告：《PostgreSQL数据库技术第 8 篇：高可用架构与复制技术》将深入讲解PostgreSQL的高可用解决方案，包括主从复制、流复制、逻辑复制、故障切换、高可用工具（Patroni、repmgr）、读写分离等内容，帮助读者构建高可用的PostgreSQL数据库集群。

本文作为PostgreSQL数据库技术系列的第七篇，专注于数据库管理与维护的实战技能，为最后一篇高可用架构的学习做好充分准备。

51学通信

探索

PostgreSQL数据库技术 第 7 篇：数据库管理与维护

PostgreSQL数据库技术 第 7 篇：数据库管理与维护

摘要

学习目标

一、角色与权限管理

1.1 角色体系概述

1.2 角色创建与管理

1.3 权限管理

1.4 权限层级与继承

二、数据库连接配置

2.1 pg_hba.conf文件详解

2.2 认证方法类型

2.3 配置示例

2.4 连接限制与资源控制

三、服务器参数优化

3.1 postgresql.conf关键参数

3.2 参数调优决策图

四、日常维护任务

4.1 VACUUM机制

4.2 自动清理配置

4.3 ANALYZE统计信息

4.4 REINDEX重建索引

五、备份与恢复

5.1 备份策略概述

5.2 SQL转储备份

5.3 SQL转储恢复

5.4 连续归档备份

5.5 备份恢复流程图

六、监控与日志分析

6.1 统计信息视图

6.2 活动会话监控

6.3 慢查询日志

6.4 pg_stat_statements扩展

七、磁盘空间管理

7.1 空间使用分析

7.2 空间回收策略

八、数据库管理知识总结

维护任务周期表

权限管理最佳实践

九、常见问题解答

Q1：VACUUM和VACUUM FULL有什么区别？

Q2：如何选择备份策略？

Q3：autovacuum参数如何调优？

Q4：如何监控数据库性能？

Q5：数据库连接数设置多少合适？

十、总结与下一篇预告

关系图谱

目录

PostgreSQL数据库技术第 7 篇：数据库管理与维护

PostgreSQL数据库技术第 7 篇：数据库管理与维护