标签归档：pg恢复

对于postgresql数据库,我们都知道他的数据在独立的oid文件里面,而字典主要在pg_type/1247,pg_class/1259,pg_attribute/1260等对象中,而数据是存储在独立的oid和toast oid文件中.如果通过提供oid文件,可以实现狸猫换太子的恢复数据.这里做一个简单测试,大概思路是这样的库里面有一个表a,然后创建一个空表b,然后把a表相关的oid文件复制成b表的oid文件,然后验证数据是否可以正常查询
创建一个空的b表,结构和a表一致

[postgres@xifenfei 13676]$ psql
psql (12.8)
Type "help" for help.

postgres=# CREATE TABLE "public"."AO_6384AB_DISCOVERED1" (
postgres(#   "DATE" "pg_catalog"."timestamp",
postgres(#   "ID" "pg_catalog"."int4",
postgres(#   "KEY" "pg_catalog"."varchar" COLLATE "pg_catalog"."default",
postgres(#   "PLUGIN_KEY" "pg_catalog"."varchar" COLLATE "pg_catalog"."default",
postgres(#   "USER_KEY" "pg_catalog"."varchar" COLLATE "pg_catalog"."default"
postgres(# )
postgres-# ;
CREATE TABLE

检查a/b的情况

postgres=# select count(1) from "AO_6384AB_DISCOVERED";
 count 
-------
   544
(1 row)

postgres=# select count(1) from "AO_6384AB_DISCOVERED1";
 count 
-------
     0
(1 row)

postgres=# \d "AO_6384AB_DISCOVERED"
                    Table "public.AO_6384AB_DISCOVERED"
   Column   |            Type             | Collation | Nullable | Default 
------------+-----------------------------+-----------+----------+---------
 DATE       | timestamp without time zone |           |          | 
 ID         | integer                     |           |          | 
 KEY        | character varying           |           |          | 
 PLUGIN_KEY | character varying           |           |          | 
 USER_KEY   | character varying           |           |          | 

postgres=# \d "AO_6384AB_DISCOVERED1"
                   Table "public.AO_6384AB_DISCOVERED1"
   Column   |            Type             | Collation | Nullable | Default 
------------+-----------------------------+-----------+----------+---------
 DATE       | timestamp without time zone |           |          | 
 ID         | integer                     |           |          | 
 KEY        | character varying           |           |          | 
 PLUGIN_KEY | character varying           |           |          | 
 USER_KEY   | character varying           |           |          | 

存在数据的a/b表相关oid信息

postgres=# select oid,relname,relfilenode from pg_class where relname like '%AO_6384AB_DISCOVERED%';
  oid  |        relname        | relfilenode 
-------+-----------------------+-------------
 16516 | AO_6384AB_DISCOVERED  |       16516
 17069 | AO_6384AB_DISCOVERED1 |       17069
(2 rows)

postgres=# select oid,relname,relfilenode from pg_class where relname like '%16516%' or  relname like '%17069%'  ;
  oid  |       relname        | relfilenode 
-------+----------------------+-------------
 16519 | pg_toast_16516       |       16519
 16521 | pg_toast_16516_index |       16521
 17072 | pg_toast_17069       |       17072
 17074 | pg_toast_17069_index |       17074
(4 rows)

关闭数据库把a表相关的oid文件拷贝替换b表的oid文件

[postgres@xifenfei 13676]$ pg_ctl stop -D /pgdata
waiting for server to shut down....2025-12-12 20:00:58.804 HKT [21445] LOG:  received fast shutdown request
2025-12-12 20:00:58.805 HKT [21445] LOG:  aborting any active transactions
2025-12-12 20:00:58.805 HKT [21471] FATAL:  terminating connection due to administrator command
2025-12-12 20:00:58.805 HKT [21473] FATAL:  terminating connection due to administrator command
2025-12-12 20:00:58.805 HKT [21856] FATAL:  terminating connection due to administrator command
2025-12-12 20:00:58.806 HKT [21472] FATAL:  terminating connection due to administrator command
2025-12-12 20:00:58.806 HKT [21445] LOG: background worker"logical replication launcher"(PID 252)exited with exit code 1
2025-12-12 20:00:58.807 HKT [21447] LOG:  shutting down
2025-12-12 20:00:58.811 HKT [21445] LOG:  database system is shut down
 done
server stopped
[postgres@xifenfei 13676]$ cp  16516 17069
[postgres@xifenfei 13676]$ cp  16519 17072
[postgres@xifenfei 13676]$ cp  16521 17074
[postgres@xifenfei 13676]$ pg_ctl start -D /pgdata
waiting for server to start....2025-12-12 20:02:05.985 HKT [22241] LOG:  
starting PostgreSQL 12.8 on x86_64-pc-linux-gnu, 
compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-26.0.1), 64-bit
2025-12-12 20:02:05.985 HKT [22241] LOG:  listening on IPv4 address "0.0.0.0", port 5432
2025-12-12 20:02:05.986 HKT [22241] LOG:  listening on IPv6 address "::", port 5432
2025-12-12 20:02:05.986 HKT [22241] LOG:  listening on Unix socket "/tmp/.s.PGSQL.5432"
2025-12-12 20:02:05.993 HKT [22242] LOG:  database system was shut down at 2025-12-12 20:00:58 HKT
2025-12-12 20:02:05.995 HKT [22241] LOG:  database system is ready to accept connections
 done
server started

查询a/b表数据

[postgres@xifenfei 13676]$ psql
psql (12.8)
Type "help" for help.

postgres=# select count(1) from "AO_6384AB_DISCOVERED1";
 count 
-------
   544
(1 row)

postgres=# select count(1) from "AO_6384AB_DISCOVERED";
 count 
-------
   544
(1 row)

通过上述从测试,证明创建一个新的表结构,完全可以访问老的oid文件中的数据