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

This example matches the Docker guide: a pinned image tag from WINDSHIFT_VERSION, the required /tmp tmpfs, an explicit WebAuthn RP ID, and a health check on the Windshift service.

services:
  windshift:
    image: ghcr.io/windshiftapp/windshift:${WINDSHIFT_VERSION}
    restart: unless-stopped
    ports:
      - "8080:8080"
    tmpfs:
      - /tmp:exec,size=64M
    mem_limit: ${WINDSHIFT_CONTAINER_MEMORY_LIMIT:-2g}
    environment:
      - BASE_URL=https://windshift.example.com
      - SSO_SECRET=${SSO_SECRET}
      - WEBAUTHN_RP_ID=windshift.example.com
      - WINDSHIFT_MEMORY_LIMIT_MB=${WINDSHIFT_MEMORY_LIMIT_MB:-2048}
      - POSTGRES_CONNECTION_STRING=postgres://windshift:${POSTGRES_PASSWORD}@postgres:5432/windshift?sslmode=disable
      - ATTACHMENT_PATH=/data/attachments
    volumes:
      - windshift-data:/data
    healthcheck:
      test: ["CMD", "/windshift", "healthcheck"]
      interval: 15s
      timeout: 5s
      retries: 5
      start_period: 30s
    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:

Verify the connection

After startup, confirm that Windshift selected PostgreSQL and reached it:

docker compose logs windshift | grep "connecting to"
# connecting to PostgreSQL database

curl http://localhost:8080/readyz
# {"status":"ready","database":"ok"}

/readyz pings the database on every request. If PostgreSQL stops, it returns HTTP 503 with {"status":"not_ready","database":"unavailable"}. It recovers on its own once PostgreSQL is back, without restarting Windshift. See Verify the deployment for the full post-start check list.

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.