PG中恢复系统表pg_authid案例

客户误操作将系统表pg_authid表删除,过后自己恢复了所有的User,但是OID是系统生成的已经与原来的不一样,需要修复

处理方式

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

具体步骤

找出原User OID对应关系

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

  • 原User OID对应关系
16384     | mintq
24824936  | xiangqd
3373      | pg_monitor
3374      | pg_read_all_settings
3375      | pg_read_all_stats
3377      | pg_stat_scan_tables
4200      | pg_signal_backend
10        | postgres
  • 新User OID对应关系
postgres=# select oid , rolname from pg_authid;
   oid    |       rolname
----------+----------------------
 54036442 | pg_monitor
 54036443 | pg_read_all_settings
 54036444 | pg_read_all_stats
 54036445 | pg_stat_scan_tables
 54036446 | pg_signal_backend
 54036447 | mintq
 54036448 | rep
 54036449 | xiangqd
 54036441 | postgres

创建一张中间表(my_authid)

  • 首先查看pg_authid表相关信息
postgres=# SELECT pg_relation_filepath('pg_authid');
 pg_relation_filepath
----------------------
 global/1260
(1 row)

postgres=# \d pg_authid
                        Table "pg_catalog.pg_authid"
     Column     |           Type           | Collation | Nullable | Default
----------------+--------------------------+-----------+----------+---------
 rolname        | name                     |           | not null |
 rolsuper       | boolean                  |           | not null |
 rolinherit     | boolean                  |           | not null |
 rolcreaterole  | boolean                  |           | not null |
 rolcreatedb    | boolean                  |           | not null |
 rolcanlogin    | boolean                  |           | not null |
 rolreplication | boolean                  |           | not null |
 rolbypassrls   | boolean                  |           | not null |
 rolconnlimit   | integer                  |           | not null |
 rolpassword    | text                     |           |          |
 rolvaliduntil  | timestamp with time zone |           |          |
Indexes:
    "pg_authid_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_authid_rolname_index" UNIQUE, btree (rolname), tablespace "pg_global"
Tablespace: "pg_global"


postgres=# SELECT pg_relation_filepath('pg_authid_oid_index');
 pg_relation_filepath
----------------------
 global/2677
(1 row)

postgres=# SELECT pg_relation_filepath('pg_authid_rolname_index');
 pg_relation_filepath
----------------------
 global/2676
(1 row)


postgres=# \d+ pg_authid_oid_index
Index "pg_catalog.pg_authid_oid_index"
 Column | Type | Definition | Storage
--------+------+------------+---------
 oid    | oid  | oid        | plain
unique, btree, for table "pg_catalog.pg_authid"
Tablespace: "pg_global"

postgres=# \d+ pg_authid_rolname_index
Index "pg_catalog.pg_authid_rolname_index"
 Column  |  Type   | Definition | Storage
---------+---------+------------+---------
 rolname | cstring | rolname    | plain
unique, btree, for table "pg_catalog.pg_authid"
Tablespace: "pg_global"
  • 将表中数据导出并创建my_authid表
copy pg_authid to '/pgsql/data/backup/pg_authid.txt' with (oids);

create table my_authid(like pg_authid) with oids;

create unique index my_authid_oid_index on my_authid(oid);
create unique index my_authid_rolname_index on my_authid(rolname);
  • 导数数据到my_authid表中
    导入之前我们要编辑pg_authid.txt文件将对应的OID修改为原User OID对应的关系,这里postgres用户我们新添加一行 (原User OID 也就是10),并将文件中原postgres用户名改为postgres2
copy my_authid from '/pgsql/data/backup/pg_authid.txt' with (oids);
VACUUM FULL FREEZE VERBOSE my_authid;
vacuum my_authid;
  • 查看my_authid相关信息
SELECT pg_relation_filepath('my_authid'), pg_relation_filepath('my_authid_oid_index'), pg_relation_filepath('my_authid_rolname_index');

postgres=# SELECT pg_relation_filepath('my_authid'), pg_relation_filepath('my_authid_oid_index'), pg_relation_filepath('my_authid_rolname_index');
 pg_relation_filepath | pg_relation_filepath | pg_relation_filepath
----------------------+----------------------+----------------------
 base/13806/54036458  | base/13806/54036464  | base/13806/54036465
(1 row)

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

  • 关闭数据
  • 替换pg_authid表对应的物理文件
//整理出pg_authid表及索引与my_authid表物理文件对应管理
global/1260  => base/13806/54036458 
global/2677  => base/13806/54036464
global/2676  => base/13806/54036465

//备份原pg_authid表及索引文件
mkdir backup
cp global/1260* ./backup/.
cp global/2677* ./backup/.
cp global/2676* ./backup/.

//将my_authid表物理文件及索引拷贝覆盖原pg_authid对应的文件及索引
cp base/13806/54036458 global/1260
cp base/13806/54036458_fsm global/1260_fsm
cp base/13806/54036458_vm global/1260_vm

cp base/13806/54036464 global/2677
cp base/13806/54036465 global/2676 
  • 检查观察文件时间及对比文件内容是否发生变化
ls -l global/1260*
ls -l backup/1260*
ls -l global/2677*
ls -l backup/2677*
ls -l global/2676*
ls -l backup/2676*

cmp global/1260 backup/1260
cmp global/2677 backup/2677
cmp global/2676 backup/2676
  • 删除系统表cache文件
find . -name "pg_internal.init*"
find . -name "pg_internal.init*" |xargs rm

####启动数据库
启动数据库查看数据库及表的owner是否正常,不在是Unknow状态


免责声明:

1、本站资源由自动抓取工具收集整理于网络。

2、本站不承担由于内容的合法性及真实性所引起的一切争议和法律责任。

3、电子书、小说等仅供网友预览使用,书籍版权归作者或出版社所有。

4、如作者、出版社认为资源涉及侵权,请联系本站,本站将在收到通知书后尽快删除您认为侵权的作品。

5、如果您喜欢本资源,请您支持作者,购买正版内容。

6、资源失效,请下方留言,欢迎分享资源链接

文章评论

0条评论