Re: Two entries with the same primary key

On Thu, Apr 12, 2012 at 9:20 AM, Ivan Evtuhovich <evtuhovich(at)gmail(dot)com> wrote:
> Hello,
>
> More then month ago we upgrade DB from 9.0 to 9.1 with pg_upgrade. Then we
> move DB to another server with standard pg streaming replication.
>
> Now we have two entries with the same primary key. And I do not know what to
> do.
>
> SELECT ctid, id from billing_invoices where id = 27362891;
>
> ctid | id
> --------------+----------
> (1112690,11) | 27362891
> (1112438,26) | 27362891
>
>
>
> \d billing_invoices
> Table "public.billing_invoices"
> Column | Type |
> Modifiers
>
> ----------------+-----------------------------+---------------------------------------------------------------
> id | integer | not null default
> nextval('billing_invoices_id_seq'::regclass)
>
> ...
> created_at | timestamp without time zone |
> updated_at | timestamp without time zone |
> Indexes:
> "billing_invoices_pkey" PRIMARY KEY, btree (id)

well, the first step is to determine the extent of the damage. we need
to get the database to the point where it can load from a standard
backup, and we need to have the database loaded into a testbed where
we can stitch together the corrections you are going to apply to the
production system. this is probably going to involve a schema level
dump, a custom format data dump, and some trial and error to see which
tables are busted (for each one, dropping the keys, restoring the
data, fixing the data, and restoring the keys, etc). take notes of
everything you fix so that corrections can be back applied to your
production system.

a full file system level backup also couldn't hurt so that the
evidence trail pointing to how this happened isn't destroyed.

one the database is fixed and internally consistent, hopefully we can
figure out how this happened. I don't see anything glaring in the
9.1 release notes that points to a fixed pg_upgrade bug that matches
your behavior, so it's not a given that pg_upgrade actually caused the
issue. do you have a database dump around time time you upgraded
(ideally, both before and after?) did you preserve the pre-upgrade
database cluster?

merlin