# PostgreSQL deployment modes

Markdown version of <https://sam-dumont.github.io/immich-memories/docs/run/reference/database/>.

SQLite on local disk is the default. Use PostgreSQL when your storage topology needs it or you
already operate an instance. The UI still stays at one replica.
For everyday backups, start with [Database and backups](https://sam-dumont.github.io/immich-memories/docs/run/database.md).

## A separate PostgreSQL service

Download `docker-compose.postgres.yml` from the same release as the base file. It adds a
`postgres:16` service pinned by digest, its persistent volume and the app database URL.
The app waits for PostgreSQL's health check before starting. For a new installation, set in `.env`:

```bash
POSTGRES_PASSWORD=a-long-random-password
COMPOSE_FILE=docker-compose.yml:docker-compose.postgres.yml
```

Then run `docker compose up -d`. The password is used in the database URL: use a URL-safe random
value, or percent-encode reserved characters when configuring your own URL. Keep both files in
`COMPOSE_FILE` for later updates. For an existing SQLite installation, follow the
[copy-first procedure](https://sam-dumont.github.io/immich-memories/docs/run/database.md#move-to-postgresql) before enabling the overlay's app URL.
On Kubernetes,
`deploy/kubernetes/overlays/postgres` does the equivalent: it is not referenced by
`base/kustomization.yaml`, so applying `base` alone keeps SQLite, and applying the overlay points
the Deployment at a `database-secret.yaml` you fill in yourself; it does not run PostgreSQL for
you. Terraform: set `database_url` (empty by default, which keeps SQLite) and, only if you share
the instance, `database_schema` (default `immich_memories`).

```ini
IMMICH_MEMORIES_DATABASE_URL=postgresql+psycopg://immich_memories:change-me@postgres:5432/immich_memories
```

Plain PostgreSQL (the overlay and the tests use 16), with no extensions: no `vchord`, no
pgvector. Every vector comparison in this app is exact numpy over a small candidate set (one film,
one story, a 14-day window), so there is nothing to install or keep in step with an upstream image.

## A separate database on your existing PostgreSQL instance

The same URL, pointed at a database on an instance you already run for something else (including
Immich's own PostgreSQL container, if you want to reuse it without touching Immich's schema):

```ini
IMMICH_MEMORIES_DATABASE_URL=postgresql+psycopg://immich_memories:change-me@database:5432/immich_memories
```

`database` is the service name in Immich's own compose file, so the host resolves when this app
runs in that file ([next to your Immich stack](https://sam-dumont.github.io/immich-memories/docs/run/docker.md#next-to-your-immich-stack)). From
anywhere else, use the address that reaches your PostgreSQL.

Create the database and a role scoped to it first. Execute each SQL statement separately;
`CREATE DATABASE` cannot run inside a transaction:

```sql
CREATE ROLE immich_memories WITH LOGIN PASSWORD 'change-me';
CREATE DATABASE immich_memories OWNER immich_memories;
```

Nothing here reads or writes Immich's own database: it is a second logical database on an
instance you already run, instead of a second container.

## A dedicated schema in Immich's own database

Point the URL at Immich's database, and set the schema so the store's tables land somewhere that
is not `public`:

```ini
IMMICH_MEMORIES_DATABASE_URL=postgresql+psycopg://immich_memories:change-me@database:5432/immich
IMMICH_MEMORIES_DATABASE_SCHEMA=immich_memories
```

`IMMICH_MEMORIES_DATABASE_SCHEMA` is not in the shipped compose file: add it to the service's
`environment:` block next to the URL.

Create the role and schema while connected to Immich's database (`immich`), with no grants on
anything Immich owns:

```sql
CREATE ROLE immich_memories WITH LOGIN PASSWORD 'change-me';
CREATE SCHEMA immich_memories AUTHORIZATION immich_memories;
GRANT CREATE ON DATABASE immich TO immich_memories;
```

Execute those statements separately. The database `CREATE` privilege lets restore recreate the
store schema after dropping it; it also permits creating other schemas in this shared database.
Restore checks this privilege before changing the existing store and refuses if it is missing.
If that grant is too broad for you, use [a separate database](#3-a-separate-database-on-your-existing-postgresql-instance) instead. There is no `GRANT` on `public`, no
grant on any Immich table, no cross-schema foreign key, no search-path change on Immich's own
role. Alembic's version table (`alembic_version`) lives inside `immich_memories`, and autogenerate
is scoped to this app's own `MetaData`, so a migration here never touches Immich's tables and an
Immich migration never touches this schema. No extensions are needed for either side, so there is
nothing to reconcile between the two.

**The one thing this mode does not protect against:** a whole-database restore of Immich (`pg_dump
-d immich` or a snapshot restore) restores every schema in that database, this one included. If you
restore Immich from a backup taken before a memory run, that run's decisions and banked answers go
back with it. `immich-memories store backup` dumps only this schema, independent of Immich's own
backup schedule, if you want the two to have separate retention.
