I’m attempting to migrate a forum to a new server. Both servers are running the latest version of discourse docker. When importing the backup via command line, I get the following error:
ERROR: could not create unique index "index_incoming_referers_on_path_and_incoming_domain_id"
DETAIL: Key (path, incoming_domain_id)=(/search/, 418) is duplicated.
EXCEPTION: psql failed: DETAIL: Key (path, incoming_domain_id)=(/search/, 418) is duplicated.
This seems to the same or similar error as:
However, in my case the duplicated records are in the /search/ path rather than /m/search as is the case on the thread linked above.
I’ve connected to the container on the old server (./launcher enter app) and in the Rails console (rails c) I’ve tried to search for the duplicated records using:
and IncomingReferer.where("path LIKE '%/search%'")
However, this results in 100’s of records being displayed. How can I work out what records are duplicated, and how can I safely delete these and rebuild? The forum is currently working fine on the old server, we just need to move to new hardware.
DELETE FROM incoming_referers WHERE path LIKE '%/search/' AND id IN (6247);
discourse=# REINDEX SCHEMA CONCURRENTLY public;
WARNING: cannot reindex invalid index "public.incoming_referers_pkey_ccnew" concurrently, skipping
WARNING: cannot reindex invalid index "public.index_incoming_referers_on_path_and_incoming_domain_id_ccnew" concurrently, skipping
WARNING: cannot reindex invalid index "pg_toast.pg_toast_20732_index_ccnew" concurrently, skipping
Then I took another backup, copied it to the new server, and it imported successfully
It would be nice if the backup process could spot duplicates to avoid any issues, I was luckily that I had access to the original server which was still running. If I was restoring a cold backup, this would probably have been more of an issue>