From: | David Garamond <lists(at)zara(dot)6(dot)isreserved(dot)com> |
---|---|
To: | pgsql-general <pgsql-general(at)postgresql(dot)org> |
Subject: | duplicate key violates unique constraint "pg_class_oid_index" |
Date: | 2004-02-07 11:30:12 |
Message-ID: | 4024CC44.8040802@zara.6.isreserved.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
postgresql 7.4.0, redhat 7.3 (under vmware 4.0 on win2k)
Windows crashed and some of the files on Redhat got corrupted, including
some files in /var/lib/pgsql/data/pg_xlog/. When I tried to start
postmaster, it fails with message "Invalid primary checkPoint record". I
think it was trying to look for files named "0000000000000021" but only
files named "0000000000000022" through "0000000000000027" were there. So
I ran pg_resetxlog (this is purely experimental installation so I can
destroy and recreate the database). After reset, pg_xlog/ ends up having
only "0000000000000023".
Now, before the crash, I had created some tables like t, t2, and t3. The
last activities I think were importing contrib/tablefunc.sql and
creating +- 10k records to t3 from a Perl script in the form of some
hundreds of transactions (all already committed). After the db is back
up, I see (with \d in psql) only t.
When I want to recreate treeadj1, postgres complains with this message:
duplicate key violates unique constraint "pg_class_oid_index"
And I see in the pg_class table there are t2 and t3 entries.
Next I tried to do pg_dump because obviously the database is
inconsistent. pg_dump fails with this message:
pg_dump: attempt to lock table "t3" failed: ERROR: relation
"public.t3" does not exist
The question: what is the best/safest way to deal with this kind of
situation:
1) mess with pg_class and possibly other system tables to fix the
inconsistencies (How? I'm currently clueless at this :-)
2) do dump with -t to only dump existing tables (I tried this once,
pg_restore fails with this message:
input file does not appear to be a valid archive (too short?)
I'm wild-guessing this is because the t table is empty. Dump file attached.)
3) restore from last backup (I'll lose more recent data).
--
dave
Attachment | Content-Type | Size |
---|---|---|
db1.dump | text/plain | 368 bytes |
From | Date | Subject | |
---|---|---|---|
Next Message | Peter Eisentraut | 2004-02-07 14:16:13 | Re: Extract transaction logging |
Previous Message | Christoffer Gurell | 2004-02-07 11:00:00 | Re: retrieving parts of a resultset |