Scripting con psql

Ejecuta archivos SQL y comprobaciones parametrizadas en CI, pipelines de despliegue y tareas administrativas repetibles con el modo no interactivo de psql.

Receta

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

Cuándo usar esto: Ejecutores de migraciones, pruebas de humo después del despliegue, scripts de inicialización y playbooks de DBA registrados en git.

Ejemplo de trabajo

#!/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;

Lo que esto demuestra:

• -v ON_ERROR_STOP=1 aborta el script ante el primer error SQL (esencial en CI)
• --single-transaction envuelve el archivo en un único BEGIN/COMMIT
• :tenant_id sustituye variables de psql (usa :'nombre' entre comillas para mayor seguridad literal)
• PGAPPNAME aparece en pg_stat_activity.application_name

Análisis Profundo

Ejecución de Archivos de Script

Variables

-- Establecer en la shell: psql -v start_date=2026-01-01
SELECT :'start_date'::date;
 
-- Solicitar si no está definida
\prompt 'Introduce el id del tenant' tenant_id

• :nombre sin comillas es sustitución de identificador SQL - peligroso para la entrada del usuario.
• :'nombre' entre comillas es sustitución de literal de cadena - todavía sensible a escapes, no una sentencia preparada completa.
• Para entradas no confiables, usa parámetros del lado del servidor a través del código de la aplicación, no variables de psql.

Comprobaciones de Humo en CI

# .github/workflows/db-smoke.yml (extracto)
- 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';

• El truco de división por cero falla el trabajo cuando el recuento es cero.
• Mantén las comprobaciones idempotentes y rápidas (menos de unos pocos segundos).

Códigos de Salida

• 0 éxito
• 1 error fatal (con ON_ERROR_STOP)
• 2 fallo de conexión
• 3 error de script en el archivo -f

Trampas Comunes

• Falta de ON_ERROR_STOP - El script continúa después de una sentencia fallida; CI pasa con aplicación parcial. Solución: Siempre usa -v ON_ERROR_STOP=1 en la automatización.
• Inyección SQL de variables - :'user_input' en SQL dinámico de operadores. Solución: Valida el formato; usa sentencias preparadas a nivel de aplicación para datos de usuario.
• Rutas relativas de \i - Depende del directorio actual cuando se inició psql. Solución: Rutas absolutas o cd en el script contenedor.
• COPY en CI sin archivos - \copy necesita la ruta del lado del cliente. Solución: Monta fixtures o usa semillas INSERT.
• Scripts largos sin transacción única - DDL parcialmente aplicado en caso de fallo. Solución: Usa --single-transaction cuando todas las sentencias sean seguras para transacciones.

Alternativas

Preguntas Frecuentes

Relacionado

• Fundamentos de psql - fundamentos interactivos
• Fundamentos de Migraciones - SQL versionado en git
• Flyway y Liquibase - ejecutores empresariales

Versiones de Stack: Esta página fue escrita para PostgreSQL 18.4 (estable 18, mantenimiento 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, y PostGIS 3.5+.