AdrianTP · December 12, 2016 22:42 · Aug 27, 2016 · Mar 2, 2016 · Feb 18, 2016 · Feb 11, 2016
diff --git a/postgres-cheatsheet.md b/postgres-cheatsheet.md
@@ -17,9 +17,17 @@ Most `\d` commands support additional param of `__schema__.name__` and accept wi
 - `\dn`: List schemas
 - `\df`: List functions
 - `\dv`: List views
-- `\df+ __function` : Show function SQL code. 
+- `\df+ __function__` : Show function SQL code. 
 - `\x`: Pretty-format query results instead of the not-so-useful ASCII 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__`.
+
 ## Configuration
 
 - Service management commands:

diff --git a/postgres-cheatsheet.md b/postgres-cheatsheet.md
@@ -105,4 +105,9 @@ Casting:
 
 
 ## Tools
-- [pg-top](http://ptop.projects.pgfoundry.org/): `top` for PG. `sudo apt-get install ptop` + `pg_top`
+- [pg-top](http://ptop.projects.pgfoundry.org/): `top` for PG. `sudo apt-get install ptop` + `pg_top`
+- [Unix-like reverse search in psql](https://dba.stackexchange.com/questions/63453/is-there-a-psql-equivalent-of-bashs-reverse-search-history):
+```bash
+$ echo "bind "^R" em-inc-search-prev" > $HOME/.editrc
+$ source $HOME/.editrc
+``` 
diff --git a/postgres-cheatsheet.md b/postgres-cheatsheet.md
@@ -35,8 +35,9 @@ sudo service postgresql restart
 sudo vim /etc/postgresql/9.3/main/postgresql.conf
 
 # Uncomment/Change inside:
-log_min_messages = debug2
-log_min_error_statement = debug2
+log_min_messages = debug5
+log_min_error_statement = debug5
+log_min_duration_statement = -1
 
 sudo service postgresql restart
 ```

diff --git a/postgres-cheatsheet.md b/postgres-cheatsheet.md
@@ -17,7 +17,8 @@ Most `\d` commands support additional param of `__schema__.name__` and accept wi
 - `\dn`: List schemas
 - `\df`: List functions
 - `\dv`: List views
-- `\df+ __function` : Show function SQL code. `\x` before pretty-formats it
+- `\df+ __function` : Show function SQL code. 
+- `\x`: Pretty-format query results instead of the not-so-useful ASCII tables
 
 ## Configuration
 

diff --git a/postgres-cheatsheet.md b/postgres-cheatsheet.md
@@ -82,7 +82,9 @@ ORDER BY
 - Execution data:
   - Queries being executed at a certain DB:
 ```sql
-SELECT datname, application_name, pid, backend_start, query_start, state_change, state, query FROM pg_stat_activity WHERE datname='__database_name__';
+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): 
 ```sql

diff --git a/postgres-cheatsheet.md b/postgres-cheatsheet.md
@@ -82,7 +82,7 @@ ORDER BY
 - Execution data:
   - Queries being executed at a certain DB:
 ```sql
-SELECT pid, datname, waiting, state, query FROM pg_stat_activity WHERE datname='__database_name__';
+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): 
 ```sql

diff --git a/postgres-cheatsheet.md b/postgres-cheatsheet.md
@@ -29,7 +29,7 @@ sudo service postgresql restart
 ```
 
 - Changing verbosity & querying Postgres log:
-  1) First edit the config file, set a decent verbosity, save and restart postgres:
+  <br/>1) First edit the config file, set a decent verbosity, save and restart postgres:
 ```
 sudo vim /etc/postgresql/9.3/main/postgresql.conf
 

diff --git a/postgres-cheatsheet.md b/postgres-cheatsheet.md
@@ -29,7 +29,7 @@ sudo service postgresql restart
 ```
 
 - Changing verbosity & querying Postgres log:
-  - First edit the config file, set a decent verbosity, save and restart postgres:
+  1) First edit the config file, set a decent verbosity, save and restart postgres:
 ```
 sudo vim /etc/postgresql/9.3/main/postgresql.conf
 
@@ -39,11 +39,11 @@ log_min_error_statement = debug2
 
 sudo service postgresql restart
 ```
-  - Now you will get tons of details of every statement, error, and even background tasks like VACUUMs
+  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
 ```
