> ## Documentation Index
> Fetch the complete documentation index at: https://ctrlrun.dev/llms.txt
> Use this file to discover all available pages before exploring further.

# Run on Postgres

> Move the store from SQLite to Postgres when more than one host must share it: pip install ctrlrun[postgres], a connection URL, a schema.

Use Postgres when workers on more than one host must share one store, because `BEGIN
IMMEDIATE` is a write lock on a local file and does not reach across hosts. The store is the
same protocol, graded by the same suite as SQLite; what changes is a URL.

**Prerequisites:** a Postgres 14 or later server, a database, a role for CTRLRun, and
`pip install "ctrlrun[postgres]"`.

<Steps>
  <Step title="Install and connect">
    ```bash theme={null}
    pip install "ctrlrun[postgres]"
    ```

    ```python theme={null}
    from ctrlrun import Control, Policy
    from ctrlrun.postgres import PostgresStateStore

    store = PostgresStateStore("postgresql://ctrlrun@db.internal:5432/ctrlrun", schema="ctrlrun")
    control = Control(Policy.from_file("ctrlrun.yaml"), store)
    ```

    The URL goes to `psycopg.connect` unchanged, so `?sslmode=require`, `?connect_timeout=5`, a
    `service=` name and the `PG*` environment variables all work. Put the password in
    `~/.pgpass` or `PGPASSWORD`, not in the URL. On the command line the schema travels as
    CTRLRun's own query parameter:

    ```bash theme={null}
    export CTRLRUN_STORE_URL='postgresql://db.internal/ctrlrun?ctrlrun_schema=ctrlrun'
    ctrlrun effects --state ambiguous
    ```
  </Step>

  <Step title="Create the schema and grant the role">
    The store does not create the schema, so a typo cannot become a second, empty,
    authoritative-looking store. Create it once, and give the role what it needs:

    ```sql theme={null}
    CREATE SCHEMA ctrlrun AUTHORIZATION ctrlrun;
    GRANT USAGE, CREATE ON SCHEMA ctrlrun TO ctrlrun;
    ```

    `CREATE` on the schema is what migrations need, and it is not optional: the store migrates
    at open, so **a role without it cannot run the CLI at all** — `ctrlrun receipts     --verify-chain` is refused before a receipt is read, naming the missing privilege. A
    read-only role with `USAGE` and `SELECT` is for your own queries against the tables. The
    database's encoding must be UTF-8; a lossy encoding is refused at open, because a receipt
    that cannot store the argument it records is not a receipt.
  </Step>

  <Step title="Let the first writer migrate">
    The schema is versioned. A writer opens the store, reads `schema_version`, and applies any
    migration forward, once, under a lock, so concurrent starts do not race. There is no flag
    that opens a database un-migrated. A binary older than the schema refuses immediately with
    `SchemaMismatch`, before reading any other table, so upgrade every reader before any writer.

    A read command (`receipts`, `effects`, `inspect`, `stats`, `approve`, `deny`, `resolve`)
    migrates nothing: it opens only a database already at head, and names the missing migration
    otherwise.
  </Step>

  <Step title="Understand the one new ambiguity">
    A connection lost during `COMMIT` is the case worth naming: Postgres very often did commit,
    and the client cannot know. The store treats it as unknown and re-reads the row to find
    out which, and only if the re-read itself fails does it refuse to proceed. `FAILED` is
    reserved for an abort the server stated. Nothing here changes what `AMBIGUOUS` means for
    the remote your executor called; that path is unchanged.
  </Step>
</Steps>

## Verify against it

`ctrlrun verify --store-url postgresql://…` runs the guarantee catalogue against a scratch
schema it creates and drops, never against yours.

The store conformance suite grades your database with the same cases SQLite passes. It is not a
command — it is a function, because it takes a live backend:

```python theme={null}
from ctrlrun.conformance.store import run
from ctrlrun.conformance.store.backends import PostgresBackend

report = run(PostgresBackend("postgresql://…"))
print(report.to_text())
raise SystemExit(0 if report.ok else 1)
```

## Throughput

Every reservation queues on one row per effect key and receipts append behind one chain head,
so the store serializes writes the way a ledger does. That is the design: a hundred agents
refunding a hundred different payments proceed in parallel; a hundred agents refunding the same
payment are one winner and ninety-nine refusals.

## If it didn't work

* `schema "ctrlrun" does not exist`: create it; the store will not.
* `SchemaMismatch: the database is at version N and this binary understands M`: upgrade the
  binary, or downgrade nothing; there is no backward migration.
* `encoding ... is not UTF8`: create the database with `ENCODING 'UTF8'`.
* `permission denied for schema`: the role lacks `CREATE` on the first open.

## Next

* [Effect keys](/concepts/effect-keys): what the unique index protects.
* [The operator's Postgres page](/postgres): grants, failover and what the store does not do for you, in full.
* [Get started](/get-started/quickstart) · [Why](/why).


## Related topics

- [SQLite or Postgres](/production/postgres.md)
- [Move from SQLite to Postgres](/cookbook/sqlite-to-postgres.md)
- [Choosing between them](/get-started/choosing.md)
- [Running on Postgres](/postgres.md)
- [CTRLRun](/index.md)