修复已损坏的索引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.

有很多关于修复损坏索引的主题，但大多数都很旧。对于像 PG 16 这样的较新版本，是否有关于查找和修复重复项问题的明确指南？我的备份（包含文件）为 37GB，不含文件为 5GB。

我想在尝试新的迁移/恢复之前，找出除了上面列出的之外是否还有更多重复项，因为迁移这些数据需要很长时间。

1 个赞
2

这种情况已经很久没有发生了。和几年前相比，并没有什么不同。

要做的是在另一个服务器上重建该索引（前提是它仍在运行），然后删除（或以其他方式修复）重复的值，当索引最终重建后，再进行一次备份，并将其恢复到新服务器。

但你可以尝试使用 rsync 移动整个东西（但至少是上传的文件）。

3 个赞
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’ 的内容都可以安全删除。

2 个赞