# Corrupt indexes in PG12, how do I fix? **URL:** \ **Category:** Self-hosting\ **Created:** [May 25, 2020, 2:45am UTC](https://meta.discourse.org/t/corrupt-indexes-in-pg12-how-do-i-fix/152570 "2020-05-25T02:45:43Z")\ **Posts on this page:** 20\ **Page:** 2
**Author:** ![itsbhanusharma](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/itsbhanusharma/32/180717_2.png) [@itsbhanusharma](https://meta.discourse.org/u/itsbhanusharma)\ **Post date:** [May 25, 2020, 5:23am UTC](https://meta.discourse.org/t/corrupt-indexes-in-pg12-how-do-i-fix/152570/21 "2020-05-25T05:23:29Z")
I’m just speculating but here is my tinfoil theory: I ran Reindex concurrently twice (so first ccnew, ccnew1) and both the times it presented errors. maybe when it fails, it just doesn’t revert, it just leaves the garbage and quits. ---
**Author:** ![sam](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/sam/32/102149_2.png) [@sam](https://meta.discourse.org/u/sam)\ **Post date:** [May 25, 2020, 5:25am UTC](https://meta.discourse.org/t/corrupt-indexes-in-pg12-how-do-i-fix/152570/22 "2020-05-25T05:25:47Z")
This sounds very very very likely. ---
**Author:** ![itsbhanusharma](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/itsbhanusharma/32/180717_2.png) [@itsbhanusharma](https://meta.discourse.org/u/itsbhanusharma)\ **Post date:** [May 25, 2020, 5:31am UTC](https://meta.discourse.org/t/corrupt-indexes-in-pg12-how-do-i-fix/152570/23 "2020-05-25T05:31:08Z")
> [@sam](#): > > `DB.exec('drop index tags_pkey_ccnew1')` Just adding my $0.02 here, Toast indexes can’t be dropped this way, I had to: ```plaintext su postgres psql \connect discourse drop index pg_toast.pg_toast_309322_index_ccnew; drop index pg_toast.pg_toast_309322_index_ccnew1; ``` The above just applies to pg toast because discourse user does not have access to that index. `PG::InsufficientPrivilege: ERROR: permission denied for schema pg_toast` ---
**Author:** ![sam](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/sam/32/102149_2.png) [@sam](https://meta.discourse.org/u/sam)\ **Post date:** [May 25, 2020, 5:32am UTC](https://meta.discourse.org/t/corrupt-indexes-in-pg12-how-do-i-fix/152570/24 "2020-05-25T05:32:24Z")
dropping toast indexes is super scary. I guess you have no choice here though. Yeah you need to do that from psql. ---
**Author:** ![itsbhanusharma](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/itsbhanusharma/32/180717_2.png) [@itsbhanusharma](https://meta.discourse.org/u/itsbhanusharma)\ **Post date:** [May 25, 2020, 5:34am UTC](https://meta.discourse.org/t/corrupt-indexes-in-pg12-how-do-i-fix/152570/25 "2020-05-25T05:34:46Z")
and here comes the happy reindex: ```plaintext discourse=# REINDEX SCHEMA CONCURRENTLY public; REINDEX ``` 🥳 ---
**Author:** ![Wingtip](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/wingtip/32/102039_2.png) [@Wingtip](https://meta.discourse.org/u/Wingtip)\ **Post date:** [May 25, 2020, 2:08pm UTC](https://meta.discourse.org/t/corrupt-indexes-in-pg12-how-do-i-fix/152570/26 "2020-05-25T14:08:06Z")
Great catch! I found all indices with “ccnew” in the name via this query. ```plaintext psql \connect discourse select tablename,indexname,indexdef from pg_indexes where indexname LIKE '%ccnew%'; ``` [incoming\_referer.csv](https://meta.discourse.org/uploads/short-url/vZNuTT4p8yKYjCui3P0MLvPcD20.csv) (7.2 KB) Turned out I had a whopping _30_ of them, all on the incoming\_referers table. So I verified all the ccnew indices were actually duplicates via the indexdef column in this query. ```plaintext select indexname,indexdef from pg_indexes where tablename = 'incoming_referers'; ``` [ccnew.csv](https://meta.discourse.org/uploads/short-url/bIWUX4KCSZ0Q3g4ExiRhIYYccNE.csv) (6.6 KB) And then dropped all of them successfully. ```plaintext DROP INDEX incoming_referers_pkey_ccnew; DROP INDEX incoming_referers_pkey_ccnew_ccnew; DROP INDEX incoming_referers_pkey_ccnew1; ...and so on for all 30 ``` At that point I reindexed the whole schema again and it again was unable to rebuild two of the same incoming\_referers ccnew indices, and also found three pg\_toast indices. I dropped them and then reindexed the whole schema yet again, again more errors, found a bunch more ccnew indices in the discourse schema, reindexed a third time… I can’t get a full reindex to complete without errors, it keeps creating and then failing to rebuild new ccnew indexes every time. After 4 full rebuilds I deleted the ccnew indexes then gave up on it. I guess I could try to rebuild non-concurrently but that would cause a bunch of downtime. Anyway, my guess is most users upgrading from PG10 to 12 who tried to reindex afterwards have these extra ccnew indexes and they should all be deleted. They’ll just take up space and multiply disk write I/O for no benefit. ---
**Author:** ![itsbhanusharma](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/itsbhanusharma/32/180717_2.png) [@itsbhanusharma](https://meta.discourse.org/u/itsbhanusharma)\ **Post date:** [May 25, 2020, 2:19pm UTC](https://meta.discourse.org/t/corrupt-indexes-in-pg12-how-do-i-fix/152570/27 "2020-05-25T14:19:05Z")
> [@Wingtip](#): > > this query. I ran it’s equivalent in Data-Explorer. was slightly easier to manage. > [@Wingtip](#): > > Anyway, my guess is most users upgrading from PG10 to 12 who tried to reindex afterwards have these extra ccnew indexes and they should all be deleted. They’ll just take up space and multiply disk write I/O for no benefit. What I did was that I took all the ccnew/ccnew1/ccnew2…ccnewn indexes, nuked them and reindexed. That fixed it for me. ccnew is a postgres thing of marking the indexes and I think it is some inefficiency in the process that causes these to be left if an indexing fails for some reason. I can only suggest you first work upon the root cause of the problem. I had duplicated tags that I had to delete before proceeding to nuking indexes. if I was left with even a single duplicated tag, it will not index properly and create another `ccnew'n'` index and fail. ---
**Author:** ![Wingtip](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/wingtip/32/102039_2.png) [@Wingtip](https://meta.discourse.org/u/Wingtip)\ **Post date:** [May 25, 2020, 2:30pm UTC](https://meta.discourse.org/t/corrupt-indexes-in-pg12-how-do-i-fix/152570/28 "2020-05-25T14:30:49Z")
You’re right, I still have duplicates-- can’t rebuild the non-ccnew indexes non-concurrently. I need to remove these duplicate rows. ```plaintext discourse=# reindex index index_incoming_referers_on_path_and_incoming_domain_id; ERROR: could not create unique index "index_incoming_referers_on_path_and_incoming_domain_id" DETAIL: Key (path, incoming_domain_id)=(/search/, 3433) is duplicated. discourse=# reindex index index_incoming_referers_on_path_and_incoming_domain_id; ERROR: could not create unique index "index_incoming_referers_on_path_and_incoming_domain_id" DETAIL: Key (path, incoming_domain_id)=(/search/, 1861) is duplicated. ``` What’s really weird is I only see one row with each of those incoming\_domain\_id values in incoming\_referer. So why are they duplicates? ```plaintext discourse=# select * from incoming_referers where path='/search/' AND incoming_domain_id IN (1861,3433); id | path | incoming_domain_id -------+----------+-------------------- 42845 | /search/ | 1861 40763 | /search/ | 3433 (2 rows) ``` @sam or @riking should I delete these two rows as follows: ```plaintext DELETE FROM incoming_referers WHERE path='/search/' AND incoming_domain_id IN (1861,3433); ``` … I guess I’m learning postgres after all, heh. ---
**Author:** ![riking](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/riking/32/170938_2.png) [@riking](https://meta.discourse.org/u/riking)\ **Post date:** [May 25, 2020, 4:12pm UTC](https://meta.discourse.org/t/corrupt-indexes-in-pg12-how-do-i-fix/152570/29 "2020-05-25T16:12:52Z")
No, that will delete both of them - you want to use the `WHERE` to find the two `id`s, and pick just one of the `id`s to delete. Your query is using the corrupt index, which is why you’re only seeing one row for each. Try this: ``` ... where path LIKE '%/search/' ... ``` ---
**Author:** ![Wingtip](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/wingtip/32/102039_2.png) [@Wingtip](https://meta.discourse.org/u/Wingtip)\ **Post date:** [May 25, 2020, 4:36pm UTC](https://meta.discourse.org/t/corrupt-indexes-in-pg12-how-do-i-fix/152570/30 "2020-05-25T16:36:23Z")
OK, that brings up 43 rows. ```plaintext discourse=# select * from incoming_referers where path LIKE '%/search/' ORDER BY incoming_domain_id; id | path | incoming_domain_id -------+-------------+-------------------- 878 | /search/ | 63 33457 | /do/search/ | 567 1580 | /search/ | 602 1888 | /search/ | 663 42983 | /search/ | 1259 4896 | /search/ | 1788 42845 | /search/ | 1861 5162 | /search/ | 1861 5176 | /search/ | 1866 43350 | /search/ | 1905 17238 | /search/ | 1905 20689 | /search/ | 1982 5781 | /hg/search/ | 1987 8031 | /search/ | 2665 10325 | /search/ | 3192 11289 | /search/ | 3414 40763 | /search/ | 3433 42849 | /search/ | 3433 13087 | /search/ | 3895 13159 | /search/ | 3949 13802 | /do/search/ | 4051 14407 | /search/ | 4209 14507 | /search/ | 4211 15394 | /search/ | 4230 15533 | /search/ | 4258 45274 | /search/ | 5303 20923 | /search/ | 5400 21317 | /search/ | 5534 22928 | /search/ | 5918 22956 | /search/ | 5926 37448 | /search/ | 6393 25094 | /search/ | 6412 25594 | /search/ | 6547 39655 | /search/ | 6596 27371 | /search/ | 6986 27452 | /a/search/ | 7003 27623 | /search/ | 7041 31041 | /search/ | 7767 36943 | /search/ | 8622 37381 | /search/ | 8711 37411 | /search/ | 8716 40424 | /search/ | 9124 44451 | /search/ | 9525 (43 rows) ``` So should I run the following? ```plaintext DELETE FROM incoming_referers WHERE path LIKE '%/search/' AND id IN (42845,43350,42849); ``` ---
**Author:** ![riking](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/riking/32/170938_2.png) [@riking](https://meta.discourse.org/u/riking)\ **Post date:** [May 25, 2020, 5:09pm UTC](https://meta.discourse.org/t/corrupt-indexes-in-pg12-how-do-i-fix/152570/31 "2020-05-25T17:09:07Z")
Yep, that DELETE looks correct and should allow you to take a clean backup. ---
**Author:** ![Wingtip](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/wingtip/32/102039_2.png) [@Wingtip](https://meta.discourse.org/u/Wingtip)\ **Post date:** [May 25, 2020, 8:01pm UTC](https://meta.discourse.org/t/corrupt-indexes-in-pg12-how-do-i-fix/152570/32 "2020-05-25T20:01:00Z")
Believe it or not, more failures. First set, I found another duplicate key on /m/search which wasn’t caught by the %/search/ wildcard for some reason and deleted that. Reindexed again (each time taking nearly an hour!) and more failures showing a duplicate key on users(username\_lower). ```plaintext discourse=# reindex index index_users_on_username_lower; ERROR: could not create unique index "index_users_on_username_lower" DETAIL: Key (username_lower)=(john_smith) is duplicated. ``` But the thing is, there was only one row with username\_lower=john\_smith! Time for the detective hat. Looking at the forum admin UI, we had two separate users respectively named “john\_smith” and “John\_Smith”-- note the different capitalization. So I nuked the all-lowercase one at the forum level as he hadn’t been active in 4 years and then that index rebuilt OK. Ran yet another full rebuild, each one taking nearly an hour, and FINALLY I think we’re good-- only one error, but no duplicates, just a pg\_toast ccnew. I dropped it. ```plaintext discourse=# REINDEX SCHEMA CONCURRENTLY public; WARNING: cannot reindex invalid index "pg_toast.pg_toast_2783329_index_ccnew" concurrently, skipping REINDEX ``` It’s been a journey. ---
**Author:** ![adrelanos](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/adrelanos/32/121409_2.png) [@adrelanos](https://meta.discourse.org/u/adrelanos)\ **Post date:** [May 25, 2020, 10:07pm UTC](https://meta.discourse.org/t/corrupt-indexes-in-pg12-how-do-i-fix/152570/33 "2020-05-25T22:07:16Z")
Can/will these database fixes be automated in a future discousre upgrade? ---
**Author:** ![sam](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/sam/32/102149_2.png) [@sam](https://meta.discourse.org/u/sam)\ **Post date:** [May 25, 2020, 10:08pm UTC](https://meta.discourse.org/t/corrupt-indexes-in-pg12-how-do-i-fix/152570/34 "2020-05-25T22:08:39Z")
More likely pg index corruption will stop in a future pg upgrade, if Peter gets a consistent repro ---
**Author:** ![Falco](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/falco/32/179432_2.png) [@Falco](https://meta.discourse.org/u/Falco)\ **Post date:** [May 27, 2020, 8:25pm UTC](https://meta.discourse.org/t/corrupt-indexes-in-pg12-how-do-i-fix/152570/35 "2020-05-27T20:25:58Z")
If a concurrent REINDEX fails because the table has invalid data, you need to: 1. Fix the invalid data, like it was done [here](https://meta.discourse.org/t/corrupt-indexes-in-pg12-how-do-i-fix/152570/5). 2. List the invalid indexes using `SELECT pg_class.relname FROM pg_class, pg_index WHERE pg_index.indisvalid = false AND pg_index.indexrelid = pg_class.oid;` 3. Drop each invalid index listed above using `DROP INDEX ;` 4. Try the `REINDEX` again. ---
**Author:** ![omarfilip](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/omarfilip/32/208019_2.png) [@omarfilip](https://meta.discourse.org/u/omarfilip)\ **Post date:** [May 29, 2020, 3:13am UTC](https://meta.discourse.org/t/corrupt-indexes-in-pg12-how-do-i-fix/152570/36 "2020-05-29T03:13:35Z")
After running the upgrade to 2.5.0beta5 and following the post-update guidance to [reindex the database](https://meta.discourse.org/t/postgresql-12-update/151236#re-indexing-your-database), I get this: ``` discourse=# REINDEX SCHEMA CONCURRENTLY public; ERROR: could not create unique index "index_plugin_store_rows_on_plugin_name_and_key_ccnew" DETAIL: Key (plugin_name, key)=(discourse-data-explorer, q:-10) is duplicated. ``` I’d rather not experiment with this one… so, how do I safely delete the duplicate? ---
**Author:** ![pfaffman](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/pfaffman/32/120154_2.png) [@pfaffman](https://meta.discourse.org/u/pfaffman)\ **Post date:** [May 29, 2020, 4:14am UTC](https://meta.discourse.org/t/corrupt-indexes-in-pg12-how-do-i-fix/152570/38 "2020-05-29T04:14:48Z")
> [@johanmueller](#): > > This is how to fix corrupt PG 12 indexes. Do not follow nor perform this upgrade. No. The upgrade doesn’t corrupt your index, it just points out that your index is corrupt. You can check by trying to restore your backup to another server (even one that’s running pg10). Or try rebuilding your index on your existing install. It’s not clear what’s causing the corrupt indexes, but there is hope that pg12 will be less likely to have it happen. There are some performance benefits of the upgrade, but holding off on it is not a bad idea ---
**Author:** ![omarfilip](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/omarfilip/32/208019_2.png) [@omarfilip](https://meta.discourse.org/u/omarfilip)\ **Post date:** [May 29, 2020, 6:08pm UTC](https://meta.discourse.org/t/corrupt-indexes-in-pg12-how-do-i-fix/152570/40 "2020-05-29T18:08:34Z")
> [@pfaffman](#): > > holding off on it is not a bad idea Too late now, so I’m still looking for guidance re. next steps. ---
**Author:** ![pfaffman](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/pfaffman/32/120154_2.png) [@pfaffman](https://meta.discourse.org/u/pfaffman)\ **Post date:** [May 29, 2020, 7:05pm UTC](https://meta.discourse.org/t/corrupt-indexes-in-pg12-how-do-i-fix/152570/41 "2020-05-29T19:05:26Z")
Something like `select id, plugin_name, key from plugin_store_rows where plugin_name like '%discourse-data-explorer%'")` should give you the rows. I think it’s safe enough to delete them. ---
**Author:** ![itsbhanusharma](https://sea3.discourse-cdn.com/meta/user_avatar/meta.discourse.org/itsbhanusharma/32/180717_2.png) [@itsbhanusharma](https://meta.discourse.org/u/itsbhanusharma)\ **Post date:** [May 29, 2020, 7:08pm UTC](https://meta.discourse.org/t/corrupt-indexes-in-pg12-how-do-i-fix/152570/42 "2020-05-29T19:08:27Z")
> [@pfaffman](#): > > like I think `ilike` would be needed [Previous page](https://meta.discourse.org/t/corrupt-indexes-in-pg12-how-do-i-fix/152570.md?page=1) [Next page](https://meta.discourse.org/t/corrupt-indexes-in-pg12-how-do-i-fix/152570.md?page=3)