标签云
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,741)
- 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 (30)
- SQL Server恢复 (11)
- TimesTen (7)
- 达梦数据库 (2)
- 生活娱乐 (2)
- 至理名言 (11)
- 虚拟化 (2)
- VMware (2)
- 软件开发 (37)
- Asp.Net (9)
- JavaScript (12)
- PHP (2)
- 小工具 (20)
-
最近发表
- GAM、SGAM 或 PFS 页上存在页错误处理
- 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数据恢复)
标签归档:pdu恢复
pg单个数据库目录恢复-pdu恢复单个数据库目录数据
在某些情况下,无法获取pg完整的PGDATA目录中的所有数据库文件目录和文件,只能恢复出来一个数据库目录,对于这种情况,可以通过pdu进行直接恢复,比如有一个目录16805
[postgres@localhost 16805]$ pwd /tmp/16805 [postgres@localhost 16805]$ ls 112 16841 2613 2682 3079 3596 4159 113 174 2615 2683 3079_fsm 3597 4160 1247 175 2615_fsm 2684 3079_vm 3598 4163 1247_fsm 2187 2615_vm 2685 3080 3599 4164 1247_vm 2224 2616 2686 3081 3600 4165 1249 2228 2616_fsm 2687 3085 3600_fsm 4166 1249_fsm 2328 2616_vm 2688 3118 3600_vm 4167 1249_vm 2336 2617 2689 3119 3601 4168 1255 2337 2617_fsm 2690 3164 3601_fsm 4169 1255_fsm 2579 2617_vm 2691 3256 3601_vm 4170 1255_vm 2600 2618 2692 3257 3602 4171 1259 2600_fsm 2618_fsm 2693 3258 3602_fsm 4172 1259_fsm 2600_vm 2618_vm 2696 3350 3602_vm 4173 1259_vm 2601 2619 2699 3351 3603 4174 13362 2601_fsm 2619_fsm 2701 3379 3603_fsm 5002 13362_fsm 2601_vm 2619_vm 2702 3380 3603_vm 548 13362_vm 2602 2620 2703 3381 3604 549 13365 2602_fsm 2650 2704 3394 3605 6102 13366 2602_vm 2651 2753 3394_fsm 3606 6104 13367 2603 2652 2753_fsm 3394_vm 3607 6106 13367_fsm 2603_fsm 2653 2753_vm 3395 3608 6110 13367_vm 2603_vm 2654 2754 3429 3609 6111 13370 2604 2655 2755 3430 3712 6112 13371 2605 2656 2756 3431 3764 6113 13372 2605_fsm 2657 2757 3433 3764_fsm 6116 13372_fsm 2605_vm 2658 2830 3439 3764_vm 6117 13372_vm 2606 2659 2831 3440 3766 6175 13375 2606_fsm 2660 2832 3455 3767 6176 13376 2606_vm 2661 2833 3456 3997 6228 13377 2607 2662 2834 3456_fsm 4143 6229 13377_fsm 2607_fsm 2663 2835 3456_vm 4144 6237 13377_vm 2607_vm 2664 2836 3466 4145 6238 13380 2608 2665 2836_fsm 3467 4146 6239 13381 2608_fsm 2666 2836_vm 3468 4147 826 1417 2608_vm 2667 2837 3501 4148 827 1418 2609 2668 2838 3502 4149 828 16806 2609_fsm 2669 2838_fsm 3503 4150 pg_filenode.map 16806_fsm 2609_vm 2670 2838_vm 3534 4151 pg_internal.init 16806_vm 2610 2673 2839 3541 4152 PG_VERSION 16809 2610_fsm 2674 2840 3541_fsm 4153 16810 2610_vm 2675 2840_fsm 3541_vm 4154 16833 2611 2678 2840_vm 3542 4155 16833_fsm 2612 2679 2841 3574 4156 16833_vm 2612_fsm 2680 2995 3575 4157 16840 2612_vm 2681 2996 3576 4158 [postgres@localhost 16805]$
利用pdu进行恢复
PDU.public=# restore db xifenfei /tmp/16805; -pg_schema:</tmp/16805/2615> -pg_class:</tmp/16805/1259>,共86行 -pg_attribute:</tmp/16805/1249>,共3274行 模式: -->public,2张表 PDU.public=# use xifenfei; |----------------------------------------| | 模式 | 表数量 | |----------------------------------------| | public | 2 | |----------------------------------------| xifenfei.public=# set public; |--------------------------------------------------| | 表名 | 表大小 | |--------------------------------------------------| | t_cas_paymentbill | 8.24 MB | | t_auth | 80.00 KB | |--------------------------------------------------| 仅显示表大小排名前 2 的表名 xifenfei.public=# \dt; |--------------------------------------------------| | 表名 | 表大小 | |--------------------------------------------------| | t_cas_paymentbill | 8.24 MB | | t_auth | 80.00 KB | |--------------------------------------------------| 共计 2 张表 xifenfei.public=# unload sch public; 正在解析表 <t_cas_paymentbill>. 已解析数据页: 1055, 已解析数据: 9636 条 表名<t_cas_paymentbill>-</tmp/16805//16806> 解析完成, 1055 个数据页 ,共计 9636 条数据. 成功 9636 条; 失败【0】条 COPY文件路径为:<xifenfei/public/t_cas_paymentbill.csv> 正在解析表 <t_auth>. 已解析数据页: 10, 已解析数据: 129 条 表名<t_auth>-</tmp/16805//16833> 解析完成, 10 个数据页 ,共计 129 条数据. 成功 129 条; 失败【0】条 COPY文件路径为:<xifenfei/public/t_auth.csv> 模式<public>共 2 张表。成功:2, 失败【0】 日志路径:log/log/xifenfei_unload_schema_public_err.txt COPY命令导出完成, 文件路径: xifenfei/COPY/public_copy.sql,共找到2个csv文件 DDL导出完成. 文件路径: xifenfei/DDL/public_ddl.sql, 共计 2 张表 xifenfei.public=# xifenfei.public=# unload ddl; DDL导出完成. 文件路径: xifenfei/DDL/public_ddl.sql, 共计 2 张表
通过pdu解析和恢复,该目录中一共两个业务表,均正常恢复出来,创建新库,导入数据测试
postgres=# create database xifenfei; CREATE DATABASE postgres=# \c xifenfei; You are now connected to database "xifenfei" as user "postgres". xifenfei=# \i /data/tools/pdu/xifenfei/DDL/public_ddl.sql psql:/data/tools/pdu/xifenfei/DDL/public_ddl.sql:1: ERROR: schema "public" already exists SET CREATE TABLE CREATE TABLE xifenfei=# \i /data/tools/pdu/xifenfei/COPY/public_copy.sql SET COPY 9636 COPY 129 xifenfei=# select count(1) from t_auth; count ------- 129 (1 row) xifenfei=# select count(1) from t_cas_paymentbill; count ------- 9636 (1 row)
PostgreSQL表文件损坏恢复—pdu恢复损坏的表文件
在某些情况下,由于PostgreSQL表文件损坏导致无法正常访问,可以通过pdu把好的block中的数据恢复出来
准备一张测试表,里面有97条记录
his5_dms=# \d hiscrm.t_sys_oper_log; Table "hiscrm.t_sys_oper_log" Column | Type | Collation | Nullable |Default --------------------+------------------------+-----------+----------+------------ id | bigint | | not null | module | character varying(50) | | | title | character varying(50) | | | alias | character varying(50) | | | business_type | integer | | | 0 method | character varying(200) | | | request_method | character varying(10) | | | operator_type | integer | | | 0 oper_name | character varying(50) | | | dept_name | character varying(50) | | | oper_url | character varying(255) | | | oper_ip | character varying(50) | | | oper_location | character varying(255) | | | oper_param | text | | | json_result | text | | | status | integer | | | 0 error_msg | text | | | oper_time | date | | | create_id | bigint | | | create_time | bigint | | | clinic_id | bigint | | | group_id | bigint | | | patient_id | bigint | | | is_patient_related | integer | | | business_content | json | | | his5_dms=# select count(1) from hiscrm.t_sys_oper_log; count ------- 97 (1 row)
查询表对应的具体文件
his5_dms=# SELECT oid,relfilenode FROM pg_class WHERE relname='t_sys_oper_log'; oid | relfilenode -------+------------- 16850 | 16850 (1 row) his5_dms=# SELECT pg_relation_filepath('hiscrm.t_sys_oper_log'); pg_relation_filepath ---------------------- base/16386/16850 (1 row) his5_dms=# SHOW data_directory; data_directory ------------------------ /var/lib/pgsql/12/data (1 row)
使用dd对文件进行破坏
[postgres@xifenfeidg ~]$ ls -l /var/lib/pgsql/12/data/base/16386/16850 -rw-------. 1 postgres postgres 90112 Sep 5 20:26 /var/lib/pgsql/12/data/base/16386/16850 [postgres@xifenfeidg ~]$ dd if=/dev/zero of=/var/lib/pgsql/12/data/base/16386/16850 bs=512 count=1 conv=notrunc 1+0 records in 1+0 records out 512 bytes copied, 0.000158756 s, 3.2 MB/s
重启pg库
[postgres@xifenfeidg bin]$ ./pg_ctl -m fast -D /var/lib/pgsql/12/data/ stop waiting for server to shut down.... done server stopped [postgres@xifenfeidg bin]$ ./pg_ctl -D /var/lib/pgsql/12/data/ start waiting for server to start....2025-03-02 19:02:11.395 HKT [64515] LOG: starting PostgreSQL 12.20 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-22), 64-bit 2025-03-02 19:02:11.396 HKT [64515] LOG: listening on IPv6 address "::1", port 5432 2025-03-02 19:02:11.396 HKT [64515] LOG: listening on IPv4 address "127.0.0.1", port 5432 2025-03-02 19:02:11.396 HKT [64515] LOG: listening on Unix socket "/var/run/postgresql/.s.PGSQL.5432" 2025-03-02 19:02:11.397 HKT [64515] LOG: listening on Unix socket "/tmp/.s.PGSQL.5432" 2025-03-02 19:02:11.403 HKT [64515] LOG: redirecting log output to logging collector process 2025-03-02 19:02:11.403 HKT [64515] HINT: Future log output will appear in directory "log". done server started
查询数据报错
[postgres@xifenfeidg bin]$ psql psql (16.8, server 12.20) Type "help" for help. postgres=# \c his5_dms; psql (16.8, server 12.20) You are now connected to database "his5_dms" as user "postgres". his5_dms=# select count(1) from hiscrm.t_sys_oper_log; ERROR: invalid page in block 0 of relation base/16386/16850
通过pdu进行恢复
跳过了坏块,把好的block中数据均恢复出来
his5_dms.hiscrm=# unload tab t_sys_oper_log; 正在解析表 <t_sys_oper_log>. 已解析数据页: 0, 已解析数据: 0 条 |-块号0 空页面或页面已损坏,已跳过 正在解析表 <t_sys_oper_log>. 已解析数据页: 11, 已解析数据: 86 条 表名<t_sys_oper_log>-</var/lib/pgsql/12/data/base/16386/16850> 解析完成, 11 个数据页 ,共计 86 条数据. 成功 86 条; 失败【0】条 COPY文件路径为:<his5_dms/hiscrm/t_sys_oper_log.csv>
导入pg库中
his5_dms=# truncate table hiscrm.t_sys_oper_log; TRUNCATE TABLE his5_dms=# \i his5_dms/COPY/hiscrm_copy.sql SET COPY 86 his5_dms=#
PostgreSQL恢复工具—pdu恢复单个表文件
在某些情况下,比如我们需要对单个的PostgreSQL库的表文件进行恢复(比如文件系统损坏,drop库/表,truncate表等原因,然后找到了部分oid文件),可以使用pdu对其进行完美恢复(相比pg_filedump也方便很多),具体操作步骤:
1. 由于只有单个表文件,无法获取字典信息,因此需要应用厂商/客户提供具体表创建语句
his5_dms=# CREATE TABLE t_xff ( his5_dms(# id bigint, his5_dms(# hospital_id bigint, his5_dms(# parent_id bigint, his5_dms(# disease_code varchar(60), his5_dms(# disease_name varchar(60), his5_dms(# type smallint, his5_dms(# py varchar(60), his5_dms(# wb varchar(60), his5_dms(# sc varchar(20), his5_dms(# order_no int, his5_dms(# state smallint, his5_dms(# create_datetime timestamp(6), his5_dms(# create_id bigint, his5_dms(# edit_datetime timestamp(6), his5_dms(# edit_id bigint, his5_dms(# search_path varchar(300), his5_dms(# diagnosis_sort int, his5_dms(# category_name varchar(40), his5_dms(# input_option varchar(40), his5_dms(# category_class smallint, his5_dms(# memo1 varchar(300), his5_dms(# memo2 varchar(300), his5_dms(# other_code varchar(60), his5_dms(# other_name varchar(60), his5_dms(# special_disease_flag smallint his5_dms(# ); CREATE TABLE
2. 把oid文件pdu放到restore库中
[root@xifenfeidg public]# pwd /tmp/pdu/restore/public [root@xifenfeidg public]# ls -l total 7144 -rw-r--r--. 1 root root 7315456 Mar 2 21:04 123456 [root@xifenfeidg public]#
3. 使用add语句在pdu加载数据类型
restore.public=# add 123456 t_xff bigint,bigint,bigint,varchar,varchar,smallint,varchar,varchar,varchar, int,smallint,timestamp,bigint,timestamp,bigint,varchar,int,varchar,varchar, smallint,varchar,varchar,varchar,varchar,smallint; 添加完成,请用\dt;查看可unload的表 restore.public=# \dt; |--------------------------------------------------| | 表名 | 表大小 | |--------------------------------------------------| | t_xff | 6.98 MB | |--------------------------------------------------| 仅显示表大小排名前 1 的表名
4.使用pdu恢复表数据
restore.public=# unload t_xff; 正在解析表 <t_xff>. 已解析数据页: 893, 已解析数据: 46998 条 <t_xff>-<restore/public/123456> 解析完成, 894 个数据页 ,共计 46998 条数据. 成功 46998 条; 失败【0】条 COPY文件路径为:<restore/public/t_xff.csv> restore.public=# unload COPY; COPY命令导出完成, 文件路径: restore/COPY/public_copy.sql
5.导入数据到pg库中
his5_dms=# \i restore/COPY/public_copy.sql SET COPY 46998 his5_dms=# select count(1) from t_xff; count ------- 46998 (1 row) his5_dms=# \x Expanded display is on. his5_dms=# select * from t_xff limit 1; -[ RECORD 1 ]--------+--------------------------- id | 323839 hospital_id | 0 parent_id | 301 disease_code | 57.8900x003 disease_name | 腹腔镜下膀胱颈悬吊术 type | 2 py | fqjxpgjxds wb | eeqgeeceks sc | order_no | 0 state | 1 create_datetime | 2022-09-29 15:22:58.588492 create_id | edit_datetime | edit_id | search_path | 301, diagnosis_sort | category_name | input_option | category_class | 3 memo1 | memo2 | other_code | other_name | special_disease_flag | 0