PG16.3 손상된 인덱스 수정 (백업에서 복원 불가 - 고유 인덱스 생성 실패)

제품 지원
1

Discourse를 새 서버로 이전하려고 했는데 백업 복원 과정에서 문제가 발생했습니다:

[2024-09-12 06:57:25] ERROR:  could not create unique index "unique_post_links"
[2024-09-12 06:57:25] DETAIL:  Key (topic_id, post_id, url)=(2026, 4639, http://www.metronet.com/~mob) is duplicated.
[2024-09-12 06:57:27] EXCEPTION: psql failed: DETAIL:  Key (topic_id, post_id, url)=(2026, 4639, http://www.metronet.com/~mob) is duplicated.

손상된 인덱스를 수정하는 방법에 대한 주제가 많이 있지만 대부분 오래되었습니다. 16과 같은 더 새로운 PG 버전에서 중복 항목을 찾는 방법과 수정하는 방법에 대한 확실한 가이드가 있을까요? 파일이 포함된 백업은 37GB, 파일이 없는 백업은 5GB입니다.

이전/복원 작업에 시간이 오래 걸리기 때문에, 새로운 이전/복원을 시도하기 전에 위에서 언급한 것 외에 더 많은 중복 항목이 있는지 확인하고 싶습니다.

2

며칠 동안 그런 일이 발생하지 않았습니다. 몇 년 전과 동일한 노트일 뿐입니다.

해야 할 일은 다른 서버(아직 실행 중이라면)에서 해당 인덱스를 재구성하고 중복된 값을 삭제(또는 다른 방식으로 복구)하는 것입니다. 인덱스가 마침내 재구성되면 백업을 한 번 더 수행한 후 새 서버에 복원하세요.

하지만 rsync를 사용하여 전체(최소한 업로드 폴더)를 이동하는 것을 시도해 볼 수도 있습니다.

3

중복 항목은 제거했지만, 제거해야 할 것 같은 이 추가 인덱스들이 남아 있습니다. 여기에 나열된 인덱스들을 모두 삭제할 수 있을까요?

discourse=# REINDEX SCHEMA CONCURRENTLY public;
WARNING:  cannot reindex invalid index "public.topic_links_pkey_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_extension_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_link_post_id_and_reflection_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_post_id_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_topic_id_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_user_id_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_user_and_clicks_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "public.unique_post_links_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "public.topic_links_pkey_ccnew1" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_extension_ccnew1" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_link_post_id_and_reflection_ccnew1" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_post_id_ccnew1" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_topic_id_ccnew1" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_user_id_ccnew1" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_user_and_clicks_ccnew1" concurrently, skipping
WARNING:  cannot reindex invalid index "public.unique_post_links_ccnew1" concurrently, skipping
WARNING:  cannot reindex invalid index "public.topic_links_pkey_ccnew2" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_extension_ccnew2" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_link_post_id_and_reflection_ccnew2" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_post_id_ccnew2" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_topic_id_ccnew2" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_user_id_ccnew2" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_user_and_clicks_ccnew2" concurrently, skipping
WARNING:  cannot reindex invalid index "public.unique_post_links_ccnew2" concurrently, skipping
WARNING:  cannot reindex invalid index "public.topic_links_pkey_ccnew3" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_extension_ccnew3" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_link_post_id_and_reflection_ccnew3" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_post_id_ccnew3" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_topic_id_ccnew3" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_user_id_ccnew3" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_user_and_clicks_ccnew3" concurrently, skipping
WARNING:  cannot reindex invalid index "public.unique_post_links_ccnew3" concurrently, skipping
WARNING:  cannot reindex invalid index "public.topic_links_pkey_ccnew4" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_extension_ccnew4" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_link_post_id_and_reflection_ccnew4" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_post_id_ccnew4" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_topic_id_ccnew4" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_user_id_ccnew4" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_user_and_clicks_ccnew4" concurrently, skipping
WARNING:  cannot reindex invalid index "public.unique_post_links_ccnew4" concurrently, skipping
WARNING:  cannot reindex invalid index "public.topic_links_pkey_ccnew5" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_extension_ccnew5" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_link_post_id_and_reflection_ccnew5" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_post_id_ccnew5" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_topic_id_ccnew5" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_user_id_ccnew5" concurrently, skipping
WARNING:  cannot reindex invalid index "public.index_topic_links_on_user_and_clicks_ccnew5" concurrently, skipping
WARNING:  cannot reindex invalid index "public.unique_post_links_ccnew5" concurrently, skipping
WARNING:  cannot reindex invalid index "pg_toast.pg_toast_102223_index_ccnew" concurrently, skipping
WARNING:  cannot reindex invalid index "pg_toast.pg_toast_102223_index_ccnew1" concurrently, skipping
WARNING:  cannot reindex invalid index "pg_toast.pg_toast_102223_index_ccnew2" concurrently, skipping
WARNING:  cannot reindex invalid index "pg_toast.pg_toast_102223_index_ccnew3" concurrently, skipping
WARNING:  cannot reindex invalid index "pg_toast.pg_toast_102223_index_ccnew4" concurrently, skipping
WARNING:  cannot reindex invalid index "pg_toast.pg_toast_102223_index_ccnew5" concurrently, skipping
REINDEX
discourse=#
4

ccnew 또는 ccnew’n’가 포함된 항목은 안전하게 삭제할 수 있습니다.