Skip to content

Postgres Deployment Strategies

VibeWarden uses PostgreSQL for two purposes:

  1. Ory Kratos — stores user identities, sessions, recovery tokens, and verification codes. This is always required when auth.mode is kratos.
  2. Audit logging — the database.external_url field connects VibeWarden itself to a Postgres instance for structured audit event persistence (optional; structured logs are always written to stdout regardless).

Important: VibeWarden's Postgres instance is for VibeWarden and its sub-components (Kratos, audit log). Your wrapped application should connect to its own, independent database. Never share VibeWarden's database with your application — doing so couples their schemas and upgrade paths.

Strategy 1 — Local Docker Compose (dev and cost-conscious single-instance)

The default Docker Compose setup starts a local Postgres container alongside VibeWarden and Kratos. This is the recommended starting point for:

  • Local development
  • Single-server deployments where a managed database is not needed
  • Cost-sensitive setups (Hetzner VPS + local Postgres is the cheapest viable stack)

How it works: the docker-compose.yml generated by vibew init includes a postgres service. Kratos and VibeWarden connect to it using the container service name (postgres) as the hostname on the internal Docker network.

vibewarden.yaml (database section omitted — uses local Postgres via Docker Compose):

# No database section needed.
# VibeWarden picks up the local Postgres automatically when running inside
# the generated Docker Compose stack.

docker-compose.yml (relevant portion):

services:
  postgres:
    image: postgres:17-alpine
    restart: unless-stopped
    environment:
      POSTGRES_USER:     ${POSTGRES_USER}
      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}
      POSTGRES_DB:       ${POSTGRES_DB}
    volumes:
      - postgres_data:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U ${POSTGRES_USER} -d ${POSTGRES_DB}"]
      interval: 5s
      timeout: 5s
      retries: 10

  kratos-migrate:
    image: oryd/kratos:v1.3.1
    environment:
      DSN: postgres://${POSTGRES_USER}:${POSTGRES_PASSWORD}@postgres:5432/${POSTGRES_DB}?sslmode=disable
    command: migrate sql -e --yes
    depends_on:
      postgres:
        condition: service_healthy

  kratos:
    image: oryd/kratos:v1.3.1
    environment:
      DSN: postgres://${POSTGRES_USER}:${POSTGRES_PASSWORD}@postgres:5432/${POSTGRES_DB}?sslmode=disable
    depends_on:
      kratos-migrate:
        condition: service_completed_successfully

volumes:
  postgres_data:

.env:

POSTGRES_USER=vibewarden
POSTGRES_PASSWORD=<strong-random-password>
POSTGRES_DB=vibewarden

Backup: see Production Deployment Guide — Postgres backup.

Limitations: - Postgres data lives on the same machine as your app. A disk failure loses both. - Not suitable for active/active multi-instance deployments (see Strategy 3). - No automated backups unless you set up a cron job yourself.

Strategy 2 — External Managed Postgres (single-instance production)

For production deployments, replace the local Postgres container with a managed database service. This provides:

  • Automated daily backups with point-in-time recovery (PITR)
  • Failover / high availability
  • Offloaded maintenance (upgrades, vacuuming, monitoring)
  • TLS enforced by the provider

vibewarden.yaml:

database:
  external_url: "postgres://vibewarden:${DB_PASSWORD}@db.example.com:5432/vibewarden?sslmode=require"
  tls_mode: require
  connect_timeout: "10s"
  pool:
    max_conns: 10
    min_conns: 2

When database.external_url is set, the generated Docker Compose omits the local postgres container and uses this URL as the Kratos DSN.

docker-compose.yml (relevant difference from Strategy 1):

services:
  # No local postgres service.

  kratos-migrate:
    image: oryd/kratos:v1.3.1
    environment:
      DSN: ${VIBEWARDEN_DATABASE_EXTERNAL_URL}
    command: migrate sql -e --yes

  kratos:
    image: oryd/kratos:v1.3.1
    environment:
      DSN: ${VIBEWARDEN_DATABASE_EXTERNAL_URL}

Pass the URL securely via an environment variable:

# .env
VIBEWARDEN_DATABASE_EXTERNAL_URL=postgres://vibewarden:strongpassword@db.example.com:5432/vibewarden?sslmode=require

AWS RDS (PostgreSQL)

Create a PostgreSQL 17 instance in RDS. Recommended settings:

  • Instance class: db.t4g.micro (dev) / db.t4g.small (prod)
  • Storage: gp3, 20 GB minimum, auto-scaling enabled
  • Multi-AZ: enabled for production
  • Public access: disabled (connect from within the same VPC or via a bastion)
  • Backup retention: 7 days minimum
  • Parameter group: default (no custom parameters required)

After the instance is available, create a dedicated role:

CREATE ROLE vibewarden WITH LOGIN PASSWORD '<strong-password>';
CREATE DATABASE vibewarden OWNER vibewarden;

Connection string:

database:
  external_url: "postgres://vibewarden:${DB_PASSWORD}@mydb.abc123.eu-west-1.rds.amazonaws.com:5432/vibewarden"
  tls_mode: verify-full
  connect_timeout: "10s"
  pool:
    max_conns: 10
    min_conns: 2

RDS issues certificates from the AWS CA. Download the bundle and set PGSSLROOTCERT if you need certificate verification against the CA bundle. For most setups tls_mode: require (no CA verification) is sufficient and avoids the certificate management overhead.

Supabase

Supabase provides a managed PostgreSQL instance with a built-in connection pooler (PgBouncer) and a REST API you do not need to use — VibeWarden connects directly to the underlying Postgres.

Locate your connection string in the Supabase dashboard: Project Settings → Database → Connection string → URI.

Use the direct connection (port 5432) for migrations. For the runtime connection, the pooler (port 6543, Transaction mode) is recommended when your instance will handle high request rates.

database:
  # Direct connection (migrations, low-traffic):
  external_url: "postgres://postgres.abcdefgh:${DB_PASSWORD}@aws-0-eu-central-1.pooler.supabase.com:5432/postgres"
  tls_mode: require
  connect_timeout: "10s"
  pool:
    max_conns: 5   # Supabase free tier limits concurrent connections
    min_conns: 1

database:
  # Pooler connection (high-traffic production):
  external_url: "postgres://postgres.abcdefgh:${DB_PASSWORD}@aws-0-eu-central-1.pooler.supabase.com:6543/postgres"
  tls_mode: require
  connect_timeout: "10s"
  pool:
    max_conns: 10
    min_conns: 2

On the Supabase free tier, Postgres pauses after 1 week of inactivity. Use a paid plan or set up a scheduled ping if you run a low-traffic production service.

Neon

Neon is a serverless PostgreSQL service with branching support. It is well suited for preview environments because each branch is an independent database.

Locate your connection string in the Neon dashboard: Project → Connection Details → Connection string.

database:
  external_url: "postgres://vibewarden:${DB_PASSWORD}@ep-cool-rain-12345.eu-central-1.aws.neon.tech/neondb?sslmode=require"
  tls_mode: require
  connect_timeout: "15s"   # Neon cold-start may add a few seconds on serverless plans
  pool:
    max_conns: 10
    min_conns: 1            # allow pool to drain to 0 between requests on serverless

Neon's serverless compute may have a cold-start delay of 2–5 seconds after a period of inactivity. Set connect_timeout to at least "15s" to avoid spurious connection errors on low-traffic deployments.

Hetzner Managed Databases

Hetzner Managed Databases (PostgreSQL) is the recommended choice for EU-hosted VibeWarden deployments. It runs in the same data centre as your Hetzner VPS, keeping latency below 1 ms.

Create a PostgreSQL 16 or 17 cluster in the Hetzner Cloud Console. After creation, whitelist your server's private IP in the database firewall.

Copy the connection string from the Overview tab (service credentials).

database:
  external_url: "postgres://vibewarden:<password>@<db-host>.hc-<region>.hetzner.cloud:5432/vibewarden"
  tls_mode: require
  connect_timeout: "10s"
  pool:
    max_conns: 10
    min_conns: 2

Hetzner Managed Databases enforce TLS. Always use tls_mode: require or stronger.

Strategy 3 — Shared External Postgres (multi-instance)

When you run multiple VibeWarden instances protecting separate apps on separate servers — and you want a single Postgres cluster to back all of them — each instance must use the same external database with isolated schemas.

Use case: small business running three separate apps (blog, admin panel, API) on three servers, with a single managed Postgres backing all VibeWarden instances for cost and operational efficiency.

Schema isolation

Each VibeWarden instance (and its Kratos sidecar) needs its own Postgres database (not just a schema) to avoid cross-instance table name collisions in Kratos migrations.

Create one database per instance:

CREATE ROLE vw_blog     WITH LOGIN PASSWORD '<pw-blog>';
CREATE ROLE vw_admin    WITH LOGIN PASSWORD '<pw-admin>';
CREATE ROLE vw_api      WITH LOGIN PASSWORD '<pw-api>';

CREATE DATABASE vw_blog  OWNER vw_blog;
CREATE DATABASE vw_admin OWNER vw_admin;
CREATE DATABASE vw_api   OWNER vw_api;

Per-instance vibewarden.yaml

Each instance points to its own database:

# blog.example.com
database:
  external_url: "postgres://vw_blog:${DB_PASSWORD_BLOG}@shared-db.example.com:5432/vw_blog"
  tls_mode: require
  pool:
    max_conns: 5
    min_conns: 1

# admin.example.com
database:
  external_url: "postgres://vw_admin:${DB_PASSWORD_ADMIN}@shared-db.example.com:5432/vw_admin"
  tls_mode: require
  pool:
    max_conns: 5
    min_conns: 1

Connection pool sizing for multi-instance

Postgres has a hard limit on max_connections (default 100 on most managed services). With N VibeWarden instances each with a pool of max_conns, the total connections consumed is:

total_connections = N_instances × (max_conns + kratos_connections)

Kratos typically uses 5–10 connections. For 10 instances:

10 × (5 + 10) = 150 connections

If this exceeds your cluster's limit, either: - Use a connection pooler (PgBouncer, Supabase pooler, or pgBouncer sidecar) in Transaction mode. - Reduce pool.max_conns per instance (e.g. 3). - Upgrade the database cluster tier.

Migration coordination

Each instance runs its own Kratos migrations against its own database, so there is no cross-instance migration contention. Kratos migrations are idempotent — running them multiple times on the same database is safe.

Config reference

All database settings live under the database: key in vibewarden.yaml.

Key Type Default Description
database.external_url string "" Full postgres:// connection URL. When set, the generated Docker Compose omits the local Postgres container.
database.tls_mode string "require" PostgreSQL SSL mode: disable, require, verify-ca, or verify-full. Applied when sslmode is not already present in external_url.
database.pool.max_conns int 10 Maximum open connections in the pool. Applied as pool_max_conns query parameter.
database.pool.min_conns int 2 Minimum idle connections kept open.
database.connect_timeout string "10s" Maximum time to wait when establishing a new connection. Go duration string. Applied as the Postgres connect_timeout parameter (in seconds).

TLS mode values

Value Behaviour
disable No TLS. Plain-text connection. Use only on localhost or trusted private networks.
require TLS required, but the server certificate is not verified. Protects against passive eavesdropping.
verify-ca TLS required; certificate must be signed by a trusted CA. Use PGSSLROOTCERT to point at the CA bundle.
verify-full TLS required; certificate CN/SAN must match the server hostname. Strongest option.

Environment variable overrides

All database.* keys can be overridden with VIBEWARDEN_DATABASE_* environment variables:

Environment variable Config key
VIBEWARDEN_DATABASE_EXTERNAL_URL database.external_url
VIBEWARDEN_DATABASE_TLS_MODE database.tls_mode
VIBEWARDEN_DATABASE_POOL_MAX_CONNS database.pool.max_conns
VIBEWARDEN_DATABASE_POOL_MIN_CONNS database.pool.min_conns
VIBEWARDEN_DATABASE_CONNECT_TIMEOUT database.connect_timeout

