Обсуждение: Two entries with the same primary key

On Thu, Apr 12, 2012 at 9:20 AM, Ivan Evtuhovich <evtuhovich@gmail.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

On Thu, Apr 12, 2012 at 9:20 AM, Ivan Evtuhovich <evtuhovich@gmail.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
>

Per some off-list conversation with Ivan, this is only happening on
the standby.  Ivan, what's the precise version of postgres you are
using?  When you first went to hs/sr?  I bet your clog files are out
of whack (and if so, probably upgrading to recent bugfix postgres and
standby resync is the correct course of action).

merlin

On Fri, Apr 13, 2012 at 7:36 AM, Ivan Evtuhovich <evtuhovich@gmail.com> wrote:
> Hello Merlin,
>
> we've resynced slave and now everything is OK, thanks you for help.
>
> And only one last question, where to read about this bug, because
> my colleges  want to know, what happens.

there are several standby related issues fixed: see release notes here:
http://www.postgresql.org/docs/9.1/interactive/release.html

for what I was thinking might have got you (which was just a wild
guess and may have nothing to do with your actual issue -- just did a
quick re-read), google the thread "Hot Backup with rsync fails at
pg_clog if under load"

merlin