PostgreSQL 磁盘空间不足

磁盘空间使用情况诊断

1. 查看磁盘空间使用情况

sql

-- 查看 PostgreSQL 数据目录大小（需要系统命令）
\! du -sh /var/lib/postgresql/15/main

-- 查看表空间大小
SELECT 
    spcname AS tablespace_name,
    pg_size_pretty(pg_tablespace_size(spcname)) AS size
FROM 
    pg_tablespace
ORDER BY 
    pg_tablespace_size(spcname) DESC;

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

-- 查看表大小（包括索引）
SELECT 
    schemaname,
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_relation_size(relid)) AS table_size,
    pg_size_pretty(pg_indexes_size(relid)) AS index_size
FROM 
    pg_stat_user_tables
ORDER BY 
    pg_total_relation_size(relid) DESC
LIMIT 20;

2. 查看 WAL 文件和日志大小

sql

-- 查看 WAL 目录大小（需要系统命令）
\! du -sh /var/lib/postgresql/15/main/pg_wal

-- 查看 WAL 保留大小配置
SHOW wal_keep_size;
SHOW max_wal_size;
SHOW min_wal_size;

-- 查看日志目录大小（需要系统命令）
\! du -sh /var/log/postgresql

3. 查看临时文件和空闲空间

sql

-- 查看临时文件使用情况
SELECT 
    datname,
    temp_files,
    temp_bytes,
    pg_size_pretty(temp_bytes) AS temp_size
FROM 
    pg_stat_database
ORDER BY 
    temp_bytes DESC;

-- 查看表的空闲空间
SELECT 
    schemaname,
    relname AS table_name,
    pg_size_pretty(pg_total_relation_size(relid)) AS total_size,
    pg_size_pretty(pg_table_size(relid)) AS table_size,
    pg_size_pretty(pg_total_relation_size(relid) - pg_table_size(relid)) AS unused_space
FROM 
    pg_stat_user_tables
ORDER BY 
    (pg_total_relation_size(relid) - pg_table_size(relid)) DESC
LIMIT 20;

磁盘空间不足处理方法

1. 清理无用数据

sql

-- 清理表的空闲空间（VACUUM FULL）
VACUUM FULL ANALYZE large_table;

-- 或者使用 pg_repack（在线清理，无需锁表）
-- 需要先安装 pg_repack 扩展
CREATE EXTENSION pg_repack;
SELECT pg_repack.repack_table('public.large_table');

-- 清理旧的 WAL 文件（如果归档有问题）
-- 注意：仅在归档正常配置但 WAL 文件堆积时使用
\! find /var/lib/postgresql/15/main/pg_wal -name "000000010000*" -mtime +7 -delete

-- 清理旧的日志文件
\! find /var/log/postgresql -name "postgresql-*.log" -mtime +30 -delete

2. 调整配置参数

sql

-- 调整 WAL 保留大小
ALTER SYSTEM SET wal_keep_size = '1GB';

-- 调整检查点参数，减少 WAL 生成
ALTER SYSTEM SET checkpoint_completion_target = '0.9';
ALTER SYSTEM SET max_wal_size = '2GB';
ALTER SYSTEM SET min_wal_size = '1GB';

-- 调整自动清理参数
ALTER SYSTEM SET autovacuum_vacuum_scale_factor = '0.05';
ALTER SYSTEM SET autovacuum_analyze_scale_factor = '0.02';

-- 重新加载配置
SELECT pg_reload_conf();

3. 扩展磁盘空间

sql

-- 添加新的表空间
-- 1. 先在操作系统级创建目录
\! mkdir -p /new-disk/postgresql/tablespace1
\! chown postgres:postgres /new-disk/postgresql/tablespace1
\! chmod 700 /new-disk/postgresql/tablespace1

-- 2. 在 PostgreSQL 中创建表空间
CREATE TABLESPACE tablespace1 LOCATION '/new-disk/postgresql/tablespace1';

-- 3. 将表移动到新表空间
ALTER TABLE large_table SET TABLESPACE tablespace1;

-- 4. 将索引移动到新表空间
ALTER INDEX idx_large_table_column SET TABLESPACE tablespace1;

4. 归档和分区表

sql

-- 创建分区表（按时间分区）
CREATE TABLE sales (
    id SERIAL PRIMARY KEY,
    sale_date DATE NOT NULL,
    amount NUMERIC(10,2)
)
PARTITION BY RANGE (sale_date);

-- 创建分区
CREATE TABLE sales_2023 PARTITION OF sales
    FOR VALUES FROM ('2023-01-01') TO ('2024-01-01');

