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