Database
psql CLI
PostgreSQL psql commands for database management.
35 commands
Windows
MacOS
Linux
#postgresql
#sql
#Popular
Connection
Connect as the postgres user
psql -U postgres
Connect to a specific host and database
psql -h localhost -p 5432 -U myuser mydb
Connect to a database using defaults
psql -d mydb
Connect with a connection URI
psql "postgresql://user:pass@host:5432/db"
List all available databases
psql -U postgres -l
Meta-Commands
List all tables in the current schema
\dt
List tables with size and description
\dt+
Describe columns of a table
\d tablename
List all schemas
\dn
List all roles and users
\du
List all indexes
\di
List all functions
\df
Query Execution
Execute a single SQL command
psql -U postgres -d mydb -c 'SELECT NOW()'
Execute SQL from a file
psql -U postgres -d mydb -f script.sql
Enable query execution timing
\timing on
Toggle expanded display mode
\x
Output unaligned tuples-only format
psql -A -t -c 'SELECT name FROM users'
Import/Export
Export a table to CSV file
\copy users TO '/tmp/users.csv' CSV HEADER
Import data from CSV into a table
\copy users FROM '/tmp/users.csv' CSV HEADER
Dump an entire database to SQL file
pg_dump -U postgres mydb > backup.sql
Dump a single table to SQL file
pg_dump -U postgres -t users mydb > users.sql
Restore a database from dump file
pg_restore -U postgres -d mydb backup.dump
User Management
Create a new database user
CREATE USER myuser WITH PASSWORD 'secret';
Grant full access to a database
GRANT ALL PRIVILEGES ON DATABASE mydb TO myuser;
Promote user to superuser
ALTER USER myuser WITH SUPERUSER;
Revoke database privileges
REVOKE ALL ON DATABASE mydb FROM myuser;
Change a user password interactively
\password myuser
Database Management
Create a new database
CREATE DATABASE mydb;
Delete a database permanently
DROP DATABASE mydb;
Change database owner
ALTER DATABASE mydb OWNER TO myuser;
Show database size in human format
SELECT pg_size_pretty(pg_database_size('mydb'));
Reclaim storage and update statistics
VACUUM ANALYZE;
Quick Commands
Connect to a PostgreSQL database on localhost
psql -h localhost -U myuser mydb
Export an entire database to a SQL backup file
pg_dump -U postgres mydb > backup.sql
List all tables with their sizes in psql
\dt+