【转】一次系统表的损坏恢复案例

http://www.pgsql.tech/article_107_10000056

1. 故障背景

因一些特殊的原因导致系统表pg_authid表中的内容被删除，过后用户自己恢复了所有的User，但是OID是系统生成的已经与原来的不一样，需要修复，用户联系到 乘数科技，然后我们对此情况进行了恢复。

2. 处理方式

由于系统表中OID全部都是原User OID与新User OID对不上，如果将用户表对应的用户的User OID全部更新为新的User OID工作量比较大，所以选择根据原User OID 重建pg_authid表。

由于是系统表，不能直接更新，所以我们创建一张与pg_authid完全相同的中间表my_authid，把这张表的内容换做成原先User OID的值，然后我们关闭数据库，然后用这张中间表的数据文件覆盖系统表pg_authid来完成User OID的修复。

2.1 具体步骤

2.1.1 找出原User OID对应关系

由于系统目前状况psql中使用\l 或者\d 我们看到的Owner都会是Unknow状态，并且会显示出原User的OID，让客户配合梳理出这些对象对应的用户则可以得出原User OID对应关系:

• 原User OID对应关系

1. 16384 | u01
2. 24824936 | u02
3. 3373 | pg_monitor
4. 3374 | pg_read_all_settings
5. 3375 | pg_read_all_stats
6. 3377 | pg_stat_scan_tables
7. 4200 | pg_signal_backend
8. 10 | postgres

• 新User OID对应关系
  1. postgres=# select oid , rolname from pg_authid;
  2. oid | rolname
  3. ----------+----------------------
  4. 54036442 | pg_monitor
  5. 54036443 | pg_read_all_settings
  6. 54036444 | pg_read_all_stats
  7. 54036445 | pg_stat_scan_tables
  8. 54036446 | pg_signal_backend
  9. 54036447 | u01
  10. 54036448 | rep
  11. 54036449 | u02
  12. 54036441 | postgres

2.1.2 创建一张中间表(my_authid)

首先查看pg_authid表相关信息:

1. postgres=# SELECT pg_relation_filepath('pg_authid');
2. pg_relation_filepath
3. ----------------------
4. global/1260
5. (1 row)
6.  
7. postgres=# \d pg_authid
8. Table "pg_catalog.pg_authid"
9. Column | Type | Collation | Nullable | Default
10. ----------------+--------------------------+-----------+----------+---------
11. rolname | name | | not null |
12. rolsuper | boolean | | not null |
13. rolinherit | boolean | | not null |
14. rolcreaterole | boolean | | not null |
15. rolcreatedb | boolean | | not null |
16. rolcanlogin | boolean | | not null |
17. rolreplication | boolean | | not null |
18. rolbypassrls | boolean | | not null |
19. rolconnlimit | integer | | not null |
20. rolpassword | text | | |
21. rolvaliduntil | timestamp with time zone | | |
22. Indexes:
23. "pg_authid_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
24. "pg_authid_rolname_index" UNIQUE, btree (rolname), tablespace "pg_global"
25. Tablespace: "pg_global"
26.  
27.  
28. postgres=# SELECT pg_relation_filepath('pg_authid_oid_index');
29. pg_relation_filepath
30. ----------------------
31. global/2677
32. (1 row)
33.  
34. postgres=# SELECT pg_relation_filepath('pg_authid_rolname_index');
35. pg_relation_filepath
36. ----------------------
37. global/2676
38. (1 row)
39.  
40.  
41. postgres=# \d+ pg_authid_oid_index
42. Index "pg_catalog.pg_authid_oid_index"
43. Column | Type | Definition | Storage
44. --------+------+------------+---------
45. oid | oid | oid | plain
46. unique, btree, for table "pg_catalog.pg_authid"
47. Tablespace: "pg_global"
48.  
49. postgres=# \d+ pg_authid_rolname_index
50. Index "pg_catalog.pg_authid_rolname_index"
51. Column | Type | Definition | Storage
52. ---------+---------+------------+---------
53. rolname | cstring | rolname | plain
54. unique, btree, for table "pg_catalog.pg_authid"
55. Tablespace: "pg_global"

• 将表中数据导出并创建my_authid表

1. copy pg_authid to '/pgsql/data/backup/pg_authid.txt' with (oids);
2. create table my_authid(like pg_authid) with oids;
3. create unique index my_authid_oid_index on my_authid(oid);
4. create unique index my_authid_rolname_index on my_authid(rolname);

• 导数据到my_authid表中

导入之前我们要编辑pg_authid.txt文件将对应的OID修改为原User OID对应的关系。另超级用户postgres的新OID为54036441，我们把原先postgres用户的那一行的用户名改成postgres2，然后再为postgres用户新加一行，这行数据的User OID 为10，其他客户建的业务用户的OID都改成原先的User OID，改完后，我们把文本的数据导入到my_authid中：

1. copy my_authid from '/pgsql/data/backup/pg_authid.txt' with (oids);
2. VACUUM FULL FREEZE VERBOSE my_authid;
3. vacuum my_authid;

• 查看my_authid相关信息

1. SELECT pg_relation_filepath('my_authid'), pg_relation_filepath('my_authid_oid_index'), pg_relation_filepath('my_authid_rolname_index');
2.  
3. postgres=# SELECT pg_relation_filepath('my_authid'), pg_relation_filepath('my_authid_oid_index'), pg_relation_filepath('my_authid_rolname_index');
4. pg_relation_filepath | pg_relation_filepath | pg_relation_filepath
5. ----------------------+----------------------+----------------------
6. base/13806/54036458 | base/13806/54036464 | base/13806/54036465
7. (1 row)

2.1.3 关闭数据库替换pg_authid表对应的物理文件

操作步骤：

• 关闭数据库
• 替换pg_authid表对应的物理文件

1. //整理出pg_authid表及索引与my_authid表物理文件对应管理
2. global/1260 => base/13806/54036458
3. global/2677 => base/13806/54036464
4. global/2676 => base/13806/54036465
5.  
6. //备份原pg_authid表及索引文件
7. mkdir backup
8. cp global/1260* ./backup/.
9. cp global/2677* ./backup/.
10. cp global/2676* ./backup/.
11.  
12. //将my_authid表物理文件及索引拷贝覆盖原pg_authid对应的文件及索引
13. cp base/13806/54036458 global/1260
14. cp base/13806/54036458_fsm global/1260_fsm
15. cp base/13806/54036458_vm global/1260_vm
16.  
17. cp base/13806/54036464 global/2677
18. cp base/13806/54036465 global/2676

• 检查观察文件时间及对比文件内容是否发生变化

1. ls -l global/1260*
2. ls -l backup/1260*
3. ls -l global/2677*
4. ls -l backup/2677*
5. ls -l global/2676*
6. ls -l backup/2676*
7.  
8. cmp global/1260 backup/1260
9. cmp global/2677 backup/2677
10. cmp global/2676 backup/2676

• 删除系统表cache文件

1. find . -name "pg_internal.init*"
2. find . -name "pg_internal.init*" |xargs rm

2.1.4 启动数据库

启动数据库查看数据库及表的owner是否正常，发现不再是Unknow状态，进一步检查所有的函数、视图等等，发现这些对象的owner都正常，然后让用户再仔细检查一下，用户反馈都正常，自此恢复结束。