Исправление поврежденного индекса 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.

Есть много тем о исправлении повреждённых индексов, но большинство из них устарели. Существует ли исчерпывающее руководство по поиску и исправлению проблемы дубликатов в более новых версиях PostgreSQL, например 16? Размер моей резервной копии с файлами составляет 37 ГБ, а без файлов — 5 ГБ.

Я хотел бы узнать, есть ли ещё дубликаты, помимо указанного выше, прежде чем пытаться выполнить новый перенос/восстановление, так как перемещение данных занимает много времени.

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’, можно безопасно удалить.