Migrating from local to external Postgres

Follow these steps to move from Strategy 1 (local Docker Compose Postgres) to Strategy 2 (external managed Postgres) with zero data loss.

Step 1 — Dump the local database

# On the server running the Docker Compose stack
docker exec myapp-postgres pg_dump \
  -U "$POSTGRES_USER" "$POSTGRES_DB" \
  > /tmp/vibewarden-$(date +%Y%m%d-%H%M%S).sql

Step 2 — Create the target database

Create the database and role on your managed service (see provider-specific instructions in Strategy 2 above).

Step 3 — Restore the dump

psql "postgres://vibewarden:<pw>@db.example.com:5432/vibewarden?sslmode=require" \
  < /tmp/vibewarden-20260101-120000.sql

Step 4 — Update vibewarden.yaml

Add the database section:

database:
  external_url: "postgres://vibewarden:${DB_PASSWORD}@db.example.com:5432/vibewarden"
  tls_mode: require
  connect_timeout: "10s"
  pool:
    max_conns: 10
    min_conns: 2

Step 5 — Set the environment variable

# .env
VIBEWARDEN_DATABASE_EXTERNAL_URL=postgres://vibewarden:strongpassword@db.example.com:5432/vibewarden?sslmode=require

Step 6 — Stop the stack, remove local Postgres, restart

docker compose down
# Remove the postgres service from docker-compose.yml
# (or let vibew init regenerate it with the external_url set)
docker compose up -d

Verify Kratos starts and can reach the database:

docker compose logs kratos | grep "Starting"
curl https://myapp.example.com/_vibewarden/healthz
# {"status":"ok"}

Step 7 — Remove the old volume (after verifying everything works)

docker volume rm myapp_postgres_data

Backup and maintenance recommendations

Backups

Strategy Recommendation
Local Docker Compose Daily pg_dump shipped to object storage (Hetzner Object Storage, S3-compatible). See production-deployment.md for a cron script.
Managed Postgres Enable automated daily backups in the provider dashboard. Enable point-in-time recovery (PITR) for production. Set retention to at least 7 days.
Multi-instance Back up each database independently. Managed services do this per-cluster automatically.

Routine maintenance

Managed services (RDS, Supabase, Neon, Hetzner) handle VACUUM, ANALYZE, and minor-version security patches automatically. For local Docker Compose Postgres:

  • Enable pg_stat_statements for query monitoring.
  • Run VACUUM ANALYZE weekly via a cron job.
  • Pin the Postgres image to a minor version (e.g. postgres:17.2-alpine) and update deliberately.
  • Monitor disk usage: SELECT pg_size_pretty(pg_database_size('vibewarden'));

Connection exhaustion

If you see sorry, too many clients already errors:

  1. Check current connections: SELECT count(*) FROM pg_stat_activity;
  2. Reduce database.pool.max_conns in vibewarden.yaml.
  3. Consider adding a PgBouncer sidecar in Transaction mode.
  4. Upgrade to a larger database tier if the connection limit is the bottleneck.

AI-agent notes

This section is machine-readable context for AI coding agents working on VibeWarden.

  • The database.external_url field in vibewarden.yaml is the single control point for switching between local and external Postgres.
  • When database.external_url is non-empty, the vibew init code generator omits the postgres service from the generated docker-compose.yml and injects ${VIBEWARDEN_DATABASE_EXTERNAL_URL} as the Kratos DSN.
  • DatabaseConfig.BuildDSN() in internal/config/config.go merges external_url, tls_mode, connect_timeout, and pool.max_conns into a single DSN string, respecting any parameters already present in the URL.
  • TLS mode validation is in internal/config/config.go Validate(); accepted values are exactly disable, require, verify-ca, verify-full.
  • Default values: tls_mode=require, pool.max_conns=10, pool.min_conns=2, connect_timeout=10s.
  • The database.url field (without external_) is a legacy alias kept for backward compatibility. New configurations should use external_url.