duplicate key value violates unique constraint and duplicated records

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 <http://a.id&gt;,
a.url, b.id <http://b.id&gt;, b.url FROM items a, items b WHERE
a.id <http://a.id&gt; <> b.id <http://b.id&gt; AND a.url = b.url ORDER by a.id
<http://a.id&gt;;*
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Interesting!! We also met the same situation on PK running on PPAS 9.0 last
night.
When surfing Internet, got returned this URL :

/messages/by-id/20140811083748.2536.10437@wrigleys.
postgresql.org

</messages/by-id/20140811083748.2536.10437@wrigleys.postgresql.org&gt;
You can check the reply.

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

<div>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.</div><div>But I would do it on 9.6.3 because there is a great feature ''max_parallel_workers_per_gather" there.</div><div>Well, see what will happen.</div><div> </div><div>-- <br />Пожалуйста!</div><div>Используйте кнопку "ответить всем".</div><div>Не удаляйте историю переписки.</div><div>Спасибо. С уважением, Timokhin 'maf' Maxim</div><div> </div><div> </div><div> </div><div>30.06.2017, 00:22, "Melvin Davidson" &lt;melvin6925@gmail.com&gt;:</div><blockquote type="cite"><div> <div> <div>On Thu, Jun 29, 2017 at 5:28 AM, Timokhin Maxim <span>&lt;<a target="_blank" href="mailto:ncx2@yandex.com">ncx2@yandex.com</a>&gt;</span> wrote:<blockquote style="margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex;"><div><div style="color:rgb(0,0,0);font-family:Arial,sans-serif;font-size:15px;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:normal;text-align:start;text-transform:none;white-space:normal;background-color:rgb(255,255,255);">Hello.</div><div style="color:rgb(0,0,0);font-family:Arial,sans-serif;font-size:15px;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:normal;text-align:start;text-transform:none;white-space:normal;background-color:rgb(255,255,255);">We are in process moving to new db from 9.4.8 -&gt; 9.6.3.1. When we did it our application started to throw exception "duplicate key value violates unique constraint" during doing INSERT:</div><div style="color:rgb(0,0,0);font-family:Arial,sans-serif;font-size:15px;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:normal;text-align:start;text-transform:none;white-space:normal;background-color:rgb(255,255,255);"> </div><div style="color:rgb(0,0,0);font-family:Arial,sans-serif;font-size:15px;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:normal;text-align:start;text-transform:none;white-space:normal;background-color:rgb(255,255,255);">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 <a target="_blank" href="http://items.id/&quot;&gt;items.id&lt;/a&gt;&#39;&lt;/div&gt;&lt;div style="color:rgb(0,0,0);font-family:Arial,sans-serif;font-size:15px;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:normal;text-align:start;text-transform:none;white-space:normal;background-color:rgb(255,255,255);"> </div><div style="color:rgb(0,0,0);font-family:Arial,sans-serif;font-size:15px;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:normal;text-align:start;text-transform:none;white-space:normal;background-color:rgb(255,255,255);">Column url has unique constraint.</div><div style="color:rgb(0,0,0);font-family:Arial,sans-serif;font-size:15px;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:normal;text-align:start;text-transform:none;white-space:normal;background-color:rgb(255,255,255);"> </div><div style="color:rgb(0,0,0);font-family:Arial,sans-serif;font-size:15px;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:normal;text-align:start;text-transform:none;white-space:normal;background-color:rgb(255,255,255);"> </div><div style="color:rgb(0,0,0);font-family:Arial,sans-serif;font-size:15px;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:normal;text-align:start;text-transform:none;white-space:normal;background-color:rgb(255,255,255);">Also, we saw that during to update value into column status:</div><div style="color:rgb(0,0,0);font-family:Arial,sans-serif;font-size:15px;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:normal;text-align:start;text-transform:none;white-space:normal;background-color:rgb(255,255,255);"><div>(psycopg2.IntegrityError) duplicate key value violates unique constraint "items_url"</div><div>    DETAIL:  Key (url)=(<a target="_blank" style="color:rgb(153,0,153);" href="http://www.domainname.ru/ap_module/content/article/400-professional/140-professional/11880&quot;&gt;http://www.domainname.ru/ap_module/content/article/400-professional/140-professional/11880&lt;/a&gt;) already exists.</div><div>     [SQL: 'UPDATE items SET status=%(status)s WHERE <a target="_blank" href="http://items.id/&quot;&gt;items.id&lt;/a&gt; IN ( ... )...</div><div> </div><div> </div><div>Our table:</div><div> </div></div><div style="color:rgb(0,0,0);font-family:Arial,sans-serif;font-size:15px;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:normal;text-align:start;text-transform:none;white-space:normal;background-color:rgb(255,255,255);"> </div><div style="color:rgb(0,0,0);font-family:Arial,sans-serif;font-size:15px;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:normal;text-align:start;text-transform:none;white-space:normal;background-color:rgb(255,255,255);"><div>     Column      |            Type             |                             Modifiers</div><div>-----------------+-----------------------------+-------------------------------------------------------------------</div><div> id              | integer                     | not null default nextval(('public.items_id_seq'::text)::regclass)</div><div> ctime           | timestamp without time zone | not null default now()</div><div> pubdate         | timestamp without time zone | not null default now()</div><div> resource_id     | integer                     | not null default 0</div><div> url             | text                        |</div><div> title           | text                        |</div><div> description     | text                        |</div><div> body            | text                        |</div><div> status          | smallint                    | not null default 0</div><div> image           | text                        |</div><div> orig_id         | integer                     | not null default 0</div><div> mtime           | timestamp without time zone | not null default now()</div><div> checksum        | text                        |</div><div> video_url       | text                        |</div><div> audio_url       | text                        |</div><div> content_type    | smallint                    | default 0</div><div> author          | text                        |</div><div> video           | text                        |</div><div> fulltext_status | smallint                    | default 0</div><div> summary         | text                        |</div><div> image_id        | integer                     |</div><div> video_id        | integer                     |</div><div> priority        | smallint                    |</div><div>Indexes:</div><div>    "items_pkey" PRIMARY KEY, btree (id)</div><div>    "items_url" UNIQUE, btree (url)</div><div>    "items_resource_id" btree (resource_id)</div><div>    "ndx__items__ctime" btree (ctime)</div><div>    "ndx__items__image" btree (image_id)</div><div>    "ndx__items__mtime" btree (mtime)</div><div>    "ndx__items__pubdate" btree (pubdate)</div><div>    "ndx__items__video" btree (video_id)</div><div>Foreign-key constraints:</div><div>    "items_fkey1" FOREIGN KEY (image_id) REFERENCES images(id) ON UPDATE CASCADE ON DELETE SET NULL</div><div>    "items_fkey2" FOREIGN KEY (video_id) REFERENCES videos(id) ON UPDATE CASCADE ON DELETE SET NULL</div><div>Referenced by:</div><div>    TABLE "cluster_image" CONSTRAINT "cluster_image_fkey2" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE</div><div>    TABLE "cluster_meta" CONSTRAINT "cluster_meta_item_id_fkey" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE</div><div>    TABLE "events" CONSTRAINT "events_fkey2" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE</div><div>    TABLE "item_cluster" CONSTRAINT "item_cluster_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE</div><div>    TABLE "items_blogs" CONSTRAINT "items_blogs_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE</div><div>    TABLE "items_reflink" CONSTRAINT "items_reflink_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE</div><div>    TABLE "items_related" CONSTRAINT "items_related_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE</div><div>    TABLE "items_summaries" CONSTRAINT "items_summaries_fkey1" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE</div><div>    TABLE "quotations" CONSTRAINT "quotations_fkey3" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE</div><div>    TABLE "summaries" CONSTRAINT "summaries_fkey2" FOREIGN KEY (item_id) REFERENCES items(id) ON UPDATE CASCADE ON DELETE CASCADE</div></div><div style="color:rgb(0,0,0);font-family:Arial,sans-serif;font-size:15px;font-style:normal;font-variant-ligatures:normal;font-variant-caps:normal;font-weight:normal;text-align:start;text-transform:none;white-space:normal;background-color:rgb(255,255,255);"><div> </div><div> </div><div>Everything would be not bad if in the table weren't appeared duplicated records in url column.</div><div>Any idea how is it possible? </div><div> </div><div>Thank you!</div></div></div><div><span><font color="#888888"> </font></span></div><div><span><font color="#888888">-- <br />Timokhin 'maf' Maxim</font></span></div><div><span><font color="#888888"> </font></span></div></blockquote></div><br /><strong>It's possible you have index corruption on 9.4.8 version that was not detected.<br /><br />Try the following query on 9.4.8 to see if any rows are selected. Then you can decide<br />how to fix from there.<br /><br />SELECT <a href="http://a.id/&quot;&gt;a.id&lt;/a&gt;, a.url,<br />       <a href="http://b.id/&quot;&gt;b.id&lt;/a&gt;, b.url<br />  FROM items a,<br />       items b<br /> WHERE <a href="http://a.id/&quot;&gt;a.id&lt;/a&gt; &lt;&gt; <a href="http://b.id/&quot;&gt;b.id&lt;/a&gt;&lt;br />   AND a.url = b.url<br /> ORDER by <a href="http://a.id/&quot;&gt;a.id&lt;/a&gt;;&lt;/strong&gt;&lt;br /><br />--<div><div><font size="4"><strong><span style="font-family:courier new,monospace;">Melvin Davidson</span></strong></font><br /><font size="3" style="font-weight:bold;"><span style="color:#8000ff;">I reserve the right to fantasize.  Whether or not you </span><br style="color:rgb(128,0,255);" /><span style="color:#8000ff;">wish to share my fantasy is entirely up to you. </span><img style="color:rgb(128,0,255);" src="http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif&quot; /></font></div></div></div></div></blockquote>

