Import failing with `could not create unique index`

installation
6

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 :+1: Cheers to the resilience of Discourse :beers:

Thanks again @Falco.

Have a good weekend :slight_smile:

6 Likes