Fixing a corrupted index PG16.3 (unable to restore from backup - could not create unique index)

Product Support

AI-generated summary

omarfilip encountered a PostgreSQL 16.3 error during Discourse migration: a duplicate key in the unique_post_links index prevented backup restoration. Seeking updated guidance for PG16, they noted their 5GB (no files) or 37GB (with files) backup size and wished to identify all duplicates before retrying. pfaffman advised rebuilding the index on the source server, fixing duplicates, then backing up and restoring — or using rsync for uploads. After resolving duplicates, omarfilip reported leftover invalid indexes (e.g., ccnew, ccnew1, etc.) from concurrent reindex attempts and asked if they could be safely dropped. itsbhanusharma confirmed that any index with ccnew or similar naming patterns can be safely dropped, implying they’re temporary or failed artifacts.

1

I attempted to move my Discourse to a new server and ran into an issue while restoring the backup:

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

There are many topics about fixing a corrupted index, but most are old. Is there a definitive guide to finding and fixing the duplicates issue with a newer PG version, like 16? My backup with files is 37GB and without files it’s 5GB.

I’d like to find out if there are more duplicates beside the one above before attempting a new move/restore as it takes a long time to move this around.

2

It hasn’t happened in a while. It’s no different note than it was years ago.

The thing to do is to rebuild that index on the other server (given that it’s still running) and delete (or otherwise repair) the values that are duplicates, and when the index finally rebuilds, make another backup, and restore it to the new server.

But you might try moving the whole thing (but at least the uploads) with rsync.

3

I got rid of the duplicates, but I’m left with these extra indexes that I think I should nuke. Can I drop all of these listed here?

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

Anything with ccnew or ccnew’n’ should be safe to drop.