DEV Community

Vincent Tommi
Vincent Tommi

Posted on

🚀 The Ultimate PostgreSQL psql Cheat Sheet & Admin Guide

#webdev #programming #beginners #postgres

Audience: Beginners → Intermediate PostgreSQL users, DevOps, Back-end Developers.
📌 Table of Contents

  1. 🔐 Connecting to PostgreSQL

  2. 🧭 Exploring Databases, Tables, and Schemas

  3. 👤 User Management

  4. 🛠 Schema, Role & Privilege Fixes

  5. 🧪 Query Debugging & Monitoring

  6. 📦 Importing & Exporting Data

  7. 🧮 Disk Usage & Performance

  8. ⚙ Useful Shortcuts & Tips

  9. 📚 Resources

1. 🔐 Connecting to PostgreSQL
Basic login:

psql -U <username> -d <database>
Enter fullscreen mode Exit fullscreen mode

Common options:

| Option | Description                              |
| ------ | ---------------------------------------- |
| `-E`   | Show the underlying SQL for `\` commands |
| `-l`   | List databases and exit                  |
| `-h`   | Show help                                |
| `\q`   | Quit session                             |
Enter fullscreen mode Exit fullscreen mode

2. 🧭 Exploring Databases, Tables, and Schemas

| Command                       | Description                    |
| ----------------------------- | ------------------------------ |
| `\l`                          | List all databases             |
| `\c db_name`                  | Connect to a database          |
| `\dt`                         | List tables                    |
| `\dt *.*`                     | List all tables in all schemas |
| `\d table_name`               | Show table structure           |
| `\d+ table`                   | Show table + size + comments   |
| `\dn`                         | List schemas                   |
| `\df`                         | List functions                 |
| `\dv`                         | List views                     |
| `\dx`                         | List installed extensions      |
| `SELECT * FROM pg_extension;` | Show extension info            |
Enter fullscreen mode Exit fullscreen mode

3. 👤 User Management & Roles
View users & roles:

\du
\du username
Enter fullscreen mode Exit fullscreen mode

Create role:

CREATE ROLE new_user LOGIN PASSWORD 'secret';
Enter fullscreen mode Exit fullscreen mode

Grant privileges:

GRANT ALL PRIVILEGES ON DATABASE your_db TO new_user;
GRANT USAGE, CREATE ON SCHEMA public TO new_user;
Enter fullscreen mode Exit fullscreen mode

Role membership:

GRANT role_b TO role_a;  -- role_a inherits permissions of role_b
Enter fullscreen mode Exit fullscreen mode

See all grants:

SELECT table_catalog, table_schema, table_name, privilege_type
FROM information_schema.table_privileges
WHERE grantee = 'username';
Enter fullscreen mode Exit fullscreen mode

4. 🛠 Schema, Role & Privilege Fixes
Fixing permission denied for schema public errors:

-- Replace with your actual DB user
ALTER SCHEMA public OWNER TO your_db_user;
GRANT ALL ON SCHEMA public TO your_db_user;
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO your_db_user;
GRANT USAGE, CREATE ON SCHEMA public TO your_db_user;
Enter fullscreen mode Exit fullscreen mode

Check schema owners:

SELECT schema_name, schema_owner
FROM information_schema.schemata
WHERE schema_name = 'public';
Enter fullscreen mode Exit fullscreen mode

5. 🧪 Query Debugging & Monitoring
Currently running queries:

SELECT datname, pid, state, query, query_start
FROM pg_stat_activity
WHERE state <> 'idle';
Enter fullscreen mode Exit fullscreen mode

Show backend processes:

SELECT pg_stat_get_backend_pid(s.backendid) AS pid,
       pg_stat_get_backend_activity(s.backendid) AS query
FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;
Enter fullscreen mode Exit fullscreen mode

Terminate all other connections:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE datname = 'your_db' AND pid <> pg_backend_pid();
Enter fullscreen mode Exit fullscreen mode

Analyze queries:

EXPLAIN ANALYZE SELECT * FROM your_table;
Enter fullscreen mode Exit fullscreen mode

Verbose logging (PostgreSQL config):
Edit postgresql.conf:

log_min_messages = debug5
log_min_error_statement = debug5
log_min_duration_statement = -1
Enter fullscreen mode Exit fullscreen mode

Then restart:

sudo service postgresql restart
Enter fullscreen mode Exit fullscreen mode

6. 📦 Importing & Exporting Data
Export to CSV:

\copy (SELECT * FROM your_table) TO 'output.csv' WITH CSV;
Enter fullscreen mode Exit fullscreen mode

Import from CSV:

\copy your_table FROM 'input.csv' WITH CSV;
Enter fullscreen mode Exit fullscreen mode

7.** 🧮 Disk Usage & Performance
**Table sizes:

SELECT
  table_name,
  pg_size_pretty(pg_total_relation_size(table_name)) AS total_size
FROM information_schema.tables
WHERE table_schema = 'public';
Enter fullscreen mode Exit fullscreen mode

Database size:

SELECT pg_size_pretty(pg_database_size('your_db'));
Enter fullscreen mode Exit fullscreen mode

8. ⚙ Useful Shortcuts & Tips

| Shortcut   | Action                         |
| ---------- | ------------------------------ |
| `\x`       | Pretty format output           |
| `\! ls`    | Run shell command              |
| `CTRL + R` | Reverse search command history |
| `\?`       | Show command help              |
Enter fullscreen mode Exit fullscreen mode

9. 📚 Resources

  • 🔗 PostgreSQL Official Docs

  • 🧠 Awesome PostgreSQL

  • 🛠 GUI tools: pgAdmin, DBeaver, TablePlus

  • 🧪 CLI tools: pg_top, pg_activity

Top comments (0)