psql runs a SQL file with psql -f file.sql (or \i file.sql from the psql prompt). The right answer is to specify the connection (-h <host> -p <port> -U <user> -d <dbname>), the right answer for a safe run is -v ON_ERROR_STOP=1 (the script stops on the first error), the right answer for an atomic run is --single-transaction (all or nothing). The mistake every team makes: the team runs psql -f schema.sql against the production database with the wrong connection string, the team’s data is destroyed.
Table of contents
- The -f flag — the right way to run a SQL file
- The connection string — the right way to specify the database
- The ON_ERROR_STOP — the right way to make the script safe
- The single-transaction — the right answer for atomicity
- The one mistake that runs the wrong file
- The variable substitution — the right answer for reusable scripts
- The output capture — the right answer for CI
- How this fits the rest of the stack
- FAQ
The -f flag — the right way to run a SQL file
The -f flag specifies the file to run: psql -f schema.sql. The right answer is -f for a script, \i from inside psql, < for stdin. The wrong answer is to copy-paste the SQL into a psql prompt — the team loses the script, the team cannot reproduce the run.
The connection string — the right way to specify the database
The connection is specified with -h <host> -p <port> -U <user> -d <dbname>. The right answer is to use the URL form (psql postgres://user:***@host:5432/dbname -f file.sql), the right answer for a password is the PGPASSWORD env var. The wrong answer is to put the password in the command line (psql -U user -W *** — the password is in the shell history).
The ON_ERROR_STOP — the right way to make the script safe
The default behavior is to continue on error. The team runs a 100-statement script, the 50th statement fails, the 51st-100th statements run, the team’s database is in an inconsistent state. The right answer is -v ON_ERROR_STOP=1 — the script stops on the first error, the team is notified.
The single-transaction — the right answer for atomicity
The --single-transaction flag wraps the entire script in a transaction. The script either succeeds (all statements committed) or fails (all statements rolled back). The right answer for a migration is --single-transaction — the team’s migration is atomic, the team’s database is never in a partial state.
The one mistake that runs the wrong file
The mistake: the team runs psql -f schema.sql against the production database. The schema.sql is the dev file, the team’s production is wiped. The right answer is to specify the connection explicitly (-h <prod-host> -U <prod-user> -d <prod-db>), the right answer is to have a dbname in the connection string, the right answer is to use a different shell prompt color for production (the team’s terminal is red for prod, green for dev).
The variable substitution — the right answer for reusable scripts
The right answer for a reusable script is psql variables. The team defines a variable with -v key=value, the team uses the variable in the script as :key. The right answer is the variable for a script that runs against multiple environments (dev, staging, prod) with the same SQL but different data.
The output capture — the right answer for CI
The right answer for a CI run is to capture the output (psql -f file.sql > output.log 2>&1), the right answer for a CI that needs to check the success is -v ON_ERROR_STOP=1 and the exit code (echo $?). The wrong answer is to ignore the exit code — the CI thinks the run succeeded, the team’s data is in a partial state.
How this fits the rest of the stack
The infrastructure question is a small piece of a larger pattern: the team’s runtime, storage, database, secret store, logs, and deployment platform are all parts of the same platform. The right answer is to model the full stack before the project ships, not after. The RunxBuild hosting calculator is the right place to do that exercise — pick the runtime, the memory tier, the storage, the secret store, and the egress, and the calculator shows what the deploy actually costs at the team’s actual usage.
Useful related references:
FAQ
How do I run a SQL file in psql?
psql -f file.sql for a script, \i file.sql from inside psql, psql < file.sql for stdin. The right answer is -f for a script.
How do I run a SQL file against a remote database?
psql -h <host> -p <port> -U <user> -d <dbname> -f file.sql. The right answer is to use the URL form or the PGPASSWORD env var for the password.
How do I make psql stop on the first error?
psql -v ON_ERROR_STOP=1 -f file.sql. The right answer is ON_ERROR_STOP=1 for any script that should not silently continue past an error.
How do I make the SQL file run as a transaction?
psql --single-transaction -f file.sql. The right answer is single-transaction for a migration, the right answer is no flag for a script that should commit per statement.
How do I pass a variable to a psql script?
psql -v key=value -f file.sql, the script uses :key to read the variable.
How do I capture the output of psql?
psql -f file.sql > output.log 2>&1. The right answer is to capture the output and check the exit code (echo $?) in CI.
How do I avoid the password in the shell history?
Use the PGPASSWORD env var, or the URL form, or the .pgpass file. The wrong answer is to put the password in the command line (-W ***).
What is the difference between -f and < ?
-f file.sql runs the file. < file.sql reads from stdin. The right answer is -f for a file, < for a piped input.