PostgreSQL Deployment

Patrimoine uses SQLite by default. PostgreSQL is an alternative for deployments that need higher concurrency or multi-user workloads.

---

When to Use PostgreSQL

• Multi-user deployments — PostgreSQL handles concurrent writes better than SQLite.
• Higher concurrency — connection pooling and row-level locking scale to more simultaneous users.
• SQLite remains the recommended choice for single-user or small-family deployments.

---

Quick Start

Use the PostgreSQL override file alongside the base docker-compose.yml:

docker compose --env-file backend/container/.env \
  -f docker-compose.yml -f docker-compose.postgres.yml up --build

This starts a PostgreSQL container and reconfigures the backend to use it.

Deploying with podman.sh instead of docker compose? It detects PostgreSQL the same way — from DATABASE_URL alone, no separate override file — see Podman Compose: Optional PostgreSQL container.

---

What the Override Does

The docker-compose.postgres.yml override:

1. Starts a PostgreSQL 18 container (docker.io/library/postgres:18-alpine) with a healthcheck (pg_isready).
2. Builds the backend image with the postgres extra (asyncpg) via a UV_EXTRA_ARGS build arg.
   • The default backend image already includes asyncpg (and the redis extra), so any image built from the repository or pulled from the registry can talk to PostgreSQL.
   • The override sets UV_EXTRA_ARGS="--extra postgres", which replaces the default: the image it builds has no arq/redis. If you also run the worker with this override, set UV_EXTRA_ARGS to --extra postgres --extra redis.
3. Sets DATABASE_URL on the backend to postgresql+asyncpg://patrimoine:patrimoine_dev@postgres:5432/patrimoine.
4. Adds a depends_on relationship so the backend waits for PostgreSQL to be healthy.
5. Publishes PostgreSQL on 127.0.0.1:5432 only, so the database is reachable from the host itself (for psql or a backup tool) but not from the network. Its password is a development default; do not change the binding to all interfaces.

Backend Auto-Detection

The backend inspects the DATABASE_URL scheme:

• postgresql+asyncpg:// — enables pool_pre_ping (validates connections before use), skips SQLite-specific PRAGMAs (WAL, foreign keys, busy timeout).
• sqlite+aiosqlite:// — applies SQLite PRAGMAs (journal_mode=WAL, foreign_keys=ON, busy_timeout=5000).

No manual configuration is needed — the backend adapts automatically.

Connection Pool

For PostgreSQL, the backend creates its engine with an explicit, bounded connection pool:

• DB_POOL_SIZE persistent connections (default 5).
• Up to DB_MAX_OVERFLOW additional connections opened under load (default 10).

Note

SQLite gets no pool arguments at all — this section only applies to PostgreSQL.

The background job loops (recurring snapshots, the planned-transaction sweep, scheduled reports, alert checks) and the request path share this same pool, so a busy multi-user deployment may need to raise DB_POOL_SIZE if requests start queuing for a connection. See Environment Variables for both settings.

With METRICS_TOKEN set, the pool is observable through patrimoine_db_pool_size, patrimoine_db_pool_checked_out and patrimoine_db_pool_overflow; see Metrics.

---

Data Persistence

PostgreSQL data is stored in a named volume (pgdata) that persists across container restarts. The volume is defined in docker-compose.postgres.yml:

volumes:
  pgdata:

To reset the database completely, remove the volume:

docker compose -f docker-compose.yml -f docker-compose.postgres.yml down -v

Caution

down -v deletes all PostgreSQL data. Use only for a fresh start.

---

Migrating an Existing SQLite Deployment to PostgreSQL

There is no direct SQLite-to-PostgreSQL data migration tool. Use the app’s own JSON backup export/import feature instead.

It relies on the Export page’s “Full backup (JSON)” button and the Import page’s backup restore, backed by the GET /api/v1/export/backup / POST /api/v1/import/backup endpoints, independent of which database engine is on either end:

1. On the SQLite deployment, sign in and download a full backup from the Export page (“Full backup (JSON)”).
2. Stand up a PostgreSQL deployment (Quick Start above, or ./podman.sh — see Podman Compose: Optional PostgreSQL container) and register the same user account there.
3. On the PostgreSQL deployment, restore the JSON file from the Import page.
   • It previews what will be created or matched per section before you confirm.
   • Existing accounts/institutions are matched by name, everything else is created fresh.

Caution

This moves one user’s data at a time and re-runs price/exchange-rate history through the normal import path rather than copying raw rows.

So it is not equivalent to pg_dump/pg_restore for a multi-user instance — for that, dump the SQLite file’s contents through the ORM by driving the same export/import cycle per user, or write a one-off script against both engines directly.

---

Backup Differences

SQLite backups use sqlite3 .backup (see Backup and Restore). With PostgreSQL, use pg_dump instead:

# Online backup
docker compose -f docker-compose.yml -f docker-compose.postgres.yml exec postgres \
  pg_dump -U patrimoine patrimoine > backup.sql

# Restore
docker compose -f docker-compose.yml -f docker-compose.postgres.yml exec -T postgres \
  psql -U patrimoine patrimoine < backup.sql

With podman.sh, the container is <POD_NAME>-postgres: podman exec patrimoine-postgres pg_dump -U <user> <db> > backup.sql (user and database come from DATABASE_URL).

The backup-db.sh script is SQLite-only. For PostgreSQL, set up a separate pg_dump cron job.

---

See Also

• Podman Compose Deployment
• Backup and Restore