PSQL

Copy
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 .`

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

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:

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

Copy
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

Copy
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`):

Copy
log_line_prefix = '%t %u %d %a '

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:

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

Copy
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):

Copy
SELECT * FROM pg_stat_activity WHERE waiting='t'

• Currently running queries with process pid:

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

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

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:

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

• 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.
• psql -c "\l+" -H -q postgres > out.html: Generate a html report of your databases