【PostgreSQL003】PostgreSQL数据表空间膨胀，磁盘爆满，应用宕机（经验总结，已更新）

1.一直以来想写下基于PostgreSQL的系列文章，作为较火的数据ETL工具，也是日常项目开发中常用的一款工具，最近刚好挤时间梳理、总结下这块儿的知识体系。
2.熟悉、梳理、总结下PostgreSQL数据库相关知识体系。空间膨胀（主键、外键、索引，增加删除等操作更新频繁）、磁盘爆满（回收机制不生效或不及时）、应用宕机（应用跟PG空间波动较大的数据库安装在同一台服务器）
3.欢迎批评指正，欢迎关注，跪谢一键三连！

1.Linux磁盘空间异常增加–问题定位排查

• 如单日增加100-200GB，问题排查步骤：查找最近更新文件 --> 找到对应组件 --> 发现PG数据目录下增加文件（根据经验怀疑是PG数据表频繁更新，回收机制异常问题导致）

2.PostgreSQL目录结构

2.1 PostgreSQL一级目录下内容

• 一级路径文件夹	路径下存储的内容
  /bin	包含PostgreSQL可执行文件，如psql和其他实用程序。
  /data	存数据文件的常见目录，包括主数据库集群。包含诸如base等子目录，其中存储实际的表数据。
  /lib	包含PostgreSQL所需的共享库。
  /share	可能包含如错误消息、时区信息和其他共享资源等文件。
  /doc	文档文件
  /include	编译与PostgreSQL交互的程序所需的头文件。

• PostgreSQL安装路径下详情如下图所示：
  

2.2 PostgreSQL二级目录data路径下内容

• 二级路径文件夹或文件	路径下存储的内容
  base/	存储数据库的基本数据文件。每个数据库都有以 OID（对象标识符）命名的子目录，包含该数据库所有表和索引的数据文件。
  global/	包含全局性质的系统表空间文件。存放所有数据库共享的系统表，如 pg_database、pg_authid 等。
  pg_tblspc/	包含表空间的符号链接。每个符号链接指向实际的表空间目录，表空间是用于组织数据库物理存储的一种方式。
  pg_twophase/	包含两阶段提交中使用的文件。两阶段提交用于确保分布式事务的一致性。
  pg_stat_tmp/	包含一些临时文件，用于存储统计信息
  pg_stat/	包含PostgreSQL收集的统计信息文件。这些文件记录数据库服务器运行时的性能统计信息，如查询计划、锁等。
  pg_logical/	包含用于逻辑复制的文件。逻辑复制允许将特定表、特定数据库对象或特定的数据更改复制到另一个数据库。
  pg_replslot/	包含复制插槽信息的文件。复制插槽用于流复制中，确保备用节点能够持续接收主节点的 WAL（Write-Ahead Logging）。
  pg_subtrans/	包含用于存储子事务信息的文件。子事务用于处理并发事务中的多个子操作。
  pg_notify/	包含用于存储异步通知信息的文件。异步通知允许数据库中的一个会话通知其他会话有关特定事件的发生。
  pg_snapshots/	包含用于存储快照信息的文件。快照是一种数据库的一致性视图，用于支持可重复读事务隔离级别。
  pg_serial/	包含用于存储序列信息的文件。序列是 PostgreSQL 中生成唯一标识符的一种方式。
  postgresql.conf	存放PostgreSQL服务器的配置参数，如端口号、日志设置等。
  pg_hba.conf	存放PostgreSQL的身份验证规则，定义哪些主机和用户能够连接到数据库，以及使用哪种身份验证方法。
  pg_ident.conf	存放标识映射规则，用于将操作系统用户映射到 PostgreSQL 数据库用户。

• data/路径下详情如下图所示：
  

3.常用问题排查命令

• 查找大小大于1GB的文件

  find /path/to/search -type f -size +1G
  

• 使用find命令结合mtime选项来找到最近更新的文件

  find . -type f -mtime -1
  

• 查看每个文件夹的占用空间

  du -sh *
  # du 是磁盘使用情况的缩写。
  # -s 参数表示汇总每个参数的总用量。
  # -h 参数表示以易读的格式（例如 KB、MB、GB）显示大小。
  # * 表示当前目录下的所有文件和文件夹。# 查看所有子目录的大小，并按大小排序
  du -h --max-depth=1 | sort -hr
  

4.PostgreSQL数据库所有表及数据库目录

• 4.1 获取当前PostgreSQL中所有的表信息

  select * from pg_tables
  

  

• 4.2 显示数据目录

  show data_directory
  

  

• 4.3 查看pg_database这张表查看每一个数据库的oid

  select oid,datname from pg_database
  

  

  • oid 对应的文件存储路径详情如下图所示：
    

• 4.4 查询某张表的存储位置

  -- 如:查询表'youli_testtable'的数据文件
  select pg_relation_filepath('youli_testtable');
  

  

• 4.5 查看该表的数据操作审计日志（查看某张表的增加原因）

  -- 编辑PostgreSQL的配置文件postgresql.conf，开启审计功能
  -- audit_logging = 'on'，
  -- 新版本为： logging_collector = on，如下查询命令可能不再适用
  SELECT *
  FROM pg_audit_log
  WHERE obj_name = 'your_table' AND action = 'INSERT';
  

• 4.6 查询服务器进程同时详细描述与之关联的用户会话和查询

  select * from pg_stat_activity;
  

  

• 4.7 清除表的碎片（耗时非常长慎用！）

  vacuum full youli_testtable;
  

  

• 4.8 不回收空间只标记

  vacuum youli_testtable;
  

  

  • 自带清理执行详情如下图所示：

• 4.9 vacuum verbose public.youli_testtable执行结果及执行日志

  • vacuum作用范围可以是整张表，清理过期元组及索引项，并且不阻塞读和写。
  • 执行结果前后数据表大小对比
    
  • 执行日志详情

    	vacuuming "postgres.public.youli_testtable"table "youli_testtable": truncated 128 to 96 pagesfinished vacuuming "postgres.public.youli_testtable": index scans: 1pages: 32 removed, 96 remain, 33 scanned (25.78% of total)tuples: 4998 removed, 11226 remain, 0 are dead but not yet removableremovable cutoff: 57093, which was 1 XIDs old when operation endedfrozen: 0 pages from table (0.00% of total) had 0 tuples frozenindex scan needed: 33 pages from table (25.78% of total) had 4998 dead item identifiers removedindex "youli_testtable_id_idx": pages: 98 in total, 26 newly deleted, 53 currently deleted, 27 reusable平均读取率：0.000 MB/s，平均写入率：0.000 MB/sbuffer usage: 458 hits, 0 misses, 0 dirtiedWAL usage: 184 records, 0 full page images, 41742 bytes系统用法：CPU:用户：0.00 s，系统：0.00 s，已用时间：0.00 s
    

• 4.10 其他参考命令

  -- 清理并分析所有数据库
  vacuumdb -a -z -v-- 并行清理并分析所有数据库，如开4个并行
  vacuumdb -a -z -j 4 -v-- 只分析特定的数据库，如分析youli
  vacuumdb --analyze-only -d youli -v
  

  

5.部分参考链接

1. https://blog.csdn.net/weixin_48154829/article/details/134382728
2. https://www.cnblogs.com/jonvy/p/16367769.html