PostgreSQL 磁盘使用监控 💾

概述

在 PostgreSQL 数据库管理中，磁盘使用监控是确保系统稳定运行的关键任务。随着业务数据的持续增长，了解如何准确监控和管理磁盘空间使用情况，对于预防系统故障和优化性能至关重要。

磁盘空间不足可能导致数据库服务停止，WAL 文件磁盘满甚至会引发数据库恐慌关机。

PostgreSQL 存储架构概述

PostgreSQL 采用多文件存储架构，每个数据库对象都有其对应的物理文件：

存储文件类型详解

文件类型	作用	存储内容	文件大小限制
主堆文件	存储表的主要数据	行数据、页面头部信息	单文件最大 1GB
TOAST 文件	存储大字段数据	超过页面大小的列值	单文件最大 1GB
索引文件	提供快速数据访问	B-Tree、Hash 等索引结构	单文件最大 1GB
TOAST 索引	TOAST 表的索引	TOAST 数据的索引信息	单文件最大 1GB

磁盘使用监控方法

PostgreSQL 提供三种监控磁盘空间的方法：

方法对比

监控方法	优点	缺点	适用场景
SQL 函数	简单易用、实时准确	需要数据库连接	日常监控、自动化脚本
oid2name 模块	提供文件名映射	需要安装额外模块	文件系统级别分析
系统目录查询	详细信息、灵活定制	查询复杂	深度分析、故障排查

实际监控示例

1. 基础表空间监控

业务场景：电商系统中需要监控订单表的磁盘使用情况，预防存储空间不足。

表基本信息查询详细空间分析

sql

-- 查询指定表的磁盘使用情况
-- 返回文件路径和页面数量
SELECT
    pg_relation_filepath(oid) AS file_path,  -- 文件路径
    relpages,                                -- 页面数量
    relpages * 8 / 1024 AS size_mb          -- 计算大小(MB)
FROM pg_class
WHERE relname = 'orders';

sql

-- 更详细的表空间分析
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_indexes_size(schemaname||'.'||tablename)) AS indexes_size
FROM pg_tables
WHERE tablename = 'orders';

预期输出：

 file_path        | relpages | size_mb
------------------+----------+---------
 base/16384/16806 |       60 |      0
(1 row)

 schemaname | tablename | total_size | table_size | indexes_size
------------+-----------+------------+------------+--------------
 public     | orders    | 2048 kB    | 1536 kB    | 512 kB
(1 row)

分析过程：

pg_relation_filepath() 返回表文件的相对路径
relpages 显示表占用的 8KB 页面数量
通过计算 relpages * 8 / 1024 得到以 MB 为单位的大小
pg_total_relation_size() 包含表、索引和 TOAST 的总大小

2. TOAST 表空间监控

业务场景：用户表包含大字段（如个人简介、头像等），需要监控 TOAST 表的空间使用。

sql

-- 查询 TOAST 表的磁盘使用情况
SELECT
    relname,
    relpages,
    pg_size_pretty(relpages * 8 * 1024) AS size_formatted
FROM pg_class,
     (SELECT reltoastrelid
      FROM pg_class
      WHERE relname = 'user_profiles') AS ss
WHERE oid = ss.reltoastrelid OR
      oid = (SELECT indexrelid
             FROM pg_index
             WHERE indrelid = ss.reltoastrelid)
ORDER BY relname;

预期输出：

       relname        | relpages | size_formatted
----------------------+----------+----------------
 pg_toast_16806       |      156 |        1248 kB
 pg_toast_16806_index |       23 |         184 kB
(2 rows)

业务意义：

TOAST 表存储大字段数据，如用户头像、长文本描述
TOAST 索引确保大字段数据的快速访问
监控 TOAST 表有助于优化大字段存储策略

3. 索引空间监控

业务场景：商品搜索系统需要监控各种索引的空间占用，优化索引策略。

sql

