Fixing a complex corruption in PostgreSQL

Since many years, we have been using PostgreSQL as the backend to our DNA Analysis solution. And very successfully so; we now have approximately 10TB of statistics in almost 500 schema's.

In order to backup a particular schema or table, we use pg_dump. However, this week it started to produce a nasty error, see below:


THE PROBLEM:
C:\Program Files\PostgreSQL\8.3\bin
>pg_dump -f c:\tmp\out.back -i -F p -v -a -h d2host -t ibmmat0001_sta.sta_db d2dna
pg_dump: reading schemas
pg_dump: reading user-defined functions
pg_dump: reading user-defined types
pg_dump: schema with OID 2613175388 does not exist
pg_dump: *** aborted because of error

With the help of google, I soon discovered we may have a corruption in one of the vital tables: pg_class.
Analyzing the problem, I first identified which tableor schema we have an issue with:
executing "select oid, * from pg_class where oid = 2613175388" shows we're dealing with a table named ls_tmp1. I know we have some of these tables in several schema's.

Next, I executed "select * from pg_tables where tablename = 'ls_tmp1'". The result is shown in the screendump below:

Notice how the troubled table does not belong to a schema (it should!). Hence the trouble reported in the pg_dump.

THE SOLUTION:
In order to repair this corruption, I created a new schema named "for_corrupt_table."
I then looked up the OID for this newly created schema.
create schema for_corrupt_table;  -- its oid became: 2613195672
Next I updated the relnamespace column to make this troubled table point to the new schema:
update pg_class set relnamespace = 2613195672 where relnamespace = 2613175388

Refreshing my pgadmin window revealed the new schema and the existing table.