psql Scripting

Run SQL files and parameterized checks in CI, deploy pipelines, and repeatable admin tasks with psql non-interactive mode.

Recipe

psql "$DATABASE_URL" \
  -v ON_ERROR_STOP=1 \
  -f migrations/001_init.sql
 
psql "$DATABASE_URL" -c "SELECT count(*) FROM schema_migrations;"

When to reach for this: Migration runners, smoke tests after deploy, seed scripts, and DBA playbooks checked into git.

Working Example

#!/usr/bin/env bash
set -euo pipefail
 
export PGAPPNAME=ci-schema-check
psql "$DATABASE_URL" \
  --single-transaction \
  -v ON_ERROR_STOP=1 \
  -v tenant_id=42 \
  -f scripts/verify_rls.sql \
  -o /tmp/verify.out

-- scripts/verify_rls.sql
\set ON_ERROR_STOP on
SELECT current_setting('app.tenant_id', true) AS tenant;
 
SELECT count(*) AS orphan_orders
FROM orders o
WHERE o.tenant_id <> :'tenant_id'::bigint;

What this demonstrates:

• -v ON_ERROR_STOP=1 aborts the script on first SQL error (essential in CI)
• --single-transaction wraps the file in one BEGIN/COMMIT
• :tenant_id substitutes psql variables (use quoted :'name' for literal safety)
• PGAPPNAME shows up in pg_stat_activity.application_name

Deep Dive

Running Script Files

Variables

-- Set in shell: psql -v start_date=2026-01-01
SELECT :'start_date'::date;
 
-- Prompt if unset
\prompt 'Enter tenant id' tenant_id

• Unquoted :name is SQL identifier substitution - dangerous for user input.
• Quoted :'name' is string literal substitution - still escape-aware, not a full prepared statement.
• For untrusted input, use server-side parameters via application code, not psql vars.

CI Smoke Checks

# .github/workflows/db-smoke.yml (excerpt)
- name: Schema smoke test
  run: |
    psql "${{ secrets.STAGING_DATABASE_URL }}" \
      -v ON_ERROR_STOP=1 \
      -f ci/assert_indexes.sql

-- ci/assert_indexes.sql
SELECT 1/(count(*) > 0)::int AS idx_orders_created_exists
FROM pg_indexes
WHERE tablename = 'orders' AND indexname = 'idx_orders_created_at';

• Division-by-zero trick fails the job when count is zero.
• Keep checks idempotent and fast (< few seconds).

Exit Codes

• 0 success
• 1 fatal error (with ON_ERROR_STOP)
• 2 connection failure
• 3 script error in -f file

Gotchas

• Missing ON_ERROR_STOP - Script continues after failed statement; CI passes with partial apply. Fix: Always -v ON_ERROR_STOP=1 in automation.
• Variable SQL injection - :'user_input' in dynamic SQL from operators. Fix: Validate format; use app-layer prepared statements for user data.
• \i relative paths - Depends on cwd when psql started. Fix: Absolute paths or cd in wrapper script.
• COPY in CI without files - \copy needs client-side path. Fix: Mount fixtures or use INSERT seeds.
• Long scripts without single transaction - Half-applied DDL on failure. Fix: --single-transaction when all statements are transaction-safe.

Alternatives

FAQs

Related

• psql Basics - interactive fundamentals
• Migrations Basics - versioned SQL in git
• Flyway & Liquibase - enterprise runners

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.