psql is the terminal client that ships with every PostgreSQL install. It connects to a database, runs SQL, and runs a second language of backslash meta-commands that describe the database, control output and load files. It is the tool that works when the GUI does not, over SSH, inside a container, in a CI job, and against a managed instance you can only reach through a tunnel. Learn a dozen of its commands and you will stop needing anything else for most of what a developer does with a database.
The official reference for psql is long and complete and almost nobody reads it end to end. The tutorials that rank instead are lists of commands with no opinion about which ones matter. This post is the working developer’s cut: how to connect, the flags you will use every week, the meta-commands worth memorising, and the scripting habits that make psql more useful than any GUI for repeatable work.
Table of contents
- What psql is, and what it is not
- Connecting: the four ways and the one to standardise on
- The flags that make psql a scripting tool
- The meta-commands worth memorising
- Moving data with \copy
- Using psql against a managed database
- How this fits the rest of the stack
- FAQ
What psql is, and what it is not
psql is a client. It does not run the database; it talks to one over a network socket or a Unix socket, sends SQL, and prints what comes back. That makes it the same tool whether the server is on your laptop, in a container, or a managed instance in another region.
It has two vocabularies. Anything that ends in a semicolon is SQL and goes to the server. Anything that starts with a backslash is a meta-command, handled by psql itself: \dt lists tables, \x toggles expanded output, \copy moves a file. The meta-commands are the reason psql is worth learning over a bare SQL prompt; they are the parts a GUI gives you as menus.
What psql is not is a data browser. Scrolling a wide table in a terminal is unpleasant and always will be. When the job is to look at rows, use a database explorer. When the job is to run a query, a migration, a dump, a script, or anything you will do twice, psql is faster and it leaves a record.
Connecting: the four ways and the one to standardise on
psql accepts connection details as flags, as a URL, or from the environment. Standardise on the URL form, because it is one string you can put in a secret and pass to everything.
# Flags: verbose, fine for a laptop
psql -h db.internal -p 5432 -U app -d production
# URL: one string, works everywhere
psql "postgresql://app:[email protected]:5432/production?sslmode=require"
# Environment: no secrets on the command line or in shell history
export PGHOST=db.internal PGUSER=app PGDATABASE=production PGPASSWORD=secret
psql
# A password file, the quiet option for scripts
echo "db.internal:5432:production:app:secret" >> ~/.pgpass && chmod 600 ~/.pgpassTwo things trip people up. The first is -W, which forces a password prompt; leave it off and psql will use PGPASSWORD or .pgpass and only prompt if it must. The second is sslmode. A managed instance will usually refuse plain connections; put ?sslmode=require in the URL and stop guessing.
Once connected, the prompt tells you where you are: production=> for a normal user, production=# for a superuser. Check it before running anything with DROP in it.
The flags that make psql a scripting tool
Interactive use is where you learn psql. Scripted use is where it earns its place. Five flags do nearly all of it.
# -c: run one command and exit
psql "$DATABASE_URL" -c "SELECT count(*) FROM orders WHERE created_at > now() - interval '1 day';"
# -f: run a file
psql "$DATABASE_URL" -f migrations/0042_add_index.sql
# -v ON_ERROR_STOP=1: stop on the first error instead of ploughing on
psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f seed.sql
# -At: unaligned, tuples only; the shape a shell script wants
psql "$DATABASE_URL" -At -c "SELECT id FROM users WHERE banned;" | while read id; do ...; done
# --csv: proper CSV, quoted, for a spreadsheet or another tool
psql "$DATABASE_URL" --csv -c "SELECT * FROM invoices WHERE month = '2026-08';" > invoices.csvON_ERROR_STOP is the one people forget until a migration file half-applies. Combine it with --single-transaction and a file either applies entirely or not at all. The psql run SQL file post goes into the transaction behaviour and the mistake of running the wrong file.
-At and --csv turn psql into a data source for other programs. A cron job that runs a query and pipes the result to a script is a monitoring system that cost nothing.
The meta-commands worth memorising
There are dozens. These are the ones a working developer uses every week, in rough order of frequency.
| Command | What it does |
|---|---|
\l | List databases. |
\c dbname | Connect to a different database. |
\dt | List tables in the current schema. \dt+ adds sizes. |
\d tablename | Describe a table: columns, types, indexes, constraints. |
\du | List roles and their attributes. |
\x | Toggle expanded output; one column per line. Essential for wide rows. |
\timing | Show how long each query takes. |
\e | Open the last query in your editor. |
\i file.sql | Run a file from inside psql. |
\o file.txt | Send output to a file until \o again. |
\copy | Copy between a table and a local file. |
\watch 5 | Re-run the last query every five seconds. |
\? | Help on meta-commands. \h CREATE INDEX is help on SQL. |
\x is the one to try first if you have only ever used a GUI: a SELECT * on a table with twenty columns becomes readable. \watch turns a query into a live dashboard, which is the fastest way to watch a queue drain or a migration progress. The psql list databases post covers \l and its neighbours in more depth.
Moving data with \copy
COPY is the server-side bulk loader, and it reads and writes files on the server’s disk, which you cannot reach on a managed instance. \copy is the psql meta-command that does the same thing through the client connection, reading and writing files where psql is running. That distinction is the whole reason to know it.
# Export a query to a local CSV
\copy (SELECT id, email, created_at FROM users WHERE plan = 'pro') TO 'pro_users.csv' CSV HEADER
# Load a local CSV into a table
\copy imports_staging FROM 'signups.csv' CSV HEADER\copy is the correct answer to most one-off import and export jobs, and it is orders of magnitude faster than a row-per-INSERT script. The psql COPY post covers the three forms and when to use each. For a full-database copy, use pg_dump and pg_restore, which are separate tools that ship beside psql and use the same connection details.
Using psql against a managed database
A managed Postgres removes the server from your reach and leaves the client. That changes three habits.
Connect over TLS, and reach it through the private network where possible. A managed instance on RunxBuild has private networking so it is not on the public internet; from a service on the platform, psql connects with the internal hostname from the environment. From a laptop, you connect through whatever access path the network security settings allow, with sslmode=require in the URL.
Read the connection limit. Every psql session is a connection. A managed instance has a limit you can see in the dashboard; a long-running \watch and a few forgotten terminals can use a surprising share of a small plan’s allowance. Close sessions, and use a pooler for the application.
Use roles deliberately. Create a read-only role for humans and analytics and keep the application’s role for the application. The database user management docs cover creating them on a managed instance; from psql, CREATE ROLE reporter LOGIN PASSWORD '...' and GRANT SELECT ON ALL TABLES IN SCHEMA public TO reporter is the whole job.
Backups and restores on a managed instance happen in the dashboard, not through psql. What psql is for on a managed database is exactly what it is for everywhere else: the query, the migration, the export, and the two-in-the-morning look at what the application actually wrote.
How this fits the rest of the stack
psql is the one Postgres tool that works everywhere the database does. Connect with a URL, script with -c, -f and ON_ERROR_STOP, memorise \d, \x, \copy and \watch, and keep a read-only role for the humans. Everything else in the reference is there when you need it. For a sense of what the database you are connecting to costs alongside the services that use it, the RunxBuild hosting calculator shows the managed instance, the connection limit that comes with each plan, and the services beside it as separate line items.
Useful related references:
- psql List Databases: The Three Commands and the Four Flags a Developer Actually Uses
- psql Run SQL File: The -f Flag, the Connection String, the Transaction, and the One Mistake That Runs the Wrong File
- Learn PostgreSQL: A Path From First Query to Production
- Database Management on RunxBuild
- Managed Databases on RunxBuild
FAQ
What is the psql tool used for?
psql is the PostgreSQL command-line client. It connects to a database server and runs SQL interactively or from scripts, and it has its own backslash meta-commands for listing tables, describing schemas, toggling output formats and copying data to and from local files. It is used for queries, migrations, exports, and any database task you want to repeat or automate.
How do I connect to a PostgreSQL database with psql?
The simplest portable form is a connection URL: psql followed by a string like postgresql://user:password@host:5432/dbname?sslmode=require. You can also pass -h, -p, -U and -d flags, or set PGHOST, PGUSER, PGDATABASE and PGPASSWORD in the environment. For scripts, a .pgpass file with mode 600 keeps the password out of the command line.
What is the difference between COPY and backslash copy in psql?
COPY is a SQL command that runs on the server and reads or writes files on the server’s own disk. The psql meta-command backslash copy does the same work through the client connection, using files on the machine where psql is running. On a managed database you cannot reach the server’s disk, so backslash copy is the one to use.
Is psql better than a GUI like pgAdmin?
They are for different jobs. A GUI is better for browsing rows and exploring an unfamiliar schema. psql is better for anything repeatable: migrations, scripted queries, exports, cron jobs, and working over SSH or inside a container where no GUI exists. Most developers end up using both, with psql for the work that gets committed.
How do I run a SQL file with psql?
Use the -f flag: psql with the connection URL, then -f path/to/file.sql. Add -v ON_ERROR_STOP=1 so the run halts at the first error instead of continuing, and —single-transaction so the file either applies entirely or rolls back. From inside an interactive session, backslash i followed by the filename does the same thing.