Re: [GENERAL] duplicate key value violates unique constraint andduplicated records - Mailing list pgsql-general
On Fri, Jun 30, 2017 at 9:07 AM, Adrian Klaver <adrian.klaver@aklaver.com> wrote:
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
Since you are doing a major version upgrade, the correct way to do that, depending on the size of your DB, is
A. pg_dumpall on old version and pg_reload on new version
OR
B. pg_upgrade
https://www.postgresql.org/docs/9.6/static/upgrading.html
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
pgsql-general by date: