## 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
```sql
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
```bash
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:
```bash
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
```sql 
# 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](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
``` 

## References
* [PostgreSQL 9.6.1 Documentation.pdf](https://www.postgresql.org/files/documentation/pdf/9.6/postgresql-9.6-A4.pdf)
* [PostgreSQL 9.0 Cheat Sheet](http://www.postgresonline.com/downloads/special_feature/postgresql90_cheatsheet_A4.pdf)