Connection
psql -h hostname -p port -U username -d database
Connect to PostgreSQL server
psql -U username database
Connect to local PostgreSQL server
\c database
Connect to a different database
\conninfo
Display connection information
Database Operations
\l
List all databases
CREATE DATABASE dbname;
Create a new database
DROP DATABASE dbname;
Delete a database
\d
List all tables, views, and sequences
\d tablename
Describe a table structure
Table Operations
SELECT * FROM tablename;
Select all records from a table
SELECT * FROM tablename LIMIT 10;
Select first 10 records from a table
INSERT INTO tablename (column1, column2) VALUES (value1, value2);
Insert a new record
UPDATE tablename SET column = value WHERE condition;
Update records
DELETE FROM tablename WHERE condition;
Delete records
TRUNCATE TABLE tablename;
Remove all records from a table
Monitoring & Statistics
SELECT pg_size_pretty(pg_database_size('dbname'));
Get database size
SELECT pg_size_pretty(pg_relation_size('tablename'));
Get table size
SELECT * FROM pg_stat_activity;
Show current activity
SELECT pid, query, state FROM pg_stat_activity;
Show running queries
SELECT * FROM pg_locks;
Show locks
SELECT pg_cancel_backend(pid);
Cancel a specific query
SELECT pg_terminate_backend(pid);
Terminate a specific connection
Performance
EXPLAIN SELECT * FROM tablename;
Show query execution plan
EXPLAIN ANALYZE SELECT * FROM tablename;
Show and execute query plan with timing
CREATE INDEX ON tablename (column);
Create an index
VACUUM tablename;
Reclaim storage occupied by dead tuples
VACUUM ANALYZE tablename;
Update statistics for query optimizer
Metadata Queries
\dt
List all tables
\di
List all indexes
\dv
List all views
\df
List all functions
\du
List all users and roles
Backup & Restore
pg_dump dbname > outfile
Backup a database
pg_dump -t tablename dbname > outfile
Backup a specific table
psql dbname < infile
Restore from a backup file
pg_dumpall > outfile
Backup all databases
pg_restore -d dbname backupfile
Restore from a pg_dump backup