Re: duplicate key value violates unique constraint and duplicated records

On Sat, Jul 1, 2017 at 10:05 AM, Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>
wrote:

> 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
>

>I don't think its an issue with primary key index corruption.

Well, have you verified that? Try running the following query and make sure
the status column shows "valid" for ALL indexes.

SELECT n.nspname as schema,
i.relname as table,
i.indexrelname as index,
i.idx_scan,
i.idx_tup_read,
i.idx_tup_fetch,
CASE WHEN idx.indisprimary THEN 'pkey'
WHEN idx.indisunique THEN 'uidx'
ELSE 'idx'
END AS type,
idx.indisexclusion,
pg_get_indexdef(idx.indexrelid),
CASE WHEN idx.indisvalid THEN 'valid'
ELSE 'INVALID'
END as statusi,
pg_relation_size(quote_ident(n.nspname)|| '.' ||
quote_ident(i.relname)) as size_in_bytes,
pg_size_pretty(pg_relation_size(quote_ident(n.nspname)|| '.' ||
quote_ident(i.relname))) as size
FROM pg_stat_all_indexes i
JOIN pg_class c ON (c.oid = i.relid)
JOIN pg_namespace n ON (n.oid = c.relnamespace)
JOIN pg_index idx ON (idx.indexrelid = i.indexrelid )
WHERE i.relname LIKE '%%'
AND n.nspname NOT LIKE 'pg_%'
ORDER BY 1, 2, 3;
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.