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@xxxxxxxxx>: > On Thu, Jun 29, 2017 at 5:28 AM, Timokhin Maxim <ncx2@xxxxxxxxxx> 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@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
