Search Postgresql Archives

duplicate key violates unique constraint "pg_class_oid_index"

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

--
-- PostgreSQL database dump
--

SET SESSION AUTHORIZATION 'dave';

SET search_path = public, pg_catalog;

--
-- TOC entry 2 (OID 1466918)
-- Name: t; Type: TABLE; Schema: public; Owner: dave
--

CREATE TABLE t (
    i integer
);


--
-- Data for TOC entry 3 (OID 1466918)
-- Name: t; Type: TABLE DATA; Schema: public; Owner: dave
--

COPY t2b (i) FROM stdin;
\.



---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux