Great question. In Prefect 3.x, the recommended way to run Prefect Server on PostgreSQL is to set the database connection URL via environment variables. The key setting is: - PREFECT_SERVER_DATABASE_CONNECTION_URL (preferred) - Format:

<postgresql+asyncpg://user:password@host:5432/database>

- Alias: PREFECT_API_DATABASE_CONNECTION_URL (supported as well) You can also set it via a profile with the dotted key

server.database.connection_url

. Common ways to set it: - CLI/profile (when starting the server on the host)

prefect config set server.database.connection_url="<postgresql+asyncpg://user:password@db-host:5432/prefect>"
  # then start the server
  prefect server start

- Docker Compose / Podman Compose

services:
    prefect-server:
      image: prefecthq/prefect:3-latest
      environment:
        - PREFECT_SERVER_DATABASE_CONNECTION_URL=<postgresql+asyncpg://prefect:prefect@postgres:5432/prefect>
      depends_on:
        - postgres

    postgres:
      image: postgres:15
      environment:
        - POSTGRES_DB=prefect
        - POSTGRES_USER=prefect
        - POSTGRES_PASSWORD=prefect
      ports:
        - "5432:5432"

Note: With Podman, you can use the same compose file (podman-compose up). Push/pull your images from JFrog as usual. - Kubernetes/Helm The Prefect Helm chart supports injecting env vars into the server container. Set

PREFECT_SERVER_DATABASE_CONNECTION_URL

in the server pod’s environment. Example values.yaml (the exact key may vary by chart version; many charts use an extraEnv-style field):

server:
    extraEnv:
      - name: PREFECT_SERVER_DATABASE_CONNECTION_URL
        value: <postgresql+asyncpg://prefect:prefect@postgres.default.svc.cluster.local:5432/prefect>

If you’re using a different chart layout, the principle is the same: add that env var to the server container. I’m happy to tailor this to your exact chart/version. Operational tips - Ensure your Postgres user can create/own objects in the target database. Prefect runs migrations on startup (migrate_on_start is True by default). - If you have large datasets/migrations, you can raise timeouts:

PREFECT_SERVER_DATABASE_TIMEOUT=600
  PREFECT_SERVER_DATABASE_CONNECTION_TIMEOUT=10

- TLS: Add query params (e.g.,

?sslmode=require

) or set SQLAlchemy connect args via the appropriate settings if your Postgres requires TLS. - Some deployments use the pg_trgm extension for text search. If your DB user cannot create extensions, have a DBA enable

pg_trgm

in the Prefect database. Docs and references - Settings reference (env vars and dotted keys): Prefect 3 settings reference - Self-hosted and Docker Compose guidance: Self-hosted with Docker Compose - Helm overview: Helm deployment - Server concepts: Prefect Server concepts Quick question to tailor this: how are you deploying the server—Docker/Podman Compose on RHEL, Helm on Kubernetes, or running it as a service on the host? If you share that, I’ll provide an exact, drop-in snippet for your setup.

Short answer: only the Server needs the Postgres DB config. Workers do not connect to the database; they only talk to the Server’s API. What to set where: - Prefect Server (API): - Set the database URL

PREFECT_SERVER_DATABASE_CONNECTION_URL=<postgresql+asyncpg://user:pass@host:5432/prefect>

- Optional timeouts, etc., if needed

PREFECT_SERVER_DATABASE_TIMEOUT=600
    PREFECT_SERVER_DATABASE_CONNECTION_TIMEOUT=10

- Workers and CLI clients: - Point them at your Server API

PREFECT_API_URL=<http://your-prefect-server-host:4200/api>

- If you enabled server auth, also set

PREFECT_API_KEY=<the same auth string configured on the server>

Notes: - You can set the DB URL via env var or via profile on the machine/pod where the Server runs (e.g.,

prefect config set server.database.connection_url=...

). Don’t set this on workers; it’s unused by them. - If you’re using Helm/Kubernetes, inject only the DB env vars into the server pod. Workers only need

PREFECT_API_URL

and (if enabled)

PREFECT_API_KEY

. References: - Settings reference (includes server.database.* keys): Prefect 3 settings reference - Self-hosted/Server overview: Prefect Server concepts - Security/auth for self-hosted Server: Security settings

Short answer: SQLite has a single-writer design, so “database is locked” shows up under concurrent load. You can mitigate it, but the durable fix is switching to Postgres. If you must stay on SQLite, do the following: - Run only one Prefect Server instance and one process - Do not scale replicas or run multiple API workers with SQLite. - Enable WAL mode (better read/write concurrency)

sqlite3 /path/to/prefect.db "PRAGMA journal_mode=WAL; PRAGMA synchronous=NORMAL;"

Ensure the server can create

.wal

and

.shm

files in that directory. - Increase timeouts and reduce DB connection concurrency

# Let database operations wait longer before erroring
  PREFECT_SERVER_DATABASE_TIMEOUT=60
  PREFECT_SERVER_DATABASE_CONNECTION_TIMEOUT=10

  # Keep one pooled connection to reduce write contention
  PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_SIZE=1
  PREFECT_SERVER_DATABASE_SQLALCHEMY_MAX_OVERFLOW=0

  # Be patient when borrowing from the pool and recycle connections more often
  PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_TIMEOUT=60
  PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_RECYCLE=600

- Use a conservative SQLite URL (optional URI flags can help in some environments)

PREFECT_SERVER_DATABASE_CONNECTION_URL=sqlite+aiosqlite:///path/to/prefect.db?cache=shared&uri=true

If you see odd behavior with URI flags, drop them and use

sqlite+aiosqlite:///path/to/prefect.db

. - Use fast local storage for the DB file - Avoid network filesystems (e.g., NFS) and ensure the directory is writable and persistent. When to move to Postgres: - If you’re seeing frequent locks under normal use or plan to scale beyond a single API instance, switch to Postgres. It removes these SQLite limitations and is what we recommend for production loads. References - Settings and env vars: Prefect 3 settings reference - Database configurations: Server database configurations - Server concepts/self-hosted: Server concepts and Self-hosted overview If you share how you’re starting the server (systemd on RHEL, Podman/Compose, etc.), I can give you exact snippets for those environments.

@Marvin Do I set these as environment variables? Or with prefect config set: PREFECT_SERVER_DATABASE_TIMEOUT=60 PREFECT_SERVER_DATABASE_CONNECTION_TIMEOUT=10 # Keep one pooled connection to reduce write contention PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_SIZE=1 PREFECT_SERVER_DATABASE_SQLALCHEMY_MAX_OVERFLOW=0 # Be patient when borrowing from the pool and recycle connections more often PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_TIMEOUT=60 PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_RECYCLE=600

Both are supported. These are standard Prefect settings, so you can either: - Set them as environment variables (best for containers/Helm/systemd)

export PREFECT_SERVER_DATABASE_TIMEOUT=60
  export PREFECT_SERVER_DATABASE_CONNECTION_TIMEOUT=10
  export PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_SIZE=1
  export PREFECT_SERVER_DATABASE_SQLALCHEMY_MAX_OVERFLOW=0
  export PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_TIMEOUT=60
  export PREFECT_SERVER_DATABASE_SQLALCHEMY_POOL_RECYCLE=600

Env vars take precedence at runtime. - Or persist them in a Prefect profile on the host running the Server

prefect config set \
    server.database.timeout=60 \
    server.database.connection_timeout=10 \
    server.database.sqlalchemy.pool_size=1 \
    server.database.sqlalchemy.max_overflow=0 \
    server.database.sqlalchemy.pool_timeout=60 \
    server.database.sqlalchemy.pool_recycle=600

Notes: - Apply these only where Prefect Server runs; workers don’t use these DB settings. - In Helm/Kubernetes, add them to the Server pod env (e.g., extraEnv). - With systemd on RHEL, add them as Environment= lines in the unit or an EnvironmentFile. Refs: - Prefect 3 settings reference - Server database configurations