Skip to content

Instantly share code, notes, and snippets.

@Kartones
Last active November 2, 2024 03:58
Show Gist options
  • Save Kartones/dd3ff5ec5ea238d4c546 to your computer and use it in GitHub Desktop.
Save Kartones/dd3ff5ec5ea238d4c546 to your computer and use it in GitHub Desktop.
PostgreSQL command line cheatsheet

PSQL

Magic words:

psql -U postgres

Some interesting flags (to see all, use -h or --help depending on your psql version):

  • -E: will describe the underlaying queries of the \ commands (cool for learning!)
  • -l: psql will list all databases and then exit (useful if the user you connect with doesn't has a default database, like at AWS RDS)

Most \d commands support additional param of __schema__.name__ and accept wildcards like *.*

  • \?: Show help (list of available commands with an explanation)
  • \q: Quit/Exit
  • \c __database__: Connect to a database
  • \d __table__: Show table definition (columns, etc.) including triggers
  • \d+ __table__: More detailed table definition including description and physical disk size
  • \l: List databases
  • \dy: List events
  • \df: List functions
  • \di: List indexes
  • \dn: List schemas
  • \dt *.*: List tables from all schemas (if *.* is omitted will only show SEARCH_PATH ones)
  • \dT+: List all data types
  • \dv: List views
  • \dx: List all extensions installed
  • \df+ __function__ : Show function SQL code.
  • \x: Pretty-format query results instead of the not-so-useful ASCII tables
  • \copy (SELECT * FROM __table_name__) TO 'file_path_and_name.csv' WITH CSV: Export a table as CSV
  • \des+: List all foreign servers
  • \dE[S+]: List all foreign tables
  • \! __bash_command__: execute __bash_command__ (e.g. \! ls)

User Related:

  • \du: List users
  • \du __username__: List a username if present.
  • create role __test1__: Create a role with an existing username.
  • create role __test2__ noinherit login password __passsword__;: Create a role with username and password.
  • set role __test__;: Change role for current session to __test__.
  • grant __test2__ to __test1__;: Allow __test1__ to set its role as __test2__.
  • \deu+: List all user mapping on server

Configuration

  • Service management commands:
sudo service postgresql stop
sudo service postgresql start
sudo service postgresql restart
  • Changing verbosity & querying Postgres log:
    1) First edit the config file, set a decent verbosity, save and restart postgres:
sudo vim /etc/postgresql/9.3/main/postgresql.conf

# Uncomment/Change inside:
log_min_messages = debug5
log_min_error_statement = debug5
log_min_duration_statement = -1

sudo service postgresql restart
  1. Now you will get tons of details of every statement, error, and even background tasks like VACUUMs
tail -f /var/log/postgresql/postgresql-9.3-main.log
  1. How to add user who executed a PG statement to log (editing postgresql.conf):
log_line_prefix = '%t %u %d %a '
  • Check Extensions enabled in postgres: SELECT * FROM pg_extension;

  • Show available extensions: SELECT * FROM pg_available_extension_versions;

Create command

There are many CREATE choices, like CREATE DATABASE __database_name__, CREATE TABLE __table_name__ ... Parameters differ but can be checked at the official documentation.

Handy queries

  • SELECT * FROM pg_proc WHERE proname='__procedurename__': List procedure/function
  • SELECT * FROM pg_views WHERE viewname='__viewname__';: List view (including the definition)
  • SELECT pg_size_pretty(pg_total_relation_size('__table_name__'));: Show DB table space in use
  • SELECT pg_size_pretty(pg_database_size('__database_name__'));: Show DB space in use
  • show statement_timeout;: Show current user's statement timeout
  • SELECT * FROM pg_indexes WHERE tablename='__table_name__' AND schemaname='__schema_name__';: Show table indexes
  • Get all indexes from all tables of a schema:
SELECT
   t.relname AS table_name,
   i.relname AS index_name,
   a.attname AS column_name
FROM
   pg_class t,
   pg_class i,
   pg_index ix,
   pg_attribute a,
    pg_namespace n
WHERE
   t.oid = ix.indrelid
   AND i.oid = ix.indexrelid
   AND a.attrelid = t.oid
   AND a.attnum = ANY(ix.indkey)
   AND t.relnamespace = n.oid
    AND n.nspname = 'kartones'
ORDER BY
   t.relname,
   i.relname
  • Execution data:
    • Queries being executed at a certain DB:
SELECT datname, application_name, pid, backend_start, query_start, state_change, state, query 
  FROM pg_stat_activity 
  WHERE datname='__database_name__';
  • Get all queries from all dbs waiting for data (might be hung):
