Re: duplicate key entries for primary key -- need urgent help

On Thu, Jul 04, 2019 at 05:34:21PM +0000, Kumar, Pawan (Nokia - IN/Bangalore) wrote:
>Thanks a lot Tomas for the reply.
>
>Which version are you running, exactly? Whih minor version?
>[Pawan]: Its (PostgreSQL) 9.5.9
>

You're missing 2 years of bugfixes, some of which are addressing data
corruption issues and might have caused this.

>sai=> select version();
>                                                 version
>----------------------------------------------------------------------------------------------------------
> PostgreSQL 9.5.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-11), 64-bit
>(1 row)
>
>Why do you think it's the issue you linked?
>
>[Pawan]: Because the thread which I shared also has problem statement like "Duplicate entries of Primary key" .
>If this is also known to this version, I will be appreciating a lot if we have some Workaround or config change.
>

Duplicate entries are clearly some sort of data corruption, but that
might have happened in various ways - it does not mean it's the same
issue. And yes, 9.5.9 has a fix for the issue in the thread you linked.

>In our production: See below entries, proc_id is primary key and we can see duplicate entries. How it is possible?
>
>sai=> select ctid,proc_id from etl_status where proc_id='2993229';
>   ctid   | proc_id
>----------+---------
> (381,20) | 2993229
> (388,28) | 2993229
>(2 rows)
>
>Any idea, how it happened?
>

No, that's impossible to say without you doing some more investigation.
We need to know when those rows were created, on which version that
happened (the system might have been updated and the corruption predates
might have happened on the previous version), and so on. For example, if
the system crashed or had any significant issues, that might be related
to data corruption issues.

We know nothing about your system, so you'll have to do a bit of
investigation, look for suspicious things, etc.

FWIW it might be a good idea to look for other cases of data corruption.
Both to know the extent of the problem, and to gain insight.

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services