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

Hi,

our current version both on master and slave is
PostgreSQL 9.1.3 on x86_64-pc-linux-gnu, compiled by gcc-4.4.real (Debian
4.4.5-8) 4.4.5, 64-bit

But as i remember, we start streaming replication on 9.1.2 and then upgrade
to 9.1.3. My ops now on vacations, and we will make standby resync
on Monday, and I'll check, if problem solved.

Thanx you for answer.

On Fri, Apr 13, 2012 at 00:48, Merlin Moncure <mmoncure@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.

On Fri, Apr 13, 2012 at 00:48, Merlin Moncure <mmoncure@gmail.com> wrote:

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