Postgres alpine image introduces unique constraint index errors

[hubertdeng123]

Between self-hosted releases 24.4.2 and 24.5.1, we've noticed that users have been experiencing issues with duplicate environments/releases. This is likely due to a mistake on our side to introduce the alpine version of postgres here. This has since been reverted back to the debian image, but there is likely quite a few of you experiencing issues.

We are creating this issue to document user issues and potential solutions. From here on out, we will only be using the debian image of postgres. We have since deleted self-hosted versions 24.4.2, 24.5.0, and 24.5.1

#3161
#3166

[hubertdeng123]

For users that are attempting to upgrade from the alpine image to the latest 24.6.0 or above, all indexes involving columns of type text, varchar, char, and citext should be reindexed before the instance is put into production (source).

For users that are attempting to fix duplicate rows of data, the process may be pretty manual 🥲. It will likely involve removing the duplicate rows of data, along with models with foreign key relations.

[candux]

I had the same problems and fixed them in the database with the follwing commands!
THIS is dangerous and will delete data! Use at your own risk! Make a backup before

ALTER TABLE ONLY public.sentry_commitfilechange
    DROP CONSTRAINT sentry_commitfilechange_commit_id_4c6f7ec25af34227_uniq;
DELETE from public.sentry_commitfilechange ou where (SELECT count(*) from public.sentry_commitfilechange inr where inr.commit_id = ou.commit_id and inr.filename = ou.filename) > 1;
ALTER TABLE ONLY public.sentry_commitfilechange
    ADD CONSTRAINT sentry_commitfilechange_commit_id_4c6f7ec25af34227_uniq UNIQUE (commit_id, filename);
    

DROP INDEX sentry_release_organization_id_f0a7ec9ba96de76;
DELETE from sentry_release_project where release_id in (SELECT id from public.sentry_release ou where (SELECT count(*) from public.sentry_release inr where inr.organization_id = ou.organization_id and inr.version = ou.version) > 1);

DELETE from sentry_releaseprojectenvironment where release_id in (SELECT id from public.sentry_release ou where (SELECT count(*) from public.sentry_release inr where inr.organization_id = ou.organization_id and inr.version = ou.version) > 1);

Delete from sentry_grouphash where group_id in (SELECT id from sentry_groupedmessage where first_release_id in (SELECT id from public.sentry_release ou where (SELECT count(*) from public.sentry_release inr where inr.organization_id = ou.organization_id and inr.version = ou.version) > 1));

DELETE from sentry_groupedmessage where first_release_id in (SELECT id from public.sentry_release ou where (SELECT count(*) from public.sentry_release inr where inr.organization_id = ou.organization_id and inr.version = ou.version) > 1);

DELETE from public.sentry_release ou where (SELECT count(*) from public.sentry_release inr where inr.organization_id = ou.organization_id and inr.version = ou.version) > 1;

CREATE UNIQUE INDEX sentry_release_organization_id_f0a7ec9ba96de76 ON public.sentry_release USING btree (organization_id, version);

REINDEX DATABASE postgres;
REINDEX system postgres;

In case there are more duplicates the reindex will fail and you have to delete thes duplicates in a similar way.

[MingNiu]

@hubertdeng123 Aren't you going to provide an official version of the SQL upgrade script?

[hubertdeng123]

@candux Thanks for the input!

@MingNiu We're trying to figure out the best way to go about this. Different folks will have different ways they'd like to address the potential duplicate environments/releases/etc in their self-hosted instance. Merging data in postgres is extremely dangerous and it's not something that we think should be done as a general solution by us. For some people, they'd like to maybe keep the latest data while others would like to keep the data up until the point where data corruption occurred.

[MingNiu]

@hubertdeng123 ok.

If I only want to keep data such as projects, members, configurations, etc., and clearing the issue record is acceptable. So can you provide the official upgrade script?

We are not familiar with the table structure and it is too difficult for us to manually fix the data.