PostgreSQL：如何检测自上次检查后是否执行过数据库恢复

针对你提到的场景——数据库未删除重建（OID不变）但执行过对象恢复，且备份可能来自同一或其他环境，可通过以下几种方式检测：

1. 检查恢复相关配置与日志 #

• 旧版本PostgreSQL（<12）：恢复过程中会生成$PGDATA/recovery.conf文件，恢复完成后该文件会被重命名为recovery.done。直接检查数据目录下是否存在这两个文件，即可判断是否执行过恢复。
• 新版本PostgreSQL（≥12）：恢复参数会写入$PGDATA/postgresql.auto.conf，比如restore_command、recovery_target_time等。执行以下命令查看是否存在恢复相关配置：

  grep -E 'restore_command|recovery_target' $PGDATA/postgresql.auto.conf
  

• 查看数据库日志：恢复过程中PostgreSQL会在日志中输出明确信息，比如starting point-in-time recovery、recovery completed等。可直接检索日志文件（默认路径$PGDATA/pg_log/）中的关键词：

  grep 'recovery completed' $PGDATA/pg_log/*.log
  

2. 查询系统视图与控制数据 #

• 实时检查恢复状态：恢复过程中，查询pg_stat_recovery视图可获取恢复的详细信息（如恢复目标、进度等）。若恢复已完成，该视图返回空结果，但可结合日志记录恢复完成的时间点：

  SELECT * FROM pg_stat_recovery;
  

• 检查控制文件信息：使用pg_controldata命令查看数据库检查点信息。若刚执行过恢复，Latest checkpoint's REDO location会和当前WAL位置存在明显差异，或者Time of latest checkpoint会远早于当前系统时间：

  pg_controldata $PGDATA | grep -E 'Latest checkpoint|Time of latest checkpoint'
  

3. 对比数据库内部元数据 #

如果恢复的备份来自其他环境，数据库内部元数据会和当前集群存在差异：

• 对比datfrozenxid：查询pg_database中的datfrozenxid字段，恢复后的数据库该值会和集群中其他同类型数据库（比如默认的postgres库）相差较大：

  SELECT datname, datfrozenxid FROM pg_database;
  

• 检查对象创建时间：对于恢复的对象，查询pg_class中的relcreationtime（PostgreSQL 12+支持），若大量对象的创建时间一致且早于数据库实际运行时间，大概率是执行过恢复：

  SELECT relname, relcreationtime FROM pg_class WHERE relnamespace = (SELECT oid FROM pg_namespace WHERE nspname = 'public') LIMIT 10;
  

4. 自定义恢复标记（最可靠方案） #

如果需要长期监控，建议在备份时添加自定义标记：

1. 在备份的源数据库中创建专门的标记表：

   CREATE TABLE IF NOT EXISTS recovery_marker (
       backup_timestamp TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
       backup_source TEXT NOT NULL,
       recovery_flag BOOLEAN DEFAULT FALSE
   );
   INSERT INTO recovery_marker (backup_source) VALUES ('prod-cluster-20240520');
   

2. 恢复完成后，查询该表是否存在，或检查recovery_flag是否被更新（可在恢复脚本中自动设置该字段为TRUE）：

   SELECT * FROM recovery_marker;
   

这种方法不受PostgreSQL版本限制，能精准区分是否为恢复后的数据库，还能记录备份来源信息。

内容的提问来源于stack exchange，提问作者user1409708