Users are experiencing corrupt indexes in PostgreSQL 12 after upgrading from PG10, causing REINDEX SCHEMA CONCURRENTLY to fail with duplicate key errors. The issue stems from duplicate data in tables like tags, incoming_referers, and users, which prevent unique indexes from being created. Users are advised to identify and delete duplicate rows (e.g., using ilike queries for case-insensitive matches) and then drop invalid indexes with ccnew suffixes, which are left behind when concurrent reindexing fails. These ccnew indexes are temporary and can be safely removed. Once the data is cleaned and invalid indexes dropped, reindexing can succeed. The process is repetitive, requiring multiple attempts, and highlights a potential bug in PostgreSQL’s concurrent reindexing behavior. Some users suggest automating the fix in future Discourse upgrades, but the root cause remains unclear. See solution for steps.
Sorry for bumping this once again, I got a very strange
discourse=# REINDEX SCHEMA CONCURRENTLY public;
ERROR: could not create unique index "index_tags_on_name_ccnew"
DETAIL: Key (name)=(chronicillness) is duplicated.
I’m assuming it can be fixed by the solution suggested above by @riking but I’m unable to figure out how to modify the syntax to suit my case. 
See:
https://twitter.com/petervgeoghegan/status/1264325695997538304?s=20
https://twitter.com/petervgeoghegan/status/1264404749899534338?s=20
So you have 2 things you can do if you see this error:
You can work around the issue … in this case the table tags has a duplicate row with the name chronicillness
Do a data explorer query searching for the rows select * from tags where name = 'chronicillness
Delete the duplicate:
./launcher enter app
rails c
Tag.find_by(id: ID_YOU_FOUND_IN_DATA_EXPLORER).destroy
If you have a backup of your database and details you would like to share with Peter… share with Peter either privately or on the PG mailing list.
So I have a discourse generated backup of postgres 10. will that be helpful? can I Just pull the postgres part from the archive and send that?
I am no sure… what Peter would likely need is for you to stop your database and copy the entire contents of the folder containing the database.
Best way to find out is email him.
Sure, I’ll drop him an email to figure this out 
NB: I got this when I ran the SQL in data explorer
| id | name | topic_count | created_at | updated_at | pm_topic_count | target_tag |
|---|---|---|---|---|---|---|
| 1710 | chronicillness | 2 | 2019-12-03T17:49:17.395Z | 2019-12-03T17:49:17.395Z | 0 | NULL |
will
Tag.find_by(id: 1710).destroy
do the trick?
oh … the index is on LOWER(name)
Run the query like so:
select * from tags where name ilike 'chronicillness'
You should be getting 2 rows.
select * from tags where name ilike 'chronicillness'
| id | name | topic_count | created_at | updated_at | pm_topic_count | target_tag |
|---|---|---|---|---|---|---|
| 329 | chronicillness | 12 | 2017-08-22T00:17:38.824Z | 2017-08-22T00:17:38.824Z | 0 | NULL |
| 1710 | chronicillness | 2 | 2019-12-03T17:49:17.395Z | 2019-12-03T17:49:17.395Z | 0 | NULL |
could it be a - in between tag names?
i see two tags: chronicillness and chronic-illness here.
Well then that is the issue … I guess you delete 1710. 2 topics will be missing that tag afterwards.
#<Tag:0x00005607bb92cf48
id: 1710,
name: "chronicillness",
topic_count: 0,
created_at: Tue, 03 Dec 2019 17:49:17 UTC +00:00,
updated_at: Tue, 03 Dec 2019 17:49:17 UTC +00:00,
pm_topic_count: 0,
target_tag_id: nil>
Why do I feel like it was a dwarf that wasn’t associated with anything?
Deleting the tag brought more stuff!
discourse=# REINDEX SCHEMA CONCURRENTLY public;
WARNING: cannot reindex invalid index "public.tags_pkey_ccnew" concurrently, skipping
WARNING: cannot reindex invalid index "public.index_tags_on_name_ccnew" concurrently, skipping
WARNING: cannot reindex invalid index "public.index_tags_on_lower_name_ccnew" concurrently, skipping
WARNING: cannot reindex invalid index "pg_toast.pg_toast_309322_index_ccnew" concurrently, skipping
ERROR: could not create unique index "index_tags_on_name_ccnew1"
DETAIL: Key (name)=(time-management) is duplicated.
Same fix you got to repeat the process.
That’s what I was doing. but are those warnings okay? I don’t have to bother about those right?
yeah all warnings are correct, you have a corrupt index, it needs fixing first.
so all copycats are now gone and I’m only left with these warnings while reindexing:
discourse=# REINDEX SCHEMA CONCURRENTLY public;
WARNING: cannot reindex invalid index "public.tags_pkey_ccnew" concurrently, skipping
WARNING: cannot reindex invalid index "public.index_tags_on_name_ccnew" concurrently, skipping
WARNING: cannot reindex invalid index "public.index_tags_on_lower_name_ccnew" concurrently, skipping
WARNING: cannot reindex invalid index "public.tags_pkey_ccnew1" concurrently, skipping
WARNING: cannot reindex invalid index "public.index_tags_on_name_ccnew1" concurrently, skipping
WARNING: cannot reindex invalid index "public.index_tags_on_lower_name_ccnew1" concurrently, skipping
WARNING: cannot reindex invalid index "pg_toast.pg_toast_309322_index_ccnew" concurrently, skipping
WARNING: cannot reindex invalid index "pg_toast.pg_toast_309322_index_ccnew1" concurrently, skipping
REINDEX
is there anything that should be done to get rid of these warnings or we have to live with these.
This is confusing to me… we have no index names than end with ccnew … did you create these by hand?
I think those are used while migration from postgres 10 to 12 as I’ve seen these in almost every user whose indexes have had issues. E.g.
https://meta.discourse.org/t/postgresql-12-update/151236/208?u=itsbhanusharma
https://meta.discourse.org/t/postgresql-12-update/151236/237?u=itsbhanusharma
EDIT: comes from postgres itself
The recommended recovery method in such cases is to drop the invalid index and try again to perform
REINDEX CONCURRENTLY. The concurrent index created during the processing has a name ending in the suffixccnew, orccoldif it is an old index definition which we failed to drop. Invalid indexes can be dropped usingDROP INDEX, including invalid toast indexes.
ouch … are the proper indexes as well already on the table?
You can list the indexes on the table using data exporer I think.
If the correct ones are there, then you can just junk these problem indexes.
Let me check. I’ll report back
So the Indexes seem to exist
Duplicate indexes also exist for problem indexes with added ccnew or ccnew1
I don’t know a way to check if those indexes are valid but if nuking them is an option, I’ll be happy to nuke and reindex.
EDIT: will DROP INDEX work as suggested here ?
Yeah drop them all … no idea how they go there … DROP INDEX is what you want to use.
./launcher enter app
rails c
DB.exec('drop index tags_pkey_ccnew1')