-  - How to add user who executed a PG statement to log (editing `postgresql.conf`):
+  3) How to add user who executed a PG statement to log (editing `postgresql.conf`):
 ```
 log_line_prefix = '%t %u %d %a '
 ```

diff --git a/postgres-cheatsheet.md b/postgres-cheatsheet.md
@@ -21,8 +21,15 @@ Most `\d` commands support additional param of `__schema__.name__` and accept wi
 
 ## Configuration
 
-Changing verbosity & querying Postgres log:
-- First edit the config file, set a decent verbosity, save and restart postgres:
+- Service management commands:
+```
+sudo service postgresql stop
+sudo service postgresql start
+sudo service postgresql restart
+```
+
+- Changing verbosity & querying Postgres log:
+  - First edit the config file, set a decent verbosity, save and restart postgres:
 ```
 sudo vim /etc/postgresql/9.3/main/postgresql.conf
 
@@ -32,11 +39,11 @@ log_min_error_statement = debug2
 
 sudo service postgresql restart
 ```
-- Now you will get tons of details of every statement, error, and even background tasks like VACUUMs
+  - 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
 ```
-- How to add user who executed a PG statement to log (editing `postgresql.conf`):
+  - How to add user who executed a PG statement to log (editing `postgresql.conf`):
 ```
 log_line_prefix = '%t %u %d %a '
 ```

diff --git a/postgres-cheatsheet.md b/postgres-cheatsheet.md
@@ -51,38 +51,38 @@ log_line_prefix = '%t %u %d %a '
 - `SELECT * FROM pg_indexes WHERE tablename='__table_name__' AND schemaname='__schema_name__';`: Show table indexes
 - Get all indexes from all tables of a schema:
 ```sql
-select
-   t.relname as table_name,
-   i.relname as index_name,
-   a.attname as column_name
-from
+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
+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
+   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:
-```
+```sql
 SELECT pid, datname, waiting, state, query FROM pg_stat_activity WHERE datname='__database_name__';
 ```
   - Get all queries from all dbs waiting for data (might be hung): 
-```
+```sql
 SELECT * FROM pg_stat_activity WHERE waiting='t'
 ```
   - Currently running queries with process pid:
-```
+```sql
 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;

diff --git a/postgres-cheatsheet.md b/postgres-cheatsheet.md
@@ -48,7 +48,6 @@ log_line_prefix = '%t %u %d %a '
 - `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 pid, datname, waiting, state, query FROM pg_stat_activity WHERE datname='__database_name__';`: Show queries being executed at a certain DB. Can also display query time, etc.
 - `SELECT * FROM pg_indexes WHERE tablename='__table_name__' AND schemaname='__schema_name__';`: Show table indexes
 - Get all indexes from all tables of a schema:
 ```sql
@@ -73,8 +72,16 @@ order by
    t.relname,
    i.relname
 ```
-- `select * from pg_stat_activity where waiting='t'`: Get all queries waiting for data (that might be hung)
-- Get all currently executing queries:
+- Execution data:
+  - Queries being executed at a certain DB:
+```
+SELECT pid, datname, waiting, 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

diff --git a/postgres-cheatsheet.md b/postgres-cheatsheet.md
@@ -1,3 +1,6 @@
+
+## PSQL
+
 Magic words:
 ```bash
 psql -U postgres
@@ -16,9 +19,7 @@ Most `\d` commands support additional param of `__schema__.name__` and accept wi
 - `\dv`: List views
 - `\df+ __function` : Show function SQL code. `\x` before pretty-formats it
 
-Casting:
-- `CAST (column AS type)` or `column::type`
-- `'__table_name__'::regclass::oid`: Get oid having a table name
+## Configuration
 
 Changing verbosity & querying Postgres log:
 - First edit the config file, set a decent verbosity, save and restart postgres:
@@ -41,7 +42,7 @@ log_line_prefix = '%t %u %d %a '
 ```
 
 
-SQL queries:
+## 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
@@ -78,4 +79,12 @@ order by
 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
+
+
+## Tools
+- [pg-top](http://ptop.projects.pgfoundry.org/): `top` for PG. `sudo apt-get install ptop` + `pg_top`
diff --git a/postgres-cheatsheet.md b/postgres-cheatsheet.md
@@ -75,6 +75,7 @@ order by
 - `select * from pg_stat_activity where waiting='t'`: Get all queries waiting for data (that might be hung)
 - Get all currently executing queries:
 ```
