5/6/22, 2:05 PM 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 )

https://gist.github.com/Kartones/dd3ff5ec5ea238d4c546 1/12
5/6/22, 2:05 PM PostgreSQL command line cheatsheet

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

2. 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

3. 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;

https://gist.github.com/Kartones/dd3ff5ec5ea238d4c546 2/12
5/6/22, 2:05 PM PostgreSQL command line cheatsheet

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

https://gist.github.com/Kartones/dd3ff5ec5ea238d4c546 3/12
5/6/22, 2:05 PM PostgreSQL command line cheatsheet

Execution data:
Queries being executed at a certain DB:

SELECT datname, application_name, pid, backend_start, query_start, state_cha

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,

https://gist.github.com/Kartones/dd3ff5ec5ea238d4c546 4/12
5/6/22, 2:05 PM PostgreSQL command line cheatsheet

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 command: Import/export from CSV to tables:

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;

https://gist.github.com/Kartones/dd3ff5ec5ea238d4c546 5/12
5/6/22, 2:05 PM PostgreSQL command line cheatsheet

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
ptop  and  pg_top :  top  for PG. Available on the APT repository
from  apt.postgresql.org .
pg_activity: Command line tool for PostgreSQL server activity monitoring.
Unix-like reverse search in psql:

$ 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

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!

https://gist.github.com/Kartones/dd3ff5ec5ea238d4c546 6/12
5/6/22, 2:05 PM PostgreSQL command line cheatsheet

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)

Load earlier comments...

vigneshrajarr commented on May 10, 2019 • edited 

This might help to find the size of all tables, their indexes and total database size.

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;

Refer here for complete explanation.

Sparksss commented on Jun 15, 2019

Sorry, sometimes i use \i 'path' for restore db from sql file.

twome commented on Jul 1, 2019

Great gist, thanks for your effort here.

https://gist.github.com/Kartones/dd3ff5ec5ea238d4c546 7/12
5/6/22, 2:05 PM PostgreSQL command line cheatsheet

Kartones commented on Jul 2, 2019

@eodenheimer

how do you create a new user?

There's specific  CREATE USER __name__ ...  syntax: https://www.postgresql.org/docs/8.0/sql-

createuser.html

Kartones commented on Jul 2, 2019

@faraonc @maziadi thanks for contributing, added your suggestions!

tomruarol commented on Sep 18, 2019 • edited 

List columns from a specific table:

\d table_name

julian-alarcon commented on Nov 5, 2019 • edited 

\deu+  List all USER MAPPING on server

\des+  List all foreign SERVERS

\dE[S+]  List all foreign tables

krish-penumarty commented on Mar 14, 2020

\dx  to list all extensions installed

loganblackk commented on Apr 7, 2020

This is awesome. Best cheatsheet I've found

Kartones commented on Apr 9, 2020

Thanks @tomruarol, @julian-alarcon & @krish-penumarty, suggestions added :)

emrulemran commented on May 8, 2020

Excellante! Muchas gracias!!

https://gist.github.com/Kartones/dd3ff5ec5ea238d4c546 8/12
5/6/22, 2:05 PM PostgreSQL command line cheatsheet

adminpykar commented on Jun 9, 2020

\?  For Help

It will list out all commands with explanation.

dvdantunes commented on Jul 2, 2020

Thanks @Kartones for this super cheatsheet 

neiodavince commented on Jul 8, 2020 • edited 

Great resource... Thanks for the great cheatsheet

shinlabs commented on Aug 28, 2020

Awesome cheatsheet ! Thank you.

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

nsbernstein52 commented on Sep 10, 2020

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.

https://gist.github.com/Kartones/dd3ff5ec5ea238d4c546 9/12
5/6/22, 2:05 PM PostgreSQL command line cheatsheet

nsbernstein52 commented on Sep 10, 2020

Related: Copying from or to PostgreSQL on AWS:

You must preceded the copy command with a backslash.

\COPY ...

dgreenberg90 commented on Oct 5, 2020

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 commented on Oct 28, 2020

Great gist, thanks

TornadoRadon commented on Nov 23, 2020

super cool sheet! saves my day 

cag000 commented on Dec 11, 2020

Thanks.., made my day     

julian-alarcon commented on Dec 17, 2020 • edited 

--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

https://gist.github.com/Kartones/dd3ff5ec5ea238d4c546 10/12
5/6/22, 2:05 PM PostgreSQL command line cheatsheet

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 commented on Jan 4, 2021

https://gist.github.com/Kartones/dd3ff5ec5ea238d4c546 11/12
5/6/22, 2:05 PM PostgreSQL command line cheatsheet

Informative Thanks for your great effort for making the blog.

ganesanb4j commented on Jan 11, 2021

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

Kartones commented on Jan 12, 2021

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 commented on Jan 28, 2021 • edited 

@Kartones Thank you for putting this together!

I find this useful too - to clear screen

\! clear

bryaneaton commented on Jul 20, 2021

This is fantastic, thanks

ShabbirK52 commented on Oct 30, 2021

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

Kartones commented on Oct 30, 2021

@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 commented on Dec 12, 2021

THANKS MANN, your gist helps me a LOT :D

https://gist.github.com/Kartones/dd3ff5ec5ea238d4c546 12/12