三月 18, 2024
摘要：在本教程中，您将学习如何在 PostgreSQL 中检查孤立的数据文件。
目录
介绍
与所有其他关系数据库系统一样，PostgreSQL 需要将数据保存在磁盘上，方法是写入预写式日志，并在发生检查点时同步磁盘上的数据文件。当涉及到数据文件时，PostgreSQL 会在关系大小达到 segment_size 后创建一个新文件，默认为 1GB。因此，如果一个关系正在增长，则这个关系可以包含许多文件。在本教程中，将要探索的问题是，是否可能有孤立文件。如果一个文件不是任何关系的一部分/引用，但该文件仍存在于磁盘上，则该文件就是孤立的。一方面，这会造成磁盘空间的浪费，另外，它也与 PostgreSQL 系统表中存储的内容不一致。
示例
磁盘上的文件不属于任何关系，这样的情况会发生吗？考虑下这样的场景：在一个会话中，我们启动一个新事务并创建一个表t，而不提交该事务。
BEGIN;
CREATE TABLE t (id integer, name text);
INSERT INTO t (id, name)
SELECT i, repeat('Pg', 32)
FROM generate_series(1, 1000000) AS s(i);
此外，我们还获取了会话的 PID 和表文件在磁盘上的位置：
select * from pg_backend_pid();
pg_backend_pid
----------------
7170
(1 row)
select pg_relation_filepath('t');
pg_relation_filepath
----------------------
base/12974/24601
(1 row)
不用提交，我们可以检查磁盘上的文件，因为 PostgreSQL 已经创建了它：
$ ls -lh $PGDATA/base/12974/24601
-rw------- 1 postgres postgres 97M Mar 18 21:19 /db/pgsql/data/base/12974/24601
如果服务器现在崩溃，或者内存限制器 OOM killer 启动并终止会话：然后会发生什么？我们可以很容易地通过终止会话（对应信号 9），而不给它任何清理的机会，来模拟这样的场景：
$ kill -9 7170
总结一下：会话在事务能够完成之前就被终止了，并且事务已经创建了一个表。必定发生的情况是，事务被回滚，并且表不会再存在：
postgres-# select 1;
server closed the connection unexpectedly
This probably means the server terminated abnormally
before or while processing the request.
The connection to the server was lost. Attempting reset: Succeeded.
postgres=# select * from t;
ERROR: relation "t" does not exist
LINE 1: select * from t;
这很好，也是意料之中的。但是，我们仍然会在磁盘上看到文件吗？
$ ls -lh $PGDATA/base/12974/24601
-rw------- 1 postgres postgres 97M Mar 18 21:19 /db/pgsql/data/base/12974/24601
是的，现在我们真的有了一个孤立的文件，它不属于 PostgreSQL 当中的任何关系：
select relname from pg_class where oid = 24601;
relname
---------
(0 rows)
找出孤立的数据文件
在最坏的情况下，这可能是数 GB 或数 TB 的空间浪费。有没有至少一种方法，可以检测出此类孤立文件呢？您需要将 PostgreSQL 在系统表中记录的内容，与文件系统上的实际文件进行比较，然后删除 PostgreSQL 不知道的所有内容，这需要非常非常小心地来完成。
数据目录下的所有这些数据文件，都应该在 pg_class 中具有对应的记录（否则 PostgreSQL 是不知道它们的）。我们可以通过下面的查询，来列出当前数据库中可能孤立的文件：
SELECT
unnest(
string_to_array(
string_agg(
(SELECT string_agg(spc.path || '/' || file, ',')
FROM pg_ls_dir(spc.path) AS file
WHERE file ~ E'^[0-9]*$'),
',')
, ',')
) AS filepath
FROM (
SELECT DISTINCT
regexp_replace(pg_relation_filepath(c.oid), E'/[0-9]*$', '') AS path
FROM pg_class c
WHERE c.relkind IN ('r', 'i', 't', 'S') AND
c.relpersistence = 'p'
) AS spc
EXCEPT
SELECT
pg_relation_filepath(c.oid) AS relpath
FROM pg_class c
WHERE c.relkind IN ('r', 'i', 't', 'S', 'm') AND
c.relpersistence IN ('p', 'u');
最后，获取到孤立文件的列表：
filepath
------------------
base/12974/24605
base/12974/24604
base/12974/24601
(3 rows)
虽然您可以使用上述查询获得孤立文件的列表，但由于一些正在进行中的事务（意味着已启动但尚未提交或回滚），可能正在创建文件（如创建表、重写关系等），您可能会得到误报。我们可以使用以下查询，来检查这些正在创建的对象：
SELECT DISTINCT pid, relation
FROM pg_locks AS l
JOIN pg_database AS d ON l.database = d.oid
WHERE d.datname = current_database() AND
locktype = 'relation' AND
granted AND
NOT EXISTS (
SELECT oid FROM pg_class AS c
WHERE c.oid = relation
);
排除掉正在创建的文件后，剩余的将是您需要仔细检查的文件。如果您确定它是一个孤立文件，您可以将其删除（当然，应当在进行备份之后）。