SELECT * FROM pg_stat_activity WHERE waiting='t'
  • Currently running queries with process pid:
SELECT 
  pg_stat_get_backend_pid(s.backendid) AS procpid, 
  pg_stat_get_backend_activity(s.backendid) AS current_query
FROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;
  • Get Connections by Database: SELECT datname, numbackends FROM pg_stat_database;

Casting:

  • CAST (column AS type) or column::type
  • '__table_name__'::regclass::oid: Get oid having a table name

Query analysis:

  • EXPLAIN __query__: see the query plan for the given query
  • EXPLAIN ANALYZE __query__: see and execute the query plan for the given query
  • ANALYZE [__table__]: collect statistics

Generating random data (source):

  • INSERT INTO some_table (a_float_value) SELECT random() * 100000 FROM generate_series(1, 1000000) i;

Get sizes of tables, indexes and full DBs:

select current_database() as database,
  pg_size_pretty(total_database_size) as total_database_size,
  schema_name,
  table_name,
  pg_size_pretty(total_table_size) as total_table_size,
  pg_size_pretty(table_size) as table_size,
  pg_size_pretty(index_size) as index_size
  from ( select table_name,
          table_schema as schema_name,
          pg_database_size(current_database()) as total_database_size,
          pg_total_relation_size(table_name) as total_table_size,
          pg_relation_size(table_name) as table_size,
          pg_indexes_size(table_name) as index_size
          from information_schema.tables
          where table_schema=current_schema() and table_name like 'table_%'
          order by total_table_size
      ) as sizes;
COPY table_name [ ( column_name [, ...] ) ]
FROM { 'filename' | STDIN }
[ [ WITH ] ( option [, ...] ) ]

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
TO { 'filename' | STDOUT }
[ [ WITH ] ( option [, ...] ) ]
  • List all grants for a specific user
SELECT table_catalog, table_schema, table_name, privilege_type
FROM   information_schema.table_privileges
WHERE  grantee = 'user_to_check' ORDER BY table_name;
  • List all assigned user roles
SELECT
    r.rolname,
    r.rolsuper,
    r.rolinherit,
    r.rolcreaterole,
    r.rolcreatedb,
    r.rolcanlogin,
    r.rolconnlimit,
    r.rolvaliduntil,
    ARRAY(SELECT b.rolname
      FROM pg_catalog.pg_auth_members m
      JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
      WHERE m.member = r.oid) as memberof, 
    r.rolreplication
FROM pg_catalog.pg_roles r
ORDER BY 1;
  • Check permissions in a table:
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name='name-of-the-table';
  • Kill all Connections:
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname = current_database() AND pid <> pg_backend_pid();

Keyboard shortcuts

  • CTRL + R: reverse-i-search

Tools

$ echo "bind "^R" em-inc-search-prev" > $HOME/.editrc
$ source $HOME/.editrc
  • Show IP of the DB Instance: SELECT inet_server_addr();
  • File to save PostgreSQL credentials and permissions (format: hostname:port:database:username:password): chmod 600 ~/.pgpass
  • Collect statistics of a database (useful to improve speed after a Database Upgrade as previous query plans are deleted): ANALYZE VERBOSE;
  • To obtain the CREATE TABLE query of a table, any visual GUI like pgAdmin allows to easily, but else you can use pg_dump, e.g.: pg_dump -t '<schema>.<table>' --schema-only <database> (source)

Resources & Documentation

  • Operations Cheat Sheet: Official PG wiki cheat sheet with an amazing amount of explanations of many topics, features, and many many internal implementation details
  • Postgres Weekly newsletter: The best way IMHO to keep up to date with PG news
  • 100 psql Tips: Name says all, lots of useful tips!
  • PostgreSQL Exercises: An awesome resource to learn to learn SQL, teaching you with simple examples in a great visual way. Highly recommended.
  • A Performance Cheat Sheet for PostgreSQL: Great explanations of EXPLAIN, EXPLAIN ANALYZE, VACUUM, configuration parameters and more. Quite interesting if you need to tune-up a postgres setup.
  • annotated.conf: Annotations of all 269 postgresql.conf settings for PostgreSQL 10.
  • psql -c "\l+" -H -q postgres > out.html: Generate a html report of your databases (source: Daniel Westermann)
@dvdantunes
Copy link

Thanks @Kartones for this super cheatsheet ☺️

@neiodavince
Copy link

neiodavince commented Jul 7, 2020

Great resource... Thanks for the great cheatsheet

@shinlabs
Copy link

Awesome cheatsheet ! Thank you.

Little suggestion : execute bash command. For example \! ls

@nsbernstein52
Copy link

copying CSV files from (mentioned above) or to PostgreSQL:
https://www.postgresql.org/docs/9.2/sql-copy.html