CREATE TABLE sales_2024 PARTITION OF sales
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

-- 将历史数据归档到外部表
-- 需要安装 postgres_fdw 扩展
CREATE EXTENSION postgres_fdw;

-- 创建外部服务器
CREATE SERVER archive_server
    FOREIGN DATA WRAPPER postgres_fdw
    OPTIONS (host 'archive-db', port '5432', dbname 'archive_db');

-- 创建用户映射
CREATE USER MAPPING FOR current_user
    SERVER archive_server
    OPTIONS (user 'archive_user', password 'archive_password');

-- 创建外部表
CREATE FOREIGN TABLE archive_sales_2022 (
    id SERIAL PRIMARY KEY,
    sale_date DATE NOT NULL,
    amount NUMERIC(10,2)
) SERVER archive_server OPTIONS (table_name 'sales_2022');

-- 将旧数据迁移到外部表并删除
INSERT INTO archive_sales_2022 SELECT * FROM sales_2022;
DROP TABLE sales_2022;

磁盘空间监控与告警

1. 配置磁盘空间监控

sql

-- 创建磁盘空间监控函数
CREATE OR REPLACE FUNCTION check_disk_space()
RETURNS TABLE (tablespace_name TEXT, size TEXT, free_space TEXT, usage_percent NUMERIC)
AS $$
BEGIN
    RETURN QUERY
    SELECT 
        spcname,
        pg_size_pretty(pg_tablespace_size(spcname)) AS size,
        pg_size_pretty(
            CASE 
                WHEN spcname = 'pg_default' THEN 
                    (SELECT pg_size_pretty(pg_total_relation_size(oid)) FROM pg_database WHERE datname = current_database())
                ELSE 
                    pg_size_pretty(pg_tablespace_size(spcname))
            END
        ) AS free_space,
        ROUND(
            (pg_tablespace_size(spcname) * 100.0) / 
            (SELECT pg_size_pretty(pg_total_relation_size(oid)) FROM pg_database WHERE datname = current_database())::NUMERIC,
            2
        ) AS usage_percent
    FROM 
        pg_tablespace
    WHERE 
        spcname NOT LIKE 'pg_%';
END;
$$ LANGUAGE plpgsql;

-- 调用监控函数
SELECT * FROM check_disk_space();

2. 配置 Prometheus 告警

yaml

# 磁盘空间告警规则
- alert: PostgreSqlDiskSpaceLow
  expr: (pg_tablespace_size_bytes / 1024 / 1024 / 1024) > 100 and (pg_tablespace_size_bytes / pg_tablespace_size_bytes{tablespace="pg_default"}) > 0.8
  for: 5m
  labels:
    severity: warning
  annotations:
    summary: "PostgreSQL 表空间使用率过高"
    description: "表空间 {{ $labels.tablespace }} 使用率超过 80%，当前值: {{ $value | printf "%.2f" }}GB"

- alert: PostgreSqlDiskSpaceCritical
  expr: (pg_tablespace_size_bytes / 1024 / 1024 / 1024) > 100 and (pg_tablespace_size_bytes / pg_tablespace_size_bytes{tablespace="pg_default"}) > 0.95
  for: 2m
  labels:
    severity: critical
  annotations:
    summary: "PostgreSQL 表空间使用率严重过高"
    description: "表空间 {{ $labels.tablespace }} 使用率超过 95%，当前值: {{ $value | printf "%.2f" }}GB"

3. 配置系统级监控

bash

# 添加到 crontab，每天检查磁盘空间
0 0 * * * /usr/bin/df -h | grep /var/lib/postgresql | awk '{if($5+0 > 80) print "PostgreSQL 磁盘空间不足: "$5" used on "$6"" | mail -s "PostgreSQL 磁盘告警" admin@example.com}'

最佳实践

1. 定期维护

定期 VACUUM：启用自动清理（autovacuum），定期清理表的空闲空间
监控磁盘使用：设置磁盘空间监控和告警，提前发现问题
归档历史数据：将旧数据归档到外部存储或创建分区表
清理日志文件：定期清理 PostgreSQL 日志和 WAL 文件

2. 配置优化

合理设置 WAL 参数：根据磁盘空间调整 wal_keep_size、max_wal_size 等参数
启用压缩：对大型表启用压缩存储
使用外部表：将不常用的历史数据存储到外部表
分区表设计：对大表使用分区表，便于管理和归档

3. 磁盘管理