-- 查询指定表的所有索引大小
SELECT
    c2.relname AS index_name,
    c2.relpages,
    pg_size_pretty(c2.relpages * 8 * 1024) AS index_size,
    pg_size_pretty(pg_relation_size(c2.oid)) AS actual_size
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'products' AND
      c.oid = i.indrelid AND
      c2.oid = i.indexrelid
ORDER BY c2.relpages DESC;

预期输出：

      index_name       | relpages | index_size | actual_size
-----------------------+----------+------------+-------------
 products_search_idx   |      245 |    1960 kB |     2008 kB
 products_category_idx |       89 |     712 kB |      728 kB
 products_pkey         |       34 |     272 kB |      280 kB
(3 rows)

4. 全库最大表监控

业务场景：数据库管理员需要定期识别占用空间最大的表，进行容量规划。

sql

-- 查找数据库中最大的表和索引
SELECT
    relname,
    relkind,
    relpages,
    pg_size_pretty(relpages * 8 * 1024) AS size_formatted,
    CASE relkind
        WHEN 'r' THEN '普通表'
        WHEN 'i' THEN '索引'
        WHEN 't' THEN 'TOAST表'
        ELSE '其他'
    END AS object_type
FROM pg_class
WHERE relpages > 0
ORDER BY relpages DESC
LIMIT 10;

预期输出：

       relname        | relkind | relpages | size_formatted | object_type
----------------------+---------+----------+----------------+-------------
 order_history        |    r    |     3290 |      25 MB     | 普通表
 user_activity_log    |    r    |     3144 |      24 MB     | 普通表
 product_search_idx   |    i    |     1205 |       9 MB     | 索引
 order_items          |    r    |      892 |       7 MB     | 普通表
(4 rows)

高级监控查询

综合空间分析

业务场景：制作数据库空间使用报告，需要获取完整的空间分析数据。

sql

-- 数据库空间使用综合分析
WITH table_sizes AS (
    SELECT
        schemaname,
        tablename,
        pg_total_relation_size(schemaname||'.'||tablename) as total_bytes,
        pg_relation_size(schemaname||'.'||tablename) as table_bytes,
        pg_indexes_size(schemaname||'.'||tablename) as index_bytes
    FROM pg_tables
    WHERE schemaname NOT IN ('information_schema', 'pg_catalog')
),
formatted_sizes AS (
    SELECT
        schemaname,
        tablename,
        pg_size_pretty(total_bytes) as total_size,
        pg_size_pretty(table_bytes) as table_size,
        pg_size_pretty(index_bytes) as index_size,
        round(100.0 * total_bytes / sum(total_bytes) OVER(), 2) as pct_of_db
    FROM table_sizes
)
SELECT *
FROM formatted_sizes
ORDER BY total_bytes DESC;

空间增长趋势监控

sql

-- 创建监控表记录历史数据
CREATE TABLE IF NOT EXISTS disk_usage_history (
    recorded_at timestamp DEFAULT now(),
    schema_name text,
    table_name text,
    total_size_bytes bigint,
    table_size_bytes bigint,
    index_size_bytes bigint
);

-- 定期插入监控数据的存储过程
CREATE OR REPLACE FUNCTION record_disk_usage()
RETURNS void AS $$
BEGIN
    INSERT INTO disk_usage_history (schema_name, table_name, total_size_bytes, table_size_bytes, index_size_bytes)
    SELECT
        schemaname,
        tablename,
        pg_total_relation_size(schemaname||'.'||tablename),
        pg_relation_size(schemaname||'.'||tablename),
        pg_indexes_size(schemaname||'.'||tablename)
    FROM pg_tables
    WHERE schemaname NOT IN ('information_schema', 'pg_catalog');
END;
$$ LANGUAGE plpgsql;

磁盘空间不足的故障处理

故障影响分析

紧急处理步骤

WARNING

当磁盘空间不足时，需要立即采取行动以避免数据库服务中断。

