単なる推測ですが、私のコンspiracy理論は以下の通りです。
Reindex を同時に 2 回実行しました（最初は ccnew、次に ccnew1）。
両方ともエラーが発生しました。
おそらく、失敗すると元に戻さず、ゴミを残したまま終了してしまうのでしょう。
それは非常に非常に非常に可能性が高いようです。
ここに私の意見（$0.02）を付け加えます。
Toast インデックスはこの方法では削除できません。
私は以下の手順を踏みました。
su postgres
psql
\connect discourse
drop index pg_toast.pg_toast_309322_index_ccnew;
drop index pg_toast.pg_toast_309322_index_ccnew1;
上記は pg_toast にのみ適用されます。discourse ユーザーにはそのインデックスへのアクセス権がないためです。
PG::InsufficientPrivilege: ERROR: permission denied for schema pg_toast
トーストインデックスの削除は非常に危険です。ただ、ここでは他に選択肢がないようですね。はい、psql からそれを行う必要があります。
さて、楽しい再インデックスの登場です：
discourse=# REINDEX SCHEMA CONCURRENTLY public;
REINDEX
素晴らしい発見です！このクエリで「ccnew」を含むすべてのインデックス名を見つけました。
psql
\connect discourse
select tablename,indexname,indexdef from pg_indexes where indexname LIKE '%ccnew%';
incoming_referer.csv (7.2 KB)
なんと、incoming_referers テーブルに 30 個もの重複インデックスが存在していました。そこで、indexdef カラムを使ってこれらすべての ccnew インデックスが実際に重複していることを確認するクエリを実行しました。
select indexname,indexdef from pg_indexes where tablename = 'incoming_referers';
ccnew.csv (6.6 KB)
その後、それらすべてを正常に削除しました。
DROP INDEX incoming_referers_pkey_ccnew;
DROP INDEX incoming_referers_pkey_ccnew_ccnew;
DROP INDEX incoming_referers_pkey_ccnew1;
...以下、30 個すべてについて同様
その時点でスキーマ全体を再インデックスしましたが、再び 2 つの同じ incoming_referers ccnew インデックスの再構築に失敗し、さらに 3 つの pg_toast インデックスも発見されました。それらを削除して再度スキーマ全体を再インデックスしましたが、またエラーが発生。discourse スキーマ内にさらに多くの ccnew インデックスが見つかり、3 回目の再インデックスを実行しました……
エラーなしで完全な再インデックスを完了させることができません。毎回新しい ccnew インデックスが作成され、再構築に失敗し続けています。4 回フル再構築を試した後、ccnew インデックスを削除してあきらめました。非同時実行（non-concurrently）で再構築を試みることも可能ですが、それでは多大なダウンタイムが発生してしまいます。
いずれにせよ、PG10 から PG12 へアップグレードしてその後再インデックスを試みたユーザーの多くは、これらの余分な ccnew インデックスを持っていると思われます。それらはすべて削除すべきです。単にディスク容量を圧迫するだけでなく、何の利益もないディスク書き込み I/O を増大させるだけです。
Data-Explorer で同等の操作を実行しました。少し管理しやすかったです。
私が行ったことは、すべての ccnew/ccnew1/ccnew2…ccnewn インデックスを削除し、再インデックスを実行することでした。これで私の場合は解決しました。
ccnew は Postgres のインデックスをマークする仕組みであり、インデックス作成が何らかの理由で失敗した場合にこれらが残ってしまうのは、プロセスの非効率性によるものだと考えています。
まず問題の根本原因に対処することをお勧めします。私の場合は、インデックスを削除する前に重複タグを削除する必要がありました。たとえ重複タグが 1 つでも残っていると、正しくインデックスが作成されず、別の ccnew'n' インデックスが生成されて失敗します。
その通りです、まだ重複が残っています。非並列で非 ccnew インデックスを再構築することはできません。これらの重複行を削除する必要があります。
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.
本当に奇妙なのは、incoming_referer テーブルにはこれらの incoming_domain_id 値それぞれを持つ行が 1 行しか表示されないことです。なぜ重複しているのでしょうか？
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 または @riking さん、以下の通りこの 2 行を削除してもよいでしょうか？
DELETE FROM incoming_referers WHERE path='/search/' AND incoming_domain_id IN (1861,3433);
…結局、Postgres を学びつつあるようです（笑）。
いいえ、それでは両方が削除されてしまいます。WHERE句を使って2つのidを検索し、削除するidを1つだけ選んでください。
現在のクエリは破損したインデックスを使用しているため、それぞれ1行しか表示されていません。以下のように試してみてください。
... where path LIKE '%/search/' ...
OK、これで 43 行が取得されました。
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)
それでは、以下のコマンドを実行すべきでしょうか？
DELETE FROM incoming_referers WHERE path LIKE '%/search/' AND id IN (42845,43350,42849);
はい、その DELETE は正しいようです。クリーンなバックアップを取得できるはずです。
信じられないかもしれませんが、また失敗が。最初のセットでは、何らかの理由で %/search/ ワイルドカードによって検出されなかった /m/search に別の重複キーがあり、それを削除しました。再度インデックスを再構築しましたが（毎回ほぼ1時間かかりました！）、users(username_lower) で重複キーを示す失敗がさらに発生しました。
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.
しかし、実際には username_lower=john_smith の行は1つだけだったのです！探偵の帽子をかぶる時が来ました。
フォーラムの管理 UI を見ると、「john_smith」と「John_Smith」という2つの別々のユーザーがいました。大文字と小文字の違いに注意してください。そこで、4年間活動していなかったため、フォーラムレベルで全て小文字のユーザーを削除し、その後そのインデックスは無事に再構築されました。
さらに別の完全な再構築を実行しました。それぞれがほぼ1時間かかりましたが、ついに問題は解決したと思います。エラーは1つだけですが、重複はなく、pg_toast ccnew だけです。それを削除しました。
discourse=# REINDEX SCHEMA CONCURRENTLY public;
WARNING: cannot reindex invalid index "pg_toast.pg_toast_2783329_index_ccnew" concurrently, skipping
REINDEX
長い旅でした。
これらのデータベース修正は、将来の Discourse アップグレードで自動化される見込みはありますか？
Peter が一貫した再現手順を得られれば、将来の PostgreSQL アップグレードで PostgreSQL インデックス破損が解消される可能性が高いです。
同時実行中の REINDEX がテーブルに無効なデータがあるために失敗した場合、以下の手順を実行する必要があります。
-
こちらで行われたように、無効なデータを修正します。
-
以下のクエリを使用して無効なインデックスをリストします。
SELECT pg_class.relname FROM pg_class, pg_index WHERE pg_index.indisvalid = false AND pg_index.indexrelid = pg_class.oid; -
上記でリストされた各無効なインデックスを
DROP INDEX <indexname>;を使用して削除します。 -
再度
REINDEXを実行します。
2.5.0beta5 へのアップグレードを実行し、データベースの再インデックス作成 という更新後のガイダンスに従ったところ、以下のエラーが発生しました。
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.
この件については試行錯誤したくないのですが、重複データを安全に削除する方法をご教示ください。
いいえ、アップグレードがインデックスを破損させるわけではありません。単にインデックスが破損していることを指摘しているだけです。別のサーバー（pg10 を実行しているサーバーでも構いません）にバックアップを復元して確認するか、既存のインストールでインデックスを再構築してみてください。破損したインデックスの原因は明確ではありませんが、pg12 ではそれが起こる可能性が低くなるという希望があります。
アップグレードにはいくつかのパフォーマンス上の利点がありますが、それを延期することも悪い考えではありません。
今では手遅れなので、次のステップについて引き続きアドバイスをお願いします。
以下のようなクエリを実行すると、該当する行が取得できます。
select id, plugin_name, key from plugin_store_rows where plugin_name like '%discourse-data-explorer%'
それらの行を削除しても安全だと思います。
ilike が必要になると思います