PostgreSQL 教程: 检查孤立的数据文件

三月 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
        );

排除掉正在创建的文件后,剩余的将是您需要仔细检查的文件。如果您确定它是一个孤立文件,您可以将其删除(当然,应当在进行备份之后)。

了解更多

PostgreSQL 监控