三月 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” 表的备份，那样我们的数据库会回到良好的状态。