步骤 1：立即评估空间情况

bash

# 检查磁盘使用情况
df -h /var/lib/postgresql/

# 检查数据库目录大小
du -sh /var/lib/postgresql/data/

# 检查最大的文件
find /var/lib/postgresql/data/ -type f -size +100M -exec ls -lh {} \;

步骤 2：快速释放空间

sql

-- 查找可以清理的临时文件和日志
SELECT
    pg_ls_dir('pg_stat_tmp') as temp_files;

-- 手动执行vacuum释放空间
VACUUM FULL pg_catalog.pg_stat_activity;

-- 清理过期的归档日志（谨慎操作）
SELECT pg_switch_wal();

步骤 3：使用表空间迁移

sql

-- 创建新表空间
CREATE TABLESPACE new_tablespace
LOCATION '/mnt/additional_storage';

-- 移动大表到新表空间
ALTER TABLE large_table SET TABLESPACE new_tablespace;

-- 移动索引到新表空间
ALTER INDEX large_table_idx SET TABLESPACE new_tablespace;

预防性监控脚本

业务场景：设置自动化监控脚本，在磁盘使用率达到阈值时发送告警。

bash

#!/bin/bash
# PostgreSQL 磁盘空间监控脚本

DB_NAME="your_database"
THRESHOLD=85  # 告警阈值 85%

# 检查磁盘使用率
usage=$(df /var/lib/postgresql/data | awk 'NR==2 {print $5}' | sed 's/%//')

if [ $usage -gt $THRESHOLD ]; then
    echo "警告：PostgreSQL 磁盘使用率已达到 ${usage}%"

    # 查询最大的表
    psql -d $DB_NAME -c "
    SELECT
        relname,
        pg_size_pretty(pg_total_relation_size(oid)) as size
    FROM pg_class
    WHERE relkind = 'r'
    ORDER BY pg_total_relation_size(oid) DESC
    LIMIT 5;
    "

    # 发送告警邮件（需要配置邮件服务）
    # mail -s "PostgreSQL 磁盘空间告警" [email protected] < /tmp/disk_alert.txt
fi

性能优化建议

TIP

文件系统在接近满载时性能会显著下降，建议在使用率达到 80% 时就开始进行空间清理。

最佳实践：

定期监控：设置自动化脚本，每小时检查磁盘使用情况
预留空间：确保至少保留 20% 的可用空间
分离存储：将数据文件、WAL 文件和归档文件放在不同的磁盘上
表空间策略：合理使用表空间分散存储压力

sql

-- 设置定期清理任务
CREATE EXTENSION IF NOT EXISTS pg_cron;

-- 每天凌晨2点执行磁盘使用检查
SELECT cron.schedule('disk-usage-check', '0 2 * * *', 'SELECT record_disk_usage()');

监控指标和告警

关键监控指标

指标	正常范围	告警阈值	紧急阈值
数据磁盘使用率	< 70%	> 80%	> 90%
WAL 磁盘使用率	< 60%	> 75%	> 85%
单表大小增长率	< 10%/天	> 20%/天	> 50%/天
TOAST 表大小比例	< 30%	> 50%	> 70%

自动化告警查询

sql

-- 创建监控视图
CREATE OR REPLACE VIEW disk_usage_summary AS
SELECT
    current_database() as database_name,
    pg_size_pretty(pg_database_size(current_database())) as database_size,
    count(*) as table_count,
    sum(pg_total_relation_size(oid)) as total_size_bytes,
    max(pg_total_relation_size(oid)) as largest_table_bytes
FROM pg_class
WHERE relkind = 'r';

-- 检查异常增长的表
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as current_size
FROM pg_tables
WHERE pg_total_relation_size(schemaname||'.'||tablename) > 1024*1024*100  -- 大于100MB的表
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC;

Details

磁盘使用监控完整脚本

python

