# Restore fails - could not create unique index
**URL:** https://meta.discourse.org/t/restore-fails-could-not-create-unique-index/151380
**Category:** Self-hosting
**Created:** [May 13, 2020, 8:29pm UTC](https://meta.discourse.org/t/restore-fails-could-not-create-unique-index/151380 "2020-05-13T20:29:04Z")
**Posts on this page:** 20
**Page:** 1
### Author:  [@Chandra](https://meta.discourse.org/u/Chandra)
#### Post date: [May 13, 2020, 8:29pm UTC](https://meta.discourse.org/t/restore-fails-could-not-create-unique-index/151380/1 "2020-05-13T20:29:04Z")
Hi, restore of backup fails with error:
```
CREATE INDEX
ERROR: could not create unique index "index_incoming_referers_on_path_and_incoming_domain_id"
DETAIL: Key (path, incoming_domain_id)=(/m/search, 25) is duplicated.
EXCEPTION: psql failed: DETAIL: Key (path, incoming_domain_id)=(/m/search, 25) is duplicated.
```
I found a similar topic but could not make out what needs to be done: [https://meta.discourse.org/t/getting-this-error-during-restore-could-not-create-unique-index/](https://meta.discourse.org/t/getting-this-error-during-restore-could-not-create-unique-index/)
Appreciate any step by step help for dummies to fix.
---
### Author:  [@simon](https://meta.discourse.org/u/simon)
#### Post date: [May 13, 2020, 8:50pm UTC](https://meta.discourse.org/t/restore-fails-could-not-create-unique-index/151380/2 "2020-05-13T20:50:49Z")
This issue with the `incoming_referers` table has come up a few times recently. I’m not sure why that particular table is causing problems, but it seems likely that the issues are related. Maybe someone else on the Discourse team will have ideas about what could be causing the duplicate records to be created.
> [@Chandra](#):
>
> Appreciate any step by step help for dummies to fix.
Do you still have access to the site that you created the backup file on? If so, the fix is to delete the duplicate record from the database and then create a new backup file. To do that, you would SSH into the old server and `cd` to the `/var/discourse` directory:
```plaintext
cd /var/discourse
```
Then run
```plaintext
./launcher enter app
```
Then enter the Rails console with
```plaintext
rails c
```
You should then see a prompt that looks similar to this:
```plaintext
[1] pry(main)>
```
Try running the following command from the Rails console and let us know what it returns:
```plaintext
IncomingReferer.where(path: "/m/search")
```
It should return an array with two or more records.
---
### Author:  [@Chandra](https://meta.discourse.org/u/Chandra)
#### Post date: [May 13, 2020, 9:04pm UTC](https://meta.discourse.org/t/restore-fails-could-not-create-unique-index/151380/3 "2020-05-13T21:04:13Z")
Thank you.
I will run this in the morning and report back.
---
### Author:  [@Chandra](https://meta.discourse.org/u/Chandra)
#### Post date: [May 14, 2020, 5:12am UTC](https://meta.discourse.org/t/restore-fails-could-not-create-unique-index/151380/4 "2020-05-14T05:12:52Z")
This is from the old installation - looks like just one record?
```
[1] pry(main)> IncomingReferer.where(path: "/m/search")
=> [#]
[2] pry(main)>
```
Edit: tried in the new server as well. It shows:
```
[1] pry(main)> IncomingReferer.where(path: "/m/search")
=> []
[2] pry(main)>
```
---
### Author:  [@simon](https://meta.discourse.org/u/simon)
#### Post date: [May 14, 2020, 5:43am UTC](https://meta.discourse.org/t/restore-fails-could-not-create-unique-index/151380/5 "2020-05-14T05:43:45Z")
Thanks for checking that! The result you got is actually the same as what I saw with another site earlier today. It is a solvable problem, but I’m going to try to get one of our engineers take a look at what is going on.
---
### Author:  [@Chandra](https://meta.discourse.org/u/Chandra)
#### Post date: [May 14, 2020, 10:30am UTC](https://meta.discourse.org/t/restore-fails-could-not-create-unique-index/151380/7 "2020-05-14T10:30:15Z")
My primary purpose to move servers was since I was on Debian 8 that is going out of support.
With this issue of restore, I took the route of upgrading to Debian 9 on the same server. It has been successful, so some respite for now.
Thank you for your support.
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [May 14, 2020, 12:41pm UTC](https://meta.discourse.org/t/restore-fails-could-not-create-unique-index/151380/8 "2020-05-14T12:41:37Z")
Replace this line
> [@Chandra](#):
>
> path: “%/m/search%”
You need to do a fuzzy search so that it doesn’t assume that the index works. One percent sign is likely enough if it’s at the beginning, I think.
You can just delete the extra record. To do it right, right though, you need to update the other table that links to this one. I have to look it up every time as there are a couple different tables that tie happens to.
This problem is blamed on third party extentions, which doesn’t make much sense. It seems like it must be postgres fault, but I don’t know. I come across this a couple times a month,it seems (score) across a bunch of sites).
---
### Author:  [@Wingtip](https://meta.discourse.org/u/Wingtip)
#### Post date: [May 16, 2020, 12:44pm UTC](https://meta.discourse.org/t/restore-fails-could-not-create-unique-index/151380/9 "2020-05-16T12:44:31Z")
I have a duplicate key issue also, is there a documented fix?
```plaintext
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.
```
```plaintext
[1] pry(main)> IncomingReferer.where(path: "/m/search")
=> [#,
#]
```
---
### Author:  [@Chandra](https://meta.discourse.org/u/Chandra)
#### Post date: [May 16, 2020, 2:27pm UTC](https://meta.discourse.org/t/restore-fails-could-not-create-unique-index/151380/10 "2020-05-16T14:27:36Z")
Even though I just upgraded my server in place and hence won’t restore to a new server anymore, I tried this out of curiosity and did not find any records with fuzzy search:
```
[1] pry(main)> IncomingReferer.where(path: "%/m/search%")
=> []
[2] pry(main)> IncomingReferer.where(path: "%/m/search")
=> []
[3] pry(main)> IncomingReferer.where(path: "/m/search%")
=> []
```
---
### Author:  [@gerhard](https://meta.discourse.org/u/gerhard)
#### Post date: [May 16, 2020, 2:48pm UTC](https://meta.discourse.org/t/restore-fails-could-not-create-unique-index/151380/11 "2020-05-16T14:48:53Z")
You need to use `LIKE` in order for wildcards to work:
```ruby
IncomingReferer.where("path LIKE '%/m/search%'")
```
---
### Author:  [@Wingtip](https://meta.discourse.org/u/Wingtip)
#### Post date: [May 16, 2020, 3:18pm UTC](https://meta.discourse.org/t/restore-fails-could-not-create-unique-index/151380/12 "2020-05-16T15:18:06Z")
That brought up quite a few more duplicate keys.
```plaintext
[1] pry(main)> IncomingReferer.where("path LIKE '%/m/search%'")
=> [#,
#,
#,
#,
#,
#,
#]
```
---
### Author:  [@sam](https://meta.discourse.org/u/sam)
#### Post date: [May 18, 2020, 5:50am UTC](https://meta.discourse.org/t/restore-fails-could-not-create-unique-index/151380/13 "2020-05-18T05:50:14Z")
I would just nuke all the dupe rows … there is little value in this information.
---
### Author:  [@Wingtip](https://meta.discourse.org/u/Wingtip)
#### Post date: [May 18, 2020, 11:42am UTC](https://meta.discourse.org/t/restore-fails-could-not-create-unique-index/151380/14 "2020-05-18T11:42:26Z")
Happy to do so, can you provide the correct command? Not familiar with postgres in particular but I do know SQL.
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [May 18, 2020, 5:11pm UTC](https://meta.discourse.org/t/restore-fails-could-not-create-unique-index/151380/15 "2020-05-18T17:11:09Z")
> [@sam](#):
>
> I would just nuke all the dupe rows … there is little value in this information.
That’s good to hear. I’ve been laboriously updating the other table that links to these. It’s a huge pain since I can never remember what it was, so it’s the first time over and over again.
---
### Author:  [@riking](https://meta.discourse.org/u/riking)
#### Post date: [May 18, 2020, 5:24pm UTC](https://meta.discourse.org/t/restore-fails-could-not-create-unique-index/151380/16 "2020-05-18T17:24:36Z")
```plaintext
IncomingReferer.find(44231).destroy
IncomingReferer.find(42228).destroy
```
---
### Author:  [@Wingtip](https://meta.discourse.org/u/Wingtip)
#### Post date: [May 18, 2020, 6:21pm UTC](https://meta.discourse.org/t/restore-fails-could-not-create-unique-index/151380/17 "2020-05-18T18:21:48Z")
Removing those two duplicates was successful, but subsequently rebuilding indices threw up new errors. Is this a major problem? How do we fix, delete that search 3433 row?
```plaintext
[1] pry(main)> IncomingReferer.find(44231).destroy
=> #
[2] pry(main)> IncomingReferer.find(42228).destroy
=> #
```
```plaintext
postgres=# \connect discourse
You are now connected to database "discourse" as user "postgres".
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_2782645_index_ccnew" concurrently, skipping
ERROR: could not create unique index "index_incoming_referers_on_path_and_incoming_domain_id_ccnew1"
DETAIL: Key (path, incoming_domain_id)=(/search/, 3433) is duplicated.
CONTEXT: parallel worker
```
---
### Author:  [@riking](https://meta.discourse.org/u/riking)
#### Post date: [May 18, 2020, 6:25pm UTC](https://meta.discourse.org/t/restore-fails-could-not-create-unique-index/151380/18 "2020-05-18T18:25:56Z")
Here’s the code handling creation… this should be handling it properly, but we could update it to an `ON CONFLICT` insert if needed?
>
---
### Author:  [@Wingtip](https://meta.discourse.org/u/Wingtip)
#### Post date: [May 18, 2020, 6:36pm UTC](https://meta.discourse.org/t/restore-fails-could-not-create-unique-index/151380/19 "2020-05-18T18:36:26Z")
I tried to rebuild those 4 indices manually. Two succeeded, two failed. Should I nuke those two duplicate rows?
```plaintext
discourse=# REINDEX INDEX CONCURRENTLY "public"."incoming_referers_pkey_ccnew";
REINDEX
discourse=# REINDEX INDEX CONCURRENTLY "public"."index_incoming_referers_on_path_and_incoming_domain_id_ccnew";
ERROR: could not create unique index "index_incoming_referers_on_path_and_incoming_domain_id_cc_ccnew"
DETAIL: Key (path, incoming_domain_id)=(/search/, 1861) is duplicated.
discourse=# REINDEX INDEX CONCURRENTLY "pg_toast"."pg_toast_2782645_index_ccnew";
REINDEX
discourse=# REINDEX INDEX CONCURRENTLY "index_incoming_referers_on_path_and_incoming_domain_id_ccnew1";
ERROR: could not create unique index "index_incoming_referers_on_path_and_incoming_domain_id_c_ccnew1"
DETAIL: Key (path, incoming_domain_id)=(/search/, 1905) is duplicated.
```
---
### Author:  [@sam](https://meta.discourse.org/u/sam)
#### Post date: [May 18, 2020, 10:08pm UTC](https://meta.discourse.org/t/restore-fails-could-not-create-unique-index/151380/20 "2020-05-18T22:08:04Z")
Yes please nuke the dupe rows
@riking pg corrupting indexes is a pg bug, not a discourse bug, we can certainly improve the performance of that insert, but the pg bug is something that needs fixing in pg
My guess is that is something todo with some sort of rude shutdown of the db engine, maybe on power loss
---
### Author:  [@pfaffman](https://meta.discourse.org/u/pfaffman)
#### Post date: [May 18, 2020, 10:20pm UTC](https://meta.discourse.org/t/restore-fails-could-not-create-unique-index/151380/21 "2020-05-18T22:20:34Z")
> [@sam](#):
>
> My guess is that is something todo with some sort of rude shutdown of the db engine, maybe on power loss
That’s a reasonable explanation. Does `./launcher shutdown app` (or rebuild) do a clean shutdown or postgres somehow? Oh, but I bet that an unattended upgrade doesn’t know how to do a clean shutdown of docker containers, does it?
[Next page](https://meta.discourse.org/t/restore-fails-could-not-create-unique-index/151380.md?page=2)