Hi, I’m attempting to migrate a forum to a new server. Both servers are running the latest version of discourse docker. When importing the backup via command line, I get the following error: ERROR: could not create unique index "index_incoming_referers_on_path_and_incoming_domain_id" DETAIL: Key (path, incoming_domain_id)=(/search/, 418) is duplicated. EXCEPTION: psql failed: DETAIL: Key (path, incoming_domain_id)=(/search/, 418) is duplicated. This seems to the same or similar error as: [תמונה] Restore fails - could not create unique index Self-hosting I have a duplicate key issue also, is there a documented fix? discourse=# REINDEX SCHEMA CONCURRENTLY public; ERROR: could not create unique index "index_incoming_referers_on_path_and_incoming_domain_id_ccnew" DETAIL: Key (path, incoming_domain_id)=(/search/, 1905) is duplicated. [1] pry(main)> IncomingReferer.where(path: "/m/search") => [# , #<IncomingReferer:0x0000557176d925c8 id: 42228, path: "/m/… However, in my case the duplicated records are in the /search/ path rather than /m/search as is the case on the thread linked above. I’ve connected to the container on the old server (./launcher enter app) and in the Rails console (rails c) I’ve tried to search for the duplicated records using: IncomingReferer.where(path: "/search") and IncomingReferer.where("path LIKE '%/search%'") However, this results in 100’s of records being displayed. How can I work out what records are duplicated, and how can I safely delete these and rebuild? The forum is currently working fine on the old server, we just need to move to new hardware.

Ok, I’ve fixed it. I did the following Enter container ./launcher enter app Connect to database su postgres -c 'psql discourse' Try to find duplicates discourse=# select * from incoming_referers where path LIKE '%/search/' ORDER BY incoming_domain_id;` id | path | incoming_domain_id ------+------------+-------------------- 3339 | /search/ | 33 6257 | /search/ | 91 1567 | /search/ | 298 1777 | /search/ | 341 3010 | /search/ | 418 6247 | /search/ | 418 4293 | /search/ | 644 2899 | /search/ | 653 3447 | /search/ | 793 3696 | /search/ | 852 4395 | /a/search/ | 1050 6968 | /search/ | 1305 5634 | /search/ | 1387 5834 | /search/ | 1437 6519 | /search/ | 1637 7127 | /search/ | 1787 7280 | /search/ | 1827 (17 rows) Delete duplicate DELETE FROM incoming_referers WHERE path LIKE '%/search/' AND id IN (6247); Then rebuild discourse=# REINDEX SCHEMA CONCURRENTLY public; WARNING: cannot reindex invalid index "public.incoming_referers_pkey_ccnew" concurrently, skipping WARNING: cannot reindex invalid index "public.index_incoming_referers_on_path_and_incoming_domain_id_ccnew" concurrently, skipping WARNING: cannot reindex invalid index "pg_toast.pg_toast_20732_index_ccnew" concurrently, skipping REINDEX Then I took another backup, copied it to the new server, and it imported successfully :smiley: It would be nice if the backup process could spot duplicates to avoid any issues, I was luckily that I had access to the original server which was still running. If I was restoring a cold backup, this would probably have been more of an issue> Thanks a lot for your help.

No, I assumed importing via the GUI would be invoking the same import process? I’ll try that now.

I suspect this means that you have a corrupt index. What version of Postgres are you running? something like: cd /var/discourse cat shared/standalone/postgres_data/PG* (I can’t quite remember the postgres filename). You can search here for “postgres corrupt index” and find a topic that I once wrote about how to track down those bad records and delete them. You basically try to rebuild that index and delete the records that it complains about then try to rebuild the index again until it rebuilds.

Just tried importing via the GUI, with exactly the same result: [Screenshot 2021-10-31 23.26.14] [תמונה] pfaffman: What version of Postgres are you running? The old server doesn’t have a file called PG_VERSION, how can I tell what version its running? I’ve updated the docket install to the latest version today. The new server (newly bootstrapped) is running postgres V13 cat shared/standalone/postgres_data/PG_VERSION 13 [תמונה] pfaffman: You can search here for “postgres corrupt index” and find a topic that I once wrote about how to track down those bad records and delete them. You basically try to rebuild that index and delete the records that it complains about then try to rebuild the index again until it rebuilds. Is there a recommended procedure on how to do this?

I had a topic that had some hints, but I don’t see it anymore. It’s been close to a year since the postgres 12 upgrade. reindex index index_incoming_referers_on_path_and_incoming_domain_id; And ActiveRecord::Base.connection.execute('reindex index index_incoming_referers_on_path_and_incoming_domain_id;') Are ways to try to rebuild the index. It’ll give you an error and you can then go and delete the errant records. You’ll need to include both the path and the ID.

שגיאת ייבוא גיבוי: "לא ניתן ליצור אינדקס ייחודי"

תמיכה התקנה

IAmGav (Gavin Perch) 31 באוקטובר,‏ 2021,‏ 9:07pm 2

Have you tried using the Admin GUI ?

נושא		תגובות	צפיות
Restore fails - could not create unique index Self-hosting	21	4257	4 ביולי,‏ 2020
"EXCEPTION: psql failed: DETAIL: Key (post_id)=(36946) is duplicated." Self-hosting	7	1373	20 ביוני,‏ 2022
Restore fails: could not create unique index Self-hosting	10	1375	13 בפברואר,‏ 2022
Import failing with `could not create unique index` Self-hosting	5	2997	13 ביוני,‏ 2020
Backup restoration fails Self-hosting backups	16	592	16 ביוני,‏ 2026

שגיאת ייבוא גיבוי: "לא ניתן ליצור אינדקס ייחודי"

נושאים קשורים