PostgreSQL 14表空间占满98%但未查询到关联表求助

针对Debian 11上PostgreSQL 14中mydata_data2表空间使用率达98%但pg_tables、pg_indexes查不到关联对象的情况，按以下步骤排查：

• 排查临时对象：临时表、临时索引不会出现在pg_tables/pg_indexes中，但会占用表空间。执行以下SQL查询该表空间下的临时对象：

SELECT relname AS object_name, relkind AS object_type
FROM pg_class
WHERE reltablespace = (SELECT oid FROM pg_tablespace WHERE spcname = 'mydata_data2')
  AND relkind IN ('t', 'i');

注：relkind='t'为临时表，relkind='i'为临时索引。

• 排查TOAST表：存储大字段的TOAST表可能单独关联到该表空间，但主表不在此空间，因此无法通过pg_tables查到。执行以下SQL找到关联的主表：

SELECT c.relname AS toast_table, p.relname AS parent_table
FROM pg_class c
JOIN pg_class p ON c.reltoastrelid = p.oid
WHERE c.reltablespace = (SELECT oid FROM pg_tablespace WHERE spcname = 'mydata_data2')
  AND c.relkind = 't';

• 排查分区表的分区：如果存在分区表，分区可能在该表空间但主表不在，pg_tables仅显示主表。执行以下SQL找到对应分区：

SELECT inhrelid::regclass AS partition_name, parent.relname AS parent_table
FROM pg_inherits
JOIN pg_class parent ON pg_inherits.inhparent = parent.oid
JOIN pg_class part ON pg_inherits.inhrelid = part.oid
WHERE part.reltablespace = (SELECT oid FROM pg_tablespace WHERE spcname = 'mydata_data2');

• 查看表空间内所有对象的大小：直接列出该表空间下所有对象的占用空间，定位大对象：

SELECT relname AS object_name,
       relpages * 8 / 1024 AS size_mb
FROM pg_class
WHERE reltablespace = (SELECT oid FROM pg_tablespace WHERE spcname = 'mydata_data2')
ORDER BY size_mb DESC;

• 检查表空间目录的遗留文件：若上述查询都无结果，直接登录服务器查看目录内的文件：

  1. 先获取表空间的物理路径：

  psql -c "SELECT spclocation FROM pg_tablespace WHERE spcname='mydata_data2';"
  

  2. 进入目录查看文件占用：

  du -sh /path/to/mydata_data2/*
  ls -lh /path/to/mydata_data2/
  

  可能存在PostgreSQL异常操作遗留的文件，或手动放入的无关文件。

• 检查未释放的空间：如果有长事务未结束，DROP或TRUNCATE操作释放的空间无法回收。查看当前长事务：

SELECT pid, query_start, query
FROM pg_stat_activity
WHERE now() - query_start > '1 hour'::interval;

结束长事务后执行VACUUM清理空间。

内容的提问来源于stack exchange，提问作者Kenneth Chirchir