Re: duplicate key value violates unique constraint and duplicated records

[Steven Chang <stevenchang1213@xxxxxxxxx>]

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.klaver@xxxxxxxxxxx>:

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.klaver@xxxxxxxxxxx <mailto:adrian.klaver@aklaver.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.klaver@xxxxxxxxxxx <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx