如何确定数据库中是否使用了PostgreSQL数据目录中的文件？

Question

我遇到过这样一种情况:对表空间中的表的大小进行求和(使用pg_class等)会发现，在特定数据库的特定表空间中有550G的数据文件。

但是，服务器上该目录中有670G的文件。

顺便说一句，我不知道怎么会这样。除了Postgres之外，没有任何其他机制将文件写入该目录。我最好的猜测是，也许数据库在自动真空过程中崩溃了，留下了听起来可信的孤立文件around...does？)

因此，我想出了一种方法，通过将ls命令的内容读入数据库，去掉大小大于1G的表的数字扩展名，并将它们与pg_class的内容进行比较，实际上，我发现了大约120G的文件没有在pg_class中反映出来。

我的问题是，删除这些文件是否安全，或者它们是否正在由数据库使用，但没有反映在pg_class中？

Answer 1

请勿手动删除PostgreSQL数据目录中的文件。

这是不安全的，并且会损坏您的数据库。

清除任何不属于数据库的文件的安全方法是执行pg_dumpall，停止服务器，删除数据目录和所有表空间目录的内容，使用inindb中断新集群并恢复转储。

如果您想要调查这个问题，可以尝试创建一个新的表空间，并将所有内容从旧的表空间移动到新的表空间。我将在接下来的回答中描述这一点。

将所有数据库中的所有表和索引移动到新的表空间：

ALTER TABLE ALL IN TABLESPACE oldtblsp SET TABLESPACE newtblsp;
ALTER INDEX ALL IN TABLESPACE oldtblsp SET TABLESPACE newtblsp;

如果oldtblsp是数据库的默认表空间：

ALTER DATABASE mydb SET TABLESPACE newtblsp;

然后运行检查点：

CHECKPOINT;

确保您没有忘记数据库：

SELECT datname
FROM pg_database d
   JOIN pg_tablespace s
      ON d.dattablespace = s.oid
WHERE s.spcname = 'oldtblsp';

通过在所有数据库中运行此查询，确保旧表空间中没有对象

SELECT t.relname, t.relnamespace::regnamespace, t.relkind
FROM pg_class t
   JOIN pg_tablespace s
      ON t.reltablespace = s.oid
WHERE s.spcname = 'oldtblsp';

这应该不会返回任何结果。

现在旧的表空间应该是空的，您可以

DROP TABLESPACE oldtblsp;

如果你真的得到一个错误

ERROR:  tablespace "tblsp" is not empty

可能留下了一些文件。

删除它们的风险自负。