## PSQL<\/p>\n
Magic words:
\n“`bash
\npsql -U postgres
\n“`
\nIf run with `-E` flag, it will describe the underlaying queries of the `\\` commands (cool for learning!).<\/p>\n
Most `\\d` commands support additional param of `__schema__.name__` and accept wildcards like `*.*`<\/p>\n
– `\\q`: Quit\/Exit
\n– `\\c __database__`: Connect to a database
\n– `\\d __table__`: Show table definition including triggers
\n– `\\dt *.*`: List tables from all schemas (if `*.*` is omitted will only show SEARCH_PATH ones)
\n– `\\l`: List databases
\n– `\\dn`: List schemas
\n– `\\df`: List functions
\n– `\\dv`: List views
\n– `\\df+ __function__` : Show function SQL code.
\n– `\\x`: Pretty-format query results instead of the not-so-useful ASCII tables<\/p>\n
User Related:
\n– `\\du`: List users
\n– `\\du __username__`: List a username if present.
\n– `create role __test1__`: Create a role with an existing username.
\n– `create role __test2__ noinherit login password __passsword__;`: Create a role with username and password.
\n– `set role __test__;`: Change role for current session to `__test__`.
\n– `grant __test2__ to __test1__;`: Allow `__test1__` to set its role as `__test2__`.<\/p>\n
## Configuration<\/p>\n
– Service management commands:
\n“`
\nsudo service postgresql stop
\nsudo service postgresql start
\nsudo service postgresql restart
\n“`<\/p>\n
– Changing verbosity & querying Postgres log:
\n
1) First edit the config file, set a decent verbosity, save and restart postgres:
\n“`
\nsudo vim \/etc\/postgresql\/9.3\/main\/postgresql.conf<\/p>\n
# Uncomment\/Change inside:
\nlog_min_messages = debug5
\nlog_min_error_statement = debug5
\nlog_min_duration_statement = -1<\/p>\n
sudo service postgresql restart
\n“`
\n 2) Now you will get tons of details of every statement, error, and even background tasks like VACUUMs
\n“`
\ntail -f \/var\/log\/postgresql\/postgresql-9.3-main.log
\n“`
\n 3) How to add user who executed a PG statement to log (editing `postgresql.conf`):
\n“`
\nlog_line_prefix = ‘%t %u %d %a ‘
\n“`<\/p>\n
## Handy queries
\n– `SELECT * FROM pg_proc WHERE proname=’__procedurename__’`: List procedure\/function
\n– `SELECT * FROM pg_views WHERE viewname=’__viewname__’;`: List view (including the definition)
\n– `SELECT pg_size_pretty(pg_total_relation_size(‘__table_name__’));`: Show DB table space in use
\n– `SELECT pg_size_pretty(pg_database_size(‘__database_name__’));`: Show DB space in use
\n– `show statement_timeout;`: Show current user’s statement timeout
\n– `SELECT * FROM pg_indexes WHERE tablename=’__table_name__’ AND schemaname=’__schema_name__’;`: Show table indexes
\n– Get all indexes from all tables of a schema:
\n“`sql
\nSELECT
\n t.relname AS table_name,
\n i.relname AS index_name,
\n a.attname AS column_name
\nFROM
\n pg_class t,
\n pg_class i,
\n pg_index ix,
\n pg_attribute a,
\n pg_namespace n
\nWHERE
\n t.oid = ix.indrelid
\n AND i.oid = ix.indexrelid
\n AND a.attrelid = t.oid
\n AND a.attnum = ANY(ix.indkey)
\n AND t.relnamespace = n.oid
\n AND n.nspname = ‘kartones’
\nORDER BY
\n t.relname,
\n i.relname
\n“`
\n– Execution data:
\n – Queries being executed at a certain DB:
\n“`sql
\nSELECT datname, application_name, pid, backend_start, query_start, state_change, state, query
\n FROM pg_stat_activity
\n WHERE datname=’__database_name__’;
\n“`
\n – Get all queries from all dbs waiting for data (might be hung):
\n“`sql
\nSELECT * FROM pg_stat_activity WHERE waiting=’t’
\n“`
\n – Currently running queries with process pid:
\n“`sql
\nSELECT pg_stat_get_backend_pid(s.backendid) AS procpid,
\n pg_stat_get_backend_activity(s.backendid) AS current_query
\nFROM (SELECT pg_stat_get_backend_idset() AS backendid) AS s;
\n“`<\/p>\n
Casting:
\n– `CAST (column AS type)` or `column::type`
\n– `’__table_name__’::regclass::oid`: Get oid having a table name<\/p>\n
Query analysis:
\n– `EXPLAIN __query__`: see the query plan for the given query
\n– `EXPLAIN ANALYZE __query__`: see and execute the query plan for the given query
\n– `ANALYZE [__table__]`: collect statistics <\/p>\n
## Tools
\n– [pg-top](http:\/\/ptop.projects.pgfoundry.org\/): `top` for PG. `sudo apt-get install ptop` + `pg_top`
\n– [Unix-like reverse search in psql](https:\/\/dba.stackexchange.com\/questions\/63453\/is-there-a-psql-equivalent-of-bashs-reverse-search-history):
\n“`bash
\n$ echo “bind “^R” em-inc-search-prev” > $HOME\/.editrc
\n$ source $HOME\/.editrc
\n“` <\/p>\n