DB Migration Failed: schema_changes id duplicate key

I’m not sure which category to put this question in so please move if this isn’t the best place.

I just updated to 2025.1 from 2024.4 today. I’m getting an error on DB migration failure (using postgres):

sqlalchemy.exc.IntegrityError: (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "schema_changes_pkey"
DETAIL:  Key (change_id)=(2) already exists.

[SQL: INSERT INTO schema_changes (schema_version, changed) VALUES (%(schema_version)s, %(changed)s) RETURNING schema_changes.change_id]
[parameters: {'schema_version': 43, 'changed': datetime.datetime(2025, 1, 27, 16, 53, 49, 211120, tzinfo=datetime.timezone.utc)}]
(Background on this error at: https://sqlalche.me/e/20/gkpj)

It’s trying to insert a new record on the schema_changes table with a change_id of 2. Here is the current contents of that table:

homeassistant=> select * from schema_changes;
 change_id | schema_version |            changed
-----------+----------------+-------------------------------
         1 |             33 | 2023-08-31 23:50:44.50582-07
         2 |             34 | 2024-03-11 06:24:46.002292-07
         3 |             35 | 2024-03-11 06:24:46.096434-07
         4 |             36 | 2024-03-11 06:24:46.250117-07
         5 |             37 | 2024-03-11 06:24:46.261012-07
         6 |             38 | 2024-03-11 06:24:46.471125-07
         7 |             39 | 2024-03-11 06:24:46.493914-07
         8 |             40 | 2024-03-11 06:24:46.505855-07
         9 |             41 | 2024-03-11 06:24:46.506798-07
        10 |             42 | 2024-03-11 06:24:46.507722-07
(10 rows)

Why is it trying to insert with an ID of 2? And not just entering it at the end of the table with an auto-generated ID of 11?

Interesting that you’re at schema version 42, trying to install 43. My version 43 was installed back in April of last year. That would have been during the update to Core 2024.4.x. I’m still at Core version 2024.12.4 and I’m showing schema version 47. It looks like 44-47 were installed with Core version 2024.9.x. Schema version 43 was installed back in December of 2023. This all assumes that the schema version numbers are the same for everyone, not relative to each install. If that assumption is wrong, then disregard all of this!

|7|41|2023-04-20 12:30:40.993874|
|8|42|2023-11-30 12:47:13.878628|
|9|43|2024-04-24 13:08:22.277255|
|10|44|2024-09-20 19:09:45.813473|
|11|45|2024-09-20 19:09:45.872607|
|12|46|2024-09-20 19:09:45.890247|
|13|47|2024-09-20 19:09:45.903918|

That’s very helpful. I migrated from sqlite to pg at around that time so the migration probably failed then and I’m just now seeing it.

I still don’t get why it’s trying to insert a record with an existing ID.

I guess the easiest thing is to just drop the DB and let it rebuild.