Thread: [GENERAL] duplicate key value violates unique constraint and duplicated records

[GENERAL] duplicate key value violates unique constraint and duplicated records

From

Timokhin Maxim

Date:

29 June 2017, 15:28:59

Hello.

We are in process moving to new db from 9.4.8 -> 9.6.3.1. When we did it our application started to throw exception "duplicate key value violates unique constraint" during doing INSERT:

INSERT INTO items (ctime, mtime, pubdate, url, title, description, body, status, fulltext_status, orig_id, image_id, video_id, resource_id, priority, checksum) VALUES (%(ctime)s, %(mtime)s, %(pubdate)s, %(url)s, %(title)s, %(description)s, %(body)s, %(status)s, %(fulltext_status)s, %(orig_id)s, %(image_id)s, %(video_id)s, %(resource_id)s, %(priority)s, %(checksum)s) RETURNING items.id'

Column url has unique constraint.

Also, we saw that during to update value into column status:

(psycopg2.IntegrityError) duplicate key value violates unique constraint "items_url"

DETAIL: Key (url)=(http://www.domainname.ru/ap_module/content/article/400-professional/140-professional/11880) already exists.

[SQL: 'UPDATE items SET status=%(status)s WHERE items.id IN ( ... )...

Our table:

Column | Type | Modifiers

-----------------+-----------------------------+-------------------------------------------------------------------

id | integer | not null default nextval(('public.items_id_seq'::text)::regclass)

ctime | timestamp without time zone | not null default now()

pubdate | timestamp without time zone | not null default now()

resource_id | integer | not null default 0

url | text |

title | text |

description | text |

body | text |

status | smallint | not null default 0

image | text |

orig_id | integer | not null default 0

mtime | timestamp without time zone | not null default now()

checksum | text |

video_url | text |

audio_url | text |

content_type | smallint | default 0

author | text |

video | text |

fulltext_status | smallint | default 0

summary | text |

image_id | integer |

video_id | integer |

priority | smallint |

Indexes:

"items_pkey" PRIMARY KEY, btree (id)

"items_url" UNIQUE, btree (url)

"items_resource_id" btree (resource_id)

"ndx__items__ctime" btree (ctime)

"ndx__items__image" btree (image_id)

"ndx__items__mtime" btree (mtime)

"ndx__items__pubdate" btree (pubdate)

"ndx__items__video" btree (video_id)

Foreign-key constraints:

"items_fkey1" FOREIGN KEY (image_id) REFERENCES images(id) ON UPDATE CASCADE ON DELETE SET NULL

"items_fkey2" FOREIGN KEY (video_id) REFERENCES videos(id) ON UPDATE CASCADE ON DELETE SET NULL

Referenced by:

TABLE "cluster_image" CONSTRAINT "cluster_image_fkey2" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE

TABLE "cluster_meta" CONSTRAINT "cluster_meta_item_id_fkey" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE

TABLE "events" CONSTRAINT "events_fkey2" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE

TABLE "item_cluster" CONSTRAINT "item_cluster_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE

TABLE "items_blogs" CONSTRAINT "items_blogs_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE

TABLE "items_reflink" CONSTRAINT "items_reflink_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE

TABLE "items_related" CONSTRAINT "items_related_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE

TABLE "items_summaries" CONSTRAINT "items_summaries_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE

TABLE "quotations" CONSTRAINT "quotations_fkey3" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE

TABLE "summaries" CONSTRAINT "summaries_fkey2" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE

Everything would be not bad if in the table weren't appeared duplicated records in url column.

Any idea how is it possible?

Thank you!

--
Timokhin 'maf' Maxim

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

From

Melvin Davidson

Date:

30 June 2017, 03:21:18

On Thu, Jun 29, 2017 at 5:28 AM, Timokhin Maxim <ncx2@yandex.com> wrote:

Hello.
We are in process moving to new db from 9.4.8 -> 9.6.3.1. When we did it our application started to throw exception "duplicate key value violates unique constraint" during doing INSERT:

INSERT INTO items (ctime, mtime, pubdate, url, title, description, body, status, fulltext_status, orig_id, image_id, video_id, resource_id, priority, checksum) VALUES (%(ctime)s, %(mtime)s, %(pubdate)s, %(url)s, %(title)s, %(description)s, %(body)s, %(status)s, %(fulltext_status)s, %(orig_id)s, %(image_id)s, %(video_id)s, %(resource_id)s, %(priority)s, %(checksum)s) RETURNING items.id'

Column url has unique constraint.

Also, we saw that during to update value into column status:
(psycopg2.IntegrityError) duplicate key value violates unique constraint "items_url"
DETAIL: Key (url)=(http://www.domainname.ru/ap_module/content/article/400-professional/140-professional/11880) already exists.
[SQL: 'UPDATE items SET status=%(status)s WHERE items.id IN ( ... )...

Our table:

Column | Type | Modifiers
-----------------+-----------------------------+-------------------------------------------------------------------
id | integer | not null default nextval(('public.items_id_seq'::text)::regclass)
ctime | timestamp without time zone | not null default now()
pubdate | timestamp without time zone | not null default now()
resource_id | integer | not null default 0
url | text |
title | text |
description | text |
body | text |
status | smallint | not null default 0
image | text |
orig_id | integer | not null default 0
mtime | timestamp without time zone | not null default now()
checksum | text |
video_url | text |
audio_url | text |
content_type | smallint | default 0
author | text |
video | text |
fulltext_status | smallint | default 0
summary | text |
image_id | integer |
video_id | integer |
priority | smallint |
Indexes:
"items_pkey" PRIMARY KEY, btree (id)
"items_url" UNIQUE, btree (url)
"items_resource_id" btree (resource_id)
"ndx__items__ctime" btree (ctime)
"ndx__items__image" btree (image_id)
"ndx__items__mtime" btree (mtime)
"ndx__items__pubdate" btree (pubdate)
"ndx__items__video" btree (video_id)
Foreign-key constraints:
"items_fkey1" FOREIGN KEY (image_id) REFERENCES images(id) ON UPDATE CASCADE ON DELETE SET NULL
"items_fkey2" FOREIGN KEY (video_id) REFERENCES videos(id) ON UPDATE CASCADE ON DELETE SET NULL
Referenced by:
TABLE "cluster_image" CONSTRAINT "cluster_image_fkey2" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "cluster_meta" CONSTRAINT "cluster_meta_item_id_fkey" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "events" CONSTRAINT "events_fkey2" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "item_cluster" CONSTRAINT "item_cluster_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "items_blogs" CONSTRAINT "items_blogs_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "items_reflink" CONSTRAINT "items_reflink_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "items_related" CONSTRAINT "items_related_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "items_summaries" CONSTRAINT "items_summaries_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "quotations" CONSTRAINT "quotations_fkey3" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "summaries" CONSTRAINT "summaries_fkey2" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE

Everything would be not bad if in the table weren't appeared duplicated records in url column.
Any idea how is it possible?

Thank you!

--
Timokhin 'maf' Maxim

It's possible you have index corruption on 9.4.8 version that was not detected.

Try the following query on 9.4.8 to see if any rows are selected. Then you can decide
how to fix from there.

SELECT a.id, a.url,
       b.id, b.url
FROM items a,
       items b
WHERE a.id <> b.id
   AND a.url = b.url
ORDER by a.id;

--

Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

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

From

Adrian Klaver

Date:

30 June 2017, 05:38:19

On 06/29/2017 02:28 AM, Timokhin Maxim wrote:
> Hello.
> We are in process moving to new db from 9.4.8 -> 9.6.3.1. When we did it
> our application started to throw exception "duplicate key value violates
> unique constraint" during doing INSERT:

How did move from 9.4.8 --> 9.6.3.1?

Also where are you getting Postgres from? I ask because 9.6.3.1 is not
a community version or was that just a typo?



> Everything would be not bad if in the table weren't appeared duplicated
> records in url column.
> Any idea how is it possible?
> Thank you!
> --
> Timokhin 'maf' Maxim


--
Adrian Klaver
adrian.klaver@aklaver.com

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

From

Steven Chang

Date:

30 June 2017, 07:07:17

Interesting!! We also met the same situation on PK running on PPAS 9.0 last night.

When surfing Internet, got returned this URL :

https://www.postgresql.org/message-id/20140811083748.2536.10437%40wrigleys.postgresql.org

You can check the reply.

2017-06-29 17:28 GMT+08:00 Timokhin Maxim <ncx2@yandex.com>:

Hello.
We are in process moving to new db from 9.4.8 -> 9.6.3.1. When we did it our application started to throw exception "duplicate key value violates unique constraint" during doing INSERT:

INSERT INTO items (ctime, mtime, pubdate, url, title, description, body, status, fulltext_status, orig_id, image_id, video_id, resource_id, priority, checksum) VALUES (%(ctime)s, %(mtime)s, %(pubdate)s, %(url)s, %(title)s, %(description)s, %(body)s, %(status)s, %(fulltext_status)s, %(orig_id)s, %(image_id)s, %(video_id)s, %(resource_id)s, %(priority)s, %(checksum)s) RETURNING items.id'

Column url has unique constraint.

Also, we saw that during to update value into column status:
(psycopg2.IntegrityError) duplicate key value violates unique constraint "items_url"
DETAIL: Key (url)=(http://www.domainname.ru/ap_module/content/article/400-professional/140-professional/11880) already exists.
[SQL: 'UPDATE items SET status=%(status)s WHERE items.id IN ( ... )...

Our table:

Column | Type | Modifiers
-----------------+-----------------------------+-------------------------------------------------------------------
id | integer | not null default nextval(('public.items_id_seq'::text)::regclass)
ctime | timestamp without time zone | not null default now()
pubdate | timestamp without time zone | not null default now()
resource_id | integer | not null default 0
url | text |
title | text |
description | text |
body | text |
status | smallint | not null default 0
image | text |
orig_id | integer | not null default 0
mtime | timestamp without time zone | not null default now()
checksum | text |
video_url | text |
audio_url | text |
content_type | smallint | default 0
author | text |
video | text |
fulltext_status | smallint | default 0
summary | text |
image_id | integer |
video_id | integer |
priority | smallint |
Indexes:
"items_pkey" PRIMARY KEY, btree (id)
"items_url" UNIQUE, btree (url)
"items_resource_id" btree (resource_id)
"ndx__items__ctime" btree (ctime)
"ndx__items__image" btree (image_id)
"ndx__items__mtime" btree (mtime)
"ndx__items__pubdate" btree (pubdate)
"ndx__items__video" btree (video_id)
Foreign-key constraints:
"items_fkey1" FOREIGN KEY (image_id) REFERENCES images(id) ON UPDATE CASCADE ON DELETE SET NULL
"items_fkey2" FOREIGN KEY (video_id) REFERENCES videos(id) ON UPDATE CASCADE ON DELETE SET NULL
Referenced by:
TABLE "cluster_image" CONSTRAINT "cluster_image_fkey2" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "cluster_meta" CONSTRAINT "cluster_meta_item_id_fkey" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "events" CONSTRAINT "events_fkey2" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "item_cluster" CONSTRAINT "item_cluster_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "items_blogs" CONSTRAINT "items_blogs_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "items_reflink" CONSTRAINT "items_reflink_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "items_related" CONSTRAINT "items_related_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "items_summaries" CONSTRAINT "items_summaries_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "quotations" CONSTRAINT "quotations_fkey3" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "summaries" CONSTRAINT "summaries_fkey2" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE

Everything would be not bad if in the table weren't appeared duplicated records in url column.
Any idea how is it possible?

Thank you!

--
Timokhin 'maf' Maxim

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

From

Timokhin Maxim

Date:

30 June 2017, 15:49:56

Hello! Yes, it looks like a bug or an index corruption. Now, I'm going to drop an index, find and fix duplicates, and create index again.

But I would do it on 9.6.3 because there is a great feature ''max_parallel_workers_per_gather" there.

Well, see what will happen.

--
Пожалуйста!

Используйте кнопку "ответить всем".

Не удаляйте историю переписки.

Спасибо. С уважением, Timokhin 'maf' Maxim

30.06.2017, 00:22, "Melvin Davidson" <melvin6925@gmail.com>:

On Thu, Jun 29, 2017 at 5:28 AM, Timokhin Maxim <ncx2@yandex.com> wrote:
Hello.
We are in process moving to new db from 9.4.8 -> 9.6.3.1. When we did it our application started to throw exception "duplicate key value violates unique constraint" during doing INSERT:

INSERT INTO items (ctime, mtime, pubdate, url, title, description, body, status, fulltext_status, orig_id, image_id, video_id, resource_id, priority, checksum) VALUES (%(ctime)s, %(mtime)s, %(pubdate)s, %(url)s, %(title)s, %(description)s, %(body)s, %(status)s, %(fulltext_status)s, %(orig_id)s, %(image_id)s, %(video_id)s, %(resource_id)s, %(priority)s, %(checksum)s) RETURNING items.id'

Column url has unique constraint.

Also, we saw that during to update value into column status:
(psycopg2.IntegrityError) duplicate key value violates unique constraint "items_url"
DETAIL: Key (url)=(http://www.domainname.ru/ap_module/content/article/400-professional/140-professional/11880) already exists.
[SQL: 'UPDATE items SET status=%(status)s WHERE items.id IN ( ... )...

Our table:

Column | Type | Modifiers
-----------------+-----------------------------+-------------------------------------------------------------------
id | integer | not null default nextval(('public.items_id_seq'::text)::regclass)
ctime | timestamp without time zone | not null default now()
pubdate | timestamp without time zone | not null default now()
resource_id | integer | not null default 0
url | text |
title | text |
description | text |
body | text |
status | smallint | not null default 0
image | text |
orig_id | integer | not null default 0
mtime | timestamp without time zone | not null default now()
checksum | text |
video_url | text |
audio_url | text |
content_type | smallint | default 0
author | text |
video | text |
fulltext_status | smallint | default 0
summary | text |
image_id | integer |
video_id | integer |
priority | smallint |
Indexes:
"items_pkey" PRIMARY KEY, btree (id)
"items_url" UNIQUE, btree (url)
"items_resource_id" btree (resource_id)
"ndx__items__ctime" btree (ctime)
"ndx__items__image" btree (image_id)
"ndx__items__mtime" btree (mtime)
"ndx__items__pubdate" btree (pubdate)
"ndx__items__video" btree (video_id)
Foreign-key constraints:
"items_fkey1" FOREIGN KEY (image_id) REFERENCES images(id) ON UPDATE CASCADE ON DELETE SET NULL
"items_fkey2" FOREIGN KEY (video_id) REFERENCES videos(id) ON UPDATE CASCADE ON DELETE SET NULL
Referenced by:
TABLE "cluster_image" CONSTRAINT "cluster_image_fkey2" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "cluster_meta" CONSTRAINT "cluster_meta_item_id_fkey" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "events" CONSTRAINT "events_fkey2" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "item_cluster" CONSTRAINT "item_cluster_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "items_blogs" CONSTRAINT "items_blogs_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "items_reflink" CONSTRAINT "items_reflink_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "items_related" CONSTRAINT "items_related_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "items_summaries" CONSTRAINT "items_summaries_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "quotations" CONSTRAINT "quotations_fkey3" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE
TABLE "summaries" CONSTRAINT "summaries_fkey2" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE

Everything would be not bad if in the table weren't appeared duplicated records in url column.
Any idea how is it possible?

Thank you!

--
Timokhin 'maf' Maxim

It's possible you have index corruption on 9.4.8 version that was not detected.

Try the following query on 9.4.8 to see if any rows are selected. Then you can decide
how to fix from there.

SELECT a.id, a.url,
       b.id, b.url
FROM items a,
       items b
WHERE a.id <> b.id
   AND a.url = b.url
ORDER by a.id;

--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

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

From

Timokhin Maxim

Date:

30 June 2017, 15:50:45

Yes, you are right. It's just a typo.

--
Пожалуйста!

Используйте кнопку "ответить всем".

Не удаляйте историю переписки.

Спасибо. С уважением, Timokhin 'maf' Maxim

30.06.2017, 02:38, "Adrian Klaver" <adrian.klaver@aklaver.com>:

On 06/29/2017 02:28 AM, Timokhin Maxim wrote:
Hello.
We are in process moving to new db from 9.4.8 -> 9.6.3.1. When we did it
our application started to throw exception "duplicate key value violates
unique constraint" during doing INSERT:

How did move from 9.4.8 --> 9.6.3.1?

Also where are you getting Postgres from? I ask because 9.6.3.1 is not
a community version or was that just a typo?

Everything would be not bad if in the table weren't appeared duplicated
records in url column.
Any idea how is it possible?
Thank you!
--
Timokhin 'maf' Maxim

--
Adrian Klaver
adrian.klaver@aklaver.com

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

From

Timokhin Maxim

Date:

30 June 2017, 17:58:32

BTW, we are moving using:

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

After that we are upping version to 9.6.3.
I've looked through the documentation https://postgrespro.ru/docs/postgrespro/9.6/app-pgbasebackup and didn't find
detailsabout 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
instructionthat says "after upping db make indexes" ?  

Thank you.


-- 
Timokhin 'maf' Maxim



30.06.2017, 00:22, "Melvin Davidson" <melvin6925@gmail.com>:
> On Thu, Jun 29, 2017 at 5:28 AM, Timokhin Maxim <ncx2@yandex.com> wrote:
>> Hello.
>> We are in process moving to new db from 9.4.8 -> 9.6.3.1. When we did it our application started to throw
exception "duplicatekey value violates unique constraint" during doing INSERT: 
>>
>> INSERT INTO items (ctime, mtime, pubdate, url, title, description, body, status, fulltext_status, orig_id, image_id,
video_id,resource_id, priority, checksum) VALUES (%(ctime)s, %(mtime)s, %(pubdate)s, %(url)s, %(title)s,
%(description)s,%(body)s, %(status)s, %(fulltext_status)s, %(orig_id)s, %(image_id)s, %(video_id)s, %(resource_id)s,
%(priority)s,%(checksum)s) RETURNING items.id' 
>>
>> Column url has unique constraint.
>>
>> Also, we saw that during to update value into column status:
>> (psycopg2.IntegrityError) duplicate key value violates unique constraint "items_url"
>>     DETAIL:  Key (url)=(http://www.domainname.ru/ap_module/content/article/400-professional/140-professional/11880)
alreadyexists. 
>>      [SQL: 'UPDATE items SET status=%(status)s WHERE items.id IN ( ... )...
>>
>> Our table:
>>
>>      Column      |            Type             |                             Modifiers
>> -----------------+-----------------------------+-------------------------------------------------------------------
>>  id              | integer                     | not null default nextval(('public.items_id_seq'::text)::regclass)
>>  ctime           | timestamp without time zone | not null default now()
>>  pubdate         | timestamp without time zone | not null default now()
>>  resource_id     | integer                     | not null default 0
>>  url             | text                        |
>>  title           | text                        |
>>  description     | text                        |
>>  body            | text                        |
>>  status          | smallint                    | not null default 0
>>  image           | text                        |
>>  orig_id         | integer                     | not null default 0
>>  mtime           | timestamp without time zone | not null default now()
>>  checksum        | text                        |
>>  video_url       | text                        |
>>  audio_url       | text                        |
>>  content_type    | smallint                    | default 0
>>  author          | text                        |
>>  video           | text                        |
>>  fulltext_status | smallint                    | default 0
>>  summary         | text                        |
>>  image_id        | integer                     |
>>  video_id        | integer                     |
>>  priority        | smallint                    |
>> Indexes:
>>     "items_pkey" PRIMARY KEY, btree (id)
>>     "items_url" UNIQUE, btree (url)
>>     "items_resource_id" btree (resource_id)
>>     "ndx__items__ctime" btree (ctime)
>>     "ndx__items__image" btree (image_id)
>>     "ndx__items__mtime" btree (mtime)
>>     "ndx__items__pubdate" btree (pubdate)
>>     "ndx__items__video" btree (video_id)
>> Foreign-key constraints:
>>     "items_fkey1" FOREIGN KEY (image_id) REFERENCES images(id) ON UPDATE CASCADE ON DELETE SET NULL
>>     "items_fkey2" FOREIGN KEY (video_id) REFERENCES videos(id) ON UPDATE CASCADE ON DELETE SET NULL
>> Referenced by:
>>     TABLE "cluster_image" CONSTRAINT "cluster_image_fkey2" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE
CASCADEON DELETE CASCADE 
>>     TABLE "cluster_meta" CONSTRAINT "cluster_meta_item_id_fkey" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE
CASCADEON DELETE CASCADE 
>>     TABLE "events" CONSTRAINT "events_fkey2" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE
CASCADE
>>     TABLE "item_cluster" CONSTRAINT "item_cluster_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE
CASCADEON DELETE CASCADE 
>>     TABLE "items_blogs" CONSTRAINT "items_blogs_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE
ONDELETE CASCADE 
>>     TABLE "items_reflink" CONSTRAINT "items_reflink_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE
CASCADEON DELETE CASCADE 
>>     TABLE "items_related" CONSTRAINT "items_related_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE
CASCADEON DELETE CASCADE 
>>     TABLE "items_summaries" CONSTRAINT "items_summaries_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE
CASCADEON DELETE CASCADE 
>>     TABLE "quotations" CONSTRAINT "quotations_fkey3" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON
DELETECASCADE 
>>     TABLE "summaries" CONSTRAINT "summaries_fkey2" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON
DELETECASCADE 
>>
>> Everything would be not bad if in the table weren't appeared duplicated records in url column.
>> Any idea how is it possible?
>>
>> Thank you!
>>
>> --
>> Timokhin 'maf' Maxim
>
> It's possible you have index corruption on 9.4.8 version that was not detected.
>
> Try the following query on 9.4.8 to see if any rows are selected. Then you can decide
> how to fix from there.
>
> SELECT a.id, a.url,
>        b.id, b.url
>   FROM items a,
>        items b
>  WHERE a.id <> b.id
>    AND a.url = b.url
>  ORDER by a.id;
>
> --
> Melvin Davidson
> I reserve the right to fantasize.  Whether or not you
> wish to share my fantasy is entirely up to you.

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

From

Adrian Klaver

Date:

30 June 2017, 19:07:00

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
detailsabout 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
instructionthat says "after upping db make indexes" ? 
>
> Thank you.
>
>
> --
> Timokhin 'maf' Maxim
>


--
Adrian Klaver
adrian.klaver@aklaver.com

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

From

Melvin Davidson

Date:

30 June 2017, 19:28:02

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

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.

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

From

Timokhin Maxim

Date:

30 June 2017, 20:33:42

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

after that server starts normally.


-- 
Timokhin 'maf' Maxim


30.06.2017, 16:07, "Adrian Klaver" <adrian.klaver@aklaver.com>:
> 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
detailsabout 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
instructionthat says "after upping db make indexes" ? 
>>
>>  Thank you.
>>
>>  --
>>  Timokhin 'maf' Maxim
>
> --
> Adrian Klaver
> adrian.klaver@aklaver.com

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

From

Adrian Klaver

Date:

01 July 2017, 05:30:32

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

"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@aklaver.com

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

From

Steven Chang

Date:

01 July 2017, 10:42:38

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.

2017-07-01 7:30 GMT+08:00 Adrian Klaver <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

"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@aklaver.com

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

From

Adrian Klaver

Date:

01 July 2017, 20:05:57

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


--
Adrian Klaver
adrian.klaver@aklaver.com

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

From

Melvin Davidson

Date:

01 July 2017, 20:51:06

On Sat, Jul 1, 2017 at 10:05 AM, Adrian Klaver <adrian.klaver@aklaver.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.klaver@aklaver.com <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@aklaver.com <mailto:adrian.klaver@aklaver.com>

--
Adrian Klaver
adrian.klaver@aklaver.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.

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

From

Steven Chang

Date:

03 July 2017, 07:08:28

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

--
Adrian Klaver
adrian.klaver@aklaver.com

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

From

Michael Paquier

Date:

03 July 2017, 07:18:06

On Mon, Jul 3, 2017 at 10:08 AM, Steven Chang <stevenchang1213@gmail.com> wrote:
> Hello :

Please avoid top-posting.

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

This is EnterpriseDB's fork of Postgres. Until it can be proved that a
corruption has happened using the community code, it is going to be
hard to say if the problem comes from PostgreSQL itself or from
something that has been changed there.
--
Michael

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

From

Steven Chang

Date:

03 July 2017, 07:53:12

Dear Michael,

I know what you mean. We also mail the issue to EDB.

Post here is just to reply Timokhin's case and see could anyone give a solution.

EDB's strength is just a orafce moudule enhancement to me,
and I don't think they could adapt a lot of kernel module codes.

Regards,

Steven

2017-07-03 9:18 GMT+08:00 Michael Paquier <michael.paquier@gmail.com>:

On Mon, Jul 3, 2017 at 10:08 AM, Steven Chang <stevenchang1213@gmail.com> wrote:
> Hello :

Please avoid top-posting.

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

This is EnterpriseDB's fork of Postgres. Until it can be proved that a
corruption has happened using the community code, it is going to be
hard to say if the problem comes from PostgreSQL itself or from
something that has been changed there.
--
Michael

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

From

Timokhin Maxim

Date:

28 July 2017, 20:34:43

Hello, everybody.
I solved the problem. The index has been corrupted after replication despite it was appeared as not corrupted.
It was solved by recreating the index.
Thank you for the help.

--
Пожалуйста!
Используйте кнопку "ответить всем".
Не удаляйте историю переписки.
Спасибо. С уважением, Timokhin 'maf' Maxim


30.06.2017, 17:33, "Timokhin Maxim" <ncx2@yandex.com>:
> 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
>
> after that server starts normally.
>
> --
> Timokhin 'maf' Maxim
>
> 30.06.2017, 16:07, "Adrian Klaver" <adrian.klaver@aklaver.com>:
>>  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
finddetails 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
asinstruction that says "after upping db make indexes" ? 
>>>
>>>   Thank you.
>>>
>>>   --
>>>   Timokhin 'maf' Maxim
>>
>>  --
>>  Adrian Klaver
>>  adrian.klaver@aklaver.com