Re: [GENERAL] duplicate key value violates unique constraint andduplicated records

On 06/30/2017 04:58 AM, Timokhin Maxim wrote:

BTW, we are moving using:

pg_basebackup -h servername -R -P -D /data/upgrade/94 -U pgsql -v —xlog-method=stream —checkpoint=fast

Going from 9.4 to 9.6 is a major version upgrade and you cannot use pg_basebackup for that. Besides I can't see how you even got the 9.6.3 server to start:

/usr/local/pgsql94/bin/pg_basebackup -D /home/aklaver/pgback_test94 -U postgres -p 5412 -v --xlog-method=stream

/usr/local/pgsql96/bin/pg_ctl -D /home/aklaver/pgback_test94/ start
server starting
FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 9.4, which is not compatible with this version 9.6.3

After that we are upping version to 9.6.3.

Given the above how did you actually get 9.6.3 to start?

I've looked through the documentation https://postgrespro.ru/docs/postgrespro/9.6/app-pgbasebackup and didn't find details about how pg_basebackup works with b-tree indexes.
Is it possible that pg_basebackup just copies indexes as is and that is cause of corruption. Or it pass indexes as instruction that says "after upping db make indexes" ?

Thank you.


--
Timokhin 'maf' Maxim

--
Adrian Klaver
adrian.klaver@aklaver.com