2009/9/24 Mihail Nasedkin <m.nasedkin@gmail.com>:
> Hi.
>
> I accidentally deleted pg_toast record from pg_class table that belongs to
> regular table (reltoastrelid).
>
> Now [select * from table_name] not work:
> ERROR: could not open relation with OID [oid_of_pg_toast_table]
>
> Is it possible to restore corruption?
Self succecc:
1. select oid, reltoastrelid from pg_catalog.pg_class where
relname='my_corrupt_table';
This is OID(step1) and RELTOASTRELID(step1)
2. pg_ctl stop
3. mkdir temp_data_dir
4. initdb temp_data_dir
pg_ctl start -D temp_data_dir
5. createdb temp_db
6. create table temp (with same structure of 'my_corrupt_table');
7. select oid, reltoastrelid from pg_catalog.pg_class where relname='temp';
Check:
RELTOASTRELID(step1) - OID(step1) = RELTOASTRELID(step7) - OID(step7)
Equal, OK.
8. drop table temp;
9. again create table temp (...)
10. select oid, reltoastrelid from pg_catalog.pg_class where relname='temp';
Calculate:
(OID(step1)-OID(step10))/(OID(step10)-OID(step7))
This is count of loops create&drop table.N(step10)
11. After N(step10) times create&drop temp table
select oid, reltoastrelid from pg_catalog.pg_class where relname='temp';
Check
OID(step11)=OID(step1) and RELTOASTRELID(step11)=RELTOASTRELID(step1)
Equal, Ok.
12. pg_ctl stop
13. Replace files with names OID(step1) and RELTOASTRELID(step1) in
the temp_data_dir
with same files from my work_data_dir
14. pg_ctl start -D temp_data_dir
15. reindex table temp;
16. dump table temp;
17. pg_ctl stop;
18. pg_ctl start -D work_data_dir
19. restore table temp
.....
.....
.....
--
---
Regards, unsubscribe now
m.nasedkin