Skip site navigation (1) Skip section navigation (2)

Re: pg_toast record in table pg_class

From: Mihail Nasedkin <m(dot)nasedkin(at)gmail(dot)com>
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: pg_toast record in table pg_class
Date: 2009-09-25 02:58:55
Message-ID: 2a6880830909241958v292cc373r565bb45437118f9e@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-general
2009/9/24 Mihail Nasedkin <m(dot)nasedkin(at)gmail(dot)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

In response to

pgsql-admin by date

Next:From: Peter EisentrautDate: 2009-09-25 15:46:25
Subject: Re: recovery is stuck when children are not processing SIGQUIT from previous crash
Previous:From: Isabella GhiureaDate: 2009-09-24 20:26:49
Subject: db size and tables size difference

pgsql-general by date

Next:From: Grant MaxwellDate: 2009-09-25 03:12:23
Subject: Re: Looking for way to replicate master db to multiple mobile databases
Previous:From: Selena DeckelmannDate: 2009-09-25 02:50:30
Subject: Re: Looking for way to replicate master db to multiple mobile databases

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group