Key violation after migration to PostgreSQL

I recently switched over from the integrated SQLite database to a PostgreSQL database since I already had that running in another VM.
I didn’t want to lose my data and especially my energy dashboard data so I exported the SQLite database and imported it in Postgres.

This all went great and it’s up and running and I can correctly pull all the imported data from my database however it has now stopped logging new data in the energy dashboard.
Sensors get logged no problem.

The core log says the following:

2022-02-02 22:00:10 ERROR (Recorder) [homeassistant.components.recorder.util] 
Error executing query: (psycopg2.errors.UniqueViolation) duplicate key value 
violates unique constraint "statistics_pkey"

DETAIL:  Key (id)=(51) already exists.

[SQL: INSERT INTO statistics (created, start, mean, min, max, last_reset, state, 
sum, metadata_id) VALUES (%(created)s, %(start)s, %(mean)s, %(min)s, 
%(max)s, %(last_reset)s, %(state)s, %(sum)s, %(metadata_id)s) 

It seems that it’s trying to insert records with id = 51 but those are already taken, which makes sense because I imported older data.
Is there a way to let HomeAssistant know that it should skip those id’s and start at say 10 000?
I’ve tried Recorder.purge with repack but that hasn’t helped.
This issue will probably also kick up with sensor data as soon as the new event_id’s have caught up with the inserted rows.

I’m used to the database handling the identity column instead of the application inserting a static value there. Is there a way to let Postgres handle this instead of HA?

Hello Firefoxx, you have found a solutions. I have the same problem.

For anyone else hitting this, I had this same problem after using pgloader and was able to recover using the steps in How to fix a sequence when it goes out of sync in Postgres
Method 2 worked for me. My steps:

  • I used psql to connect to my homeassistant database
  • Copy&paste the long SQL statement from Method 2 in the link above
  • Copy&paste that output
  • Restart Home Assistant
1 Like

Nice one @dannys42!
Would have been nice if I would’ve found that one back then. I have some SQL experience but with SQL Server, not with Postgres so I didn’t know the setval function.

In the end I believe I fixed it manually by updating the existing records to id + 2 bilion.