Re: duplicate key value violates unique constraint and duplicated records

Hello :

PG VERSION : PPAS 9.3 , enterprisedb
os version : 2.6.32-358.el6.x86_64

pg_basebackup job was not performed by me. But I think it was executed
regularly.
Any switch or parameter would cause this issue ???

Why I don't think not a index curruption issue ?
1. I found this document :

https://www.postgresql.org/message-id/20140811083748.2536.10437%40wrigleys.postgresql.org
2. Return only on row if query data using where equal condition, but
got 2 rows by like condition

Steven

2017-07-01 22:05 GMT+08:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>:

> On 06/30/2017 09:42 PM, Steven Chang wrote:
>
>> Uh...we also met duplicate rows with primary key column through
>> restoring database by pg_basebackup.
>> HAAAA.........................
>> I don't think its an issue with primary key index corruption.
>>
>
> That is interesting, more information would be helpful though:
>
> Postgres version?
>
> OS and version?
>
> The pg_basebackup command line invocation?
>
> Why you don't think it is index corruption?
>
>
>
>>
>>
>> 2017-07-01 7:30 GMT+08:00 Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
>> <mailto:adrian(dot)klaver(at)aklaver(dot)com>>:
>>
>> On 06/30/2017 07:33 AM, Timokhin Maxim wrote:
>>
>> Sure, here it is.
>>
>> pg_basebackup -h servername -R -P -D /data/upgrade/94 -U pgsql
>> -v —xlog-method=stream —checkpoint=fast
>>
>> /usr/pgsql-9.5/bin/initdb -D /data/upgrade/95/ —encoding=utf8
>> —locale=ru_RU.utf8 —lc-collate=ru_RU.utf8 —lc-ctype=ru_RU.utf8
>> —lc-messages=en_US.utf8
>>
>> Then updating:
>> /usr/pgsql-9.5/bin/pg_upgrade -b /usr/pgsql-9.4/bin/ -d
>> /data/upgrade/94 -B /usr/pgsql-9.5/bin/ -D /data/upgrade/95 -k
>>
>> and so on to 9.6
>>
>>
>> The original 9.4 database has the same encoding setup?
>>
>> FYI, you can use pg_upgrade to go straight from 9.4 to 9.6.
>>
>> https://www.postgresql.org/docs/9.6/static/pgupgrade.html
>> <https://www.postgresql.org/docs/9.6/static/pgupgrade.html>
>>
>> "pg_upgrade supports upgrades from 8.4.X and later to the current
>> major release of PostgreSQL, including snapshot and alpha releases."
>>
>>
>>
>> after that server starts normally.
>>
>>
>> -- Timokhin 'maf' Maxim
>>
>>
>>
>>
>>
>> -- Adrian Klaver
>> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>>
>>
>>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com
>