三月 9, 2024
摘要:在本教程中,您将学习如何在 PostgreSQL 中检查数据文件的完整性。
目录
背景
在极罕见的情况下,与表相关的数据文件丢失,这可能是由于操作系统(硬件问题,如表空间关联的存储设备脱落)或人为误操作,这会导致在操作系统级别无意地删除某些数据文件。虽然完全不建议去触碰/data/base/
目录,和访问在/var/lib/postgresql/14/main/base/
下的文件,但是,有时候它确实发生了。
我们当前的数据库运行良好,结构如下:
List of databases
Name | Owner | Encoding | Collate | Ctype | Access privileges | Size | Tablespace | Description
-----------+----------+----------+---------+---------+-----------------------+---------+------------+--------------------------------------------
redrock | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | 9561 kB | pg_default |
postgres | postgres | UTF8 | C.UTF-8 | C.UTF-8 | | 8553 kB | pg_default | default administrative connection database
template0 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +| 8401 kB | pg_default | unmodifiable empty database
| | | | | postgres=CTc/postgres | | |
template1 | postgres | UTF8 | C.UTF-8 | C.UTF-8 | =c/postgres +| 8553 kB | pg_default | default template for new databases
| | | | | postgres=CTc/postgres | | |
(4 rows)
不知为何,我们在 PostgreSQL 日志中得到了下面的错误消息:
2023-06-14 09:58:06.408 UTC [4056] LOG: listening on IPv4 address "127.0.0.1", port 5432
2023-06-14 09:58:06.412 UTC [4056] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432"
2023-06-14 09:58:06.423 UTC [4057] LOG: database system was shut down at 2023-06-14 09:58:04 UTC
2023-06-14 09:58:06.432 UTC [4056] LOG: database system is ready to accept connections
2023-06-16 10:00:58.130 UTC [35062] postgres@redrock ERROR: could not open file "base/16384/16391": No such file or directory
2023-06-16 10:00:58.130 UTC [35062] postgres@redrock STATEMENT: select * from test limit 1;
2023-06-16 10:01:59.191 UTC [35224] postgres@redrock ERROR: could not open file "base/16384/16391": No such file or directory
2023-06-16 10:01:59.191 UTC [35224] postgres@redrock STATEMENT: select * from test limit 1;
有时,如果只是缺失了关系中的一个段,则 PostgreSQL 日志中可能会没有任何错误消息。那么,我们如何才能找到所有缺失文件的关系呢?
发现缺失文件的关系对象
您可以使用下面的函数,来检查 PostgreSQL 中一个关系的数据完整性:
CREATE OR REPLACE FUNCTION check_relation_integrity(relation regclass)
RETURNS boolean AS $$
DECLARE
blk_size integer;
fsm_size bigint;
min_size bigint;
main_size bigint;
BEGIN
IF pg_stat_file(pg_relation_filepath(relation)) IS NULL THEN
RETURN FALSE;
END IF;
SELECT current_setting('block_size') INTO blk_size;
SELECT pg_relation_size(relation, 'fsm') INTO fsm_size;
SELECT pg_relation_size(relation, 'main') INTO main_size;
IF fsm_size < blk_size * 3 THEN
RETURN TRUE;
END IF;
min_size := (fsm_size / blk_size - 3) * (500 * blk_size / 1024) * blk_size;
RETURN (main_size > min_size);
END;
$$ LANGUAGE plpgsql;
基本的检查逻辑是,我们可以通过 FSM 文件的大小,来校验一个关系的主数据文件大小。每一个关系都有一个空闲空间映射(FSM)文件,来记录关系对象的主数据文件中每个页面的可用空间。在数据块大小为 8192 的情况下,主数据文件大小约为 FSM 文件大小的 4000 倍。
下面的基本查询,会告诉我们数据库中有哪些表缺失文件:
SELECT current_database() AS dbname, n.nspname AS schema, c.relname,
pg_relation_filepath(c.oid) AS path
FROM pg_class c, pg_namespace n
WHERE c.relnamespace = n.oid AND
c.relkind IN ('r', 'i', 't', 'S') AND
c.relpersistence = 'p' AND
NOT check_relation_integrity(c.oid);
dbname | schema | relname | path
---------+--------+---------+------------------
redrock | public | test | base/16384/16391
(1 row)
经过检查,我们发现这是因为一个文件(base/16384/16391)被删除了。所以,我们需要确认这个base/16384/16391
文件是否存在于/base
位置下。
postgres@dbserver:~/14/main$ ls -l base/16384/16391
ls: cannot access 'base/16384/16391': No such file or directory
从上面的输出中,我们已经确定关系编号为 16391 的表 “test” 的文件被删除了。我们需要确定它是被错误地手动删除,还是由于硬件故障。
恢复缺失的文件
在硬件故障的情况下,首先,我们需要修复硬件问题,或将数据库迁移到新硬件,然后执行恢复,如下所述。
要恢复,我们可以选择下面的一个方法:
- 如果有备用服务器在正常运行,并且问题没有同步发生,那么我们可以通过从备用服务器获取逻辑备份,删除主服务器上的表,并使用备份重新创建它来解决问题。
- 执行 PITR,如果我们有备份,并配置了适当的备份归档。
- 如果我们使用 pg_basebackup 备份,配置了适当的备份,那就可以用来恢复数据库 “redrock”。
- 如果我们有一个表级的逻辑备份,那么我们可以恢复 “test” 表的备份,那样我们的数据库会回到良好的状态。