@Marvin I am noticing a lot of sqlite database locks which is adversely affecting the visual state and reporting of flows, subflows and tasks in the UI. For example. I have a flow with multiple concurrent tasks that run. They do appear to be running however the UI shows that there are 0 tasks running and there is no activity on the graph. How can I add a postgres server to my prefect instance and migrate from sqlite to the postgres instance while keeping the blocks, variables, deployments and other details in place?

To migrate from SQLite to PostgreSQL and keep all your Prefect data intact, follow these steps: 1. Set Up PostgreSQL Server: You can quickly start a PostgreSQL instance using Docker. Run the following command:

docker run -d --name prefect-postgres -v prefectdb:/var/lib/postgresql/data -p 5432:5432 -e POSTGRES_USER=postgres -e POSTGRES_PASSWORD=yourTopSecretPassword -e POSTGRES_DB=prefect postgres:latest

Replace

yourTopSecretPassword

with a secure password. 2. Configure Prefect to Use PostgreSQL: Set the Prefect database connection URL to point to your new PostgreSQL instance:

prefect config set PREFECT_API_DATABASE_CONNECTION_URL="<postgresql+asyncpg://postgres:yourTopSecretPassword@localhost:5432/prefect>"

3. Confirm Configuration: Verify that the environment variable is set correctly:

prefect config view --show-sources

You should see output similar to this:

PREFECT_PROFILE='my_profile'
   PREFECT_API_DATABASE_CONNECTION_URL='********' (from profile)
   PREFECT_API_URL='<http://127.0.0.1:4200/api>' (from profile)

4. Start Prefect Server: Start the Prefect server to begin using your PostgreSQL database instance:

prefect server start

5. Migration of Existing Data: Unfortunately, Prefect does not yet provide built-in commands for migrating data directly between SQLite and PostgreSQL. You will need to manually export your SQLite data and import it into your PostgreSQL instance. This process involves using database tools like

pgLoader

, or performing dumps and restores using SQL scripts. Detailed documentation on these steps can be found here: https://docs.prefect.io/latest/guides/host/ If you need more detailed help on how to export and import data between these databases, let me know!

unfortunately i think marvin's dead on here right now we dont have a way to cleanly migrate all data between sqlite / postgres

ah ok

Documenting my approach to migrating prefect (3.0.1) data from sqlite to postgres below. Note that this replaces

total_run_time

with an estimate, but that's close enough for me to look into preserving that column. 1. Shutdown the server. 2. Make a backup of the sqlite db file. 3. Make another backup. 4. Run the following queries on the sqlite db:

UPDATE flow_run SET total_run_time='0'

,

UPDATE flow_run SET total_run_time='0'

5. Instantiate a postgres instance, start it, point prefect to it as described here 6. Run

prefect server database upgrade

7. Install pgloader 8. Run pgloader with

load database
    from <SQLITE_DB_LOC>
    into <POSTGRES_URI>

  with include no drop, truncate, create no tables, create no indexes, reset sequences
  excluding table names like 'alembic_version'
  BEFORE LOAD DO
    $$ ALTER TABLE task_run ALTER COLUMN total_run_time DROP NOT NULL; $$,
    $$ ALTER TABLE flow_run ALTER COLUMN total_run_time DROP NOT NULL; $$
  AFTER LOAD DO
    $$ UPDATE task_run SET total_run_time = end_time - start_time; $$,
    $$ UPDATE task_run SET total_run_time = '00:00:00'::interval  WHERE total_run_time iS NULL; $$,
    $$ ALTER TABLE task_run ALTER COLUMN total_run_time SET NOT NULL; $$,

    $$ UPDATE flow_run SET total_run_time = end_time - start_time; $$,
    $$ UPDATE flow_run SET total_run_time = '00:00:00'::interval  WHERE total_run_time iS NULL; $$,
    $$ ALTER TABLE flow_run ALTER COLUMN total_run_time SET NOT NULL; $$
  ;

You'll get a bunch of seemingly harmless warnings, but everything should load fine, check pgloader output. 1. Start the server, check that everything's present (might wanna compare dbs as well) and you're done.

thank you for sharing this!