--00032555a49637e4cb04744c5529
Content-Type: text/plain; charset=UTF-8

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

--00032555a49637e4cb04744c5529
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

Hi.<br><br>I accidentally deleted pg_toast record from pg_class table that =
belongs to regular table (reltoastrelid).<br><br>Now [select * from table_n=
ame] not work:<br>ERROR:=C2=A0 could not open relation with OID [oid_of_pg_=
toast_table]<br>
<br>Is it possible to restore corruption?<br><br clear=3D"all"><br>-- <br>-=
--<br>Regards,<br>M.Nasedkin<br>

--00032555a49637e4cb04744c5529--

2009/9/24 Mihail Nasedkin <m.nasedkin [at] 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:=C2=A0 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=3D'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=3D'temp=
';
Check:
RELTOASTRELID(step1) - OID(step1) =3D 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=3D'tem=
p';
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=3D'temp';
Check
OID(step11)=3DOID(step1) and RELTOASTRELID(step11)=3DRELTOASTRELID(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

--
Sent via pgsql-admin mailing list (pgsql-admin [at] postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-admin