1. list of table indexes:
SELECT
tablename,
indexname,
indexdef
FROM
pg_indexes
WHERE
schemaname = 'public’

\d table_name

2. DB & tables related

• \q: Quit/Exit
• \c __database__: Connect to a database
• \d __table__: Show table definition 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
• \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

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

4. Service management commands:

sudo service postgresql stop
sudo service postgresql start
sudo service postgresql restart

5. 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 '

6. SQL 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

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

7. Casting:

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

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

9. Generating random data (source):

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