PostgreSQL Backend

Use PostgreSQL for shared team state, encrypted sensitive outputs, and full SQL query capabilities.

Setup

Set the OXID_DATABASE_URL environment variable to connect Oxid to your PostgreSQL instance:

export OXID_DATABASE_URL="postgresql://oxid_user:password@db.example.com:5432/oxid_state"

Then initialize as normal:

oxid init

Oxid creates all required tables automatically on first init.

Environment Variables

Flag	Description	Default
OXID_DATABASE_URL	PostgreSQL connection string. When set, Oxid uses PostgreSQL instead of SQLite.	- (uses SQLite)
OXID_DATABASE_SCHEMA	PostgreSQL schema to use for all Oxid tables. Useful for multi-environment setups.	public

Custom Schema

Use OXID_DATABASE_SCHEMA to isolate state for different environments in the same database:

# Production
export OXID_DATABASE_URL="postgresql://user:pass@db:5432/infra"
export OXID_DATABASE_SCHEMA="production"
oxid init

# Staging
export OXID_DATABASE_SCHEMA="staging"
oxid init

TipUsing schemas lets you share a single PostgreSQL instance across environments while keeping state fully isolated.

Encrypted Outputs

When using PostgreSQL, Oxid automatically encrypts sensitive output values at rest using AES-256 via the pgcrypto extension. No configuration required.

Encryption key is derived from SHA-256 of the OXID_DATABASE_URL
Only outputs marked sensitive = true are encrypted
Decryption is transparent - oxid output handles it automatically
Non-sensitive outputs are stored in plaintext

See Encrypted Outputs for details.

NoteOxid enables the pgcrypto extension automatically. Ensure your database user has the CREATE EXTENSION privilege, or install it manually: CREATE EXTENSION IF NOT EXISTS pgcrypto;

Team Workflows

Shared state

All team members point to the same PostgreSQL instance. State changes from any member are immediately visible to all others:

# .envrc (shared via version control)
export OXID_DATABASE_URL="postgresql://oxid:$OXID_DB_PASS@db.internal:5432/infra"

# Team member A
oxid apply  # Creates resources, updates state

# Team member B (immediately sees the changes)
oxid state list
oxid plan

CI/CD pipeline

# GitHub Actions example
env:
  OXID_DATABASE_URL: ${{ secrets.OXID_DATABASE_URL }}

steps:
  - run: oxid init
  - run: oxid plan --out plan.oxid
  - run: oxid apply plan.oxid --auto-approve

Connection Pooling

Oxid manages database connections internally with connection pooling. For high-concurrency scenarios (many parallel resource operations), PostgreSQL handles this natively. No external connection pooler is required for typical workloads.

For very large teams or high-parallelism deployments, consider using PgBouncer in front of PostgreSQL:

export OXID_DATABASE_URL="postgresql://oxid:pass@pgbouncer:6432/infra?sslmode=require"

Required Permissions

The database user needs the following permissions:

-- Create a dedicated user
CREATE USER oxid_user WITH PASSWORD 'secure-password';

-- Grant permissions
GRANT CREATE ON DATABASE oxid_state TO oxid_user;
GRANT USAGE, CREATE ON SCHEMA public TO oxid_user;

-- For pgcrypto (if not already installed)
GRANT CREATE ON SCHEMA public TO oxid_user;  -- OR install it as superuser:
-- CREATE EXTENSION IF NOT EXISTS pgcrypto;

Migration from SQLite

To migrate from local SQLite to PostgreSQL:

# 1. Set the PostgreSQL URL
export OXID_DATABASE_URL="postgresql://user:pass@host:5432/oxid_state"

# 2. Initialize PostgreSQL tables
oxid init

# 3. Sync state from your existing Terraform/Oxid state
oxid sync --state-file terraform.tfstate
# Or re-apply to populate state:
oxid apply