Sqlite to Postgres database migration

[Pheggas]

Hello. I am coming here for help. We started using Prefect in development environment (so we used sqlite). Then we started to use it for some production flows. Now, we want to switch to postgres database because we already have 19 Gigs of sqlite and it starts to really lag and lock the database basically non-stop.

The issue is - how? There is no tutorial or guide what so ever on web. I tried it with Chatgpt and got into point where i finally migrate the db using pgloader and .load file like:

LOAD DATABASE
     FROM sqlite:///home/username/.prefect/prefect.db
     INTO postgresql://postgres:redacted@localhost:5432/prefect_db

WITH include drop, create tables, create indexes, reset sequences

CAST
     -- Convert SQLite’s DATETIME type to PostgreSQL’s timestamptz and drop its default.
     type datetime to timestamptz drop default,
     type timestamptz drop default,
     type timestamp drop default,
     
     -- Convert json columns to jsonb so that PostgreSQL can index them.
     type json to jsonb drop default,
     
     -- (Optional) Explicit per‑column rules for known problematic defaults:
     column flow.created                drop default,
     column flow.updated                drop default,
     column concurrency_limit.created   drop default,
     column concurrency_limit.updated   drop default,
     column saved_search.created        drop default,
     column saved_search.updated        drop default,
     column task_run_state_cache.created drop default,
     column task_run_state_cache.updated drop default;

And it worked quite well. There were some warnings for truncating the names for shorter ones. Then there were issues with the revision so i manually changed the revision id in the DB from latest sqlite revision to latest postgres revision and downgraded it using prefect command to that (latest) postgres revision.

It worked and prefect server finally loaded. But when i went to the web ui, only some of the data actually loaded. There were only: Flows, Work pools, and Automations (and Event feed but i dont need that one). In the logs there were a lot of errors regarding type mismatch (a column of type text is being compared to a column of type UUID), then undefined custom type (“state_type” does not exist), and potentially some other.

Did have someone done full migration to postgres from sqlite or do people just start over with the right database?

[lelouvincx]

Can you list all tables got in error?