联系：手机/微信(+86 17813235971) QQ(107644445)
标题：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=#
