Issues when using with postgres

Hi I recently switched from SQLite to Postgres, and HA is so much snappier when fetching large amounts of data :), but I have noticed that I have gaps in my data at regular intervals.

When i look at my logs i can see that it fails to insert rows

2024-04-01 20:14:34.609 ERROR (Recorder) [homeassistant.components.recorder.core] Unhandled database error while processing task CommitTask(): (psycopg2.errors.UniqueViolation) duplicate key value violates unique constraint "states_meta_pkey"
DETAIL:  Key (metadata_id)=(3) already exists.

[SQL: INSERT INTO states_meta (entity_id) SELECT p0::VARCHAR FROM (VALUES (%(entity_id__0)s, 0), (%(entity_id__1)s, 1), (%(entity_id__2)s, 2), (%(entity_id__3)s, 3)) AS imp_sen(p0, sen_counter) ORDER BY sen_counter RETURNING states_meta.metadata_id, states_meta.metadata_id AS metadata_id__1]
[parameters: {'entity_id__0': 'sensor.living_room_right_switch_brightness', 'entity_id__1': 'sensor.living_room_right_switch_action_step_size', 'entity_id__2': 'sensor.living_room_left_switch_brightness', 'entity_id__3': 'sensor.living_room_left_switch_action_step_size'}]
(Background on this error at: https://sqlalche.me/e/20/gkpj)
Traceback (most recent call last):
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2104, in _exec_insertmany_context
    dialect.do_execute(
  File "/usr/local/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 924, in do_execute
    cursor.execute(statement, parameters)
psycopg2.errors.UniqueViolation: duplicate key value violates unique constraint "states_meta_pkey"
DETAIL:  Key (metadata_id)=(3) already exists.

Some other errors i have found

(Background on this error at: https://sqlalche.me/e/20/gkpj)
	
UnboundLocalError: cannot access local variable 'modified_statistic_ids' where it is not associated with a value

Could that be the cause of the gaps?

Also when i inspect the SQLite databas, then there are foreign keys between tables, but those are missing in my postgres database, is that normal?

I’m using the following configuration

recorder:
  db_url: !secret postgres
  db_retry_wait: 15 # Wait 15 seconds before retrying

I migrated my sqlite database using pgloader to postgres, but i let homeassistant create the database schema.

IN case anyone finds this, I think the following solved the insert issue

This specific error should be fixed by fix UnboundLocalError on modified_statistic_ids in compile_statistics by pdecat · Pull Request #116795 · home-assistant/core · GitHub

Wayback machine is down right now, is your link the same as How to fix a sequence when it goes out of sync in Postgres - DEV Community?

Edit: Wayback machine is back, can confirm it’s the same.