从零开始学PostgreSQL (十)：磁盘使用情况

目录

1、PostgreSQL磁盘管理和故障处理

2、磁盘使用情况

3、磁盘满导致的故障

4、应急措施与优化方案

5、注意事项

6、总结

PostgreSQL 磁盘管理和故障处理

磁盘使用情况

每个表都有一个主堆磁盘文件，其中大部分数据都存储在那里。如果表有任何可能包含大量值的列，则也可能存在一个与该表相关的TOAST文件，用于存储不适合舒适地放入主表中的宽值。如果存在TOAST表，则会有一个有效的索引。此外，主表也可能有关联的索引。每个表和索引都存储在单独的磁盘文件中——如果文件大小超过1GB，则可能不止一个文件。

您可以使用三种方式来监控磁盘空间：使用SQL函数（、使用oid2name模块或手动检查系统目录。SQL函数是最简单的使用方法，并通常推荐使用。本节的剩余部分展示了如何通过检查系统目录来进行监控。

使用psql并针对最近执行过VACUUM或ANALYZE操作的数据库，您可以发出查询来查看任何表的磁盘使用情况：

SELECT pg_relation_filepath(oid), relpages FROM pg_class WHERE relname = 'weather';
结果如下所示：
pg_relation_filepath | relpages 
----------------------+----------
 base/16411/16418     |    34696
(1 row)

每一页通常是8KB大小。（注意，relpages只在执行VACUUM、ANALYZE或某些DDL命令如CREATE INDEX时才会更新。）文件路径名称对于直接检查表的磁盘文件是有用的。

要显示TOAST表使用的空间，请使用如下查询：

SELECT relname, relpages
FROM pg_class,
     (SELECT reltoastrelid
      FROM pg_class
      WHERE relname = 'weather') AS ss
WHERE oid = ss.reltoastrelid OR
      oid = (SELECT indexrelid
             FROM pg_index
             WHERE indrelid = ss.reltoastrelid)
ORDER BY relname;

结果如下所示：

1       relname        | relpages
2----------------------+----------
3 pg_toast_16806       |        0
4 pg_toast_16806_index |        1

显示索引大小也很简单：

SELECT c2.relname, c2.relpages
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'weather' AND
      c.oid = i.indrelid AND
      c2.oid = i.indexrelid
ORDER BY c2.relname;

结果如下所示：

relname     | relpages 
----------------+----------
 test2_date_idx |     3994
 test2_mm_idx   |     3716
(2 rows)

利用上述信息，很容易找到最大的表和索引：

SELECT relname, relpages
FROM pg_class
ORDER BY relpages DESC;

结果如下所示：

relname                     | relpages 
------------------------------------------------+----------
 weather                                        |    34696
 test2_date_idx                                 |     3994
 test2_mm_idx                                   |     3716
 pg_proc                                        |       98
 pg_toast_2618                                  |       64
 pg_attribute                                   |       57
 pg_description                                 |       44
 pg_proc_proname_args_nsp_index                 |       32

总结

• 每个表通常包含一个主堆磁盘文件来存储数据，如果包含宽值列，则可能还有一个TOAST文件来存储不适合主表的数据。

• 通过查询pg_class表可以查看表和索引的磁盘使用情况，使用relpages字段来估算每个对象占用的页数。

• 使用pg_relation_filepath函数可以获取表的磁盘文件路径，这对于直接检查文件有用。

• 通过查询pg_class和pg_index表可以查看TOAST表和索引的磁盘使用情况。

• 使用上述查询可以轻松识别哪些表和索引占用最多的磁盘空间。

磁盘满导致的故障

数据库管理员最重要的磁盘监控任务之一就是确保磁盘不会填满。虽然磁盘填满不会导致数据损坏，但它可能会阻止有用的活动发生。如果持有WAL文件的磁盘变得满了，可能会导致数据库服务器崩溃并随后关闭。

如果你无法通过删除其他文件来释放磁盘上的额外空间，你可以通过使用表空间将一些数据库文件移动到其他文件系统中。

小贴士

一些文件系统在其接近满时性能会变差，因此不要等到磁盘完全满时才采取行动。

