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…

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

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

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

Same fix :slight_smile: 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" concur…

[image] itsbhanusharma: tags_pkey_ccnew This is confusing to me… we have no index names than end with ccnew … did you create these by hand?

Corrupt indexes in PG12, how do I fix?

Support Self-hosting

sam (Sam Saffron) May 25, 2020, 3:16am 11

Same fix you got to repeat the process.

Move a Discourse site to another VPS with rsync

Topic		Replies	Views
Can't restore due to corrupt indexes (with some clues on how to deal with corrupt indexes) Self-hosting	12	4770	January 6, 2020
Restoring from backup failing: failure creating postgres index Self-hosting	7	1008	May 28, 2020
Restore fails - could not create unique index Self-hosting	21	4030	July 4, 2020
2.7.0.beta2 upgrade failed with ERROR: duplicate key Self-hosting	24	2311	January 26, 2021
PostgreSQL 13 update Announcements	2	11162	September 13, 2023

Corrupt indexes in PG12, how do I fix?

Related topics