Postgres

Opt-in PostgreSQL engine, compose profiles, cutover lock, snapshot abort, and rollback that discards PG-era writes.

SQLite remains the default for unconfigured installations. A nonempty DURINDOOR_PG_URL selects PostgreSQL directly; DURINDOOR_DATABASE_ENGINE=postgres additionally keeps startup PostgreSQL-only when the URL is missing or empty. This explicit mode never reads SQLite settings or falls back to SQLite. Packaged startup removes exact empty process-environment values before loading the managed file. Installations without either selector retain the dashboard-managed cutover workflow below.

The dashboard page is Settings → Database (/dashboard/settings/database). Cutover, test, and rollback routes require a dashboard JWT or CLI token plus the current dashboard password (x-9r-password).

Managed startup configuration

Settings → Database → Startup configuration shows password-free connection fields and the source of each database key. Save probes PostgreSQL before atomically writing DATA_DIR/durindoor-database.env (file mode 0600, directory mode 0700). The file overrides process environment values for DURINDOOR_DATABASE_ENGINE, DURINDOOR_PG_URL, and DURINDOOR_PG_SSLMODE only. Other environment keys are ignored. An explicit managed SQLite selection clears inherited PostgreSQL selectors at startup. Packaged custom-server.js removes an exact empty process DURINDOOR_PG_URL before app imports, so that value alone behaves as unset and does not force PostgreSQL. DURINDOOR_DATABASE_ENGINE=postgres still selects fail-closed PostgreSQL with a missing or empty URL. Managed-file values are applied after normalization: a managed PostgreSQL URL's presence, even if empty, selects PostgreSQL unless an explicit managed SQLite selection clears it. Direct driver imports that bypass packaged normalization also retain the URL-presence check, including empty process URLs. A stored cutover-target URL only prefills connection fields; it does not select PostgreSQL startup.

Passwords are write-only: leaving the field untouched preserves the password from the managed URL, or the process URL when no managed URL exists. Test probes without saving. Remove override deletes the managed file; the next startup uses the process environment again. Saving or removing an override requires restarting DurinDoor. Neither action changes the active adapter or migrates data. Complete a data cutover before selecting PostgreSQL startup; selecting SQLite does not copy PostgreSQL-era writes.

The separate PostgreSQL connection card below Startup configuration configures the data-cutover target. Enter host, port, database, user, SSL mode and the write-only target password, then Test connection and save target. A successful probe saves the URL encrypted in the settings row; failed probes leave the previous target intact. No URL or stored password is displayed. The target is available to Cut over to Postgres immediately, without restarting or saving a managed startup override. Configuring a target alone leaves the startup engine on its stored/active selection, usually SQLite.

Connection selectors support disable, require, and verify-full. Legacy inherited modes such as prefer are conservatively displayed as require before editing. Startup fields that have not been edited follow refreshed target and engine status, including a successful cutover. Unsaved field edits and newly entered passwords are preserved during refreshes; stored passwords are never populated into either form.

Version policy

Compose has postgres16, postgres17, and postgres18 profiles. Start one profile at a time; all publish host port 5432. Use a supported major and confirm compatibility with your database tooling before cutover. DURINDOOR_PG_PREFERRED_VERSION defaults to 18. Version-dependent features are enabled only when the cluster meets their minimum version.

Quick start

Start a cluster. Default compose up does not start Postgres. Before using a profile, replace its example PostgreSQL password and review the host port publication. For SQLite cutover, leave DURINDOOR_PG_URL unset or empty and do not set DURINDOOR_DATABASE_ENGINE=postgres.

docker compose --profile postgres18 up -d

For a fresh SQLite installation, open Settings → Database → PostgreSQL connection. Enter the target fields and password, then Test connection and save target. This persists the validated target encrypted in SQLite without selecting PostgreSQL startup.