如果你的系统支持每个用户的磁盘配额，那么数据库自然会受到服务器运行的用户配额的限制。超过配额会产生与磁盘空间完全耗尽相同的影响。

以下是一些处理PostgreSQL服务器磁盘满的步骤和建议：

1. 立即应对措施

紧急措施

停止写入操作：如果可能的话，立即停止所有写入数据库的操作，以防止进一步消耗磁盘空间。

紧急释放空间：删除不必要的文件或压缩日志文件，以释放一些空间。例如，可以通过归档或删除旧的WAL日志文件来释放空间。

2. 短期解决方案

清理磁盘空间

归档日志：如果启用了归档日志功能，确保归档日志文件被正确地归档并从主目录中删除。

清理临时文件：删除临时文件夹中的文件，如`/tmp`或`/var/tmp`。

清理数据库缓存：如果数据库缓存过大，可以考虑重启PostgreSQL服务以清除缓存。

VACUUM和ANALYZE：运行`VACUUM FULL`命令以回收已删除行的空间，但这需要额外的磁盘空间。在磁盘空间紧张的情况下，可以先尝试`VACUUM`和`ANALYZE`。

删除旧的备份文件：如果服务器上有旧的数据库备份文件，可以考虑删除它们以释放空间。

3. 长期解决方案

优化磁盘使用

调整归档策略：如果启用了归档日志，可以考虑修改归档策略以减少归档日志的数量或频率。

使用表空间：可以考虑使用表空间来将数据文件移动到其他磁盘分区或机器上。参考PostgreSQL文档中的[表空间管理](https://www.postgresql.org/docs/current/sql-createtablespace.html)章节。

增加磁盘空间：如果可能，增加磁盘空间或更换更大容量的硬盘。

定期维护：定期运行`VACUUM`和`ANALYZE`来清理和优化数据库。

配置日志文件：调整日志文件的配置，比如设置日志文件的大小限制和归档策略。

监控磁盘使用：设置监控机制，以便在磁盘空间接近满时及时收到通知。

4. 预防措施

预防磁盘满

设置警告阈值：在磁盘空间接近满时设置警告阈值，以便提前采取行动。

定期检查磁盘使用情况：使用`df -h`命令定期检查磁盘使用情况。

定期审查和调整归档策略：确保归档策略合理，不会导致磁盘空间迅速耗尽。

使用磁盘配额：如果支持，可以为数据库用户设置磁盘配额，以防止单一用户过度使用磁盘空间。

注意事项

• 文件系统性能：一些文件系统在接近满时性能会变差，因此不要等到磁盘完全满时才采取行动。

• 磁盘配额：如果系统支持每个用户的磁盘配额，数据库会受到服务器运行用户配额的限制，超过配额会产生与磁盘空间耗尽相同的影响。

• 定期检查：定期使用df -h命令检查磁盘使用情况。

• 配置调整：根据实际情况调整归档策略和日志文件配置，以减少磁盘空间的压力。

• 表空间管理：使用表空间来优化磁盘使用，并确保数据分布合理。

总结

处理PostgreSQL服务器磁盘满的问题需要立即采取行动以防止进一步的问题，并通过短期和长期措施来解决根本原因。同时，采取预防措施以避免未来再次发生此类问题。通过监控磁盘使用情况、定期维护和合理配置，可以有效管理磁盘资源，确保数据库的稳定运行。

上一篇：从零开始学PostgreSQL (九)：任务进度报告

从零开始学PostgreSQL (八)：监控数据库动态

从零开始学PostgreSQL (七)：高可用性、负载平衡和复制

从零开始学PostgreSQL (六)：备份和恢复

从零开始学PostgreSQL (五)：日常数据库维护任务

从零开始学PostgreSQL (四)：数据库角色

从零开始学PostgreSQL (三)：索引篇

从零开始学PostgreSQL (二)：配置文件

从零开始学PostgreSQL (一)：Centos8 RPM安装PostgreSQL16

公众号内直接回复加群也可以,扫码加群也可以，觉得帖子写的不错，点点关注，点点赞，多多转载，请多多支持。