Correction d'un index corrompu PG16.3 (impossible de restaurer à partir de la sauvegarde - impossible de créer un index unique)

Soutien
1

J’ai tenté de déplacer mon Discourse vers un nouveau serveur et j’ai rencontré un problème lors de la restauration de la sauvegarde :

[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.

Il existe de nombreux sujets sur la correction d’un index corrompu, mais la plupart sont anciens. Existe-t-il un guide définitif pour trouver et corriger le problème des doublons avec une version plus récente de PG, comme la 16 ? Ma sauvegarde avec les fichiers pèse 37 Go et sans les fichiers, elle pèse 5 Go.

J’aimerais savoir s’il existe d’autres doublons en plus de celui mentionné ci-dessus avant de tenter une nouvelle migration/restauration, car cela prend beaucoup de temps pour déplacer ces données.

1 « J'aime »
2

Cela ne s’est pas produit depuis un certain temps. Ce n’est pas différent de ce que c’était il y a des années.

La chose à faire est de reconstruire cet index sur l’autre serveur (étant donné qu’il fonctionne toujours) et de supprimer (ou de réparer autrement) les valeurs qui font doublon, et lorsque l’index sera enfin reconstruit, faites une autre sauvegarde et restaurez-la sur le nouveau serveur.

Mais vous pourriez essayer de déplacer l’ensemble (mais au moins les téléchargements) avec rsync.

3 « J'aime »
3

J’ai supprimé les doublons, mais il me reste ces index supplémentaires que je pense devoir supprimer. Puis-je supprimer tous ceux qui sont listés ici ?

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

Tout ce qui contient ccnew ou ccnew’n’ devrait pouvoir être supprimé.

2 « J'aime »