psql: Connect and Run SQL in CI
psql -h host -U user -d db -c "SQL" runs a single statement against PostgreSQL and exits.
psql is the standard PostgreSQL client. In pipelines you drive it non-interactively with -c for one statement or -f for a script, passing credentials through PGPASSWORD or a service URL.
What it does
psql opens a connection to a PostgreSQL server and executes SQL. With -c it runs one command and exits; without -c or -f it starts an interactive REPL. Connection details come from -h/-p/-U/-d flags, a PGHOST/PGUSER/PGDATABASE environment, or a single connection URI.
Common usage
# one statement, password via env
PGPASSWORD=secret psql -h localhost -U app -d appdb -c "SELECT 1"
# connection URI form
psql "postgresql://app:secret@localhost:5432/appdb" -c "\\dt"
# list tables with a meta-command
psql -h localhost -U app -d appdb -c "\\dt"Options
| Flag | What it does |
|---|---|
| -h <host> | Server host (or a socket directory) |
| -p <port> | Server port (default 5432) |
| -U <user> | Database user to connect as |
| -d <db> | Database name to connect to |
| -c "<sql>" | Run a single command or meta-command, then exit |
| -t / -A | Tuples-only / unaligned output (for scripting) |
| -v ON_ERROR_STOP=1 | Exit non-zero on the first SQL error |
In CI
Set PGPASSWORD in the environment (or use a .pgpass file) so psql never prompts and hangs. Point -h at the service container hostname, not localhost, when running the job in a container network. Add -v ON_ERROR_STOP=1 for any command whose failure should fail the job.
Common errors in CI
"psql: error: could not connect to server: Connection refused ... Is the server running on host ... and accepting TCP/IP connections on port 5432?" means Postgres is not up yet or the host/port is wrong; wait for readiness with pg_isready first. "FATAL: password authentication failed for user" is a wrong PGPASSWORD or user. "FATAL: database \"appdb\" does not exist" means the target database was never created; run createdb or point -d at the right name.