Thread: pg_toast record in table pg_class
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?
--
---
Regards,
M.Nasedkin
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?
--
---
Regards,
M.Nasedkin
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