COPY table_name [ ( column_name [, ...] ) ]
FROM { 'filename' | STDIN }
[ [ WITH ] ( option [, ...] ) ]

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
TO { 'filename' | STDOUT }
[ [ WITH ] ( option [, ...] ) ]

There are 10 very useful options.

Related: Using MS Excel to manipulate large datasets:
Each spreadsheet can handle slightly over 1,000,000 rows.
It is simple to save the spreadsheets as CSV files.

I was able to prepare ~60,000,000 records in 60 xlsx files, and then I copied them into PG, across 5 tables. It took ~1.5 hours, mostly unattended, and was fairly straightforward. Just decide whether you are going to use a header row (I strongly recommend doing so) and if you want the copy to include the primary key. You can find numerous examples online.

@nsbernstein52
Copy link

Related: Copying from or to PostgreSQL on AWS:
You must preceded the copy command with a backslash.
\COPY ...

@dgreenberg90
Copy link

How do we change the default user when using psql in the CLI?
I had installed Postgres 12 before and then uninstalled it entirely (and deleted the entire Postgres 12 folder) and just installed version 13. I used "postgres" as the username, but it keeps showing "dgree" as my default username. I just don't want to have to type psql -U postgres every single time since there is not even a user named "dgree" when I use the \du command.

@starlingvibes
Copy link

Great gist, thanks

@TornadoRadon
Copy link

❄super cool sheet! saves my day 👍👍

@syahrulrasyid
Copy link

Thanks.., made my day 👍 👍 👍

@julian-alarcon
Copy link

julian-alarcon commented Dec 16, 2020

--Show IP of the DB Instance
SELECT inet_server_addr();

--List all grants for an specific user

SELECT table_catalog, table_schema, table_name, privilege_type
FROM   information_schema.table_privileges
WHERE  grantee = 'user_to_check' ORDER BY table_name;

--List all assigned roles for the users

    r.rolname,
    r.rolsuper,
    r.rolinherit,
    r.rolcreaterole,
    r.rolcreatedb,
    r.rolcanlogin,
    r.rolconnlimit, r.rolvaliduntil,
  ARRAY(SELECT b.rolname
    FROM pg_catalog.pg_auth_members m
    JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
    WHERE m.member = r.oid) as memberof
, r.rolreplication
FROM pg_catalog.pg_roles r
ORDER BY 1;

--Check permissions in a table

FROM information_schema.role_table_grants
WHERE table_name='name-of-the-table';

--Check Extensions enabled in postgres
SELECT * FROM pg_extension;

--Show available extensions
SELECT * FROM pg_available_extension_versions;

--Connections by Database
SELECT datname, numbackends FROM pg_stat_database;

--Check current queries

FROM pg_stat_activity
WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;

--Kill all Connections

FROM pg_stat_activity
WHERE datname = current_database() AND pid <> pg_backend_pid();

-- File to save PostgreSQL credentials and permissions (hostname:port:database:username:password)
chmod 600 ~/.pgpass

--Collect statistics of a database (useful to improve speed after a Database Upgrade as previous query plans are deleted)
ANALYZE VERBOSE;

@prathapreddy57
Copy link

Informative Thanks for your great effort for making the blog.

@ganesanb4j
Copy link

Very useful. Are there any resources for Postgres best practices being used in production?

@Kartones
Copy link
Author

Many many thanks @julian-alarcon! I'll update with all your suggestions 🙏

@ganesanb4j I'll add the resource I most like to keep up to date with PG, the Postgres Weekly newsletter. That's really the only recommendation I can provide you with, but I really like it 😃

@prashanta
Copy link

prashanta commented Jan 27, 2021

@Kartones Thank you for putting this together!

I find this useful too - to clear screen
\! clear

@bryaneaton
Copy link

This is fantastic, thanks

@ShabbirK52
Copy link

How to view query used to create a particular table in a db?

@Kartones
Copy link
Author

@ShabbirK52 Updated the Tools section with how to do it (I've always either done a full DB dump/export, or used a GUI to do it).

@damarowen
Copy link

THANKS MANN, your gist helps me a LOT :D

@miguelff
Copy link

miguelff commented Jul 4, 2022

Just stumbled accross this. Thank you @Kartones, really useful!

@manjulananduri
Copy link

Thanks for the cheatsheet. To extend this I've created a small cheatsheet to compare PostgreSQL with MySQL syntax, you can check it out here : https://tipseason.com/postgres-vs-mysql-comparision-cheatsheet/

@thiagozs
Copy link

Very nice tips! Thank you

@EliteLabyrinth
Copy link

❤️

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment