Postgres for Everything: One Database to Back Your Homelab

Run one shared Postgres instance per host, with one database and one role per app, and delete the four redundant Postgres containers your compose files are quietly accumulating. Each bundled instance costs 80–200MB of RAM, its own version lifecycle, and — the part that actually hurts — its own backup job that you probably haven't written. The exceptions are real but narrow: apps that pin extensions or exact versions get their own instance, and Immich is the canonical example. Everything else can share.

How the sprawl happens

Every self-hosted app's example compose ships a database service, because that makes the quickstart work. Copy five quickstarts and you're running five Postgreses — a 15, two 16s, a 17, and an alpine variant — each holding one small database. Nobody decided this; it accreted. On my own box the cull took an evening and freed about 600MB of RAM, but the bigger win was going from "five backup jobs I should write someday" to one that exists.

The shared-instance layout

One postgres:17 container on a shared Docker network, apps connecting by hostname. Provisioning a new app is two statements:

CREATE ROLE miniflux LOGIN PASSWORD 'generate-something-real';
CREATE DATABASE miniflux OWNER miniflux;

Then point the app at it (DATABASE_URL=postgres://miniflux:...@postgres/miniflux) and delete the bundled db service from its compose file. Keep the provisioning statements in an init/ directory of SQL files in your compose repo — the compose-in-git discipline applies doubly to the database that everything else depends on. One role per app, owner of its own database only, no superuser grants: the point is that a compromised or misbehaving app can wreck its own schema and nothing else.

When per-app instances still win

Immich deserves its callout: it ships a specific Postgres image with vector extensions and its migrations expect exactly that environment. Don't fight it — run its bundled stack as designed, and let it be the one exception on the host. The rule of thumb: share by default, isolate when the app documents database requirements more specific than "a Postgres".

Backups: pg_dump nightly is enough until it isn't

The workhorse is logical dumps on a timer:

docker exec postgres pg_dumpall -U postgres | \
  zstd > /backup/pg-$(date +%F).sql.zst

Cron that nightly, keep 14 days locally, and let your normal 3-2-1 backup job carry the dump directory off-site. A homelab's honest recovery point objective is "since last night", and this delivers it in one line with zero moving parts. Two warnings from the school of hard restores. First, a filesystem copy of a running Postgres data directory is not a backup — it's a corruption lottery; dump, or snapshot at the hypervisor with the guest agent quiescing writes. Second, a dump you've never restored is a hypothesis: zstdcat pg-2026-05-22.sql.zst | psql -U postgres into a scratch container twice a year, and actually open one of the apps against it.

WAL archiving with pgBackRest or wal-g buys point-in-time recovery — the ability to restore to 14:59, one minute before the mistake. It's superb and it's what you'd run in production; at home it earns its complexity only when losing a day genuinely matters (active Paperless archives, finance data). Most homelabs never need it, per the PostgreSQL docs' own tiering of the options.

Major upgrades without tears

The reason bundled databases rot on old majors is that Postgres won't start version 17 on version 15's data directory, and inside containers pg_upgrade is awkward. The shared instance makes the clean path cheap, because you only do it once a year for everything: dump all, start the new major alongside on a different port, restore, repoint apps, retire the old container. Logical dumps restore across any version gap, which is also your escape hatch from any experiment gone wrong. The community pgautoupgrade image automates the dance if you'd rather not choreograph it; either way, take the pre-upgrade dump — that part is not optional.

Extensions worth knowing

Three that earn their keep on a homelab instance: pg_trgm, which gives any app fuzzy text search and which several (Nextcloud, some wikis) will use if present; pgvector, the default substrate for semantic search experiments before any dedicated vector store is justified; and pg_stat_statements, which answers "what is hammering the database" the day something is. All three ship in or install trivially onto the standard image, one CREATE EXTENSION away.

What I'd do

One postgres:17 on a shared network, SQL provisioning files in git, one role and database per app, Immich left alone on its bundled stack. The nightly pg_dumpall one-liner into your existing off-site job, a restore drill on the calendar twice a year, and the whole-instance dump/restore dance annually when a new major has had a few point releases to settle. Skip WAL archiving until you can name the table where losing a day would hurt. It's not exciting, and that's the achievement: the database layer is the one part of a homelab that should be boring on purpose.