PostgreSQL Cheatsheet
Basic Syntax
Core PostgreSQL command forms.
| Command | Description |
|---|---|
psql |
Open an interactive PostgreSQL shell using local defaults |
psql -U user -d dbname |
Connect as a specific user to a specific database |
psql -h host -p 5432 -U user -d dbname |
Connect to a remote PostgreSQL server |
psql -c "SQL_STATEMENT" |
Run one SQL command and exit |
sudo -u postgres psql |
Open psql as the local postgres superuser |
Connect and Switch
Common ways to connect and move between databases.
| Command | Description |
|---|---|
sudo -u postgres psql |
Connect locally as the postgres system user |
psql -U app_user -d app_db |
Connect to app_db as app_user |
psql "host=localhost port=5432 dbname=app_db user=app_user" |
Connect with a connection string |
c app_db |
Switch to another database inside psql |
conninfo |
Show the current connection details |
Databases
Create, list, rename, and remove databases.
| Command | Description |
|---|---|
CREATE DATABASE app_db; |
Create a new database |
CREATE DATABASE app_db OWNER app_user; |
Create a database owned by a specific role |
l |
List databases |
ALTER DATABASE app_db RENAME TO app_prod; |
Rename a database |
DROP DATABASE app_db; |
Delete a database |
Roles and Users
Create login roles and inspect existing roles.
| Command | Description |
|---|---|
CREATE ROLE app_user; |
Create a role without login |
CREATE ROLE app_user WITH LOGIN PASSWORD 'strong_password'; |
Create a login role |
CREATE USER app_user WITH PASSWORD 'strong_password'; |
Shortcut for a login role |
ALTER ROLE app_user WITH PASSWORD 'new_password'; |
Change a role password |
du |
List roles and attributes |
Grant and Revoke Privileges
Give or remove access at the database, schema, and table levels.
| Command | Description |
|---|---|
GRANT CONNECT ON DATABASE app_db TO app_user; |
Allow a role to connect to a database |
GRANT USAGE, CREATE ON SCHEMA public TO app_user; |
Allow schema access and object creation |
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLE orders TO app_user; |
Grant table privileges |
REVOKE INSERT, UPDATE ON TABLE orders FROM app_user; |
Remove selected table privileges |
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO app_user; |
Grant defaults for future tables |
Table and Schema Introspection
Inspect schemas, tables, columns, and query results.
| Command | Description |
|---|---|
dn |
List schemas |
dt |
List tables in the current search path |
dt public.* |
List tables in the public schema |
d orders |
Describe a table, view, or sequence |
SELECT * FROM orders LIMIT 10; |
Preview rows from a table |
psql Meta-Commands
Useful built-in psql commands for daily administration.
| Command | Description |
|---|---|
? |
Show psql meta-command help |
h CREATE ROLE |
Show SQL help for one statement |
x |
Toggle expanded output for wide rows |
timing |
Toggle query timing display |
q |
Quit psql |
Backup and Restore
Common logical backup and restore commands.
| Command | Description |
|---|---|
pg_dump -U app_user -d app_db > app_db.sql |
Export a database as plain SQL |
pg_dump -Fc -U app_user -d app_db -f app_db.dump |
Create a custom-format backup |
psql -U app_user -d app_db < app_db.sql |
Restore a plain SQL dump |
pg_restore -U app_user -d app_db app_db.dump |
Restore a custom-format dump |
pg_dumpall > cluster.sql |
Back up all databases and global objects |
Version and Service Checks
Quick checks for server version and service status.
| Command | Description |
|---|---|
SELECT version(); |
Show the PostgreSQL server version |
psql --version |
Show the client version |
SHOW server_version; |
Show the server version only |
sudo systemctl status postgresql |
Check the PostgreSQL service state |
sudo systemctl restart postgresql |
Restart the PostgreSQL service |
Related Guides
Use these guides for full PostgreSQL walkthroughs.
| Guide | Description |
|---|---|
| PostgreSQL User Management: Create Users and Grant Privileges | Full guide to roles, passwords, and grants |
| How to Check the PostgreSQL Version | Find the installed and running PostgreSQL version |
| How to Install PostgreSQL on Ubuntu 20.04 | Install PostgreSQL on Ubuntu |
| How to Install PostgreSQL on Debian 10 | Install PostgreSQL on Debian |
| How to Install PostgreSQL on CentOS 8 | Install PostgreSQL on CentOS |
AI Assistant
