PostgreSQL Cheatsheet
Quick reference for connecting to PostgreSQL, managing databases and roles, granting privileges, and running backup commands.
PostgreSQL handles everything from local development databases to production application backends. This cheatsheet covers common `psql` connection patterns, database and role management, privilege commands, introspection queries, and backup basics.
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 |