Compose ships example user, password, and database values of durindoor; use the password you configured instead. The service hostname is postgres18 when the app shares its Compose network. DURINDOOR_PG_SSLMODE is appended when the URL has no sslmode= already.

Choose Cut over to Postgres and confirm the migration before restarting or saving PostgreSQL startup settings. Direct PostgreSQL startup does not copy existing SQLite data.

After cutover, the active engine is PostgreSQL. Optionally use Startup configuration to save a managed PostgreSQL override, then restart to apply it. Its Test button only probes; it does not configure the data-cutover target.

After a later restart on a healthy PG boot, the log line is [DB] Driver: pg | engine: postgres. The dashboard Engine card shows postgres as soon as cutover returns ok.

Connection URL

Resolved in this order:

DATA_DIR/durindoor-database.env overrides the three database environment keys at startup
DURINDOOR_PG_URL from the process environment when not overridden
settings.postgresUrl (AES-256-GCM blob in the settings row; legacy dashboard persist writes here)
Legacy DATA_DIR/durindoor-secrets.json (mode 0600, read-only)

Startup configuration writes only the managed environment file. Legacy cutover configuration remains in the settings row. GET /api/settings/database/engine returns redacted host, port, database, user, sslmode, and source badges under startupEnv. It never returns the password or URL.

PostgreSQL-only installations

After migrating the main database, set both DURINDOOR_DATABASE_ENGINE=postgres and DURINDOOR_PG_URL in the service's protected environment file. The independent engine selector prevents an empty or missing URL from recreating SQLite. PostgreSQL connection, cluster-probe, and migration failures reject initialization.

PostgreSQL supports individual query results up to 32 MiB. Larger results return an error; a failed large query does not prevent a subsequent small query.

Proxy timeline storage uses PostgreSQL tables proxyTimelineTraces and proxyTimelineEvents in this mode. To preserve an existing SQLite timeline:

Stop DurinDoor and any watchdog that restarts it; archive the sidecar with its WAL checkpointed.
Apply PostgreSQL migration 025 using the new runtime against the existing database, without admitting inference traffic.
With a private HDD staging directory, run:
```
TMPDIR=/private/staging \
  scripts/copy-proxy-timeline-to-pg.sh /archive/proxy-timeline.sqlite
```
Supply DURINDOOR_PG_URL through the protected environment. The helper also accepts DURINDOOR_PG_COPY_URL for a privileged connection to the same database: server-side COPY requires a superuser or pg_read_server_files. It refuses nonempty target tables, verifies counts and semantic aggregates, repairs the event sequence, and commits both tables atomically. Keep privileged credentials out of shell history.
Verify timeline reads, new inference traces, and PostgreSQL usage writes before removing live SQLite files. Keep the archived sidecar and a verified PostgreSQL dump for rollback.

Cut over an existing SQLite installation

Take a full backup before pressing Cut over to Postgres. Plan a maintenance window because writes pause during the operation. The destination tables are replaced by the mirror; do not target a PostgreSQL database containing data you need to preserve.

The operation probes the cluster, applies migrations, checkpoints SQLite, creates a cutover snapshot, copies rows, checks row counts, and switches the active engine. Request-detail snapshots are skipped unless includeRequestDetails is enabled. Existing SQLite timeline history needs the separate migration above.

A snapshot or row-count failure before the switch leaves SQLite live. Read databaseEngineError and repair the cause before retrying. A second cutover or rollback returns HTTP 503 with Retry-After: 5 while the operation is busy. Reads continue, but writes can fail with database cutover in progress.

Verify success

Dashboard Engine card reads postgres.
servingFallback is false (no "Serving SQLite" banner).
databaseEngineError is empty.
A snapshot exists under $DATA_DIR/db/backups with prefix data.sqlite.postgres-cutover-.
After restart, boot logs [DB] Driver: pg | engine: postgres.

Boot fallback

