<@ULVA73B9P> I am self hosting prefect on docker d...
# ask-marvino
Olve Heitmann
11/19/2025, 8:12 PM@Marvin I am self hosting prefect on docker desktop, and recently the Prefect Web UI has become a lot slower, and the prefect server container a lot more demanding (docker stats currently shows ~5GB of ram consumption by the server, and it spikes higher). I have tried doing some light maintenance on my instance (restarting service, deleting old flow runs, VACUUMing SQLite database, imposing environment limits on retention period in docker compose, etc). Any tips?
m
Marvin
11/19/2025, 8:12 PM✅ thought for 370.5 seconds
Marvin
11/19/2025, 8:18 PMThanks for the detail — that helps. A few common causes for slow UI and high RAM on a self‑hosted server (especially on Docker Desktop with SQLite) and the quickest fixes:
Assumptions: You’re on Prefect 3.x and currently using SQLite (since you VACUUM’d a SQLite DB). If that’s wrong, let me know.
Top culprits and immediate fixes
- Large/old events history causing big queries and UI websocket backfill
- Set a shorter events retention window (2–3 days if you have high volume)
-
PREFECT_SERVER_EVENTS_RETENTION_PERIOD=P2DPREFECT_SERVER_EVENTS_MAXIMUM_WEBSOCKET_BACKFILL=PT300SPREFECT_SERVER_EVENTS_WEBSOCKET_BACKFILL_PAGE_SIZE=100PREFECT_SERVER_EVENTS_RELATED_RESOURCE_CACHE_TTL=PT300S--no-services--no-servicesprefect server start --help Copy code
PREFECT_SERVER_EVENTS_RETENTION_PERIOD=P2D
PREFECT_SERVER_EVENTS_MAXIMUM_WEBSOCKET_BACKFILL=PT300S
PREFECT_SERVER_EVENTS_WEBSOCKET_BACKFILL_PAGE_SIZE=100
PREFECT_SERVER_EVENTS_RELATED_RESOURCE_CACHE_TTL=PT300S--no-servicespostgresPREFECT_API_DATABASE_CONNECTION_URL=<postgresql+asyncpg://prefect:prefect@postgres:5432/prefect>PREFECT_API_DATABASE_MIGRATE_ON_START=true Copy code
services:
postgres:
image: postgres:15-alpine
environment:
POSTGRES_USER: prefect
POSTGRES_PASSWORD: prefect
POSTGRES_DB: prefect
volumes:
- postgres_data:/var/lib/postgresql/data
healthcheck:
test: ["CMD-SHELL", "pg_isready -U prefect"]
interval: 5s
timeout: 5s
retries: 5
ports:
- "5432:5432"
prefect-server:
image: prefecthq/prefect:3-latest
depends_on:
postgres:
condition: service_healthy
environment:
PREFECT_API_DATABASE_CONNECTION_URL: <postgresql+asyncpg://prefect:prefect@postgres:5432/prefect>
PREFECT_API_DATABASE_MIGRATE_ON_START: "true"
# keep the events tweaks from above here as well
PREFECT_SERVER_EVENTS_RETENTION_PERIOD: "P2D"
PREFECT_SERVER_EVENTS_MAXIMUM_WEBSOCKET_BACKFILL: "PT300S"
PREFECT_SERVER_EVENTS_WEBSOCKET_BACKFILL_PAGE_SIZE: "100"
PREFECT_SERVER_EVENTS_RELATED_RESOURCE_CACHE_TTL: "PT300S"
command: prefect server start --host 0.0.0.0
ports:
- "4200:4200"
volumes:
postgres_data:Marvin
11/19/2025, 8:18 PM3) If you later scale up or split services, consider Redis for event messaging/caching
- Useful when you run multiple API replicas; for a single container, the default in‑memory broker/cache is fine.
- If you do add Redis, you’ll set:
Copy code
PREFECT_MESSAGING_BROKER=prefect.server.utilities.messaging.memory # keep memory for single container
PREFECT_MESSAGING_CACHE=prefect.server.utilities.messaging.memory # keep memory for single container Copy code
prefect server database upgrade -y Copy code
prefect server database reset -yprefect server database upgrade --helpprefect server database reset --helpprefect server start --helpprefect server starto
Olve Heitmann
11/19/2025, 8:33 PM1. I am on prefect 3.4.17, so not latest but pretty recent.
2. Chattyness varies a lot - some flows are very chatty with logs and have up to ~5000 tasks. Some flows are very small / simple (these run the most often). Flows with "real work" perhaps ~6 a day, including the smaller once a lot hgiher (some run every half hour).
3. I have added, but not yet run, your recommended changes (ie no docker compose down / up yet)
4. If possible, I'd like to keep logs/deploys/etc from the old sqlite db when migrating to postgres.
5. Current compose, after adding postgres:
# (top-level) optional but recommended to avoid project-name drift
name: qfin
services:
postgres:
image: postgres:15-alpine
environment:
POSTGRES_USER: prefect
POSTGRES_PASSWORD: prefect
POSTGRES_DB: prefect
volumes:
- postgres_data:/var/lib/postgresql/data
healthcheck:
test: ["CMD-SHELL", "pg_isready -U prefect"]
interval: 5s
timeout: 5s
retries: 5
ports:
- "5432:5432"
prefect-server:
image: prefecthq/prefect:3-latest
depends_on:
postgres:
condition: service_healthy
restart: always
ports:
- "4200:4200"
environment:
PREFECT_API_DATABASE_CONNECTION_URL: postgresql+asyncpg://prefect:prefect@postgres:5432/prefect
PREFECT_API_DATABASE_MIGRATE_ON_START: "true"
PREFECT_SERVER_EVENTS_RETENTION_PERIOD: "P2D"
PREFECT_SERVER_EVENTS_MAXIMUM_WEBSOCKET_BACKFILL: "PT300S"
PREFECT_SERVER_EVENTS_WEBSOCKET_BACKFILL_PAGE_SIZE: "100"
PREFECT_SERVER_EVENTS_RELATED_RESOURCE_CACHE_TTL: "PT300S"
command: prefect server start --host 0.0.0.0
healthcheck:
test: ["CMD", "python", "-c", "import urllib.request; urllib.request.urlopen('http://127.0.0.1:4200/api/health', timeout=5).read()"]
interval: 30s
timeout: 10s
retries: 3
start_period: 40s
volumes: # persist Prefect's local data/db
- prefect_data:/root/.prefect
create-work-pools: # Run once to create work pools, container dies after creating pools
image: prefecthq/prefect:3-latest
(...)
prefect-prod-worker:
image: prefecthq/prefect:3-latest
(...)
prefect-dev-worker:
image: prefecthq/prefect:3-latest
(...)
volumes:
prefect_data:
name: brrr_prefect_data
postgres_data:
the old prefect_data volumes is where my sqlite have been living. I have shortened compose for other services to not break length limit
@Marvin
m
Marvin
11/19/2025, 8:33 PMYour message was too long, here's your message at the allowed limit:
1 I am on prefect 3 4 17 so not latest but pretty recent 2 Chattyness varies a lot - some flows are very chatty with logs and have up to ~5000 tasks Some flows are very small / simple (these run the most often) Flows with real work perhaps ~6 a day including the smaller once a lot hgiher (some run every half hour) 3 I have added but not yet run your recommended changes (ie no docker compose down / up yet) 4 If possible I'd like to keep logs/deploys/etc from the old sqlite db when migrating to postgres 5 Current compose after adding postgres # (top-level) optional but recommended to avoid project-name drift name qfin services postgres image postgres 15-alpine environment POSTGRES_USER prefect POSTGRES_PASSWORD prefect POSTGRES_DB prefect volumes - postgres_data /var/lib/postgresql/data healthcheck test [ CMD-SHELL pg_isready -U prefect ] interval 5s timeout 5s retries 5 ports - 5432 5432 prefect-server image prefecthq/prefect 3-latest depends_on postgres condition service_healthy restart always ports - 4200 4200 environment PREFECT_API_DATABASE_CONNECTION_URL <postgresql+asyncpg //prefect prefect@postgres 5432/prefect> PREFECT_API_DATABASE_MIGRATE_ON_START true PREFECT_SERVER_EVENTS_RETENTION_PERIOD P2D PREFECT_SERVER_EVENTS_MAXIMUM_WEBSOCKET_BACKFILL PT300S PREFECT_SERVER_EVENTS_WEBSOCKET_BACKFILL_PAGE_SIZE 100 PREFECT_SERVER_EVENTS_RELATED_RESOURCE_CACHE_TTL PT300S command prefect server start --host 0 0 0 0 healthcheck test [ CMD python -c import urllib request; urllib request urlopen('<http //127 0 0 1 4200/api/health>' timeout=5) read() ] interval 30s timeout 10s retries 3 start_period 40s volumes # persist Prefect's local data/db - prefect_data /root/ prefect create-work-pools # Run once to create work pools container dies after creating pools image prefecthq/prefect 3-latest depends_on prefect-server condition service_healthy environment PREFECT_API_URL <http //prefect-server 4200/api> restart no entrypoint > sh -c echo 'Waiting for Prefect server to be ready ' && until prefect work-pool ls; do echo 'Waiting for Prefect server ' && sleep 5 done && echo 'Creating work pools ' && prefect work-pool create 'production-pool' --type docker || echo 'Production pool already exists' && prefect work-pool create 'dev-pool' --type docker || echo 'Dev pool already exists' && echo 'Work pools created successfully ' && prefect work-pool ls && sleep 10 prefect-prod-worker image prefecthq/prefect 3-latest restart always environment PREFECT_API_URL <http //prefect-server 4200/api> PREFECT_WORK_POOL production-pool DOCKER_HOST unix ///var/run/docker sock command > sh -c python -m pip install --no-cache-dir -q prefect-docker docker || true && echo 'Waiting for Prefect API ' && until prefect work-pool ls >/dev/null 2>&1; do echo 'Waiting for Prefect server ' && sleep 3 done && echo \ Waiting for pool $${PREFECT_WORK_POOL} \ && until prefect work-pool inspect \ $${PREFECT_WORK_POOL}\ >/dev/null 2>&1; do echo \ Pool $${PREFECT_WORK_POOL} not ready yet \ && sleep 3 done && echo \ Starting worker for pool $${PREFECT_WORK_POOL} \ && prefect worker start -p \ $${PREFECT_WORK_POOL}\ depends_on prefect-server condition service_healthy create-work-pools condition service_completed_successfully volumes # Allow prefect workers to spin up containers by mounting the Unix socket Docker Engine uses to accept API commands - /var/run/docker sock /var/run/docker sock prefect-dev-worker image prefecthq/prefect 3-latest restart always environment PREFECT_API_URL <http //prefect-server 4200/api> PREFECT_WORK_POOL dev-pool DOCKER_HOST unix ///var/run/docker sock command > sh -c python -m pip install --no-cache-dir -q prefect-docker docker || true && echo 'Waiting
Marvin
11/19/2025, 8:36 PM✅ thought for 310.3 seconds
Marvin
11/19/2025, 8:41 PMAwesome — thanks for the detail. Given your workload (some very chatty flows, thousands of tasks, and steady cadence), the two biggest wins will be:
- shorten events backfill/retention (you added these — great), and
- move from SQLite to Postgres while preserving your data.
Below is a safe, minimal‑downtime migration plan that keeps your deployments, flow/task runs, logs, events, automations, etc.
Important compose fixes first
- In your compose, remove angle brackets around values. These two should be plain strings:
-
PREFECT_API_DATABASE_CONNECTION_URL=<postgresql+asyncpg://prefect:prefect@postgres:5432/prefect><http://127.0.0.1:4200/api/health>< >image: prefecthq/prefect:3.4.17 Copy code
docker run --rm -v brrr_prefect_data:/data -v $(pwd):/backup alpine sh -c "cp /data/prefect.db /backup/prefect.db.bak"/root/.prefect/prefect.dbbrrr_prefect_data Copy code
docker compose up -d postgres Copy code
docker exec -it $(docker ps -qf "name=postgres") psql -U prefect -d prefect -c "CREATE EXTENSION IF NOT EXISTS pg_trgm;" Copy code
docker run --rm --network qfin_default \
-v brrr_prefect_data:/root/.prefect \
dimitri/pgloader:latest \
pgloader sqlite:///root/.prefect/prefect.db <postgresql://prefect:prefect@postgres/prefect> Copy code
docker exec -it $(docker ps -qf "name=postgres") psql -U prefect -d prefect -c "SELECT COUNT(*) FROM flow_run;"
docker exec -it $(docker ps -qf "name=postgres") psql -U prefect -d prefect -c "SELECT COUNT(*) FROM task_run;"
docker exec -it $(docker ps -qf "name=postgres") psql -U prefect -d prefect -c "SELECT COUNT(*) FROM log;"
docker exec -it $(docker ps -qf "name=postgres") psql -U prefect -d prefect -c "SELECT COUNT(*) FROM events;" Copy code
PREFECT_API_DATABASE_CONNECTION_URL=<postgresql+asyncpg://prefect:prefect@postgres:5432/prefect>
PREFECT_API_DATABASE_MIGRATE_ON_START=true
PREFECT_SERVER_EVENTS_RETENTION_PERIOD=P2D
PREFECT_SERVER_EVENTS_MAXIMUM_WEBSOCKET_BACKFILL=PT300S
PREFECT_SERVER_EVENTS_WEBSOCKET_BACKFILL_PAGE_SIZE=100
PREFECT_SERVER_EVENTS_RELATED_RESOURCE_CACHE_TTL=PT300S Copy code
docker compose up -d prefect-serverPREFECT_API_DATABASE_MIGRATE_ON_START=true Copy code
docker exec -it $(docker ps -qf "name=prefect-server") prefect server database upgrade -yP2D Copy code
PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_SIZE=10
PREFECT_SERVER_DATABASE_SQLALCHEMY_MAX_OVERFLOW=5
PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_TIMEOUT=30Marvin
11/19/2025, 8:41 PM- Very chatty flows: consider trimming log verbosity where possible; logs and events volume directly affects UI query time and DB size.
Links you may find handy
- Server events settings (retention, websocket backfill, cache): Settings reference — server events
- Database settings and connection string: Settings reference — database
- Server CLI and database commands: Self-hosting with the Server CLI
Want me to mark up your full compose (including workers) with exact changes and pinned versions before you do
docker compose down && up -do
Olve Heitmann
11/19/2025, 10:59 PM@Marvin olveh@olve-machine:/mnt/c/qfin/qfin/prefect_setup/server_workpools_worker$ docker run --rm --network qfin_default \
-v brrr_prefect_data:/root/.prefect \
dimitri/pgloader:latest \
pgloader sqlite:///root/.prefect/prefect.db postgresql://prefect:prefect@postgres/prefect
Unable to find image 'dimitri/pgloader:latest' locally
latest: Pulling from dimitri/pgloader
59ce1b6c0c82: Pull complete
7422dc743b5a: Pull complete
3ab3a7d17840: Pull complete
8d9a861a619b: Pull complete
Digest: sha256:756758ca8269404bdca9384c2fb04bcc7de744a713d26885ba8b556e4005eab4
Status: Downloaded newer image for dimitri/pgloader:latest
2025-11-19T225831.041366Z LOG pgloader version "3.6.7~devel"
2025-11-19T225831.188028Z LOG Migrating from #<SQLITE-CONNECTION sqlite:///root/.prefect/prefect.db {10072F8D03}>
2025-11-19T225831.191788Z LOG Migrating into #<PGSQL-CONNECTION pgsql://prefect@postgres:5432/prefect {1007445123}>
2025-11-19T225831.816040Z ERROR Database error 22007: invalid input syntax for type timestamp with time zone: "strftime('%Y-%m-%d %H:%M:%f000', 'now')"
QUERY: CREATE TABLE flow
(
id text default '(
lower(hex(randomblob(4)))
|| ''-''
|| lower(hex(randomblob(2)))
|| ''-4''
|| substr(lower(hex(randomblob(2))),2)
|| ''-''
|| substr(''89ab'',abs(random()) % 4 + 1, 1)
|| substr(lower(hex(randomblob(2))),2)
|| ''-''
|| lower(hex(randomblob(6)))
)',
created timestamptz default 'strftime(''%Y-%m-%d %H:%M:%f000'', ''now'')',
updated timestamptz default 'strftime(''%Y-%m-%d %H:%M:%f000'', ''now'')',
name text,
tags json default '[]',
labels json
);
2025-11-19T225831.816040Z FATAL Failed to create the schema, see above.
2025-11-19T225831.823561Z LOG report summary reset
table name errors rows bytes total time
----------------- --------- --------- --------- --------------
fetch 0 0 0.000s
fetch meta data 0 256 0.369s
Create Schemas 0 0 0.004s
Create SQL Types 0 0 0.026s
Create tables 0 0 0.000s
----------------- --------- --------- --------- --------------
----------------- --------- --------- --------- --------------
m
Marvin
11/19/2025, 10:59 PM✅ thought for 80.3 seconds
Marvin
11/19/2025, 11:00 PMGreat data point — that pgloader error is exactly what happens when it tries to CREATE the schema from SQLite DDL. SQLite uses
strftime(...)alembic_versionstrftime(...) Copy code
docker run --rm --network qfin_default \
-e PREFECT_API_DATABASE_CONNECTION_URL=<postgresql+asyncpg://prefect:prefect@postgres:5432/prefect> \
prefecthq/prefect:3.4.17 prefect server database upgrade -ypgloader.load Copy code
LOAD DATABASE
FROM sqlite:///root/.prefect/prefect.db
INTO <postgresql://prefect:prefect@postgres/prefect>
WITH data only,
batch rows = 5000,
prefetch rows = 5000,
on error stop
EXCLUDING TABLE NAMES LIKE 'alembic_version'
; Copy code
docker run --rm --network qfin_default \
-v brrr_prefect_data:/root/.prefect \
-v $(pwd)/pgloader.load:/load.load \
dimitri/pgloader:latest pgloader /load.loadalembic_versionalembic_version Copy code
docker exec -it $(docker ps -qf "name=postgres") psql -U prefect -d prefect -c "SELECT COUNT(*) FROM flow_run;"
docker exec -it $(docker ps -qf "name=postgres") psql -U prefect -d prefect -c "SELECT COUNT(*) FROM task_run;"
docker exec -it $(docker ps -qf "name=postgres") psql -U prefect -d prefect -c "SELECT COUNT(*) FROM log;"
docker exec -it $(docker ps -qf "name=postgres") psql -U prefect -d prefect -c "SELECT COUNT(*) FROM events;"prefecthq/prefect:3.4.17 Copy code
docker exec -it $(docker ps -qf "name=prefect-server") prefect server database upgrade -y Copy code
PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_SIZE=10
PREFECT_SERVER_DATABASE_SQLALCHEMY_MAX_OVERFLOW=5
PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_TIMEOUT=30o
Olve Heitmann
11/19/2025, 11:15 PM@Marvin docker run --rm --network qfin_default \
-e PREFECT_API_DATABASE_CONNECTION_URL=postgresql+asyncpg://prefect:prefect@postgres:5432/prefect \
prefecthq/prefect:3-latest prefect server database upgrade -y
docker: Error response from daemon: failed to set up container networking: network qfin_default not found
m
Marvin
11/19/2025, 11:15 PM✅ thought for 32.4 seconds
Marvin
11/19/2025, 11:16 PMThat error just means the Compose network doesn’t exist yet. You’ve got two easy options to proceed:
Option A — Use the Compose network (qfin_default)
1) Create the network by starting Postgres from your compose:
Copy code
docker compose up -d postgres Copy code
docker run --rm --network qfin_default \
-e PREFECT_API_DATABASE_CONNECTION_URL=<postgresql+asyncpg://prefect:prefect@postgres:5432/prefect> \
prefecthq/prefect:3.4.17 prefect server database upgrade -y Copy code
docker run --rm \
-e PREFECT_API_DATABASE_CONNECTION_URL=<postgresql+asyncpg://prefect:prefect@host.docker.internal:5432/prefect> \
prefecthq/prefect:3.4.17 prefect server database upgrade -y Copy code
LOAD DATABASE
FROM sqlite:///root/.prefect/prefect.db
INTO <postgresql://prefect:prefect@host.docker.internal:5432/prefect>
WITH data only,
batch rows = 5000,
prefetch rows = 5000,
on error stop
EXCLUDING TABLE NAMES LIKE 'alembic_version'
; Copy code
docker run --rm \
-v brrr_prefect_data:/root/.prefect \
-v $(pwd)/pgloader.load:/load.load \
dimitri/pgloader:latest pgloader /load.loadPREFECT_API_DATABASE_CONNECTION_URL=<postgresql+asyncpg://prefect:prefect@postgres:5432/prefect>prefecthq/prefect:3.4.17 Copy code
migrate:
image: prefecthq/prefect:3.4.17
depends_on:
postgres:
condition: service_healthy
environment:
PREFECT_API_DATABASE_CONNECTION_URL: <postgresql+asyncpg://prefect:prefect@postgres:5432/prefect>
command: prefect server database upgrade -y Copy code
docker compose run --rm migrate Copy code
docker exec -it $(docker ps -qf "name=postgres") \
psql -U prefect -d prefect -c "SELECT COUNT(*) FROM flow_run;"
docker exec -it $(docker ps -qf "name=postgres") \
psql -U prefect -d prefect -c "SELECT COUNT(*) FROM task_run;"
docker exec -it $(docker ps -qf "name=postgres") \
psql -U prefect -d prefect -c "SELECT COUNT(*) FROM log;"
docker exec -it $(docker ps -qf "name=postgres") \
psql -U prefect -d prefect -c "SELECT COUNT(*) FROM events;"o
Olve Heitmann
11/19/2025, 11:24 PM@Marvin running docker run --rm --network qfin_default -v brrr_prefect_data:/root/.prefect -v $(pwd)/pgloader.load:/load.load dimitri/pgloader:latest pgloader /load.load
prints a lot of warrnings such as
2025-11-19T231750.799068Z WARNING Source column "public"."deployment_version"."entrypoint" is casted to type "text" which is not the same as "character varying", the type of current target database column "public"."deployment_version"."entrypoint".
(for many columns)
as well as some sqlite specific such as
2025-11-19T231750.806826Z ERROR pgloader failed to find target table for source NIL."sqlite_stat1" with name "sqlite_stat1" in target catalog
also get some errors on keys
2025-11-19T231751.349881Z ERROR Database error 23505: duplicate key value violates unique constraint "uq_configuration__key"
DETAIL: Key (key)=(TELEMETRY_SESSION) already exists.
CONTEXT: COPY configuration, line 1
(among others)
m
Marvin
11/19/2025, 11:24 PM✅ thought for 38.0 seconds
Marvin
11/19/2025, 11:24 PMThanks for sharing the output — super helpful.
What you’re seeing is expected with a first pass:
- The varchar vs text warnings are harmless. Postgres will coerce text into varchar (unless there’s a length limit, which Prefect doesn’t use here).
- The sqlite_stat1 error is pgloader trying to copy SQLite’s internal tables; we should exclude them.
- The duplicate key in configuration is because we created the Postgres schema/migrations first (which seed some configuration rows), then pgloader tried to insert those same rows.
Let’s tighten the pgloader config to avoid these and rerun.
1) Keep the Postgres schema created by Prefect
You already ran the migration to create the schema. Good — keep that.
2) Use a more specific pgloader.load
Update your load file to exclude SQLite internal tables and avoid duplicates by truncating the small, seeded configuration table first.
Create/replace pgloader.load with:
Copy code
LOAD DATABASE
FROM sqlite:///root/.prefect/prefect.db
INTO <postgresql://prefect:prefect@postgres/prefect>
WITH data only,
batch rows = 5000,
prefetch rows = 5000,
on error stop
-- Exclude SQLite internal tables and Alembic version
EXCLUDING TABLE NAMES LIKE 'sqlite_%'
EXCLUDING TABLE NAMES LIKE 'alembic_version'
-- Truncate small seeded table to avoid duplicate unique keys
BEFORE LOAD DO
$$ TRUNCATE TABLE configuration; $$
; Copy code
docker run --rm --network qfin_default \
-v brrr_prefect_data:/root/.prefect \
-v $(pwd)/pgloader.load:/load.load \
dimitri/pgloader:latest pgloader /load.loadconfiguration Copy code
BEFORE LOAD DO
$$ TRUNCATE TABLE configuration; $$
$$ TRUNCATE TABLE <table_name>; $$ Copy code
docker exec -it $(docker ps -qf "name=postgres") \
psql -U prefect -d prefect -c "SELECT COUNT(*) FROM flow_run;"
docker exec -it $(docker ps -qf "name=postgres") \
psql -U prefect -d prefect -c "SELECT COUNT(*) FROM task_run;"
docker exec -it $(docker ps -qf "name=postgres") \
psql -U prefect -d prefect -c "SELECT COUNT(*) FROM log;"
docker exec -it $(docker ps -qf "name=postgres") \
psql -U prefect -d prefect -c "SELECT COUNT(*) FROM events;"prefecthq/prefect:3.4.17 Copy code
docker exec -it $(docker ps -qf "name=prefect-server") prefect server database upgrade -yo
Olve Heitmann
11/19/2025, 11:28 PM@Marvin lots of warnings on columns remain, but also more severe looking stuff
2025-11-19T232620.306892Z ERROR Database error 23505: duplicate key value violates unique constraint "pk_flow"
DETAIL: Key (id)=(1e44683c-a3b2-452b-925c-da74daebd1e2) already exists.
CONTEXT: COPY flow, line 1
2025-11-19T232620.364549Z ERROR Database error 23505: duplicate key value violates unique constraint "pk_concurrency_limit"
DETAIL: Key (id)=(0e3c7b58-c543-4aba-87df-b386d09ba8b6) already exists.
CONTEXT: COPY concurrency_limit, line 1
2025-11-19T232620.599021Z ERROR Database error 23505: duplicate key value violates unique constraint "pk_block_schema_reference"
DETAIL: Key (id)=(2d6c80e4-870b-41a0-bd14-d423e5c2cd1e) already exists.
CONTEXT: COPY block_schema_reference, line 1
2025-11-19T232620.606709Z ERROR Database error 23505: duplicate key value violates unique constraint "uq_block_schema__checksum_version"
DETAIL: Key (checksum, version)=(sha256:61e9dbea14935ccb2cfac7eb38f01c4e878a81073806f2aec993820fa0d91eb3, 3.4.17) already exists.
CONTEXT: COPY block_schema, line 1
2025-11-19T232620.748930Z ERROR Database error 23505: duplicate key value violates unique constraint "pk_block_document_reference"
DETAIL: Key (id)=(3f615161-685e-4f14-af19-5c8658d80ccd) already exists.
CONTEXT: COPY block_document_reference, line 1
2025-11-19T232620.748930Z ERROR Database error 23505: duplicate key value violates unique constraint "pk_block_document"
DETAIL: Key (id)=(e4c96407-768c-4b88-acbd-2265f448c5f3) already exists.
CONTEXT: COPY block_document, line 1
2025-11-19T232620.806587Z ERROR Database error 23505: duplicate key value violates unique constraint "pk_worker"
DETAIL: Key (id)=(47b18cd5-76c4-42a5-a718-94e3530d026b) already exists.
CONTEXT: COPY worker, line 1
2025-11-19T232620.814274Z ERROR Database error 23505: duplicate key value violates unique constraint "uq_block_type__slug"
DETAIL: Key (slug)=(webhook) already exists.
CONTEXT: COPY block_type, line 1
2025-11-19T232621.075652Z ERROR Database error 23505: duplicate key value violates unique constraint "pk_flow_run_state"
DETAIL: Key (id)=(01993eaf-d045-77b0-b695-1ee3e0b4640f) already exists.
CONTEXT: COPY flow_run_state, line 1
2025-11-19T232621.164059Z ERROR Database error 23505: duplicate key value violates unique constraint "uq_work_pool__name"
DETAIL: Key (name)=(production-pool) already exists.
CONTEXT: COPY work_pool, line 1
2025-11-19T232625.556442Z ERROR Database error 23505: duplicate key value violates unique constraint "pk_log"
DETAIL: Key (id)=(01993eaf-ec3b-7d7d-b153-47a9f5cc9cf7) already exists.
CONTEXT: COPY log, line 1
2025-11-19T232630.599636Z ERROR Database error 23505: duplicate key value violates unique constraint "pk_task_run_state"
DETAIL: Key (id)=(019956d5-40f5-7bb8-a0ac-a79f661c9969) already exists.
CONTEXT: COPY task_run_state, line 1
2025-11-19T232630.645800Z ERROR Database error 23505: duplicate key value violates unique constraint "pk_work_queue"
DETAIL: Key (id)=(09012307-9413-41d3-90c0-33005637bd37) already exists.
CONTEXT: COPY work_queue, line 1
Heap exhausted during garbage collection: 1456 bytes available, 4496 requested
....
fatal error encountered in SBCL pid 1 tid 9:
Heap exhausted, game over.
Welcome to LDB, a low-level debugger for the Lisp runtime environment.
ldb> Error opening /dev/tty: No such device or address
m
Marvin
11/19/2025, 11:28 PMYour message was too long, here's your message at the allowed limit:
lots of warnings on columns remain but also more severe looking stuff 2025-11-19T23 26 20 306892Z ERROR Database error 23505 duplicate key value violates unique constraint pk_flow DETAIL Key (id)=(1e44683c-a3b2-452b-925c-da74daebd1e2) already exists CONTEXT COPY flow line 1 2025-11-19T23 26 20 364549Z ERROR Database error 23505 duplicate key value violates unique constraint pk_concurrency_limit DETAIL Key (id)=(0e3c7b58-c543-4aba-87df-b386d09ba8b6) already exists CONTEXT COPY concurrency_limit line 1 2025-11-19T23 26 20 599021Z ERROR Database error 23505 duplicate key value violates unique constraint pk_block_schema_reference DETAIL Key (id)=(2d6c80e4-870b-41a0-bd14-d423e5c2cd1e) already exists CONTEXT COPY block_schema_reference line 1 2025-11-19T23 26 20 606709Z ERROR Database error 23505 duplicate key value violates unique constraint uq_block_schema__checksum_version DETAIL Key (checksum version)=(sha256 61e9dbea14935ccb2cfac7eb38f01c4e878a81073806f2aec993820fa0d91eb3 3 4 17) already exists CONTEXT COPY block_schema line 1 2025-11-19T23 26 20 748930Z ERROR Database error 23505 duplicate key value violates unique constraint pk_block_document_reference DETAIL Key (id)=(3f615161-685e-4f14-af19-5c8658d80ccd) already exists CONTEXT COPY block_document_reference line 1 2025-11-19T23 26 20 748930Z ERROR Database error 23505 duplicate key value violates unique constraint pk_block_document DETAIL Key (id)=(e4c96407-768c-4b88-acbd-2265f448c5f3) already exists CONTEXT COPY block_document line 1 2025-11-19T23 26 20 806587Z ERROR Database error 23505 duplicate key value violates unique constraint pk_worker DETAIL Key (id)=(47b18cd5-76c4-42a5-a718-94e3530d026b) already exists CONTEXT COPY worker line 1 2025-11-19T23 26 20 814274Z ERROR Database error 23505 duplicate key value violates unique constraint uq_block_type__slug DETAIL Key (slug)=(webhook) already exists CONTEXT COPY block_type line 1 2025-11-19T23 26 21 075652Z ERROR Database error 23505 duplicate key value violates unique constraint pk_flow_run_state DETAIL Key (id)=(01993eaf-d045-77b0-b695-1ee3e0b4640f) already exists CONTEXT COPY flow_run_state line 1 2025-11-19T23 26 21 164059Z ERROR Database error 23505 duplicate key value violates unique constraint uq_work_pool__name DETAIL Key (name)=(production-pool) already exists CONTEXT COPY work_pool line 1 2025-11-19T23 26 25 556442Z ERROR Database error 23505 duplicate key value violates unique constraint pk_log DETAIL Key (id)=(01993eaf-ec3b-7d7d-b153-47a9f5cc9cf7) already exists CONTEXT COPY log line 1 2025-11-19T23 26 30 599636Z ERROR Database error 23505 duplicate key value violates unique constraint pk_task_run_state DETAIL Key (id)=(019956d5-40f5-7bb8-a0ac-a79f661c9969) already exists CONTEXT COPY task_run_state line 1 2025-11-19T23 26 30 645800Z ERROR Database error 23505 duplicate key value violates unique constraint pk_work_queue DETAIL Key (id)=(09012307-9413-41d3-90c0-33005637bd37) already exists CONTEXT COPY work_queue line 1 Heap exhausted during garbage collection 1456 bytes available 4496 requested Gen Boxed Code Raw LgBox LgCode LgRaw Pin Alloc Waste Trig WP GCs Mem-age 0 1715 0 1026 0 0 0 5 86492304 3324784 43467962 2741 1 0 0000 1 97 0 6544 0 0 13496 13460 651964464 7884752 10737418 20137 0 1 1470 2 250 0 6076 0 0 0 90 202151936 5138432 2000000 6326 0 0 6800 3 0 0 0 0 0 0 0 0 0 2000000 0 0 0 0000 4 0 0 0 0 0 0 0 0 0 2000000 0 0 0 0000 5 0 0 0 0 0 0 0 0 0 2000000 0 0 0 0000 6 1734 7 1426 287 0 110 0 114478960 2306192 2000000 3564 0 0 0000 Total bytes allocated = 1055087664 Dynamic-space-size bytes = 1073741824 GC control variables GC-INHIBIT = true GC-PENDING = true STOP-FOR-GC-PENDING = false fatal error encountered in SBCL pid 1 tid
Marvin
11/19/2025, 11:28 PM✅ thought for 59.6 seconds
Marvin
11/19/2025, 11:29 PMThose duplicate key errors mean rows already exist in your Postgres tables (likely from a partial pgloader run and/or because the server/work-pool creator connected and seeded data). The cleanest fix is:
- ensure the target DB has the correct schema but is empty
- then run pgloader in data-only mode
- exclude SQLite internals and alembic_version
- optionally reduce batch sizes to avoid the pgloader heap OOM
Do this step-by-step:
1) Stop anything that writes to Prefect
- Stop your Prefect server, workers, and any “create-work-pools”/init jobs so nothing touches Postgres during migration.
2) Bring up Postgres (so the network exists)
Copy code
docker compose up -d postgres Copy code
docker run --rm --network qfin_default \
-e PREFECT_API_DATABASE_CONNECTION_URL=<postgresql+asyncpg://prefect:prefect@postgres:5432/prefect> \
prefecthq/prefect:3.4.17 prefect server database reset -y Copy code
docker run --rm --network qfin_default \
-e PREFECT_API_DATABASE_CONNECTION_URL=<postgresql+asyncpg://prefect:prefect@postgres:5432/prefect> \
prefecthq/prefect:3.4.17 prefect server database upgrade -y Copy code
LOAD DATABASE
FROM sqlite:///root/.prefect/prefect.db
INTO <postgresql://prefect:prefect@postgres:5432/prefect>
WITH data only,
batch rows = 1000,
prefetch rows = 1000,
on error stop
BEFORE LOAD DO
$$
DO $do$
DECLARE r record;
BEGIN
FOR r IN
SELECT tablename
FROM pg_tables
WHERE schemaname = 'public'
AND tablename <> 'alembic_version'
LOOP
EXECUTE 'TRUNCATE TABLE public.' || quote_ident(r.tablename) || ' CASCADE';
END LOOP;
END;
$do$;
$$
EXCLUDING TABLE NAMES LIKE 'sqlite_%'
EXCLUDING TABLE NAMES LIKE 'alembic_version'
; Copy code
docker run --rm --network qfin_default \
-v brrr_prefect_data:/root/.prefect \
-v $(pwd)/pgloader.load:/load.load \
dimitri/pgloader:latest pgloader /load.loaddocker run --memory=4g ... Copy code
docker exec -it $(docker ps -qf "name=postgres") psql -U prefect -d prefect -c "SELECT COUNT(*) FROM flow_run;"
docker exec -it $(docker ps -qf "name=postgres") psql -U prefect -d prefect -c "SELECT COUNT(*) FROM task_run;"
docker exec -it $(docker ps -qf "name=postgres") psql -U prefect -d prefect -c "SELECT COUNT(*) FROM log;"
docker exec -it $(docker ps -qf "name=postgres") psql -U prefect -d prefect -c "SELECT COUNT(*) FROM events;"PREFECT_SERVER_EVENTS_RETENTION_PERIOD=P2DPREFECT_SERVER_EVENTS_MAXIMUM_WEBSOCKET_BACKFILL=PT300SPREFECT_SERVER_EVENTS_WEBSOCKET_BACKFILL_PAGE_SIZE=100PREFECT_SERVER_EVENTS_RELATED_RESOURCE_CACHE_TTL=PT300Ssqlite_stat1Marvin
11/19/2025, 11:29 PMIf you hit any new constraint errors
- Paste the exact table/constraint from pgloader. I’ll update the load file to either:
- exclude a table you should keep from Postgres (rare), or
- add a TRUNCATE/ordering tweak for that table.
Once you’re up on Postgres, the UI slowness and memory spikes should drop significantly, especially with your shorter events backfill/retention.
o
Olve Heitmann
11/19/2025, 11:32 PM@Marvin erver database reset -y
Downgrading database...
Traceback (most recent call last):
File "/usr/local/lib/python3.12/site-packages/prefect/cli/_utilities.py", line 44, in wrapper
return fn(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/prefect/cli/_types.py", line 156, in sync_fn
return asyncio.run(async_fn(*args, **kwargs))
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/asyncio/runners.py", line 195, in run
return runner.run(main)
^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/asyncio/runners.py", line 118, in run
return self._loop.run_until_complete(task)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/asyncio/base_events.py", line 691, in run_until_complete
return future.result()
^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/prefect/cli/server.py", line 415, in reset
await db.drop_db()
File "/usr/local/lib/python3.12/site-packages/prefect/server/database/interface.py", line 81, in drop_db
await self.run_migrations_downgrade(revision="base")
File "/usr/local/lib/python3.12/site-packages/prefect/server/database/interface.py", line 89, in run_migrations_downgrade
await run_sync_in_worker_thread(alembic_downgrade, revision=revision)
File "/usr/local/lib/python3.12/site-packages/prefect/utilities/asyncutils.py", line 233, in run_sync_in_worker_thread
result = await anyio.to_thread.run_sync(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/anyio/to_thread.py", line 56, in run_sync
return await get_async_backend().run_sync_in_worker_thread(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/anyio/_backends/_asyncio.py", line 2476, in run_sync_in_worker_thread
return await future
^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/anyio/_backends/_asyncio.py", line 967, in run
result = context.run(func, *args)
^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/prefect/utilities/asyncutils.py", line 243, in call_with_mark
return call()
^^^^^^
File "/usr/local/lib/python3.12/site-packages/prefect/server/database/alembic_commands.py", line 36, in wrapper
return fn(*args, **kwargs)
^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/prefect/server/database/alembic_commands.py", line 87, in alembic_downgrade
alembic.command.downgrade(alembic_config(), revision, sql=dry_run)
File "/usr/local/lib/python3.12/site-packages/alembic/command.py", line 530, in downgrade
script.run_env()
File "/usr/local/lib/python3.12/site-packages/alembic/script/base.py", line 549, in run_env
util.load_python_file(self.dir, "env.py")
File "/usr/local/lib/python3.12/site-packages/alembic/util/pyfiles.py", line 116, in load_python_file
module = load_module_py(module_id, path)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/alembic/util/pyfiles.py", line 136, in load_module_py
spec.loader.exec_module(module) # type: ignore
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "frozen importlib._bootstrap_external", line 999, in exec_module
File "frozen importlib._bootstrap", line 488, in _call_with_frames_removed
File "/usr/local/lib/python3.12/site-packages/prefect/server/database/_migrations/env.py", line 201, in module
run_async_from_worker_thread(apply_migrations)
File "/usr/local/lib/python3.12/site-packages/prefect/utilities/asyncutils.py", line 254, in run_async_from_worker_thread
return anyio.from_thread.run(call)
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/anyio/from_thread.py", line 59, in run
return async_backend.run_async_from_thread(func, args, token=token)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/anyio/_backends/_asyncio.py", line 2516, in run_async_from_thread
return f.result()
^^^^^^^^^^
File "/usr/local/lib/python3.12/concurrent/futures/_base.py", line 456, in result
return self.__get_result()
^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/concurrent/futures/_base.py", line 401, in __get_result
raise self._exception
File "/usr/local/lib/python3.12/site-packages/anyio/_backends/_asyncio.py", line 2503, in task_wrapper
return await func(*args)
^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/prefect/server/database/_migrations/env.py", line 190, in apply_migrations
await connection.run_sync(do_run_migrations)
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/ext/asyncio/engine.py", line 887, in run_sync
return await greenlet_spawn(
^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/sqlalchemy/util/_concurrency_py3k.py", line 203, in greenlet_spawn
result = context.switch(value)
^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/prefect/server/database/_migrations/env.py", line 159, in do_run_migrations
context.run_migrations()
File "string", line 8, in run_migrations
File "/usr/local/lib/python3.12/site-packages/alembic/runtime/environment.py", line 946, in run_migrations
self.get_context().run_migrations(**kw)
File "/usr/local/lib/python3.12/site-packages/alembic/runtime/migration.py", line 615, in run_migrations
for step in self._migrations_fn(heads, self):
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/alembic/command.py", line 519, in downgrade
return script._downgrade_revs(revision, rev)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/alembic/script/base.py", line 431, in _downgrade_revs
with self._catch_revision_errors(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/contextlib.py", line 158, in exit
self.gen.throw(value)
File "/usr/local/lib/python3.12/site-packages/alembic/script/base.py", line 249, in _catch_revision_errors
raise util.CommandError(resolution) from re
alembic.util.exc.CommandError: Can't locate revision identified by '8bb517bae6f9'
An exception occurred.
m
Marvin
11/19/2025, 11:32 PMYour message was too long, here's your message at the allowed limit:
erver database reset -y Downgrading database Traceback (most recent call last) File /usr/local/lib/python3 12/site-packages/alembic/script/base py line 217 in _catch_revision_errors yield File /usr/local/lib/python3 12/site-packages/alembic/script/base py line 443 in _downgrade_revs for script in revs ^^^^ File /usr/local/lib/python3 12/site-packages/alembic/script/revision py line 814 in iterate_revisions revisions heads = fn( ^^^ File /usr/local/lib/python3 12/site-packages/alembic/script/revision py line 1391 in _collect_downgrade_revisions heads = self get_revisions(upper) ^^^^^^^^^^^^^^^^^^^^^^^^^ File /usr/local/lib/python3 12/site-packages/alembic/script/revision py line 542 in get_revisions return sum([self get_revisions(id_elem) for id_elem in id_] ()) ^^^^^^^^^^^^^^^^^^^^^^^^^^^ File /usr/local/lib/python3 12/site-packages/alembic/script/revision py line 565 in get_revisions return tuple( ^^^^^^ File /usr/local/lib/python3 12/site-packages/alembic/script/revision py line 566 in <genexpr> self _revision_for_ident(rev_id branch_label) File /usr/local/lib/python3 12/site-packages/alembic/script/revision py line 637 in _revision_for_ident raise ResolutionError( alembic script revision ResolutionError No such revision or branch '8bb517bae6f9' The above exception was the direct cause of the following exception Traceback (most recent call last) File /usr/local/lib/python3 12/site-packages/prefect/cli/_utilities py line 44 in wrapper return fn(*args *kwargs) ^^^^^^^^^^^^^^^^^^^ File /usr/local/lib/python3 12/site-packages/prefect/cli/_types py line 156 in sync_fn return asyncio run(async_fn(*args *kwargs)) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File /usr/local/lib/python3 12/asyncio/runners py line 195 in run return runner run(main) ^^^^^^^^^^^^^^^^ File /usr/local/lib/python3 12/asyncio/runners py line 118 in run return self _loop run_until_complete(task) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File /usr/local/lib/python3 12/asyncio/base_events py line 691 in run_until_complete return future result() ^^^^^^^^^^^^^^^ File /usr/local/lib/python3 12/site-packages/prefect/cli/server py line 415 in reset await db drop_db() File /usr/local/lib/python3 12/site-packages/prefect/server/database/interface py line 81 in drop_db await self run_migrations_downgrade(revision= base ) File /usr/local/lib/python3 12/site-packages/prefect/server/database/interface py line 89 in run_migrations_downgrade await run_sync_in_worker_thread(alembic_downgrade revision=revision) File /usr/local/lib/python3 12/site-packages/prefect/utilities/asyncutils py line 233 in run_sync_in_worker_thread result = await <http //anyio to|anyio to>_thread run_sync( ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File /usr/local/lib/python3 12/site-packages/anyio/to_thread py line 56 in run_sync return await get_async_backend() run_sync_in_worker_thread( ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File /usr/local/lib/python3 12/site-packages/anyio/_backends/_asyncio py line 2476 in run_sync_in_worker_thread return await future ^^^^^^^^^^^^ File /usr/local/lib/python3 12/site-packages/anyio/_backends/_asyncio py line 967 in run result = context run(func *args) ^^^^^^^^^^^^^^^^^^^^^^^^ File /usr/local/lib/python3 12/site-packages/prefect/utilities/asyncutils py line 243 in call_with_mark return call() ^^^^^^ File /usr/local/lib/python3 12/site-packages/prefect/server/database/alembic_commands py line 36 in wrapper return fn(*args **kwargs) ^^^^^^^^^^^^^^^^^^^ File /usr/local/lib/python3 12/site-packages/prefect/server/database/alembic_commands py line 87 in alembic_downgrade alembic command downgrade(alembic_config() revision sql=dry_run) File /usr/local/lib/python3 12/site-packages/alembic/command py line 530 in downgrade script run_env() File /usr/local/lib/python3 12/site-packages/alembic/script/base py line 549 in run_env util load_python_file(self dir env py ) File /usr/local/lib/python3 12/site-packa
Marvin
11/19/2025, 11:32 PMges/alembic/util/pyfiles py line 116 in load_python_file module = load_module_py(module_id path) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File /usr/local/lib/python3 12/site-packages/alembic/util/pyfiles py line 136 in load_module_py spec loader exec_module(module) # type ignore ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File <frozen importlib _bootstrap_external> line 999 in exec_module File <frozen importlib _bootstrap> line 488 in _call_with_frames_removed File /usr/local/lib/python3 12/site-packages/prefect/server/database/_migrations/env py line 201 in <module> run_async_from_worker_thread(apply_migrations) File /usr/local/lib/python3 12/site-packages/prefect/utilities/asyncutils py line 254 in run_async_from_worker_thread return anyio from_thread run(call) ^^^^^^^^^^^^^^^^^^^^^^^^^^^ File /usr/local/lib/python3 12/site-packages/anyio/from_thread py line 59 in run return async_backend run_async_from_thread(func args token=token) ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^ File /usr/local/lib/python3 12/site-packages/anyio/_backends/_asyncio py line 2516 in run_async_from_thread return f result() ^^^^^^^^^^ File /usr/local/lib/python3 12/concurrent/futures/_base py line 456 in result return self __get_result() ^^^^^^^^^^^^^^^^^^^ File /usr/local/lib/python3 12/concurrent/futures/_base py line 401 in __get_result raise self _exception File /usr/local/lib/python3 12/site-packages/anyio/_backends/_asyncio py line 2503 in task_wrapper return await func(*args) ^^^^^^^^^^^^^^^^^ File /usr/local/lib/python3 12/site-packages/prefect/server/database/_migrations/env py line 190 in apply_migrations await connection run_sync(do_run_migrations) File /usr/local/lib/python3 12/site-packages/sqlalchemy/ext/asyncio/engine py line 887 in run_sync return await greenlet_spawn( ^^^^^^^^^^^^^^^^^^^^^ File /usr/local/lib/python3 12/site-packages/sqlalchemy/util/_concurrency_py3k
Marvin
11/19/2025, 11:34 PM✅ thought for 118.5 seconds
Marvin
11/19/2025, 11:36 PMThanks — that traceback explains it. The reset failed because your Postgres DB’s
alembic_version8bb517bae6f9reset Copy code
docker run --rm --network qfin_default \
-e PREFECT_API_DATABASE_CONNECTION_URL=<postgresql+asyncpg://prefect:prefect@postgres:5432/prefect> \
prefecthq/prefect:3.4.17 prefect server database stamp head Copy code
docker run --rm --network qfin_default \
-e PREFECT_API_DATABASE_CONNECTION_URL=<postgresql+asyncpg://prefect:prefect@postgres:5432/prefect> \
prefecthq/prefect:3.4.17 prefect server database reset -y Copy code
LOAD DATABASE
FROM sqlite:///root/.prefect/prefect.db
INTO <postgresql://prefect:prefect@postgres:5432/prefect>
WITH data only,
batch rows = 1000,
prefetch rows = 1000,
on error stop
BEFORE LOAD DO
$$
DO $do$
DECLARE r record;
BEGIN
FOR r IN
SELECT tablename
FROM pg_tables
WHERE schemaname = 'public'
AND tablename <> 'alembic_version'
LOOP
EXECUTE 'TRUNCATE TABLE public.' || quote_ident(r.tablename) || ' CASCADE';
END LOOP;
END;
$do$;
$$
EXCLUDING TABLE NAMES LIKE 'sqlite_%'
EXCLUDING TABLE NAMES LIKE 'alembic_version'
; Copy code
docker run --rm --network qfin_default \
-v brrr_prefect_data:/root/.prefect \
-v $(pwd)/pgloader.load:/load.load \
dimitri/pgloader:latest pgloader /load.loaddocker volume lsqfin_postgres_datadocker volume rm qfin_postgres_data Copy code
docker compose up -d postgres Copy code
docker run --rm --network qfin_default \
-e PREFECT_API_DATABASE_CONNECTION_URL=<postgresql+asyncpg://prefect:prefect@postgres:5432/prefect> \
prefecthq/prefect:3.4.17 prefect server database upgrade -y--memory=4g Copy code
docker run --rm --network qfin_default \
-e PREFECT_API_DATABASE_CONNECTION_URL=<postgresql+asyncpg://prefect:prefect@postgres:5432/prefect> \
prefecthq/prefect:3.4.17 prefect server database upgrade -yprefecthq/prefect:3.4.17PREFECT_SERVER_EVENTS_RETENTION_PERIOD=P2DPREFECT_SERVER_EVENTS_MAXIMUM_WEBSOCKET_BACKFILL=PT300SPREFECT_SERVER_EVENTS_WEBSOCKET_BACKFILL_PAGE_SIZE=100PREFECT_SERVER_EVENTS_RELATED_RESOURCE_CACHE_TTL=PT300S Copy code
docker exec -it $(docker ps -qf "name=postgres") psql -U prefect -d prefect -c "SELECT COUNT(*) FROM flow_run;"
docker exec -it $(docker ps -qf "name=postgres") psql -U prefect -d prefect -c "SELECT COUNT(*) FROM task_run;"
docker exec -it $(docker ps -qf "name=postgres") psql -U prefect -d prefect -c "SELECT COUNT(*) FROM log;"
docker exec -it $(docker ps -qf "name=postgres") psql -U prefect -d prefect -c "SELECT COUNT(*) FROM events;"Marvin
11/19/2025, 11:36 PMIf you hit any new duplicate/constraint messages from pgloader, paste the exact table/constraint and I’ll tweak the load file further (e.g., exclude or pre-truncate specific seeded tables).
o
Olve Heitmann
11/19/2025, 11:41 PM@Marvin both option a and b (nuke) fails with ✔️ Container qfin-postgres-1 Running 0.0s
olveh@olve-machine:/mnt/c/qfin/qfin/prefect_setup/server_workpools_worker$ docker run --rm --network qfin_default -e PREFECT_API_DATABASE_CONNECTION_URL=postgresql+asyncpg://prefect:prefect@postgres:5432/prefect prefecthq/prefect:3-latest prefect server database upgrade -y
Running upgrade migrations ...
Traceback (most recent call last):
File "/usr/local/lib/python3.12/site-packages/alembic/script/base.py", line 217, in _catch_revision_errors
yield
also there is no 3.4.17 locally or on hub so i use latest both in compose file and in my commands
m
Marvin
11/19/2025, 11:41 PM✅ thought for 49.5 seconds
Marvin
11/19/2025, 11:42 PMThanks for sticking with this — a couple things to fix before we try again:
Key issues I see
- You’re still passing angle brackets in the env var:
PREFECT_API_DATABASE_CONNECTION_URL=<...>prefecthq/prefect:3-latest Copy code
services:
prefect-server:
image: prefecthq/prefect:3-latest
environment:
PREFECT_API_DATABASE_CONNECTION_URL: <postgresql+asyncpg://prefect:prefect@postgres:5432/prefect>
PREFECT_API_DATABASE_MIGRATE_ON_START: "false" # we will run migrations manually
PREFECT_SERVER_EVENTS_RETENTION_PERIOD: "P2D"
PREFECT_SERVER_EVENTS_MAXIMUM_WEBSOCKET_BACKFILL: "PT300S"
PREFECT_SERVER_EVENTS_WEBSOCKET_BACKFILL_PAGE_SIZE: "100"
PREFECT_SERVER_EVENTS_RELATED_RESOURCE_CACHE_TTL: "PT300S"
command: prefect server start --host 0.0.0.0 Copy code
docker compose down
docker volume rm qfin_postgres_data Copy code
docker volume ls | grep qfin Copy code
docker compose up -d postgres
# optional but recommended
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "CREATE EXTENSION IF NOT EXISTS pg_trgm;"
# run migrations using the same image/env as your server service
docker compose run --rm prefect-server prefect server database upgrade -y Copy code
LOAD DATABASE
FROM sqlite:///root/.prefect/prefect.db
INTO <postgresql://prefect:prefect@postgres:5432/prefect>
WITH data only,
batch rows = 1000,
prefetch rows = 1000,
on error stop
BEFORE LOAD DO
$$
DO $do$
DECLARE r record;
BEGIN
FOR r IN
SELECT tablename
FROM pg_tables
WHERE schemaname = 'public'
AND tablename <> 'alembic_version'
LOOP
EXECUTE 'TRUNCATE TABLE public.' || quote_ident(r.tablename) || ' CASCADE';
END LOOP;
END;
$do$;
$$
EXCLUDING TABLE NAMES LIKE 'sqlite_%'
EXCLUDING TABLE NAMES LIKE 'alembic_version'
; Copy code
docker run --rm --network qfin_default \
-v brrr_prefect_data:/root/.prefect \
-v $(pwd)/pgloader.load:/load.load \
dimitri/pgloader:latest pgloader /load.load--memory=4g Copy code
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM flow_run;"
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM task_run;"
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM log;"
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM events;" Copy code
docker compose up -d prefect-server
# once healthy, start workers
docker compose up -d prefect-prod-worker prefect-dev-workerMarvin
11/19/2025, 11:42 PM- A stale alembic_version (should not happen after volume nuke)
- Please paste the FULL traceback (from “Running upgrade migrations ...” down) and I’ll diagnose exactly.
Useful docs
- Server CLI (database): Self-hosted Server CLI
- DB settings: Settings reference — database
- Events settings: Settings reference — server events
Once we get through the “upgrade” step cleanly, the pgloader run with TRUNCATE will avoid all the duplicate key errors you hit earlier, and your UI/memory situation should improve significantly.
o
Olve Heitmann
11/19/2025, 11:50 PM@Marvin olveh@olve-machine:/mnt/c/qfin/qfin/prefect_setup/server_workpools_worker$ docker compose run --rm prefect-server prefect server database upgrade -y
WARN[0000] volume "brrr_prefect_data" already exists but was created for project "brrr" (expected "qfin"). Use
external: truem
Marvin
11/19/2025, 11:50 PM✅ thought for 48.4 seconds
Marvin
11/19/2025, 11:50 PMNice — migrations succeeded, that’s the big hurdle.
About the warning:
- “volume brrr_prefect_data already exists but was created for project brrr (expected qfin)” just means your Compose project name changed but you’re reusing a volume created under the old project.
- Functionally it’s fine. To silence it, declare the volume as external and keep the explicit name.
Update your volumes section like this:
Copy code
volumes:
prefect_data:
external: true
name: brrr_prefect_data
postgres_data: Copy code
services:
prefect-server:
...
volumes:
- prefect_data:/root/.prefect~/.prefect Copy code
LOAD DATABASE
FROM sqlite:///root/.prefect/prefect.db
INTO <postgresql://prefect:prefect@postgres:5432/prefect>
WITH data only,
batch rows = 1000,
prefetch rows = 1000,
on error stop
BEFORE LOAD DO
$$
DO $do$
DECLARE r record;
BEGIN
FOR r IN
SELECT tablename
FROM pg_tables
WHERE schemaname = 'public'
AND tablename <> 'alembic_version'
LOOP
EXECUTE 'TRUNCATE TABLE public.' || quote_ident(r.tablename) || ' CASCADE';
END LOOP;
END;
$do$;
$$
EXCLUDING TABLE NAMES LIKE 'sqlite_%'
EXCLUDING TABLE NAMES LIKE 'alembic_version'
; Copy code
docker run --rm --network qfin_default \
-v brrr_prefect_data:/root/.prefect \
-v $(pwd)/pgloader.load:/load.load \
dimitri/pgloader:latest pgloader /load.load--memory=4g Copy code
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM flow_run;"
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM task_run;"
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM log;"
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM events;" Copy code
docker run --rm -v brrr_prefect_data:/data -v $(pwd):/backup alpine sh -c "cp /data/prefect.db /backup/prefect.db.backup.sqlite"prefect-server Copy code
docker volume rm brrr_prefect_datao
Olve Heitmann
11/19/2025, 11:55 PM@Marvin some ugly errors remain on pgloader:
2025-11-19T235313.708873Z ERROR Error while formatting a row from "flow_run":
2025-11-19T235313.708873Z ERROR A SB-KERNEL::HEAP-EXHAUSTED-ERROR condition without bindings for heap statistics. (If
you did not expect to see this message, please report it.
2025-11-19T235313.724105Z ERROR Database error 22007: invalid input syntax for type interval: "1970-01-01 000000.000000"
CONTEXT: COPY flow_run, line 1, column total_run_time: "1970-01-01 000000.000000"
Heap exhausted during allocation: 373587968 bytes available, 499842272 requested.
2025-11-19T235325.489317Z ERROR Database error 22007: invalid input syntax for type interval: "1970-01-01 000000.082295"
CONTEXT: COPY task_run, line 1, column total_run_time: "1970-01-01 000000.082295"
2025-11-19T235328.651281Z ERROR PostgreSQL Database error 23503: insert or update on table "flow_run_state" violates foreign key constraint "fk_flow_run_state__flow_run_id__flow_run"
DETAIL: Key (flow_run_id)=(6bd95e67-8a67-4607-afff-975415b1cf34) is not present in table "flow_run"
2025-11-19T235328.666501Z ERROR PostgreSQL Database error 23503: insert or update on table "task_run_state" violates foreign key constraint "fk_task_run_state__task_run_id__task_run"
DETAIL: Key (task_run_id)=(019956d5-40f5-727a-98e9-46d436b79e67) is not present in table "task_run".
QUERY: ALTER TABLE "public"."task_run_state" ADD CONSTRAINT "fk_task_run_state__task_run_id__task_run" FOREIGN KEY (task_run_id) REFERENCES task_run(id) ON DELETE CASCADE
m
Marvin
11/19/2025, 11:55 PM✅ thought for 97.7 seconds
Marvin
11/19/2025, 11:57 PMGood finds — those errors tell us two things:
- The interval columns (
flow_run.total_run_timetask_run.total_run_time Copy code
LOAD DATABASE
FROM sqlite:///root/.prefect/prefect.db
INTO <postgresql://prefect:prefect@postgres:5432/prefect>
WITH data only,
batch rows = 500,
prefetch rows = 500,
on error stop
BEFORE LOAD DO
$$
-- 1) Empty all public tables, keep alembic_version
DO $do$
DECLARE r record;
BEGIN
FOR r IN
SELECT tablename
FROM pg_tables
WHERE schemaname = 'public'
AND tablename <> 'alembic_version'
LOOP
EXECUTE 'TRUNCATE TABLE public.' || quote_ident(r.tablename) || ' CASCADE';
END LOOP;
END;
$do$;
-- 2) Temporarily make total_run_time columns TEXT so COPY won't fail
ALTER TABLE flow_run ALTER COLUMN total_run_time TYPE text;
ALTER TABLE task_run ALTER COLUMN total_run_time TYPE text;
$$
EXCLUDING TABLE NAMES LIKE 'sqlite_%'
EXCLUDING TABLE NAMES LIKE 'alembic_version'
AFTER LOAD DO
$$
-- 3) Normalize text values: strip the date part, keep only time part 'HH:MM:<http://SS.US|SS.US>'
UPDATE flow_run
SET total_run_time =
CASE
WHEN total_run_time IS NULL OR total_run_time = '' THEN NULL
WHEN position(' ' in total_run_time) > 0 THEN substring(total_run_time from position(' ' in total_run_time) + 1)
ELSE total_run_time
END;
UPDATE task_run
SET total_run_time =
CASE
WHEN total_run_time IS NULL OR total_run_time = '' THEN NULL
WHEN position(' ' in total_run_time) > 0 THEN substring(total_run_time from position(' ' in total_run_time) + 1)
ELSE total_run_time
END;
-- 4) Cast back to INTERVAL (Postgres accepts 'HH:MM:<http://SS.US|SS.US>' as an interval literal)
ALTER TABLE flow_run
ALTER COLUMN total_run_time TYPE interval
USING NULLIF(total_run_time, '')::interval;
ALTER TABLE task_run
ALTER COLUMN total_run_time TYPE interval
USING NULLIF(total_run_time, '')::interval;
$$
; Copy code
docker run --rm --network qfin_default \
-v brrr_prefect_data:/root/.prefect \
-v $(pwd)/pgloader.load:/load.load \
dimitri/pgloader:latest pgloader /load.loadbatch rows = 200prefetch rows = 200docker run --memory=4g ...EXCLUDING TABLE NAMES LIKE 'events'EXCLUDING TABLE NAMES LIKE 'log'Marvin
11/19/2025, 11:57 PMdocker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM flow_run;"
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM task_run;"
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM flow_run_state;"
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM task_run_state;"
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM log;"
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM events;"
Copy code
6) Compose volume notice
- Your warning about the old volume is fine; declare it as external to silence:o
Olve Heitmann
11/20/2025, 12:01 AM@Marvin docker run --rm --network qfin_default \
-v brrr_prefect_data:/root/.prefect \
-v $(pwd)/pgloader.load:/load.load \
dimitri/pgloader:latest pgloader /load.load
2025-11-20T000054.030426Z LOG pgloader version "3.6.7~devel"
2025-11-20T000054.038033Z LOG Parsing commands from file #P"/load.load"
KABOOM!
COLUMNS-ERROR: Database error 42601: cannot insert multiple commands into a prepared statement
QUERY: DO $do$
DECLARE r record;
BEGIN
FOR r IN
SELECT tablename
FROM pg_tables
WHERE schemaname = 'public'
AND tablename <> 'alembic_version'
LOOP
EXECUTE 'TRUNCATE TABLE public.' || quote_ident(r.tablename) || ' CASCADE';
END LOOP;
END;
$do$;
-- 2) Temporarily make total_run_time columns TEXT so COPY won't fail
ALTER TABLE flow_run ALTER COLUMN total_run_time TYPE text;
ALTER TABLE task_run ALTER COLUMN total_run_time TYPE text;
An unhandled error condition has been signalled:
Database error 42601: cannot insert multiple commands into a prepared statement
m
Marvin
11/20/2025, 12:01 AM✅ thought for 71.4 seconds
Marvin
11/20/2025, 12:02 AMGreat catch — that pgloader error is because it tried to send multiple SQL statements as a single prepared statement. We can fix this by:
- letting pgloader handle truncation automatically, and
- splitting each ALTER/UPDATE into its own BEFORE/AFTER clause.
Also, we’ll transform the total_run_time columns to text before the load, then normalize and cast back to interval after the load. Here’s a working load file.
Try this pgloader.load
- Note: we keep batch/prefetch small to avoid heap OOM. You can tune up once it works.
- If your logs/events are huge, I’ve added an optional “exclude” variant below.
Copy code
LOAD DATABASE
FROM sqlite:///root/.prefect/prefect.db
INTO <postgresql://prefect:prefect@postgres:5432/prefect>
WITH data only,
truncate, -- pgloader will TRUNCATE each target table before copying
batch rows = 500,
prefetch rows = 500,
on error stop
EXCLUDING TABLE NAMES LIKE 'sqlite_%'
EXCLUDING TABLE NAMES LIKE 'alembic_version'
-- Temporarily change interval columns to text so COPY won’t fail on SQLite-formatted values
BEFORE LOAD DO $$ ALTER TABLE flow_run ALTER COLUMN total_run_time TYPE text; $$
BEFORE LOAD DO $$ ALTER TABLE task_run ALTER COLUMN total_run_time TYPE text; $$
-- Normalize the text values to time-only strings
AFTER LOAD DO $$
UPDATE flow_run
SET total_run_time =
CASE
WHEN total_run_time IS NULL OR total_run_time = '' THEN NULL
WHEN position(' ' in total_run_time) > 0 THEN substring(total_run_time from position(' ' in total_run_time) + 1)
ELSE total_run_time
END;
$$
AFTER LOAD DO $$
UPDATE task_run
SET total_run_time =
CASE
WHEN total_run_time IS NULL OR total_run_time = '' THEN NULL
WHEN position(' ' in total_run_time) > 0 THEN substring(total_run_time from position(' ' in total_run_time) + 1)
ELSE total_run_time
END;
$$
-- Cast back to INTERVAL
AFTER LOAD DO $$
ALTER TABLE flow_run
ALTER COLUMN total_run_time TYPE interval
USING NULLIF(total_run_time, '')::interval;
$$
AFTER LOAD DO $$
ALTER TABLE task_run
ALTER COLUMN total_run_time TYPE interval
USING NULLIF(total_run_time, '')::interval;
$$
; Copy code
docker run --rm --network qfin_default \
-v brrr_prefect_data:/root/.prefect \
-v $(pwd)/pgloader.load:/load.load \
dimitri/pgloader:latest pgloader /load.load Copy code
docker run --rm --memory=4g --network qfin_default \
-v brrr_prefect_data:/root/.prefect \
-v $(pwd)/pgloader.load:/load.load \
dimitri/pgloader:latest pgloader /load.load Copy code
EXCLUDING TABLE NAMES LIKE 'log'
EXCLUDING TABLE NAMES LIKE 'events'Marvin
11/20/2025, 12:02 AMdocker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM log;"
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM events;"
```
One more reminder
- Make sure your compose uses a plain URL (no angle brackets) and you’re using the same image tag for server and CLI (you’re on 3-latest now, which is fine as long as both use it). Keep your event settings to reduce UI load:
-
PREFECT_SERVER_EVENTS_RETENTION_PERIOD=P2DPREFECT_SERVER_EVENTS_MAXIMUM_WEBSOCKET_BACKFILL=PT300SPREFECT_SERVER_EVENTS_WEBSOCKET_BACKFILL_PAGE_SIZE=100PREFECT_SERVER_EVENTS_RELATED_RESOURCE_CACHE_TTL=PT300So
Olve Heitmann
11/20/2025, 12:05 AM@Marvin
i set rows to 200, still
Heap exhausted during allocation: 6553600 bytes available, 110034048 requested.
2025-11-20T000404.239052Z ERROR Error while formatting a row from "flow_run":
2025-11-20T000404.239052Z ERROR A SB-KERNEL::HEAP-EXHAUSTED-ERROR condition without bindings for heap statistics. (If
you did not expect to see this message, please report it.
2025-11-20T000404.246653Z ERROR The value
NIL
is not of type
NUMBER
when binding SB-KERNEL::X
2025-11-20T000404.246653Z WARNING PostgreSQL warning: there is no transaction in progress
Heap exhausted during allocation: 6553600 bytes available, 499842272 requested.
fatal error encountered in SBCL pid 1 tid 12:
Heap exhausted, game over.
m
Marvin
11/20/2025, 12:05 AM✅ thought for 124.9 seconds
Marvin
11/20/2025, 12:07 AMYou’ve hit two separate pgloader pain points:
- SBCL heap exhaustion inside the pgloader container
- Type/constraint mismatches that we’ve been working around
At this point, the fastest path is to get you unblocked by reducing what pgloader has to chew on, then decide how much history you want to bring over.
You have three viable paths:
Option 1 — Keep pgloader, but make it lighter
- Exclude the heaviest tables (log, events) for now, finish the core migration, then decide if you want to backfill a recent slice of logs/events.
- Bump container memory and keep batches small.
Use this load file to migrate everything except logs/events:
Copy code
LOAD DATABASE
FROM sqlite:///root/.prefect/prefect.db
INTO <postgresql://prefect:prefect@postgres:5432/prefect>
WITH data only,
truncate,
batch rows = 200,
prefetch rows = 200,
on error stop
EXCLUDING TABLE NAMES LIKE 'sqlite_%'
EXCLUDING TABLE NAMES LIKE 'alembic_version'
EXCLUDING TABLE NAMES LIKE 'log'
EXCLUDING TABLE NAMES LIKE 'events'
BEFORE LOAD DO $$ ALTER TABLE flow_run ALTER COLUMN total_run_time TYPE text; $$
BEFORE LOAD DO $$ ALTER TABLE task_run ALTER COLUMN total_run_time TYPE text; $$
AFTER LOAD DO $$
UPDATE flow_run
SET total_run_time =
CASE
WHEN total_run_time IS NULL OR total_run_time = '' THEN NULL
WHEN position(' ' in total_run_time) > 0 THEN substring(total_run_time from position(' ' in total_run_time) + 1)
ELSE total_run_time
END;
$$
AFTER LOAD DO $$
UPDATE task_run
SET total_run_time =
CASE
WHEN total_run_time IS NULL OR total_run_time = '' THEN NULL
WHEN position(' ' in total_run_time) > 0 THEN substring(total_run_time from position(' ' in total_run_time) + 1)
ELSE total_run_time
END;
$$
AFTER LOAD DO $$
ALTER TABLE flow_run
ALTER COLUMN total_run_time TYPE interval
USING NULLIF(total_run_time, '')::interval;
$$
AFTER LOAD DO $$
ALTER TABLE task_run
ALTER COLUMN total_run_time TYPE interval
USING NULLIF(total_run_time, '')::interval;
$$
; Copy code
docker run --rm --memory=6g --memory-swap=6g --network qfin_default \
-v brrr_prefect_data:/root/.prefect \
-v $(pwd)/pgloader.load:/load.load \
dimitri/pgloader:latest pgloader /load.load Copy code
LOAD DATABASE
FROM sqlite:///root/.prefect/prefect.db
INTO <postgresql://prefect:prefect@postgres:5432/prefect>
WITH data only,
truncate,
batch rows = 500,
prefetch rows = 500,
on error stop
INCLUDING ONLY TABLE NAMES MATCHING
'block_type', 'block_schema', 'block_schema_reference',
'block_document', 'block_document_reference',
'deployment', 'deployment_version', 'flow', 'flow_tag',
'work_pool', 'work_pool_queue', 'worker', 'concurrency_limit',
'variable', 'automation%', 'artifact%'
EXCLUDING TABLE NAMES LIKE 'sqlite_%'
EXCLUDING TABLE NAMES LIKE 'alembic_version'
;Marvin
11/20/2025, 12:07 AM- Import into Postgres using psql’s \copy into staging tables, then INSERT … ON CONFLICT DO NOTHING into the real tables
- We can share exact commands tailored to your schema version, but this is more steps; happy to write them if you want this route
My recommendation
- Given the repeated SBCL heap crashes, go with Option 1: exclude log + events for now, finish the rest, and revisit logs/events later if truly needed. This will get you running on Postgres quickly and will materially improve UI load and RAM usage.
- Also increase Docker Desktop/WSL memory allocation generally to help pgloader; the SBCL heap exhaustion is a known annoyance in resource-constrained environments.
Quick confirmations
- Your compose is now using a plain URL (no < >) and both the server and the CLI use the same image tag (3-latest). Good.
- The “brrr_prefect_data was created for project brrr” warning is harmless; set the volume to external to silence it:
Copy code
volumes:
prefect_data:
external: true
name: brrr_prefect_data
postgres_data:o
Olve Heitmann
11/20/2025, 12:18 AM@Marvin i tried "1" and it seems to have freezed. stuck for 10min + at WARNING Source column "public"."task_run"."labels" is casted to type "json" which is not the same as "jsonb", the type of current target database column "public"."task_run"."labels".
m
Marvin
11/20/2025, 12:18 AM✅ thought for 54.2 seconds
Marvin
11/20/2025, 12:19 AMTotally get it — pgloader can look “stuck” on those type warnings and it’s touchy on memory. Two tracks to unblock you quickly:
First, verify if it’s actually stuck or just busy
- Check copy/index progress in Postgres:
Copy code
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT now(), phase, relid::regclass, bytes_processed, tuples_processed FROM pg_stat_progress_copy;"
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c \"SELECT state, query, wait_event_type, wait_event FROM pg_stat_activity WHERE state <> 'idle';\" Copy code
LOAD DATABASE
FROM sqlite:///root/.prefect/prefect.db
INTO <postgresql://prefect:prefect@postgres:5432/prefect>
WITH data only,
truncate,
batch rows = 200,
prefetch rows = 200,
on error stop
INCLUDING ONLY TABLE NAMES MATCHING
'block_type', 'block_schema', 'block_schema_reference',
'block_document', 'block_document_reference',
'deployment', 'deployment_version', 'flow', 'flow_tag',
'work_pool', 'work_pool_queue', 'worker', 'concurrency_limit',
'variable', 'automation%', 'artifact%'
EXCLUDING TABLE NAMES LIKE 'sqlite_%'
EXCLUDING TABLE NAMES LIKE 'alembic_version'
; Copy code
docker run --rm --network qfin_default \
-v brrr_prefect_data:/root/.prefect \
-v $(pwd)/pgloader.load:/load.load \
--memory=6g --memory-swap=6g \
dimitri/pgloader:latest pgloader /load.load Copy code
docker run --rm -v brrr_prefect_data:/data alpine sh -lc '
apk add --no-cache sqlite;
sqlite3 -csv /data/prefect.db "
.headers on
.mode csv
.output /data/flow_run_7d.csv
SELECT * FROM flow_run WHERE start_time >= datetime(\"now\",\"-7 day\");
.output /data/task_run_7d.csv
SELECT * FROM task_run WHERE start_time >= datetime(\"now\",\"-7 day\");
.output /data/flow_run_state_7d.csv
SELECT s.* FROM flow_run_state s JOIN flow_run r ON r.id=s.flow_run_id WHERE r.start_time >= datetime(\"now\",\"-7 day\");
.output /data/task_run_state_7d.csv
SELECT s.* FROM task_run_state s JOIN task_run r ON r.id=s.task_run_id WHERE r.start_time >= datetime(\"now\",\"-7 day\");
"
' Copy code
docker exec -it qfin-postgres-1 bash -lc "
psql -U prefect -d prefect -c \"SET session_replication_role = replica;\"
psql -U prefect -d prefect -c \"\\copy flow_run FROM '/var/lib/postgresql/data/../flow_run_7d.csv' CSV HEADER\";
psql -U prefect -d prefect -c \"\\copy task_run FROM '/var/lib/postgresql/data/../task_run_7d.csv' CSV HEADER\";
psql -U prefect -d prefect -c \"\\copy flow_run_state FROM '/var/lib/postgresql/data/../flow_run_state_7d.csv' CSV HEADER\";
psql -U prefect -d prefect -c \"\\copy task_run_state FROM '/var/lib/postgresql/data/../task_run_state_7d.csv' CSV HEADER\";
psql -U prefect -d prefect -c \"SET session_replication_role = DEFAULT;\"
"Marvin
11/20/2025, 12:19 AMNote: We may need to adjust the file path inside the Postgres container; the clean way is to mount your
brrr_prefect_data/imports/imports/flow_run_7d.csvdocker-composelogevents--memory=6g --memory-swap=6g Copy code
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM deployment;"
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM flow;"
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM work_pool;"prefect_dataprefect.dbPREFECT_SERVER_EVENTS_RETENTION_PERIOD=P2DPREFECT_SERVER_EVENTS_MAXIMUM_WEBSOCKET_BACKFILL=PT300SPREFECT_SERVER_EVENTS_WEBSOCKET_BACKFILL_PAGE_SIZE=100PREFECT_SERVER_EVENTS_RELATED_RESOURCE_CACHE_TTL=PT300So
Olve Heitmann
11/20/2025, 12:21 AM@Marvin olveh@olve-machine:/mnt/c/qfin/qfin/prefect_setup/server_workpools_worker$ docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c "SELECT now(), phase, relid::regclass, bytes_processed, tuples_processed FROM pg_stat_progress_copy;"
docker exec -it qfin-postgres-1 psql -U prefect -d prefect -c \"SELECT state, query, wait_event_type, wait_event FROM pg_stat_activity WHERE state <> 'idle';\"
ERROR: column "phase" does not exist
LINE 1: SELECT now(), phase, relid::regclass, bytes_processed, tuple...
^
the input device is not a TTY
": command not found
m
Marvin
11/20/2025, 12:21 AM✅ thought for 34.8 seconds
Marvin
11/20/2025, 12:21 AMThanks for the updates — two quick fixes and a path to get you unblocked fast.
First: the monitoring queries
My bad on the columns. Use these:
- COPY progress (no “phase” column in this view):
Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT now(), relid::regclass, bytes_processed, bytes_total, tuples_processed, tuples_total, elapsed_time FROM pg_stat_progress_copy;" Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT pid, state, query, wait_event_type, wait_event FROM pg_stat_activity WHERE state <> 'idle';" Copy code
LOAD DATABASE
FROM sqlite:///root/.prefect/prefect.db
INTO <postgresql://prefect:prefect@postgres:5432/prefect>
WITH data only,
truncate,
batch rows = 200,
prefetch rows = 200,
on error stop
-- Only copy small metadata tables needed to keep working
INCLUDING ONLY TABLE NAMES MATCHING
'block_type', 'block_schema', 'block_schema_reference',
'block_document', 'block_document_reference',
'flow', 'deployment', 'deployment_version',
'work_pool', 'work_pool_queue', 'work_queue',
'worker', 'variable', 'concurrency_limit'
EXCLUDING TABLE NAMES LIKE 'sqlite_%'
EXCLUDING TABLE NAMES LIKE 'alembic_version'
; Copy code
docker run --rm --network qfin_default \
--memory=6g --memory-swap=6g \
-v brrr_prefect_data:/root/.prefect \
-v $(pwd)/pgloader.load:/load.load \
dimitri/pgloader:latest pgloader /load.load Copy code
volumes:
prefect_data:
external: true
name: brrr_prefect_data
postgres_data: Copy code
PREFECT_API_DATABASE_CONNECTION_URL: <postgresql+asyncpg://prefect:prefect@postgres:5432/prefect>
PREFECT_API_DATABASE_MIGRATE_ON_START: "false" # you ran upgrade manually already
PREFECT_SERVER_EVENTS_RETENTION_PERIOD: "P2D"
PREFECT_SERVER_EVENTS_MAXIMUM_WEBSOCKET_BACKFILL: "PT300S"
PREFECT_SERVER_EVENTS_WEBSOCKET_BACKFILL_PAGE_SIZE: "100"
PREFECT_SERVER_EVENTS_RELATED_RESOURCE_CACHE_TTL: "PT300S"o
Olve Heitmann
11/20/2025, 12:27 AM@Marvin there is something wrong with your new pgloader.load. Also do i need to do anything to start fresh, or can we just use hte pgloader?
ESRAP-PARSE-ERROR: At
-- Only copy small metadata tables needed to keep working
INCLUDING ONLY TABLE NAMES MATCHING
^ (Line 12, Column 28, Position 324)
In context INCLUDING-LIKE:
While parsing INCLUDING-LIKE. Expected:
the character Tab
or the character Newline
or the character Return
or the character Space
or the string "--"
or the string "/*"
or the string "like"
An unhandled error condition has been signalled: At
-- Only copy small metadata tables needed to keep working
INCLUDING ONLY TABLE NAMES MATCHING
^ (Line 12, Column 28, Position 324)
In context INCLUDING-LIKE:
While parsing INCLUDING-LIKE. Expected:
the character Tab
or the character Newline
or the character Return
or the character Space
or the string "--"
or the string "/*"
or the string "like"
What I am doing here?
At
-- Only copy small metadata tables needed to keep working
INCLUDING ONLY TABLE NAMES MATCHING
^ (Line 12, Column 28, Position 324)
In context INCLUDING-LIKE:
While parsing INCLUDING-LIKE. Expected:
the character Tab
or the character Newline
or the character Return
or the character Space
or the string "--"
or the string "/*"
or the string "like"
m
Marvin
11/20/2025, 12:27 AM✅ thought for 41.9 seconds
Marvin
11/20/2025, 12:28 AMGood catch — pgloader’s DSL wants LIKE, not MATCHING, and it’s picky about the exact syntax. Also: you do NOT need to fully reset Postgres; we can “start fresh” for just the tables we load by truncating them before the copy.
Try this minimal “metadata‑only” load file (corrected syntax)
- Copies core metadata (deployments, blocks, pools/queues, variables, etc.)
- Excludes runs/logs/events to avoid the heavy/fragile tables for now
- Truncates target tables first so you won’t get duplicates even if a prior attempt partially loaded
Create pgloader.load with:
Copy code
LOAD DATABASE
FROM sqlite:///root/.prefect/prefect.db
INTO <postgresql://prefect:prefect@postgres:5432/prefect>
WITH data only,
batch rows = 200,
prefetch rows = 200,
on error stop
INCLUDING ONLY TABLE NAMES LIKE 'block_type',
LIKE 'block_schema',
LIKE 'block_schema_reference',
LIKE 'block_document',
LIKE 'block_document_reference',
LIKE 'flow',
LIKE 'flow_tag',
LIKE 'deployment',
LIKE 'deployment_version',
LIKE 'work_pool',
LIKE 'work_pool_queue',
LIKE 'work_queue',
LIKE 'worker',
LIKE 'variable',
LIKE 'concurrency_limit'
EXCLUDING TABLE NAMES LIKE 'sqlite_%',
LIKE 'alembic_version'
-- Ensure a clean slate for the tables we are loading
BEFORE LOAD DO $$ TRUNCATE TABLE block_document_reference CASCADE; $$
BEFORE LOAD DO $$ TRUNCATE TABLE block_document CASCADE; $$
BEFORE LOAD DO $$ TRUNCATE TABLE block_schema_reference CASCADE; $$
BEFORE LOAD DO $$ TRUNCATE TABLE block_schema CASCADE; $$
BEFORE LOAD DO $$ TRUNCATE TABLE block_type CASCADE; $$
BEFORE LOAD DO $$ TRUNCATE TABLE flow_tag CASCADE; $$
BEFORE LOAD DO $$ TRUNCATE TABLE deployment_version CASCADE; $$
BEFORE LOAD DO $$ TRUNCATE TABLE deployment CASCADE; $$
BEFORE LOAD DO $$ TRUNCATE TABLE flow CASCADE; $$
BEFORE LOAD DO $$ TRUNCATE TABLE work_queue CASCADE; $$
BEFORE LOAD DO $$ TRUNCATE TABLE work_pool_queue CASCADE; $$
BEFORE LOAD DO $$ TRUNCATE TABLE work_pool CASCADE; $$
BEFORE LOAD DO $$ TRUNCATE TABLE worker CASCADE; $$
BEFORE LOAD DO $$ TRUNCATE TABLE variable CASCADE; $$
BEFORE LOAD DO $$ TRUNCATE TABLE concurrency_limit CASCADE; $$
; Copy code
docker run --rm --network qfin_default \
--memory=6g --memory-swap=6g \
-v brrr_prefect_data:/root/.prefect \
-v $(pwd)/pgloader.load:/load.load \
dimitri/pgloader:latest pgloader /load.loadalembic_version Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM deployment;"
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM flow;"
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM work_pool;"
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM block_document;"Marvin
11/20/2025, 12:28 AM- Server CLI (database commands): Self-hosted Server CLI
- Database settings: Settings reference — database
- Events settings (retention/backfill): Settings reference — server events
If pgloader still complains about a specific table, paste the exact error line — I’ll tweak the include list or truncation order as needed.
o
Olve Heitmann
11/20/2025, 12:30 AM@Marvin.... KABOOM!
ESRAP-PARSE-ERROR: At
INCLUDING ONLY TABLE NAMES LIKE 'block_type',
LIKE 'block_schema',
^ (Line 11, Column 31, Position 298)
In context ANOTHER-LIKE-EXPRESSION:
While parsing LIKE-EXPRESSION. Expected:
the character ' (APOSTROPHE)
While parsing IGNORE-WHITESPACE. Expected:
the character Tab
or the character Newline
or the character Return
or the character Space
or the string "--"
or the string "/*"
An unhandled error condition has been signalled: At
INCLUDING ONLY TABLE NAMES LIKE 'block_type',
LIKE 'block_schema',
^ (Line 11, Column 31, Position 298)
In context ANOTHER-LIKE-EXPRESSION:
While parsing LIKE-EXPRESSION. Expected:
the character ' (APOSTROPHE)
While parsing IGNORE-WHITESPACE. Expected:
the character Tab
or the character Newline
or the character Return
or the character Space
or the string "--"
or the string "/*"
What I am doing here?
At
INCLUDING ONLY TABLE NAMES LIKE 'block_type',
LIKE 'block_schema',
^ (Line 11, Column 31, Position 298)
In context ANOTHER-LIKE-EXPRESSION:
While parsing LIKE-EXPRESSION. Expected:
m
Marvin
11/20/2025, 12:30 AM✅ thought for 26.6 seconds
Marvin
11/20/2025, 12:30 AMYou’re right — my last pgloader file used the wrong syntax. pgloader doesn’t support a comma-separated list of LIKEs on one line; you either repeat the INCLUDING line or use a single MATCHING regex.
Let’s keep it simple and get you unblocked with a “metadata-only” copy that:
- includes deployments/blocks/pools/queues/variables/etc.
- excludes runs/logs/events
- truncates the target tables first (no need to “start fresh” DB-wide)
Use this pgloader.load (valid syntax)
Copy code
LOAD DATABASE
FROM sqlite:///root/.prefect/prefect.db
INTO <postgresql://prefect:prefect@postgres:5432/prefect>
WITH data only,
truncate,
batch rows = 200,
prefetch rows = 200,
on error stop
INCLUDING ONLY TABLE NAMES LIKE 'block_%'
INCLUDING ONLY TABLE NAMES LIKE 'deployment%'
INCLUDING ONLY TABLE NAMES LIKE 'work_pool%'
INCLUDING ONLY TABLE NAMES LIKE 'work_queue'
INCLUDING ONLY TABLE NAMES LIKE 'flow'
INCLUDING ONLY TABLE NAMES LIKE 'flow_tag'
INCLUDING ONLY TABLE NAMES LIKE 'variable'
INCLUDING ONLY TABLE NAMES LIKE 'concurrency_limit'
INCLUDING ONLY TABLE NAMES LIKE 'worker'
EXCLUDING TABLE NAMES LIKE 'sqlite_%'
EXCLUDING TABLE NAMES LIKE 'alembic_version'
; Copy code
docker run --rm --network qfin_default \
--memory=6g --memory-swap=6g \
-v brrr_prefect_data:/root/.prefect \
-v $(pwd)/pgloader.load:/load.load \
dimitri/pgloader:latest pgloader /load.loaddocker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT now(), relid::regclass, bytes_processed, bytes_total, tuples_processed, tuples_total, elapsed_time FROM pg_stat_progress_copy;"docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT pid, state, query, wait_event_type, wait_event FROM pg_stat_activity WHERE state <> 'idle';" Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM deployment;"
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM flow;"
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM work_pool;"
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM block_document;"o
Olve Heitmann
11/20/2025, 12:32 AMdocker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT now(), relid::regclass, bytes_processed, bytes_total, tuples_processed, tuples_total, elapsed_time FROM pg_stat_progress_copy;"
fails for tuples_total and elapsed_time missing, aslo have no idea what i am looking at in the active queries @Marvin
m
Marvin
11/20/2025, 12:33 AM✅ thought for 24.7 seconds
Marvin
11/20/2025, 12:33 AMGood catch — those columns vary by Postgres version.
- Check your version:
Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT version();" Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "
SELECT now(),
relid::regclass,
bytes_processed,
bytes_total,
tuples_processed
FROM pg_stat_progress_copy;" Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "
SELECT pid, state, wait_event_type, wait_event,
left(query, 120) AS query
FROM pg_stat_activity
WHERE datname = 'prefect' AND state <> 'idle'
ORDER BY query_start DESC
LIMIT 10;"COPY public.deployment FROM STDINWITH truncateBEFORE LOAD DO TRUNCATE Copy code
LOAD DATABASE
FROM sqlite:///root/.prefect/prefect.db
INTO <postgresql://prefect:prefect@postgres:5432/prefect>
WITH data only,
truncate,
batch rows = 200,
prefetch rows = 200,
on error stop
INCLUDING ONLY TABLE NAMES LIKE 'block_%'
INCLUDING ONLY TABLE NAMES LIKE 'deployment%'
INCLUDING ONLY TABLE NAMES LIKE 'work_pool%'
INCLUDING ONLY TABLE NAMES LIKE 'work_queue'
INCLUDING ONLY TABLE NAMES LIKE 'flow'
INCLUDING ONLY TABLE NAMES LIKE 'flow_tag'
INCLUDING ONLY TABLE NAMES LIKE 'variable'
INCLUDING ONLY TABLE NAMES LIKE 'concurrency_limit'
INCLUDING ONLY TABLE NAMES LIKE 'worker'
EXCLUDING TABLE NAMES LIKE 'sqlite_%'
EXCLUDING TABLE NAMES LIKE 'alembic_version'
; Copy code
docker run --rm --network qfin_default \
--memory=6g --memory-swap=6g \
-v brrr_prefect_data:/root/.prefect \
-v $(pwd)/pgloader.load:/load.load \
dimitri/pgloader:latest pgloader /load.load Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "
SELECT relname, n_live_tup
FROM pg_stat_user_tables
WHERE relname IN ('deployment','deployment_version','flow','flow_tag','block_document','block_document_reference','block_schema','block_schema_reference','block_type','work_pool','work_pool_queue','work_queue','worker','variable','concurrency_limit')
ORDER BY relname;"o
Olve Heitmann
11/20/2025, 12:36 AM@Marvin when i query pg_stat_progress_copy repeatedly the bytes_processed and tuples_processed numbers are not moving. When i run the active thing it looks like copy events and UPDATE flow_run is still present. To me that looks like hanging stuff from the old failed runs
m
Marvin
11/20/2025, 12:36 AM✅ thought for 34.5 seconds
Marvin
11/20/2025, 12:37 AMYou’re right: if pg_stat_progress_copy isn’t moving and you still see COPY events / UPDATE flow_run in pg_stat_activity, you’ve got stuck backends from prior attempts.
Here’s the quickest way to clear them and get a clean “metadata-only” import through:
1) Stop any running pgloader container
Copy code
docker ps | grep pgloader
docker kill <container_id> Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "
SELECT pid, usename, application_name, state, wait_event_type, wait_event,
left(query, 160) AS query
FROM pg_stat_activity
WHERE datname = 'prefect' AND state <> 'idle'
ORDER BY query_start;"
" Copy code
-- cancel
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT pg_cancel_backend(pid) FROM pg_stat_activity WHERE datname='prefect' AND state <> 'idle';"
-- terminate
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='prefect' AND state <> 'idle';" Copy code
docker restart qfin-postgres-1 Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "
TRUNCATE TABLE
events,
log,
flow_run_state,
task_run_state,
task_run,
flow_run
CASCADE;" Copy code
LOAD DATABASE
FROM sqlite:///root/.prefect/prefect.db
INTO <postgresql://prefect:prefect@postgres:5432/prefect>
WITH data only,
truncate,
batch rows = 200,
prefetch rows = 200,
on error stop
INCLUDING ONLY TABLE NAMES LIKE 'block_%'
INCLUDING ONLY TABLE NAMES LIKE 'deployment%'
INCLUDING ONLY TABLE NAMES LIKE 'work_pool%'
INCLUDING ONLY TABLE NAMES LIKE 'work_queue'
INCLUDING ONLY TABLE NAMES LIKE 'flow'
INCLUDING ONLY TABLE NAMES LIKE 'flow_tag'
INCLUDING ONLY TABLE NAMES LIKE 'variable'
INCLUDING ONLY TABLE NAMES LIKE 'concurrency_limit'
INCLUDING ONLY TABLE NAMES LIKE 'worker'
EXCLUDING TABLE NAMES LIKE 'sqlite_%'
EXCLUDING TABLE NAMES LIKE 'alembic_version'
; Copy code
docker run --rm --network qfin_default \
--memory=6g --memory-swap=6g \
-v brrr_prefect_data:/root/.prefect \
-v $(pwd)/pgloader.load:/load.load \
dimitri/pgloader:latest pgloader /load.load Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "
SELECT relname, n_live_tup
FROM pg_stat_user_tables
WHERE relname IN ('deployment','deployment_version','flow','flow_tag',
'block_type','block_schema','block_schema_reference',
'block_document','block_document_reference',
'work_pool','work_pool_queue','work_queue','worker',
'variable','concurrency_limit')
ORDER BY relname;"
"o
Olve Heitmann
11/20/2025, 12:43 AM@Marvin looking better. However deployment shows 0 - despite us having 30 flows. Is that an issue? I had quite a few deployments, prefer not to recreate those and the schedules. also need commands to restart server etc.
m
Marvin
11/20/2025, 12:43 AM✅ thought for 119.8 seconds
Marvin
11/20/2025, 12:45 AMThanks for the status update. Two things to tackle:
1) Why deployments show 0
- That means the deployment table didn’t get copied in your last pgloader pass, or it was truncated but not refilled due to the includes/excludes.
- We can import just the deployment-related tables quickly without touching runs/logs/events.
Do this to load your deployments (and schedules) from SQLite to Postgres:
- Create a focused pgloader file for deployment metadata only:
Copy code
LOAD DATABASE
FROM sqlite:///root/.prefect/prefect.db
INTO <postgresql://prefect:prefect@postgres:5432/prefect>
WITH data only,
truncate,
batch rows = 200,
prefetch rows = 200,
on error stop
INCLUDING ONLY TABLE NAMES LIKE 'flow'
INCLUDING ONLY TABLE NAMES LIKE 'deployment'
INCLUDING ONLY TABLE NAMES LIKE 'deployment_version'
INCLUDING ONLY TABLE NAMES LIKE 'deployment_schedule'
INCLUDING ONLY TABLE NAMES LIKE 'work_pool%'
INCLUDING ONLY TABLE NAMES LIKE 'work_queue'
EXCLUDING TABLE NAMES LIKE 'sqlite_%'
EXCLUDING TABLE NAMES LIKE 'alembic_version'
; Copy code
docker run --rm --network qfin_default \
--memory=6g --memory-swap=6g \
-v brrr_prefect_data:/root/.prefect \
-v $(pwd)/pgloader.load:/load.load \
dimitri/pgloader:latest pgloader /load.load Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM deployment;"
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM deployment_schedule;" Copy code
prefect deployments lsflowwork_pool/work_queue Copy code
INCLUDING ONLY TABLE NAMES LIKE 'block_%' Copy code
docker compose restart prefect-server Copy code
docker compose down
docker compose up -d prefect-server Copy code
docker restart qfin-postgres-1 Copy code
curl -s <http://localhost:4200/api/health> Copy code
prefect deployments ls
prefect deployments inspect "<flow-name>/<deployment-name>"
prefect deployments schedule list "<flow-name>/<deployment-name>" Copy code
export PREFECT_API_URL=<http://localhost:4200/api> Copy code
prefect deployments lsSELECT COUNT(*) FROM flow;SELECT COUNT(*) FROM work_pool;SELECT COUNT(*) FROM work_queue;SELECT COUNT(*) FROM block_document;SELECT COUNT(*) FROM deployment;SELECT COUNT(*) FROM deployment_schedule;o
Olve Heitmann
11/20/2025, 12:53 AM@Marvin so the flows and work pools etc are present, the deployments are still missing, but by select count and cli. naturally also the schedules. very annoying. i am deploying from source (using blocks / git) if that matters
m
Marvin
11/20/2025, 12:54 AM✅ thought for 58.8 seconds
Marvin
11/20/2025, 12:55 AMGot it — flows and pools are present, but deployments (and schedules) are still empty. Since you deploy from source (git blocks), let’s get those deployments back without recreating by hand.
First, confirm the old SQLite actually has deployments
Copy code
docker run --rm -v brrr_prefect_data:/data alpine sh -lc '
apk add --no-cache sqlite;
sqlite3 /data/prefect.db "SELECT COUNT(*) FROM deployment;"
sqlite3 /data/prefect.db "SELECT COUNT(*) FROM deployment_schedule;"
' Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "
TRUNCATE TABLE deployment_schedule, deployment_version, deployment CASCADE;" Copy code
LOAD DATABASE
FROM sqlite:///root/.prefect/prefect.db
INTO <postgresql://prefect:prefect@postgres:5432/prefect>
WITH data only,
truncate,
batch rows = 200,
prefetch rows = 200,
on error stop
INCLUDING ONLY TABLE NAMES LIKE 'flow'
INCLUDING ONLY TABLE NAMES LIKE 'deployment'
INCLUDING ONLY TABLE NAMES LIKE 'deployment_version'
INCLUDING ONLY TABLE NAMES LIKE 'deployment_schedule'
INCLUDING ONLY TABLE NAMES LIKE 'work_pool%'
INCLUDING ONLY TABLE NAMES LIKE 'work_queue'
INCLUDING ONLY TABLE NAMES LIKE 'block_%'
EXCLUDING TABLE NAMES LIKE 'sqlite_%'
EXCLUDING TABLE NAMES LIKE 'alembic_version'
-- Temporarily relax constraints so FK ordering does not block the load
BEFORE LOAD DO $$ SET session_replication_role = 'replica'; $$
AFTER LOAD DO $$ SET session_replication_role = 'origin'; $$
; Copy code
docker run --rm --network qfin_default \
--memory=6g --memory-swap=6g \
-v brrr_prefect_data:/root/.prefect \
-v $(pwd)/pgloader.load:/load.load \
dimitri/pgloader:latest pgloader /load.load Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM deployment;"
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "SELECT COUNT(*) FROM deployment_schedule;" Copy code
prefect deployments ls.prefect Copy code
prefect deploy Copy code
from prefect import flow
flow.from_source("<https://github.com/owner/repo>", entrypoint="path/to/file.py:my_flow").deploy(
name="my-deployment",
work_pool_name="production-pool",
schedules=[...],
parameters={...},
) Copy code
docker compose restart prefect-server Copy code
docker compose down
docker compose up -d prefect-server Copy code
docker restart qfin-postgres-1 Copy code
curl -s <http://localhost:4200/api/health>Marvin
11/20/2025, 12:55 AMexport PREFECT_API_URL=http://localhost:4200/api
prefect deployments ls
```
Your call on next step
- Try the targeted pgloader load above for deployments. If it still won’t copy, I recommend re-registering deployments from source — it’s quick and keeps your schedules intact as they’re defined in code/project. If you confirm which method you used originally (project vs code), I’ll give you exact commands or a small script to recreate all 30 in one shot.
o
Olve Heitmann
11/20/2025, 1:06 AMUGH
Olve Heitmann
11/20/2025, 2:01 PM@Marvin after migrating and deploying manually i get the following when trying to run deployment
Worker 'DockerWorker 185c9cea-2f4d-4fdb-9414-a7a35be5c1c0' submitting flow run '01cbe7ce-1463-4b21-b746-688e309933f9'
025808 PM
prefect.flow_runs.worker
Completed submission of flow run '01cbe7ce-1463-4b21-b746-688e309933f9'
025811 PM
prefect.flow_runs.worker
Opening process...
025821 PM
prefect.flow_runs.runner
> Running git_clone step...
025826 PM
prefect.flow_runs
Engine execution exited with unexpected exception
Traceback (most recent call last):
File "/opt/prefect/app/.venv/lib/python3.12/site-packages/prefect/flow_engine.py", line 1552, in run_flow
ret_val = run_flow_sync(**kwargs)
^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/prefect/app/.venv/lib/python3.12/site-packages/prefect/flow_engine.py", line 1394, in run_flow_sync
with engine.run_context():
^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/contextlib.py", line 137, in enter
return next(self.gen)
^^^^^^^^^^^^^^
File "/opt/prefect/app/.venv/lib/python3.12/site-packages/prefect/flow_engine.py", line 772, in run_context
with self.setup_run_context():
^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/contextlib.py", line 137, in enter
return next(self.gen)
^^^^^^^^^^^^^^
File "/opt/prefect/app/.venv/lib/python3.12/site-packages/prefect/flow_engine.py", line 603, in setup_run_context
self.flow_run = client.read_flow_run(self.flow_run.id)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/prefect/app/.venv/lib/python3.12/site-packages/prefect/client/orchestration/_flow_runs/client.py", line 218, in read_flow_run
response = self.request(
^^^^^^^^^^^^^
File "/opt/prefect/app/.venv/lib/python3.12/site-packages/prefect/client/orchestration/base.py", line 33, in request
return self._client.send(request)
^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/opt/prefect/app/.venv/lib/python3.12/site-packages/prefect/client/base.py", line 622, in send
response.raise_for_status()
File "/opt/prefect/app/.venv/lib/python3.12/site-packages/prefect/client/base.py", line 163, in raise_for_status
raise PrefectHTTPStatusError.from_httpx_error(exc) from exc.cause
prefect.exceptions.PrefectHTTPStatusError: Server error '500 Internal Server Error' for url 'http://host.docker.internal:4200/api/flow_runs/01cbe7ce-1463-4b21-b746-688e309933f9'
Response: {'exception_message': 'Internal Server Error'}
For more information check: https://developer.mozilla.org/en-US/docs/Web/HTTP/Status/500
025832 PM
prefect.flow_runs
Process for flow run 'tidy-bat' exited with status code: 1
m
Marvin
11/20/2025, 2:01 PM✅ thought for 40.2 seconds
Marvin
11/20/2025, 2:01 PMThanks — that 500 is coming from the server. The client stack shows a simple GET of the flow run by id, so we need the server traceback to pinpoint it.
Quick checks to isolate the cause
1) Get the server traceback around the time of the failure
- Replace the container name with yours if different:
Copy code
docker logs --since=15m qfin-prefect-server-1 Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "
SELECT id, state_type, start_time, end_time, total_run_time
FROM flow_run
WHERE id = '01cbe7ce-1463-4b21-b746-688e309933f9';"total_run_time Copy code
curl -s <http://localhost:4200/api/version>3-latestprefecthq/prefect:3.4.17 Copy code
curl -i <http://localhost:4200/api/flow_runs/01cbe7ce-1463-4b21-b746-688e309933f9> Copy code
docker compose restart prefect-server Copy code
docker compose down
docker compose up -d prefect-server Copy code
docker restart qfin-postgres-1 Copy code
curl -s <http://localhost:4200/api/health> Copy code
export PREFECT_API_URL=<http://localhost:4200/api>
prefect deployments lso
Olve Heitmann
11/20/2025, 2:09 PM@Marvin tracebacks are too long to paste in total, but sdeems to revolve around " File "/usr/local/lib/python3.12/site-packages/prefect/server/utilities/server.py", line 50, in handle_response_scoped_depends
response = await default_handler(request)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/fastapi/routing.py", line 302, in app
raw_response = await run_endpoint_function(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/fastapi/routing.py", line 213, in run_endpoint_function
return await dependant.call(**values)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/prefect/server/api/flow_runs.py", line 334, in read_flow_run
return schemas.responses.FlowRunResponse.model_validate(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/prefect/server/schemas/responses.py", line 320, in model_validate
response = super().model_validate(obj)
^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/usr/local/lib/python3.12/site-packages/pydantic/main.py", line 705, in model_validate
return cls.__pydantic_validator__.validate_python(
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
pydantic_core._pydantic_core.ValidationError: 1 validation error for FlowRunResponse
estimated_run_time
Error extracting attribute: TypeError: can only concatenate str (not "Interval") to str [type=get_attribute_error, input_value=FlowRun(id=01cbe7ce-1463-4b21-b746-688e309933f9), input_type=FlowRun]
For further information visit https://errors.pydantic.dev/"
cant find the request n it self
for the postgres: state_type is running, total_run_time 000000
http://localhost:4200/api/version lists 0.8.4 which seems very weird?
http://localhost:4200/api/flow_runs/01cbe7ce-1463-4b21-b746-688e309933f9 gives Internal Server Error as exception message
m
Marvin
11/20/2025, 2:09 PM✅ thought for 47.3 seconds
Marvin
11/20/2025, 2:10 PMThanks — the traceback points to a server-side serialization error when building FlowRunResponse: Pydantic is encountering a non-Python timedelta for a duration field and choking.
Given your prior pgloader attempts, the most likely cause is a type mismatch on interval fields in the flow_run/task_run tables. Let’s verify and fix that, then align versions and retry.
1) Verify interval column types in Postgres
Run these and share results:
Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'flow_run' AND column_name IN ('total_run_time');"
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'task_run' AND column_name IN ('total_run_time');"intervaltext Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "
ALTER TABLE flow_run
ALTER COLUMN total_run_time TYPE interval
USING NULLIF(total_run_time::text, '')::interval;"
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "
ALTER TABLE task_run
ALTER COLUMN total_run_time TYPE interval
USING NULLIF(total_run_time::text, '')::interval;" Copy code
image: prefecthq/prefect:3.4.17 Copy code
docker compose up -d --force-recreate --no-deps prefect-server
docker compose up -d --force-recreate --no-deps prefect-prod-worker prefect-dev-worker Copy code
docker exec -i qfin-prefect-server-1 prefect version Copy code
docker exec -i <worker-container-name> prefect version Copy code
docker exec -i qfin-prefect-server-1 printenv | grep PREFECT_API_DATABASE_CONNECTION_URL Copy code
<postgresql+asyncpg://prefect:prefect@postgres:5432/prefect> Copy code
docker compose restart prefect-server
curl -i <http://localhost:4200/api/flow_runs/01cbe7ce-1463-4b21-b746-688e309933f9> Copy code
docker exec -i qfin-postgres-1 psql -U prefect -d prefect -c "
SELECT id, state_type, start_time, end_time, total_run_time
FROM flow_run
WHERE id = '01cbe7ce-1463-4b21-b746-688e309933f9';"total_run_time Copy code
export PREFECT_API_URL=<http://localhost:4200/api>
prefect deployments ls Copy code
prefect deployments run "<flow-name>/<deployment-name>" Copy code
docker compose restart prefect-server Copy code
docker compose down
docker compose up -d prefect-server Copy code
docker restart qfin-postgres-1 Copy code
curl -s <http://localhost:4200/api/health>prefect versionMarvin
11/20/2025, 2:10 PMLinks for reference:
- Server CLI (database): Self-hosted Server CLI
- DB settings (asyncpg URL, timeouts): Settings reference — database
- Events settings (keep your shorter retention/backfill): Settings reference — server events
79 Views