Restore fails with UniqueViolation: duplicate key value violates unique constraint

[stayallive]

Self-Hosted Version

23.7.0.dev0 b4100de

CPU Architecture

x86_64

Docker Version

20.10.7

Docker Compose Version

2.19.0

Steps to Reproduce

1. Run ./scripts/backup.sh (done on another instance with data but exact same version/config/keys/everything)
2. Run ./scripts/reset.sh (only needed if already attempted to restore once)
3. Run ./install.sh
4. Run ./scripts/restore.sh

Expected Result

Have an exact replica of the instance without any historical data.

Actual Result

UniqueViolation: duplicate key value violates unique constraint "sentry_useremail_user_id_email_ade975f1_uniq"
DETAIL:  Key (user_id, email)=(1, [email protected]) already exists.

When removing all sentry.useremail entries that have the same email as their user field it errored on the default project being duplicated and there I gave up and reverted back since I was not confident in what this backup was doing with my manual edits.

Example of the sentry.useremail where it fails on (a user with that email adress does exists ofcourse, possible that the restore of the user creates the sentry.useremail already?):

{
  "model": "sentry.useremail",
  "pk": 1,
  "fields": {
    "user": [
      "[email protected]"
    ],
    "email": "[email protected]",
    "validation_hash": "",
    "date_hash_added": "2018-06-23T17:05:47.312Z",
    "is_verified": true
  }
},

it's possible ./install.sh does a little too much since I believe it creates a default project but the duplicate error on the sentry.useremail entities is still very much unexpected.

See also: #2081.

Event ID

No response

[azaslavsky]

Which database are you using? Are you attempting to a restore to a truly clean database? A common cause of this problem is attempting to restore to a database that either: a) already has data in it, or b) has no data, but has not had the primary keys reset, causing unique key collisions. If I had to guess, based on the fact that this is happening with multiple tables, that you are experiencing the latter: your pk counter has incremented past 1, so importing an entry with the an already spent pk causes the import failure.

I'm actively working on improving the import flow to be robust to this issue, but in the meantime, can you try reseting the primary keys on an empty database and trying again?

[stayallive]

Sounds plausible. How do I migrate the database after the reset without it inserting data?

Since reset.sh removes all Docker volumes I’m assuming this is enough to completely start over.

I figured install.sh would leave an empty database but I guess it doesn’t?

The database has been around for a minute with records dating back to 2018 which might be another complication.

(to add some context; I need to migrate the instance to another server and figured this was the quickest way without needing to figure out how to move the docker volumes and expected it to work by following the instructions provided in the docs and think I’ve followed them but good chance I missed something)

[azaslavsky]

I've been looking at this a bit recently. I wonder what happens if you just manually remove all of the sentry.useremail entries from the backup you are trying to restore? The emails should still be included in the individual sentry.user entries, and should get autocreated via signals.

[edgariscoding]

@stayalive, did you happen to get this issue resolved? I'm having the same issue but for sentry.environment

#3201

[stayallive]

I ended up editing the backup manually to fix my errors until it imported correctly 🙈