Postgres is the default `store.sql` image in `dev` and `prod`. `oke docker` matches
any image whose reference contains `postgres` or `pgvector` (after more specific
recipes), then derives env, healthcheck, and connection URL.

<Callout title="The one rule">
  The driver id stays `postgres` no matter which Postgres-wire image you pin — vendor choice lives
  in `images["store.sql"]`, not in `drivers.store.sql`.
</Callout>

## Quick start

<Steps>

<Step>
### Pin the image

```typescript title="oke.config.ts"
images: {
  "store.sql": "postgres:18-alpine", // any Postgres / pgvector image
},
```

</Step>

<Step>
### Bring the stack up

```bash
oke dev
```

`oke docker` writes `POSTGRES_USER` / `POSTGRES_PASSWORD` / `POSTGRES_DB` into
`.env.local` as `${OKE_STORE_SQL_USER}` / `${OKE_STORE_SQL_PASSWORD}` /
`${OKE_STORE_SQL_DB}` refs — never literal secrets in compose YAML.

</Step>

<Step>
### Connect

```bash
# App reads this (also accepts OKE_STORE_SQL_URL)
echo "$DATABASE_URL"
# postgres://oke:…@127.0.0.1:5432/oke
```

When PgDog is pinned, `DATABASE_URL` points at `:6432` instead — see
[PgDog](/docs/recipes/pgdog).

</Step>

</Steps>

## Required env

| Variable                 | Who sets it            | Meaning                              |
| ------------------------ | ---------------------- | ------------------------------------ |
| `OKE_STORE_SQL_USER`     | `oke docker` → compose | Injected as `POSTGRES_USER`          |
| `OKE_STORE_SQL_PASSWORD` | `oke docker` → compose | Injected as `POSTGRES_PASSWORD`      |
| `OKE_STORE_SQL_DB`       | `oke docker` → compose | Injected as `POSTGRES_DB`            |
| `DATABASE_URL`           | stack env for the app  | What Bun.SQL / Drizzle actually open |
| `PGDATA`                 | stack default          | `/var/lib/postgresql/data/pgdata`    |
| `POSTGRES_INITDB_ARGS`   | stack default          | `--data-checksums`                   |

## Data and backup

| Path                                          | What lives there                                     |
| --------------------------------------------- | ---------------------------------------------------- |
| `$PGDATA` (`/var/lib/postgresql/data/pgdata`) | Cluster data directory                               |
| Image `VOLUME` `/var/lib/postgresql/data`     | Official image mount — Docker attaches a volume here |

The recipe does **not** declare a named compose volume. Persistence rides the image's
`VOLUME` (anonymous unless you add a named mount in `compose.override.yml`).

**Backup means:** `pg_dump` / `pg_dumpall`, or a Docker volume backup of
`/var/lib/postgresql/data`. Losing that volume loses the cluster.

## Production note

For multi-replica apps, keep SQL on a shared Postgres — Clock CronStore and durable
journal need one backend. See [Clock](/docs/elements/clock#what-the-runtime-guarantees).

Scale out with [PgDog](/docs/recipes/pgdog) so `N × Bun.SQL pool` does not exhaust
`max_connections`. Do not also stack a managed provider's pooler on the same URL.

## What the recipe configures

| Field          | Value                                                     |
| -------------- | --------------------------------------------------------- |
| Container port | `5432`                                                    |
| Healthcheck    | `pg_isready -U $POSTGRES_USER`, every 5s, 10 retries      |
| Connection URL | `postgres://user:pass@host:5432/db`                       |
| Preload        | `postgres -c shared_preload_libraries=pg_stat_statements` |

## Query performance

<Callout title="Preload, then create">
  `pg_stat_statements` must load at postmaster start. `CREATE EXTENSION` alone is not enough.
  Recreate `store-sql` after a recipe change — the data volume can stay.
</Callout>

| Step    | What you do                                                       |
| ------- | ----------------------------------------------------------------- |
| Preload | Recipe already sets `shared_preload_libraries=pg_stat_statements` |
| Create  | `CREATE EXTENSION IF NOT EXISTS pg_stat_statements`               |
| Console | Store → SQL band → **Performance**                                |

Default `postgres:18-alpine` does **not** ship `hypopg` / `index_advisor`. Pin the
opt-in image when you want Suggest indexes:

```typescript title="oke.config.ts"
images: {
  "store.sql": "oke-postgres-advisor:18-alpine",
},
```

`oke docker` writes `Dockerfile.postgres-advisor` and a compose `build:`. Driver id
stays `postgres`. Suggest copies `CREATE INDEX` DDL — it does not create indexes.

## Troubleshooting

<Accordions>
<Accordion title="oke boot: postgres driver needs DATABASE_URL">

The `postgres` driver fails boot when neither `DATABASE_URL` nor `OKE_STORE_SQL_URL` is
set. Re-run `oke dev` so the stack writes `.env.local`, or export
`DATABASE_URL` yourself when pointing at a managed host.

</Accordion>
<Accordion title="PgStatStatementsNotPreloaded">

The Console Performance view needs the library preloaded **and** the extension
created. Recreate `store-sql` so the new `command` applies, then run
`CREATE EXTENSION IF NOT EXISTS pg_stat_statements`.

Existing clusters that started before this recipe change keep the old
postmaster flags until recreate.

</Accordion>
<Accordion title="pg_isready fails / store-sql unhealthy">

Wrong `POSTGRES_USER` or the container is still initializing. Check
`docker compose … logs store-sql` for `database system is ready to accept connections`.

Credential refs must resolve in `.env.local` — empty `${OKE_STORE_SQL_PASSWORD}` leaves
Postgres refusing auth.

</Accordion>
</Accordions>

## Learn more

- [PgDog](/docs/recipes/pgdog) — transaction pooling in front of this recipe
- [Store · SQL](/docs/elements/store#sql) — schema push / generate / migrate
- [Environment variables](/docs/reference/environment-variables) — `DATABASE_URL` precedence
- [Neon](/docs/providers/neon) · [Supabase](/docs/providers/supabase) — managed alternatives

## Next

<Cards>
  <Card title="PgDog" description="Add pooling in front of Postgres." href="/docs/recipes/pgdog" />
  <Card title="Neon" description="A managed alternative." href="/docs/providers/neon" />
  <Card title="Redis" description="The default store.kv image." href="/docs/recipes/redis" />
</Cards>