How do I repair a corrupted system table in PostgreSQL?

From: David Hamilton <david(dot)hamilton(at)troocorp(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: How do I repair a corrupted system table in PostgreSQL?
Date: 2011-06-01 13:24:08
Message-ID: BANLkTi=2LX3d01uK+DSMByhTsTRg5x_Fmw@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I just attempted to do a pg_upgrade on a fairly large PostgreSQL database
cluster from version 8.3.0 to version 9.0.4. Everything looked like it was
going to work just fine until the new schema was being created on the target
cluster. It died trying to create a group role twice for some reason.

After looking at all the scripts, it was quite obvious that it was
duplicating a group role 4 times. I brought the 8.3.0 database back up and
it was very apparent that there was a row repeated in the pg_authidtable.

I tried bringing the database up in single-user mode in order to try to REINDEX
TABLE pg_authid. This failed when trying to create the new index with
duplicated values. I tried deleting the offending group role. This removed
one of the 4 rows in pg_authid, but just seemed to confuse things further.

I saw mention that running a full vacuum on the table may repair such
corruption, but I have little hope of that working. So, while the data
restores, I'll fish for ideas.

Browse pgsql-general by date

  From Date Subject
Next Message jlhgis 2011-06-01 14:09:54 Re: troubles with initdb
Previous Message Michal Politowski 2011-06-01 13:06:49 Mixed up protocol packets in server response?