-SELECT pg_stat_get_backend_pid(s.backendid) AS procpid, pg_stat_get_backend_activity(s.backendid) AS current_query
+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;
 ```
diff --git a/postgres-cheatsheet.md b/postgres-cheatsheet.md
@@ -72,4 +72,9 @@ order by
    t.relname,
    i.relname
 ```
-- `select * from pg_stat_activity where waiting='t'`: Get all queries waiting for data (that might be hung)
+- `select * from pg_stat_activity where waiting='t'`: Get all queries waiting for data (that might be hung)
+- Get all currently executing queries:
+```
+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;
+```
diff --git a/postgres-cheatsheet.md b/postgres-cheatsheet.md
@@ -35,6 +35,11 @@ sudo service postgresql restart
 ```
 tail -f /var/log/postgresql/postgresql-9.3-main.log
 ```
+- How to add user who executed a PG statement to log (editing `postgresql.conf`):
+```
+log_line_prefix = '%t %u %d %a '
+```
+
 
 SQL queries:
 - `SELECT * FROM pg_proc WHERE proname='__procedurename__'`: List procedure/function

diff --git a/postgres-cheatsheet.md b/postgres-cheatsheet.md
@@ -66,4 +66,5 @@ where
 order by
    t.relname,
    i.relname
-```
+```
+- `select * from pg_stat_activity where waiting='t'`: Get all queries waiting for data (that might be hung)
diff --git a/postgres-cheatsheet.md b/postgres-cheatsheet.md
@@ -20,6 +20,22 @@ Casting:
 - `CAST (column AS type)` or `column::type`
 - `'__table_name__'::regclass::oid`: Get oid having a table name
 
+Changing verbosity & querying Postgres log:
+- 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 = debug2
+log_min_error_statement = debug2
+
+sudo service postgresql restart
+```
+- 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
+```
+
 SQL queries:
 - `SELECT * FROM pg_proc WHERE proname='__procedurename__'`: List procedure/function
 - `SELECT * FROM pg_views WHERE viewname='__viewname__';`: List view (including the definition)

diff --git a/postgres-cheatsheet.md b/postgres-cheatsheet.md
@@ -2,6 +2,7 @@ Magic words:
 ```bash
 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 `*.*`
 

diff --git a/postgres-cheatsheet.md b/postgres-cheatsheet.md
@@ -14,18 +14,19 @@ Most `\d` commands support additional param of `__schema__.name__` and accept wi
 - `\df`: List functions
 - `\dv`: List views
 - `\df+ __function` : Show function SQL code. `\x` before pretty-formats it
+
+Casting:
+- `CAST (column AS type)` or `column::type`
+- `'__table_name__'::regclass::oid`: Get oid having a table name
+
+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 pid, datname, waiting, state, query FROM pg_stat_activity WHERE datname='__database_name__';`: Show queries being executed at a certain DB. Can also display query time, etc.
 - `SELECT * FROM pg_indexes WHERE tablename='__table_name__' AND schemaname='__schema_name__';`: Show table indexes
-
-Casting:
-- `CAST (column AS type)` or `column::type`
-
-SQL queries:
 - Get all indexes from all tables of a schema:
 ```sql
 select

diff --git a/postgres-cheatsheet.md b/postgres-cheatsheet.md
@@ -20,7 +20,7 @@ Most `\d` commands support additional param of `__schema__.name__` and accept wi
 - `SELECT pg_size_pretty(pg_database_size('__database_name__'));`: Show DB space in use
 - `show statement_timeout;`: Show current user's statement timeout
 - `SELECT pid, datname, waiting, state, query FROM pg_stat_activity WHERE datname='__database_name__';`: Show queries being executed at a certain DB. Can also display query time, etc.
-- `SELECT * FROM pg_indexes WHERE tablename='__table_name__';`: Show table indexes
+- `SELECT * FROM pg_indexes WHERE tablename='__table_name__' AND schemaname='__schema_name__';`: Show table indexes
 
 Casting:
 - `CAST (column AS type)` or `column::type`

diff --git a/postgres-cheatsheet.md b/postgres-cheatsheet.md
@@ -26,7 +26,7 @@ Casting:
 - `CAST (column AS type)` or `column::type`
 
 SQL queries:
-- Get all indexes from all tables of a scheam:
+- Get all indexes from all tables of a schema:
 ```sql
 select
    t.relname as table_name,