Only legacy dashboard-managed installations use this fallback. If databaseEngine is postgres and openActiveAdapter cannot use the cluster (no URL, connect error, migration failure), it records databaseEngineError and returns a SQLite adapter. Explicit DURINDOOR_DATABASE_ENGINE=postgres or DURINDOOR_PG_URL startup instead rejects the failure without opening SQLite.

servingFallback is true only when the running adapter is SQLite after a legacy stored PostgreSQL selection. Healthy PostgreSQL startup is not a fallback merely because its settings row still defaults to SQLite. An explicit managed SQLite selection is also not a fallback, even if the legacy settings select PostgreSQL. Pending managed-file edits affect startupEnv, not the active Engine card or fallback banner; restarting is required before those selectors change the runtime.

Known databaseEngineError strings:

PG engine is on but no connection URL is configured
PG connect failed: …
PG cluster reachable but version query failed
PG migration failed: …

The fallback is one-shot per process. Repeated outages do not loop. Restart after you fix the URL or cluster.

The boot log still prints engine: postgres for the requested engine even when the adapter that came back is SQLite. Trust servingFallback and the Engine card, not that log line alone.

Rollback

Rollback restores SQLite as of the cutover snapshot. Writes made while PostgreSQL was live (keys, usage, settings) are not copied back. The PG cluster is left untouched. The confirm dialog on Switch back to SQLite says so.

For an explicit PostgreSQL-only deployment, remove DURINDOOR_DATABASE_ENGINE=postgres and DURINDOOR_PG_URL from the protected service environment and remove or change any managed PostgreSQL startup override before restarting on a SQLite recovery snapshot. In Startup configuration, use Remove override to delete DATA_DIR/durindoor-database.env, or save an explicit SQLite selection instead. Removing only process selectors is insufficient: a retained managed PostgreSQL override reapplies them on restart. Removing the override alone is also insufficient if the process environment still selects PostgreSQL. These startup changes do not restore data or switch the running adapter. Preserve the archived timeline sidecar alongside the main SQLite recovery file; main-database rollback does not copy PostgreSQL timeline writes back.

Switch back to SQLite restores an allowlisted snapshot from currentBackupsDir() into currentDataFile(), reopens SQLite, and sets databaseEngine back to "sqlite". Snapshot paths outside that backups directory are refused. A literal ~/.9router path is never used; path.join does not expand ~.

The button stays disabled until snapshots exist and activeEngine is postgres.

POST /api/settings/database/rollback returns HTTP 409 { error: "rollback_requires_force" } unless the body includes force: true. The dashboard confirm sends { force: true }.

Use the dashboard rollback for its validated snapshot path and engine-setting update. Stop the service before an offline recovery, preserve the current database and sidecar, and follow Data management. Do not overwrite a live SQLite file.

Troubleshooting

Symptom	Cause	Fix
Runtime still on SQLite	Connect, URL, or migration failed	Read `databaseEngineError` on the Database page
Engine card says sqlite, banner says Serving SQLite	Legacy PostgreSQL boot fell back to SQLite	Fix the URL or cluster, then restart
`pg_isready` fails	Cluster down or wrong port	`docker compose ps postgres18` and `docker compose logs postgres18`
Mirror of `requestDetails` is huge	Observability log can be gigabytes	Leave `includeRequestDetails` false (the default)
Cutover returns 503	Another cutover or rollback holds the lock	Wait and retry. `Retry-After: 5`
Cutover returns `cutover snapshot failed`	Copy into `currentBackupsDir()` returned empty	Check disk and permissions on `$DATA_DIR/db/backups`, retry
Cutover returns `Mirror row-count mismatch`	A table's `COUNT(*)` did not match after copy	Inspect `databaseEngineError`; SQLite is still live; retry
HTTP writes fail with `database cutover in progress`	Lock is held; readers still serve	Wait for cutover or rollback to finish
Rollback returns 409 `rollback_requires_force`	Body omitted `force: true` after a successful cutover	Confirm you accept discarding PG-era writes, then send `force: true`

Postgres

On this page