PostgreSQL

Use PostgreSQL instead of SQLite when you need more write concurrency or must use existing database infrastructure.

Connection String

Set a PostgreSQL connection string with a CLI flag or environment variable:

# CLI flag
./windshift --postgres-connection-string "postgres://user:password@localhost:5432/windshift?sslmode=disable"

# Short flag
./windshift --pg-conn "postgres://user:password@localhost:5432/windshift?sslmode=disable"

# Environment variable
export POSTGRES_CONNECTION_STRING="postgres://user:password@localhost:5432/windshift?sslmode=disable"
./windshift

When you set a PostgreSQL connection string, Windshift uses PostgreSQL and ignores the --db flag.

Connection Pool

Windshift limits the PostgreSQL pool size. This prevents a busy instance or agent traffic burst from exhausting database connections. Windshift sizes the pool from MAX_READ_CONNS:

Windshift recycles idle connections after their lifetime and idle timeout. To raise the limit, increase MAX_READ_CONNS (--max-read-conns). Keep it below the PostgreSQL server's max_connections. Reserve connections for other clients, runners, and sidecars.

Per-user concurrency

MAX_USER_CONCURRENCY (default 16) limits the simultaneous /api requests from one authenticated user or agent. This prevents one heavy client from monopolizing the pool. Set it to 0 to remove the limit.

Go's database/sql package manages the pool.

Docker Compose Setup

services:
  windshift:
    image: ghcr.io/windshiftapp/windshift:latest
    restart: unless-stopped
    ports:
      - "8080:8080"
    tmpfs:
      - /tmp:exec,size=64M
    environment:
      - BASE_URL=https://windshift.example.com
      - SSO_SECRET=${SSO_SECRET}
      - POSTGRES_CONNECTION_STRING=postgres://windshift:${POSTGRES_PASSWORD}@postgres:5432/windshift?sslmode=disable
      - ATTACHMENT_PATH=/data/attachments
    volumes:
      - windshift-data:/data
    depends_on:
      postgres:
        condition: service_healthy

  postgres:
    image: postgres:18
    restart: unless-stopped
    environment:
      - POSTGRES_USER=windshift
      - POSTGRES_PASSWORD=${POSTGRES_PASSWORD}
      - POSTGRES_DB=windshift
    volumes:
      - postgres-data:/var/lib/postgresql/data
    healthcheck:
      test: ["CMD-SHELL", "pg_isready -U windshift"]
      interval: 5s
      timeout: 5s
      retries: 5

volumes:
  windshift-data:
  postgres-data:

PostgreSQL major upgrades

The Compose examples use PostgreSQL 18. Do not change a PostgreSQL image to a different major version when it uses an existing data volume. Use pg_upgrade or a dump and restore. PostgreSQL will not start a data directory created by another major version.

Schema Management

Windshift manages database schemas automatically. At startup, it runs embedded migration files for items, users, tests, workflows, portals, notifications, and permissions. It converts SQL placeholders between SQLite (?) and PostgreSQL ($1, $2, and similar) syntax.

Do not run manual schema setup or migration commands.

SSL/TLS

For a production PostgreSQL connection, set sslmode in the connection string:

# Require SSL
--pg-conn "postgres://user:pass@db.example.com:5432/windshift?sslmode=require"

# Verify server certificate
--pg-conn "postgres://user:pass@db.example.com:5432/windshift?sslmode=verify-full&sslrootcert=/path/to/ca.pem"

The split-variable configuration supports the same TLS modes with POSTGRES_SSLMODE:

DB_TYPE=postgres
POSTGRES_HOST=db.example.com
POSTGRES_USER=windshift
POSTGRES_PASSWORD=secret
POSTGRES_DB=windshift
POSTGRES_SSLMODE=require

POSTGRES_SSLMODE defaults to disable. This setting fits the bundled postgres container on the same Docker network. For a remote or managed database, use require, verify-ca, or verify-full. Accepted modes are disable, allow, prefer, require, verify-ca, and verify-full.

Backups

Use standard PostgreSQL backup tools:

# Logical backup
pg_dump -U windshift windshift > backup.sql

# Compressed backup
pg_dump -U windshift -Fc windshift > backup.dump

# Restore
pg_restore -U windshift -d windshift backup.dump

Migrating from SQLite

To migrate an existing SQLite database to PostgreSQL:

1. Set up a new PostgreSQL database.
2. Start Windshift with the PostgreSQL connection string. Windshift creates all tables.
3. Export the SQLite data. Import it into PostgreSQL with standard tools.

There is no built-in migration command at this time.