During an upgrade to the latest Discourse version, carlokok encountered a migration failure due to a PostgreSQL unique constraint violation: duplicate email entries for talk@remobjects.com. The root cause, explained by sam, stems from PostgreSQL collation changes during major upgrades, which can corrupt indexes and allow duplicate rows despite constraints. The fix involves identifying and either deleting or renaming the duplicate email entry, then proceeding with the upgrade. sam emphasized the critical need to run a full database reindex afterward using REINDEX DATABASE VERBOSE to ensure index integrity across the system. carlokok confirmed the solution worked but asked whether any special steps were needed to rerun migrations—sam reiterated that reindexing is essential regardless of migration status. Jonathan5 raised a broader concern: whether there’s a way to proactively check forum health to prevent such issues. While no automated health-check tool was suggested, the incident underscores the importance of validating database integrity—especially after major PostgreSQL upgrades—to avoid silent corruption that only surfaces during migrations or data operations.
Oh … we have seen this before on major version upgrades of PG. @saj recently worked on something here.
The long story is that on some PG upgrades the DB collation changes underneath you and you due to libc changing stuff. You have no choice but to reindex.
What happened to you is that:
You did a major upgrade
Your index become somewhat corrupt
A second insert of a user for talk@remobjects.com was made - you now have a duplicate row despite the index disallowing it.
We touch the row and it highlights this issue cause it can not update an unrelated column
Fix is:
Find the user ids of the 2 users sharing the talk@remobjects.com email.
Either delete or rename the email on one of them
Proceed as planned
Do a full db reindex urgently to catch other issues
Your solution did solve it but the “upgrade” already passed (e no more updates); do I need to do anything special to rerun these migrations? I did ofc delete the dupe emails.