psql — The Interactive PostgreSQL Client

psql is PostgreSQL's interactive terminal: it's where you connect to a database, run SQL queries and drive the client through meta-commands that all start with a backslash – such as \l for every database, \dt for the tables or \d to describe one. Beyond querying, it doubles as an administration tool: create roles, grant privileges, back up and restore databases. This guide collects the commands you reach for most in day-to-day work, from opening a connection to exporting CSV.

Connect

psql -U <user> -d <database> — Connect to a database with a specific user.

psql -U postgres -d myapp

psql -h <host> -p <port> -U <user> -d <database> — Connect to a remote PostgreSQL server.

psql -h db.example.com -p 5432 -U admin -d myapp

psql '<connection-string>' — Connect using a connection URI.

psql 'postgresql://user:pass@host:5432/dbname'

psql -U <user> -d <database> -c '<query>' — Execute a query and exit.

psql -U postgres -d myapp -c 'SELECT count(*) FROM users;'

psql -U <user> -d <database> -f <file> — Execute SQL from a file.

psql -U postgres -d myapp -f schema.sql

Meta-Commands — Info

\l — List all databases.

\l

\c <database> — Connect to / switch to a different database.

\c myapp

\dt — List all tables in the current schema.

\dt

\dt+ — List tables with sizes and descriptions.

\dt+

\d <table> — Describe a table (columns, types, indexes).

\d users

\di — List all indexes.

\di

\dv — List all views.

\dv

\dn — List all schemas.

\dn

\df — List all functions.

\df

Meta-Commands — Utility

\x — Toggle expanded display (vertical output).

\x auto

\timing — Toggle query execution timing display.

\timing

\i <file> — Execute SQL from a file.

\i /path/to/migration.sql

\o <file> — Send query output to a file.

\o output.txt

\e — Open the query buffer in an editor.

\e

\q — Quit psql.

\q

\? — Show help for meta-commands.

\?

Roles & Permissions

\du — List all roles/users.

\du

CREATE ROLE <name> WITH LOGIN PASSWORD '<pass>'; — Create a new role with login capability.

CREATE ROLE appuser WITH LOGIN PASSWORD 'secretpass';

GRANT ALL PRIVILEGES ON DATABASE <db> TO <role>; — Grant all privileges on a database.

GRANT ALL PRIVILEGES ON DATABASE myapp TO appuser;

GRANT SELECT ON ALL TABLES IN SCHEMA public TO <role>; — Grant read access to all tables in a schema.

GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;

ALTER ROLE <name> WITH <options>; — Modify role attributes.

ALTER ROLE appuser WITH CREATEDB;

Import & Export

pg_dump -U <user> <database> > <file> — Export a database to SQL.

pg_dump -U postgres myapp > backup.sql

pg_dump -U <user> -Fc <database> > <file> — Export in custom compressed format.

pg_dump -U postgres -Fc myapp > backup.dump

pg_restore -U <user> -d <database> <file> — Restore from custom format dump.

pg_restore -U postgres -d myapp backup.dump

\COPY <table> TO '<file>' CSV HEADER — Export a table to CSV.

\COPY users TO '/tmp/users.csv' CSV HEADER

\COPY <table> FROM '<file>' CSV HEADER — Import CSV data into a table.

\COPY users FROM '/tmp/users.csv' CSV HEADER

pg_dumpall -U <user> > <file> — Export all databases including roles.

pg_dumpall -U postgres > all-databases.sql

Common Patterns

psql -U <user> -d <database> -t -A -c '<query>' — Query with no headers, no alignment (for scripting).

psql -U postgres -d myapp -t -A -c 'SELECT email FROM users LIMIT 5'

SELECT pg_size_pretty(pg_database_size('<database>')); — Show database size in human-readable format.

SELECT pg_size_pretty(pg_database_size('myapp'));

SELECT * FROM pg_stat_activity; — Show current connections and running queries.

SELECT pid, state, query FROM pg_stat_activity;

SELECT version(); — Show PostgreSQL version.

SELECT version();

Conclusion

psql is far more than a prompt for SQL: the backslash meta-commands let you inspect schemas, indexes and roles in seconds, while pg_dump/pg_restore put complete backups at your fingertips. A word on safety: DROP, TRUNCATE and a DELETE without a WHERE clause are irreversible – on production databases, work inside a transaction (BEGIN; … COMMIT;) so you can ROLLBACK if something looks wrong. Never put passwords in plain text into scripts or the command line: store them in ~/.pgpass (with chmod 600) or pass them via the PGPASSWORD environment variable – the same goes for pg_dump credentials.