PostgreSQL cheatsheet

postgresql-cheatsheet.md

PSQL

psql -U postgres

If run with -E flag, it will describe the underlaying queries of the \ commands (cool for learning!).

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

Command	Description
\q	Quit/Exit
\c :database	Connect to a database
\d :table	Show table definition including triggers
\dt *.*	List tables from all schemas (if *.* is omitted will only show SEARCH_PATH ones)
\l	List databases
\dn	List schemas
\du	List roles
\df	List functions
\df+ :function	Show function SQL code. \x before pretty-formats it
\dv	List views
\x	Pretty-format query results instead of the not-so-useful ASCII tables

Typical setup for new site

createuser <username> -P
createdb <database_name> -O <username>

User Related:

Command	Description
\du	List users
\du :username	List a username if present
select rolname FROM pg_roles;	See existing roles
create role :role1	Create a role with an existing username
create role :role2 noinherit login password :password;	Create a role with username and password
set role :test3;	Change role for current session to :test3
grant :role2 to :role1;	Allow :role1 to set its role as :role2

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 '

Handy queries

# Search using a regular expression 
select column FROM table WHERE column ~ 'foo.*';

# Show DB table space in use
select pg_size_pretty(pg_total_relation_size('__table_name__'));

# Show current user's statement timeout
show statement_timeout;

# Show queries being executed at a certain DB. Can also display query time, etc
select pid, datname, waiting, state, query FROM pg_stat_activity WHERE datname='__database_name__';

# Show all tables in schema
select * from pg_catalog.pg_tables;

# Show table indexes
SELECT * FROM pg_indexes WHERE tablename='__table_name__' AND schemaname='__schema_name__';

# Show all columns for the table
select 
   column_name
   , data_type
   , character_maximum_length
from 
   INFORMATION_SCHEMA.COLUMNS
where 
   table_name = 'excel_report'

# To see where the data directory or via `ps auxw |  grep postgres | grep -- -D`
# http://stackoverflow.com/questions/3004523/postgresql-database-default-location-on-linux/3006482#3006482
show data_directory;

# To see all the run-time parameters
show all;

# To see tablespaces
select * from pg_tablespace;

# 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

# Get all indexes for all non-system tables
SELECT
  U.usename                AS user_name,
  ns.nspname               AS schema_name,
  idx.indrelid :: REGCLASS AS table_name,
  i.relname                AS index_name,
  idx.indisunique          AS is_unique,
  idx.indisprimary         AS is_primary,
  am.amname                AS index_type,
  idx.indkey,
       ARRAY(
           SELECT pg_get_indexdef(idx.indexrelid, k + 1, TRUE)
           FROM
             generate_subscripts(idx.indkey, 1) AS k
           ORDER BY k
       ) AS index_keys,
  (idx.indexprs IS NOT NULL) OR (idx.indkey::int[] @> array[0]) AS is_functional,
  idx.indpred IS NOT NULL AS is_partial
FROM pg_index AS idx
  JOIN pg_class AS i
    ON i.oid = idx.indexrelid
  JOIN pg_am AS am
    ON i.relam = am.oid
  JOIN pg_namespace AS NS ON i.relnamespace = NS.OID
  JOIN pg_user AS U ON i.relowner = U.usesysid
WHERE NOT nspname LIKE 'pg%'; -- Excluding system tables

# Execution info: 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__';

# Execution info: Get all queries from all dbs waiting for data (might be hung): 
SELECT * FROM pg_stat_activity WHERE waiting='t'

# Execution info: 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;

# Space: Database size
SELECT pg_size_pretty(pg_database_size('Database Name'));

# Space: Find top largest tables
SELECT
   relname AS objectname
   , relkind AS objecttype
   , reltuples AS "#entries"
   , pg_size_pretty(relpages::bigint*8*1024) AS size
FROM pg_class
WHERE relpages >= 8
ORDER BY relpages DESC;

Casting:

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

Tools

• pg-top: top for PG. sudo apt-get install ptop + pg_top
• Unix-like reverse search in psql:

$ echo "bind "^R" em-inc-search-prev" > $HOME/.editrc
$ source $HOME/.editrc

References

• PostgreSQL 9.6.1 Documentation.pdf
• PostgreSQL 9.0 Cheat Sheet

Helpful script https://gist.github.com/rgreenjr/3637525