#!/usr/bin/env python3
import psycopg2
import smtplib
from email.mime.text import MIMEText
import subprocess
import json

class PostgreSQLDiskMonitor:
    def __init__(self, connection_params):
        self.conn_params = connection_params
        self.thresholds = {
            'disk_usage': 85,
            'table_growth': 20,
            'wal_usage': 75
        }

    def check_disk_usage(self):
        """检查磁盘使用率"""
        try:
            result = subprocess.run(['df', '-h', '/var/lib/postgresql/'],
                                  capture_output=True, text=True)
            lines = result.stdout.strip().split('\n')
            usage_line = lines[1]
            usage_percent = int(usage_line.split()[4].rstrip('%'))
            return usage_percent
        except Exception as e:
            print(f"检查磁盘使用率失败: {e}")
            return None

    def check_large_tables(self):
        """检查大表"""
        query = """
        SELECT
            schemaname || '.' || tablename as full_name,
            pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as size,
            pg_total_relation_size(schemaname||'.'||tablename) as size_bytes
        FROM pg_tables
        WHERE schemaname NOT IN ('information_schema', 'pg_catalog')
        ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
        LIMIT 10;
        """

        try:
            with psycopg2.connect(**self.conn_params) as conn:
                with conn.cursor() as cur:
                    cur.execute(query)
                    return cur.fetchall()
        except Exception as e:
            print(f"查询大表失败: {e}")
            return []

    def generate_report(self):
        """生成监控报告"""
        disk_usage = self.check_disk_usage()
        large_tables = self.check_large_tables()

        report = {
            'timestamp': str(datetime.now()),
            'disk_usage_percent': disk_usage,
            'alert_level': 'normal',
            'large_tables': large_tables
        }

        if disk_usage and disk_usage > self.thresholds['disk_usage']:
            report['alert_level'] = 'critical'

        return report

# 使用示例
monitor = PostgreSQLDiskMonitor({
    'host': 'localhost',
    'database': 'your_db',
    'user': 'postgres',
    'password': 'your_password'
})

report = monitor.generate_report()
print(json.dumps(report, indent=2, ensure_ascii=False))

总结

PostgreSQL 磁盘使用监控是数据库管理的基础工作，需要：

理解存储架构：掌握主堆文件、TOAST 文件和索引文件的作用
选择合适的监控方法：SQL 函数适合日常监控，系统目录查询适合深度分析
建立预警机制：设置合理的阈值和自动化监控脚本
制定应急预案：准备磁盘空间不足时的快速处理流程
持续优化：定期分析空间使用趋势，优化存储策略

通过系统性的磁盘监控，可以有效预防因存储空间不足导致的数据库故障，确保业务系统的稳定运行。

PostgreSQL 磁盘使用监控 💾 ​

概述 ​

PostgreSQL 存储架构概述 ​

存储文件类型详解 ​

磁盘使用监控方法 ​

方法对比 ​

实际监控示例 ​

1. 基础表空间监控 ​

2. TOAST 表空间监控 ​

3. 索引空间监控 ​

4. 全库最大表监控 ​

高级监控查询 ​

综合空间分析 ​

空间增长趋势监控 ​

磁盘空间不足的故障处理 ​

故障影响分析 ​

紧急处理步骤 ​

预防性监控脚本 ​

性能优化建议 ​

监控指标和告警 ​

关键监控指标 ​

自动化告警查询 ​

总结 ​

PostgreSQL 磁盘使用监控 💾

概述

PostgreSQL 存储架构概述

存储文件类型详解

磁盘使用监控方法

方法对比

实际监控示例

1. 基础表空间监控

2. TOAST 表空间监控

3. 索引空间监控

4. 全库最大表监控

高级监控查询

综合空间分析

空间增长趋势监控

磁盘空间不足的故障处理

故障影响分析

紧急处理步骤

预防性监控脚本

性能优化建议

监控指标和告警

关键监控指标

自动化告警查询

总结