联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
对于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文件中的数据
