단순한 추측이지만, 제 개인적인 가설은 다음과 같습니다:
Reindex를 두 번 동시에 실행했습니다(즉, 먼저 ccnew, 그다음 ccnew1).
그리고 두 번 모두 오류가 발생했습니다.
아마 실패했을 때 롤백을 하지 않고 그냥 잔여물을 남겨둔 채 종료되는 것 같습니다.
이건 정말, 정말, 정말 그럴 가능성이 높아 보입니다.
여기 제 의견을 조금만 덧붙이고 싶습니다.
이런 방식으로 토스트 인덱스를 삭제할 수 없습니다.
저는 다음과 같이 실행해야 했습니다:
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에서 그렇게 해야 합니다.
그리고 기다리던 재색인(reindex)이 시작됩니다:
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개도 모두
그 시점에서 스키마 전체를 다시 인덱싱했지만, 여전히 incoming_referers의 동일한 ccnew 인덱스 두 개를 재구성하지 못했고, pg_toast 인덱스 세 개도 발견했습니다. 그것들을 삭제하고 스키마 전체를 다시 한 번 재인덱싱했지만, 또다시 더 많은 오류가 발생했고 discourse 스키마에서 더 많은 ccnew 인덱스를 발견했으며, 세 번째로 재인덱싱했습니다…
오류 없이 완전한 재인덱싱을 완료할 수 없습니다. 매번 새로운 ccnew 인덱스를 생성하고 재구성에 실패하는 일이 반복됩니다. 4번의 전체 재구성을 거친 후 ccnew 인덱스를 삭제하고 포기했습니다. 비동기적이지 않게(non-concurrently) 재구성을 시도해 볼 수는 있지만, 그렇게 하면 상당한 다운타임이 발생할 것입니다.
어쨌든, 제 추측으로는 PG10에서 12로 업그레이드한 후 재인덱싱을 시도한 대부분의 사용자에게 이러한 추가 ccnew 인덱스가 있을 것이며, 그것들은 모두 삭제해야 합니다. 그것들은 단순히 공간을 차지하고, 아무런 이득 없이 디스크 쓰기 I/O를 배가시킬 뿐입니다.
Data-Explorer에서 해당 쿼리의 동등한 쿼리를 실행해 보았습니다. 관리하기가 약간 더 쉬웠습니다.
제가 한 일은 모든 ccnew/ccnew1/ccnew2…ccnewn 인덱스를 가져와 삭제한 후 재인덱싱하는 것이었습니다. 그렇게 하니까 제 문제는 해결되었습니다.
ccnew는 인덱스를 표시하는 PostgreSQL의 기능이며, 인덱싱이 어떤 이유로든 실패할 경우 이러한 인덱스가 남아 있게 만드는 과정의 비효율성이라고 생각합니다.
문제의 근본 원인을 먼저 해결해 보시길 권합니다. 저는 인덱스를 삭제하기 전에 삭제해야 했던 중복된 태그가 있었습니다. 만약 중복된 태그가 하나라도 남아 있다면, 인덱싱이 제대로 수행되지 않고 또 다른 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 값에 해당하는 행이 하나씩만 보인다는 것입니다. 그런데 왜 중복이라고 하는 걸까요?
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, 아래처럼 이 두 행을 삭제해도 될까요?
DELETE FROM incoming_referers WHERE path='/search/' AND incoming_domain_id IN (1861,3433);
… 결국 postgres를 배우고 있나 봅니다, 헷.
아니요, 그렇게 하면 두 개 모두 삭제됩니다. WHERE를 사용하여 두 개의 id를 찾고, 그중 하나만 선택하여 삭제해야 합니다.
현재 쿼리는 손상된 인덱스를 사용하고 있어 각 항목마다 한 줄씩만 표시되고 있습니다. 아래를 시도해 보세요:
... where path LIKE '%/search/' ...
좋아요, 그러면 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인 행이 단 하나뿐이라는 점이었습니다. 탐정 모자를 써야 할 때입니다.
포럼 관리자 UI를 살펴보니, 각각 "john_smith"과 "John_Smith"라는 이름의 두 명의 독립된 사용자가 있었습니다. 대소문자가 다릅니다. 그래서 4년 동안 활동이 없었던 소문자만 있는 계정을 포럼 레벨에서 삭제했고, 그 후 인덱스가 정상적으로 재구축되었습니다.
다시 전체 재구축을 실행했는데, 역시 거의 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 업그레이드에서 자동화될 수 있나요?
피터가 일관된 재현 방법을 찾게 된다면, 향후 PostgreSQL 업그레이드에서 pg 인덱스 손상 문제가 해결될 가능성이 더 높습니다.
동시 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가 필요할 것 같습니다.