Alright, we made it out on the other side and we’re back online. Thanks for the hints @Falco .
To help other people with their problems, here a breakdown of the things we did.
So we had a couple of corrupt indexes which caused the import to fail. We were able to get it fixed by manually deleting the duplicates. We also had 8 users which had a duplicate username_lower
(too many mike’s and marco’s). We renamed those by updating both username
and username_lower
. From the user data we noticed that the first corruption has happened in December 2019.
Instead of doing “make backup” -> “restore backup” -> “fail on duplicate” -> “fix” cycle, we decided to reindex all indexes. We found all indexes with unique constraints with the following query:
select idx.relname as index_name,
insp.nspname as index_schema,
tbl.relname as table_name,
tnsp.nspname as table_schema
from pg_index pgi
join pg_class idx on idx.oid = pgi.indexrelid
join pg_namespace insp on insp.oid = idx.relnamespace
join pg_class tbl on tbl.oid = pgi.indrelid
join pg_namespace tnsp on tnsp.oid = tbl.relnamespace
where pgi.indisunique --<< only unique indexes
and tnsp.nspname = 'public'
Once all indexes worked, we were able to make a backup and import it correctly in the new instance. Migrations ran as expected, we swapped instances and we got up and running Cheers to the resilience of Discourse
Thanks again @Falco.
Have a good weekend