<div>Yes, you are right. It's just a typo.</div><div> </div><div>-- <br />Пожалуйста!</div><div>Используйте кнопку "ответить всем".</div><div>Не удаляйте историю переписки.</div><div>Спасибо. С уважением, Timokhin 'maf' Maxim</div><div> </div><div> </div><div> </div><div>30.06.2017, 02:38, "Adrian Klaver" &lt;adrian.klaver@aklaver.com&gt;:</div><blockquote type="cite"><p>On 06/29/2017 02:28 AM, Timokhin Maxim wrote:</p><blockquote> Hello.<br /> We are in process moving to new db from 9.4.8 -&gt; 9.6.3.1. When we did it<br /> our application started to throw exception "duplicate key value violates<br /> unique constraint" during doing INSERT:</blockquote><p><br />How did move from 9.4.8 --&gt; 9.6.3.1?<br /><br />Also where are you getting Postgres from? I ask because 9.6.3.1 is not<br />a community version or was that just a typo?<br /><br /><br /> </p><blockquote> Everything would be not bad if in the table weren't appeared duplicated<br /> records in url column.<br /> Any idea how is it possible?<br /> Thank you!<br /> --<br /> Timokhin 'maf' Maxim</blockquote><p><br /> </p><span>--<br />Adrian Klaver<br /><a href="mailto:adrian.klaver@aklaver.com">adrian.klaver@aklaver.com</a></span></blockquote>

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

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/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/po

stgrespro/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
<https://www.postgresql.org/docs/9.6/static/upgrading.html&gt;*--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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 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&gt;

"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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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&gt;

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

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 :

/messages/by-id/20140811083748.2536.10437@wrigleys.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 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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general