标签云
asm恢复 bbed bootstrap$ dul In Memory kcbzib_kcrsds_1 kccpb_sanity_check_2 MySQL恢复 ORA-00312 ORA-00607 ORA-00704 ORA-00742 ORA-01110 ORA-01555 ORA-01578 ORA-01595 ORA-08103 ORA-600 2131 ORA-600 2662 ORA-600 3020 ORA-600 4000 ORA-600 4137 ORA-600 4193 ORA-600 4194 ORA-600 16703 ORA-600 kcbzib_kcrsds_1 ORA-600 KCLCHKBLK_4 ORA-15042 ORA-15196 ORACLE 12C oracle dul ORACLE PATCH Oracle Recovery Tools oracle加密恢复 oracle勒索 oracle勒索恢复 oracle异常恢复 Oracle 恢复 ORACLE恢复 ORACLE数据库恢复 oracle 比特币 OSD-04016 YOUR FILES ARE ENCRYPTED 勒索恢复 比特币加密文章分类
- Others (2)
- 中间件 (2)
- WebLogic (2)
- 操作系统 (103)
- 数据库 (1,740)
- DB2 (22)
- MySQL (75)
- Oracle (1,590)
- Data Guard (52)
- EXADATA (8)
- GoldenGate (24)
- ORA-xxxxx (161)
- ORACLE 12C (72)
- ORACLE 18C (6)
- ORACLE 19C (15)
- ORACLE 21C (3)
- Oracle 23ai (8)
- Oracle ASM (68)
- Oracle Bug (8)
- Oracle RAC (54)
- Oracle 安全 (6)
- Oracle 开发 (28)
- Oracle 监听 (28)
- Oracle备份恢复 (582)
- Oracle安装升级 (95)
- Oracle性能优化 (62)
- 专题索引 (5)
- 勒索恢复 (84)
- PostgreSQL (27)
- pdu工具 (5)
- PostgreSQL恢复 (9)
- SQL Server (29)
- SQL Server恢复 (10)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (37)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (20)
-
最近发表
- ORA-600 krhpfh_03-1208
- VMware勒索加密恢复(vmdk勒索恢复)
- ORA-39773: parse of metadata stream failed故障处理
- sql数据库备份失败—失败: 23(数据错误(循环冗余检查)
- vmdk文件被加密恢复(虚拟机文件加密)
- 差点被误操作的ORA-600 kcratr_nab_less_than_odr故障
- win平台19c 打patch遭遇2个小问题汇总
- pg单个数据库目录恢复-pdu恢复单个数据库目录数据
- pg删除数据恢复—pdu恢复pg delete数据
- .[OnlyBuy@cyberfear.com].REVRAC勒索mysql恢复
- 表dml操作权限授权给public,导致只读用户失效
- 21c数据库恢复遭遇ora-600 ktugct: corruption detected
- pg_control丢失/损坏处理
- 当前主流数据库版本服务支持周期-202503
- pg启动报invalid checkpoint record处理
- 删除redo导致ORA-00313 ORA-00312故障处理
- Navicat连接postgresql时出现column “datlastsysoid” does not exist错误解决
- aix磁盘损坏oracle数据库恢复
- pg误删除数据恢复(PostgreSQL delete数据恢复)
- PostgreSQL表文件损坏恢复—pdu恢复损坏的表文件
标签归档:PostgreSQL恢复
PostgreSQL恢复工具—pdu工具介绍
张晨同学开发了一个PostgreSQL数据恢复工具PDU(PDU: Postgresql Data Rescue Tool),我这边配合做一些测试
使用帮助命令
PDU.public=# ; PDU数据拯救工具 | 命令帮助 ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ **基础操作** b; │ 初始化数据库元信息 exit; │ 退出工具 **数据库切换** use <db>; │ 指定目标数据库 (例: use logs;) set <schema>; │ 指定操作模式 (例: set recovery;) **元数据展示** \l; │ 列出所有数据库 \dn; │ 显示当前数据库模式 \dt; │ 列出当前模式下的表 \d+ <table>; │ 查看表结构详情 (例: \d+ users;) \d <table>; │ 查看表列类型 (例: \d users;) **数据导出** unload <table>; │ 导出表数据 → ./<表名>.csv (例: unload orders;) unload SCH; │ 导出当前模式所有数据 unload DDL; │ 生成模式结构定义文件 unload COPY; │ 生成PSQL COPY语句脚本 **误删数据恢复** scan t1; │ 扫描被误删的表 restore del <Tx Number>; │ 通过事务号恢复被误删的数据 -------------------------------------------------------------------------------------- scan drop; │ 扫描wal日志中的drop事务 restore drop <Tx Number>; │ 通过事务号恢复被drop的表 add <oid> <tablename> <attibutes>; │ 将表信息手动添加到restore库中 例如: <add 12345 t1 varchar,varchar,timestamp,varchar,numeric,varchar,varchar,varchar,numeric;> ━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━ 语法规则 ◈ 指令后缀必须带 `;`
加载PostgreSQL元数据
PDU.public=# b; 开始初始化... -pg_database:</var/lib/pgsql/12/data/global/1262> 【postgres】 -pg_schema:</var/lib/pgsql/12/data/base/14399/2615> -pg_class:</var/lib/pgsql/12/data/base/14399/1259>,共55行 -pg_attribute:</var/lib/pgsql/12/data/base/14399/1249>,共2913行 模式: -->public,2张表 【his5_dms】 -pg_schema:</var/lib/pgsql/12/data/base/16386/2615> -pg_class:</var/lib/pgsql/12/data/base/16386/1259>,共793行 -pg_attribute:</var/lib/pgsql/12/data/base/16386/1249>,共31329行 模式: -->public,660张表 -->hiscrm,55张表 -->pgagent,8张表 -->report,7张表 -->statistics,10张表
查看当前有哪些库
PDU.public=# \l; |------------------| | 数据库名 | |------------------| | postgres | | template1 | | template0 | | his5_dms | | restore | |------------------| 5 rows selected
进入某个库
PDU.public=# use his5_dms; |----------------------------------------| | 模式 | 表数量 | |----------------------------------------| | public | 660 | | hiscrm | 55 | | pgagent | 8 | | report | 7 | | statistics | 10 | |----------------------------------------|
进入某个模式
his5_dms.public=# set hiscrm; |--------------------------------------------------| | 表名 | 表大小 | |--------------------------------------------------| | t_patient_other | 600.00 KB | | t_sys_oper_log | 88.00 KB | | t_auth | 88.00 KB | | t_setting_user | 56.00 KB | | t_field_define | 32.00 KB | | t_oper_log | 16.00 KB | | t_role | 16.00 KB | | t_sys_login_log | 8.00 KB | | t_appointment_item | 8.00 KB | | t_clinic | 8.00 KB | | t_dept | 8.00 KB | | t_employee | 8.00 KB | | t_menu | 8.00 KB | | t_patient_label | 8.00 KB | | t_patient_label_detail_tpl | 8.00 KB | | t_patient_source_ref | 8.00 KB | | t_return_visit_tpl | 8.00 KB | | t_setting_clinic | 8.00 KB | | t_setting_notify | 8.00 KB | | t_sms_template_category | 8.00 KB | |--------------------------------------------------| 仅显示表大小排名前 50 的表名
显示部分表
his5_dms.hiscrm=# \dt; |--------------------------------------------------| | 表名 | 表大小 | |--------------------------------------------------| | t_patient_other | 600.00 KB | | t_sys_oper_log | 88.00 KB | | t_auth | 88.00 KB | | t_setting_user | 56.00 KB | | t_field_define | 32.00 KB | | t_oper_log | 16.00 KB | | t_role | 16.00 KB | | t_sys_login_log | 8.00 KB | | t_appointment_item | 8.00 KB | | t_clinic | 8.00 KB | | t_dept | 8.00 KB | | t_employee | 8.00 KB | | t_menu | 8.00 KB | | t_patient_label | 8.00 KB | | t_patient_label_detail_tpl | 8.00 KB | | t_patient_source_ref | 8.00 KB | | t_return_visit_tpl | 8.00 KB | | t_setting_clinic | 8.00 KB | | t_setting_notify | 8.00 KB | | t_sms_template_category | 8.00 KB | |--------------------------------------------------| 仅显示表大小排名前 50 的表名
显示某个表的信息
his5_dms.hiscrm=# \d+ t_auth; ---------------------------------------------------------------- | 建表语句 | ---------------------------------------------------------------- CREATE TABLE t_auth ( id bigint, clinic_id bigint, group_id bigint, parient_id varchar(64), menu_id varchar(64), auth_key varchar(60), auth_name varchar(64), uris varchar, rely varchar(255), state bigint, sort bigint, tag bigint, explain varchar(255), desc varchar(255) ); ---------------------------------------------------------------- | | ---------------------------------------------------------------- his5_dms.hiscrm=# \d t_auth; ---------------------------------------------------------------- | 列类型 | ---------------------------------------------------------------- bigint,bigint,bigint,varchar,varchar,varchar,varchar,varchar,varchar,bigint,bigint,bigint,varchar,varchar
恢复表数据
his5_dms.hiscrm=# unload t_auth; 正在解析表 <t_auth>. 已解析数据页: 11, 已解析数据: 492 条 <t_auth>-</var/lib/pgsql/12/data/base/16386/16895> 解析完成, 12 个数据页 ,共计 492 条数据. 成功 492 条; 失败【0】条 COPY文件路径为:<his5_dms/hiscrm/t_auth.csv>
确认恢复表的数据情况
[root@xifenfeidg hiscrm]# wc -l t_auth.csv 492 t_auth.csv
PostgreSQL中查询表实际数据情况
his5_dms=# select count(1) from hiscrm.t_auth; count ------- 492 (1 row) is5_dms=# \d hiscrm.t_auth; Table "hiscrm.t_auth" Column | Type | Collation | Nullable | Default ------------+------------------------+-----------+----------+------------------------- id | bigint | | not null | clinic_id | bigint | | | group_id | bigint | | | parient_id | character varying(64) | | not null | menu_id | character varying(64) | | not null | auth_key | character varying(60) | | not null | auth_name | character varying(64) | | not null | uris | text | | not null | rely | character varying(255) | | | NULL::character varying state | bigint | | not null | sort | bigint | | not null | tag | bigint | | | '0'::bigint explain | character varying(255) | | | NULL::character varying desc | character varying(255) | | | NULL::character varying
truncate表
his5_dms=# truncate table hiscrm.t_auth; TRUNCATE TABLE his5_dms=# select count(1) from hiscrm.t_auth; count ------- 0 (1 row)
导入数据,并验证恢复效果
his5_dms=# \i /tmp/pdu/his5_dms/COPY/hiscrm_copy.sql SET COPY 492 his5_dms=# select count(1) from hiscrm.t_auth; count ------- 492 (1 row)
通过验证pdu可以在数据库离线的情况下,恢复PostgreSQL数据库中表的数据,更加方便和灵活的实现替代pg_filedump功能,而且pdu还在不断完善和新功能更新中
PostgreSQL恢复系列:pg_filedump恢复字典构造
pg_filedump是在pg数据库极端情况下直接解析数据库文件的利器,但是由于是开源软件,本身难以实现批量处理,通过对底层基表分析,可以实现批量处理功能
分析PostgreSQL库中数据库信息
--数据库查询结果 postgres=# select oid,datname,datdba,dattablespace from pg_database; oid | datname | datdba | dattablespace -------+-------------+--------+--------------- 14187 | postgres | 10 | 1663 16403 | db_xff | 10 | 1663 1 | template1 | 10 | 1663 14186 | template0 | 10 | 1663 16407 | db_xifenfei | 16405 | 16406 (5 rows) --通过dump 该文件解析数据 <Data> ----- Item 1 -- Length: 0 Offset: 5 (0x0005) Flags: REDIRECT Item 2 -- Length: 0 Offset: 6 (0x0006) Flags: REDIRECT Item 3 -- Length: 260 Offset: 7320 (0x1c98) Flags: NORMAL COPY: 14187 postgres Item 4 -- Length: 260 Offset: 7056 (0x1b90) Flags: NORMAL COPY: 16403 db_xff Item 5 -- Length: 297 Offset: 7888 (0x1ed0) Flags: NORMAL COPY: 1 template1 Item 6 -- Length: 297 Offset: 7584 (0x1da0) Flags: NORMAL COPY: 14186 template0 Item 7 -- Length: 260 Offset: 6792 (0x1a88) Flags: NORMAL COPY: 16407 db_xifenfei
分析PostgreSQL 表空间信息
--sql查询表空间信息 postgres=# select * from pg_tablespace; oid | spcname | spcowner | spcacl | spcoptions -------+--------------+----------+--------+------------ 1663 | pg_default | 10 | | 1664 | pg_global | 10 | | 16406 | tbs_xifenfei | 16405 | | (3 rows) --通过dump 该文件解析数据 <Data> ----- Item 1 -- Length: 96 Offset: 8096 (0x1fa0) Flags: NORMAL COPY: 1663 pg_default Item 2 -- Length: 96 Offset: 8000 (0x1f40) Flags: NORMAL COPY: 1664 pg_global Item 3 -- Length: 96 Offset: 7904 (0x1ee0) Flags: NORMAL COPY: 16406 tbs_xifenfei
分析PostgreSQL 对象id、name、path对应关系
--对象信息查询 postgres=# select oid ,relname,relnamespace,reltype,reloftype,relowner,relam,relfilenode, reltablespace from pg_class where relname like 't_t%' or relname like 't_x%'; oid | relname | relnamespace | reltype | reloftype | relowner | relam | relfilenode | reltablespace -------+------------+--------------+---------+-----------+----------+-------+-------------+--------------- 16387 | t_xifenfei | 2200 | 16389 | 0 | 10 | 2 | 16390 | 0 16391 | t_xff | 2200 | 16393 | 0 | 10 | 2 | 16391 | 0 16394 | t_xff2 | 2200 | 16396 | 0 | 10 | 2 | 16394 | 0 16397 | t_xff3 | 2200 | 16399 | 0 | 10 | 2 | 16397 | 0 16400 | t_xff4 | 2200 | 16402 | 0 | 10 | 2 | 16400 | 0 16408 | t_tbs | 2200 | 16410 | 0 | 10 | 2 | 16408 | 16406 (6 rows) --通过dump 该文件解析数据(显示部分) COPY: 16394 t_xff2 2200 16396 0 10 2 16394 0 0 0.000000000000 0 0 f f p r Item 29 -- Length: 0 Offset: 31 (0x001f) Flags: REDIRECT Item 30 -- Length: 172 Offset: 2592 (0x0a20) Flags: NORMAL COPY: 16397 t_xff3 2200 16399 0 10 2 16397 0 0 0.000000000000 0 0 f f p r Item 31 -- Length: 205 Offset: 3376 (0x0d30) Flags: NORMAL COPY: 12093 pg_shadow 11 12094 0 10 0 0 0 0 0.000000000000 0 0 f f p v Item 32 -- Length: 172 Offset: 2416 (0x0970) Flags: NORMAL COPY: 16400 t_xff4 2200 16402 0 10 2 16400 0 0 0.000000000000 0 0 f f p r
这个里面获取到pg_class.reltablespace是表空间的id值,根据自定义表空间的规则:在pgdata/pg_tblspc创建link指向创建表空间时候的文件夹路径
-bash-4.2$ pwd /var/lib/pgsql/12/data/pg_tblspc -bash-4.2$ ls -ltr total 0 lrwxrwxrwx 1 postgres postgres 30 Apr 15 20:13 16406 -> /var/lib/pgsql/12/data/tbs_xff
结合上述的pg_database,pg_tablespace,pg_class信息,可以获取到每个表对应实际的存储路径
分析PostgreSQL 模式信息
--sql查询模式信息 postgres=# select * from pg_namespace; oid | nspname | nspowner | nspacl -------+--------------------+----------+------------------------------------- 99 | pg_toast | 10 | 12314 | pg_temp_1 | 10 | 12315 | pg_toast_temp_1 | 10 | 11 | pg_catalog | 10 | {postgres=UC/postgres,=U/postgres} 2200 | public | 10 | {postgres=UC/postgres,=UC/postgres} 13887 | information_schema | 10 | {postgres=UC/postgres,=U/postgres} 16404 | u_xifenfei | 10 | (7 rows) --通过dump 该文件解析数据 <Data> ----- Item 1 -- Length: 0 Offset: 6 (0x0006) Flags: REDIRECT Item 2 -- Length: 96 Offset: 8096 (0x1fa0) Flags: NORMAL COPY: 99 pg_toast Item 3 -- Length: 0 Offset: 7 (0x0007) Flags: REDIRECT Item 4 -- Length: 96 Offset: 8000 (0x1f40) Flags: NORMAL COPY: 12314 pg_temp_1 Item 5 -- Length: 96 Offset: 7904 (0x1ee0) Flags: NORMAL COPY: 12315 pg_toast_temp_1 Item 6 -- Length: 141 Offset: 7760 (0x1e50) Flags: NORMAL COPY: 11 pg_catalog Item 7 -- Length: 141 Offset: 7616 (0x1dc0) Flags: NORMAL COPY: 2200 public Item 8 -- Length: 0 Offset: 9 (0x0009) Flags: REDIRECT Item 9 -- Length: 141 Offset: 7472 (0x1d30) Flags: NORMAL COPY: 13887 information_schema Item 10 -- Length: 96 Offset: 7376 (0x1cd0) Flags: NORMAL COPY: 16404 u_xifenfei
通过pg_namespace,pg_class信息,可以获取到对象所属的模式关系,基于上述汇总,可以获取到某个模式下面,所有表id和实际存储路径,现在使用pg_filedump进行恢复,还缺少表的列类型信息,通过pg_type和pg_attribute来获取。
获取PostgreSQL表的列名称和类型[编号]信息
--sql查询列信息 postgres=# \d t_tbs Table "public.t_tbs" Column | Type | Collation | Nullable | Default ----------+------+-----------+----------+--------- oid | oid | | | spcname | name | | | spcowner | oid | | | Tablespace: "tbs_xifenfei" postgres=# select attrelid,attname,atttypid,attstattarget,attlen,attnum from pg_attribute where attrelid=16408; attrelid | attname | atttypid | attstattarget | attlen | attnum ----------+----------+----------+---------------+--------+-------- 16408 | tableoid | 26 | 0 | 4 | -6 16408 | cmax | 29 | 0 | 4 | -5 16408 | xmax | 28 | 0 | 4 | -4 16408 | cmin | 29 | 0 | 4 | -3 16408 | xmin | 28 | 0 | 4 | -2 16408 | ctid | 27 | 0 | 6 | -1 16408 | oid | 26 | -1 | 4 | 1 16408 | spcname | 19 | -1 | 64 | 2 16408 | spcowner | 26 | -1 | 4 | 3 (9 rows) --dump 内容(截取部分) Item 11 -- Length: 144 Offset: 1424 (0x0590) Flags: NORMAL COPY: 16408 oid 26 -1 4 1 Item 12 -- Length: 144 Offset: 1280 (0x0500) Flags: NORMAL COPY: 16408 spcname 19 -1 64 2 Item 13 -- Length: 144 Offset: 1136 (0x0470) Flags: NORMAL COPY: 16408 spcowner 26 -1 4 3 Item 14 -- Length: 144 Offset: 992 (0x03e0) Flags: NORMAL COPY: 16408 ctid 27 0 6 -1 Item 15 -- Length: 144 Offset: 848 (0x0350) Flags: NORMAL COPY: 16408 xmin 28 0 4 -2 Item 16 -- Length: 144 Offset: 704 (0x02c0) Flags: NORMAL COPY: 16408 cmin 29 0 4 -3 Item 17 -- Length: 144 Offset: 560 (0x0230) Flags: NORMAL COPY: 16408 xmax 28 0 4 -4 Item 18 -- Length: 144 Offset: 416 (0x01a0) Flags: NORMAL COPY: 16408 cmax 29 0 4 -5
PostgreSQL获取类型编号和实际类型名称对应关系
--查询类型编号和实际类型关系 postgres=# select oid,typname from pg_type; oid | typname -------+--------------------------------------- 16 | bool 17 | bytea 18 | char 19 | name 20 | int8 21 | int2 22 | int2vector 23 | int4 24 | regproc 25 | text 26 | oid 27 | tid 28 | xid 29 | cid …… --dump 内容(截取部分) Item 1 -- Length: 176 Offset: 8016 (0x1f50) Flags: NORMAL COPY: 16 bool Item 2 -- Length: 176 Offset: 7840 (0x1ea0) Flags: NORMAL COPY: 17 bytea Item 3 -- Length: 176 Offset: 7664 (0x1df0) Flags: NORMAL COPY: 18 char Item 4 -- Length: 176 Offset: 7488 (0x1d40) Flags: NORMAL COPY: 19 name Item 5 -- Length: 176 Offset: 7312 (0x1c90) Flags: NORMAL COPY: 20 int8 Item 6 -- Length: 176 Offset: 7136 (0x1be0) Flags: NORMAL COPY: 21 int2 Item 7 -- Length: 176 Offset: 6960 (0x1b30) Flags: NORMAL COPY: 22 int2vector Item 8 -- Length: 176 Offset: 6784 (0x1a80) Flags: NORMAL COPY: 23 int4 Item 9 -- Length: 176 Offset: 6608 (0x19d0) Flags: NORMAL COPY: 24 regproc Item 10 -- Length: 176 Offset: 6432 (0x1920) Flags: NORMAL COPY: 25 text Item 11 -- Length: 176 Offset: 6256 (0x1870) Flags: NORMAL COPY: 26 oid Item 12 -- Length: 176 Offset: 6080 (0x17c0) Flags: NORMAL COPY: 27 tid Item 13 -- Length: 176 Offset: 5904 (0x1710) Flags: NORMAL COPY: 28 xid Item 14 -- Length: 176 Offset: 5728 (0x1660) Flags: NORMAL COPY: 29 cid Item 15 -- Length: 176 Offset: 5552 (0x15b0) Flags: NORMAL COPY: 30 oidvector
通过pg_class、pg_type和pg_attribute可以获取对象的表的列名称,数据类型等信息。通过以上几个对象,即可获取到pg_filedmp处理所需要的所有信息,然后可以实现批量处理