diff --git a/postgres-cheatsheet.md b/postgres-cheatsheet.md
@@ -24,3 +24,28 @@ Most `\d` commands support additional param of `__schema__.name__` and accept wi
 
 Casting:
 - `CAST (column AS type)` or `column::type`
+
+SQL queries:
+- Get all indexes from all tables of a scheam:
+```sql
+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
+```
diff --git a/postgres-cheatsheet.md b/postgres-cheatsheet.md
@@ -20,6 +20,7 @@ Most `\d` commands support additional param of `__schema__.name__` and accept wi
 - `SELECT pg_size_pretty(pg_database_size('__database_name__'));`: Show DB space in use
 - `show statement_timeout;`: Show current user's statement timeout
 - `SELECT pid, datname, waiting, state, query FROM pg_stat_activity WHERE datname='__database_name__';`: Show queries being executed at a certain DB. Can also display query time, etc.
+- `SELECT * FROM pg_indexes WHERE tablename='__table_name__';`: Show table indexes
 
 Casting:
 - `CAST (column AS type)` or `column::type`
diff --git a/postgres-cheatsheet.md b/postgres-cheatsheet.md
@@ -19,6 +19,7 @@ Most `\d` commands support additional param of `__schema__.name__` and accept wi
 - `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 pid, datname, waiting, state, query FROM pg_stat_activity WHERE datname='__database_name__';`: Show queries being executed at a certain DB. Can also display query time, etc.
 
 Casting:
 - `CAST (column AS type)` or `column::type`
diff --git a/postgres-cheatsheet.md b/postgres-cheatsheet.md
@@ -18,6 +18,7 @@ Most `\d` commands support additional param of `__schema__.name__` and accept wi
 - `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
 
 Casting:
 - `CAST (column AS type)` or `column::type`
diff --git a/postgres-cheatsheet.md b/postgres-cheatsheet.md
@@ -18,3 +18,6 @@ Most `\d` commands support additional param of `__schema__.name__` and accept wi
 - `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
+
+Casting:
+- `CAST (column AS type)` or `column::type`
diff --git a/postgres-cheatsheet.md b/postgres-cheatsheet.md
@@ -8,7 +8,7 @@ Most `\d` commands support additional param of `__schema__.name__` and accept wi
 - `\q`: Quit/Exit
 - `\c __database__`: Connect to a database
 - `\d __table__`: Show table definition including triggers
-- `\dt`: List tables
+- `\dt *.*`: List tables from all schemas (if `*.*` is omitted will only show SEARCH_PATH ones)
 - `\l`: List databases
 - `\dn`: List schemas
 - `\df`: List functions

diff --git a/postgres-cheatsheet.md b/postgres-cheatsheet.md
@@ -7,12 +7,14 @@ Most `\d` commands support additional param of `__schema__.name__` and accept wi
 
 - `\q`: Quit/Exit
 - `\c __database__`: Connect to a database
+- `\d __table__`: Show table definition including triggers
 - `\dt`: List tables
 - `\l`: List databases
 - `\dn`: List schemas
 - `\df`: List functions
 - `\dv`: List views
+- `\df+ __function` : Show function SQL code. `\x` before pretty-formats it
 - `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.
+- `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
diff --git a/postgres-cheatsheet.md b/postgres-cheatsheet.md
@@ -14,3 +14,5 @@ Most `\d` commands support additional param of `__schema__.name__` and accept wi
 - `\dv`: List views
 - `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.
diff --git a/postgres-cheatsheet.md b/postgres-cheatsheet.md
@@ -9,6 +9,7 @@ Most `\d` commands support additional param of `__schema__.name__` and accept wi
 - `\c __database__`: Connect to a database
 - `\dt`: List tables
 - `\l`: List databases
+- `\dn`: List schemas
 - `\df`: List functions
 - `\dv`: List views
 - `SELECT * FROM pg_proc WHERE proname='__procedurename__'`: List procedure/function