使用 LVM：使用逻辑卷管理，便于灵活扩展磁盘空间
监控 IO 性能：定期检查磁盘 IO 性能，避免 IO 瓶颈
使用 SSD：对于性能要求高的数据库，使用 SSD 存储
定期备份：确保备份策略合理，避免备份文件占用过多空间

常见问题（FAQ）

Q1：如何快速释放 PostgreSQL 磁盘空间？

A1：可以通过以下方法快速释放磁盘空间：

运行 VACUUM FULL 或 pg_repack 清理表的空闲空间
删除不再需要的大型表或索引
清理旧的 WAL 文件（如果归档配置正确）
清理临时文件和日志文件
将旧数据归档到外部存储

Q2：为什么 VACUUM 后磁盘空间没有释放？

A2：可能的原因包括：

VACUUM 只标记空闲空间，不释放给操作系统
需要使用 VACUUM FULL 或 pg_repack 才能释放空间
表上有未提交的事务或长时间运行的查询
索引碎片严重，需要重建索引

Q3：如何防止 PostgreSQL 磁盘空间不足？

A3：防止磁盘空间不足的方法：

配置合理的监控和告警
定期清理和维护数据库
合理设置 WAL 和日志参数
使用分区表和外部表管理数据
规划足够的磁盘空间，使用 LVM 便于扩展
定期检查和优化查询，减少临时文件使用

Q4：如何安全地扩展 PostgreSQL 磁盘空间？

A4：安全扩展磁盘空间的步骤：

在操作系统级扩展磁盘或添加新磁盘
使用 LVM 扩展逻辑卷（如果使用 LVM）
扩展文件系统
为 PostgreSQL 创建新的表空间
将大型表和索引移动到新表空间
验证数据完整性和性能

Q5：如何处理 WAL 文件堆积问题？

A5：处理 WAL 文件堆积的方法：

检查 WAL 归档配置是否正确
确保归档命令能够正常执行
检查 wal_keep_size 参数是否设置过大
清理旧的 WAL 文件（在确保归档完成后）
考虑使用流复制替代 WAL 归档

Q6：如何监控 PostgreSQL 磁盘 IO 性能？

A6：监控磁盘 IO 性能的方法：

使用 iostat 命令监控系统级 IO 性能
使用 pg_stat_io 视图监控 PostgreSQL IO 统计
使用 Prometheus + Grafana 可视化监控 IO 指标
监控 PostgreSQL 等待事件，识别 IO 瓶颈
定期进行 IO 性能测试，如使用 pg_test_fsync 工具

PostgreSQL 磁盘空间不足 ​

磁盘空间使用情况诊断 ​

1. 查看磁盘空间使用情况 ​

2. 查看 WAL 文件和日志大小 ​

3. 查看临时文件和空闲空间 ​

磁盘空间不足处理方法 ​

1. 清理无用数据 ​

2. 调整配置参数 ​

3. 扩展磁盘空间 ​

4. 归档和分区表 ​

磁盘空间监控与告警 ​

1. 配置磁盘空间监控 ​

2. 配置 Prometheus 告警 ​

3. 配置系统级监控 ​

最佳实践 ​

1. 定期维护 ​

2. 配置优化 ​

3. 磁盘管理 ​

常见问题（FAQ） ​

Q1：如何快速释放 PostgreSQL 磁盘空间？ ​

Q2：为什么 VACUUM 后磁盘空间没有释放？ ​

Q3：如何防止 PostgreSQL 磁盘空间不足？ ​

Q4：如何安全地扩展 PostgreSQL 磁盘空间？ ​

Q5：如何处理 WAL 文件堆积问题？ ​

Q6：如何监控 PostgreSQL 磁盘 IO 性能？ ​

PostgreSQL 磁盘空间不足

磁盘空间使用情况诊断

1. 查看磁盘空间使用情况

2. 查看 WAL 文件和日志大小

3. 查看临时文件和空闲空间

磁盘空间不足处理方法

1. 清理无用数据

2. 调整配置参数

3. 扩展磁盘空间

4. 归档和分区表

磁盘空间监控与告警

1. 配置磁盘空间监控

2. 配置 Prometheus 告警

3. 配置系统级监控

最佳实践

1. 定期维护

2. 配置优化

3. 磁盘管理

常见问题（FAQ）

Q1：如何快速释放 PostgreSQL 磁盘空间？

Q2：为什么 VACUUM 后磁盘空间没有释放？

Q3：如何防止 PostgreSQL 磁盘空间不足？

Q4：如何安全地扩展 PostgreSQL 磁盘空间？

Q5：如何处理 WAL 文件堆积问题？

Q6：如何监控 PostgreSQL 磁盘 IO 性能？