psql -f: Run SQL Scripts and Migrations
psql -f file.sql executes every statement in a SQL file, ideal for migrations and fixtures in CI.
For applying a schema or seeding test data, feed psql a file with -f. The key CI concern is making a failing statement fail the whole job, which is what ON_ERROR_STOP and a wrapping transaction give you.
What it does
psql -f reads SQL from a file and runs each statement in order. By default it keeps going after an error and still exits 0, so you must opt into strict behavior for CI. Input piped on stdin behaves the same way as -f -.
Common usage
# apply a migration, stop and fail on the first error
psql -h db -U app -d appdb -v ON_ERROR_STOP=1 -f migrate.sql
# wrap the whole file in one transaction (all or nothing)
psql -h db -U app -d appdb -v ON_ERROR_STOP=1 --single-transaction -f seed.sql
# feed SQL on stdin
psql -h db -U app -d appdb < schema.sqlOptions
| Flag | What it does |
|---|---|
| -f <file> | Read and execute SQL from a file |
| -v ON_ERROR_STOP=1 | Abort and exit non-zero on the first error |
| --single-transaction | Run the whole file in one transaction |
| -1 | Short alias for --single-transaction |
| -q | Quiet: suppress informational output |
| -e | Echo each command as it runs |
In CI
Always pass -v ON_ERROR_STOP=1; without it psql runs the rest of the script after a failed statement and still returns 0, so a broken migration silently passes the job. Add --single-transaction when a partially applied migration would leave a bad state.
Common errors in CI
"psql: error: <file>: No such file or directory" means the path is wrong relative to the job working directory. "ERROR: relation \"users\" already exists" during a re-run means the migration is not idempotent; guard with IF NOT EXISTS or reset the database. A script that clearly failed but the step still passed means ON_ERROR_STOP was not set.