# Cheat sheet Postgres

### PSQL

Magic words:

```bash
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 `*.*`

* `\?`: Show help (list of available commands with an explanation)
* `\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
* `\! __bash_command__`: execute `__bash_command__` (e.g. `\! ls`)

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:

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

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

* Check Extensions enabled in postgres: `SELECT * FROM pg_extension;`
* Show available extensions: `SELECT * FROM pg_available_extension_versions;`

### 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](https://www.postgresql.org/search/?u=%2Fdocs%2F9.1%2F\&q=CREATE).

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

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

* 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__';
```

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

* Get Connections by Database: `SELECT datname, numbackends FROM pg_stat_database;`

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](https://www.citusdata.com/blog/2019/07/17/postgres-tips-for-average-and-power-user/)):

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

Get sizes of tables, indexes and full DBs:

```sql
select current_database() as database,
  pg_size_pretty(total_database_size) as total_database_size,
  schema_name,
  table_name,
  pg_size_pretty(total_table_size) as total_table_size,
  pg_size_pretty(table_size) as table_size,
  pg_size_pretty(index_size) as index_size
  from ( select table_name,
          table_schema as schema_name,
          pg_database_size(current_database()) as total_database_size,
          pg_total_relation_size(table_name) as total_table_size,
          pg_relation_size(table_name) as table_size,
          pg_indexes_size(table_name) as index_size
          from information_schema.tables
          where table_schema=current_schema() and table_name like 'table_%'
          order by total_table_size
      ) as sizes;
```

* [COPY command](https://www.postgresql.org/docs/9.2/sql-copy.html): Import/export from CSV to tables:

```sql
COPY table_name [ ( column_name [, ...] ) ]
FROM { 'filename' | STDIN }
[ [ WITH ] ( option [, ...] ) ]

COPY { table_name [ ( column_name [, ...] ) ] | ( query ) }
TO { 'filename' | STDOUT }
[ [ WITH ] ( option [, ...] ) ]
```

* List all grants for a specific user

```sql
SELECT table_catalog, table_schema, table_name, privilege_type
FROM   information_schema.table_privileges
WHERE  grantee = 'user_to_check' ORDER BY table_name;
```

* List all assigned user roles

```sql
SELECT
    r.rolname,
    r.rolsuper,
    r.rolinherit,
    r.rolcreaterole,
    r.rolcreatedb,
    r.rolcanlogin,
    r.rolconnlimit,
    r.rolvaliduntil,
    ARRAY(SELECT b.rolname
      FROM pg_catalog.pg_auth_members m
      JOIN pg_catalog.pg_roles b ON (m.roleid = b.oid)
      WHERE m.member = r.oid) as memberof, 
    r.rolreplication
FROM pg_catalog.pg_roles r
ORDER BY 1;
```

* Check permissions in a table:

```sql
SELECT grantee, privilege_type
FROM information_schema.role_table_grants
WHERE table_name='name-of-the-table';
```

* Kill all Connections:

```sql
SELECT pg_terminate_backend(pg_stat_activity.pid)
FROM pg_stat_activity
WHERE datname = current_database() AND pid <> pg_backend_pid();
```

### 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](https://github.com/julmon/pg_activity): Command line tool for PostgreSQL server activity monitoring.
* [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
```

* Show IP of the DB Instance: `SELECT inet_server_addr();`
* File to save PostgreSQL credentials and permissions (format: `hostname:port:database:username:password`): `chmod 600 ~/.pgpass`
* Collect statistics of a database (useful to improve speed after a Database Upgrade as previous query plans are deleted): `ANALYZE VERBOSE;`
* To obtain the `CREATE TABLE` query of a table, any visual GUI like [pgAdmin](https://www.pgadmin.org/) allows to easily, but else you can use `pg_dump`, e.g.: `pg_dump -t '<schema>.<table>' --schema-only <database>` ([source](https://stackoverflow.com/questions/2593803/how-to-generate-the-create-table-sql-statement-for-an-existing-table-in-postgr))

### Resources & Documentation

* [Postgres Weekly](https://postgresweekly.com/) newsletter: The best way IMHO to keep up to date with PG news
* [100 psql Tips](https://mydbanotebook.org/psql_tips_all.html): Name says all, lots of useful tips!
* [PostgreSQL Exercises](https://pgexercises.com/): 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](https://severalnines.com/blog/performance-cheat-sheet-postgresql): Great explanations of `EXPLAIN`, `EXPLAIN ANALYZE`, `VACUUM`, configuration parameters and more. Quite interesting if you need to tune-up a postgres setup.
* [annotated.conf](https://github.com/jberkus/annotated.conf): Annotations of all 269 postgresql.conf settings for PostgreSQL 10.
* `psql -c "\l+" -H -q postgres > out.html`: Generate a html report of your databases (source: [Daniel Westermann](https://twitter.com/westermanndanie/status/1242117182982586372))


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://blog.micsoftvn.com/use-cases/for-sysadmin/cheat-sheet/cheat-sheet-postgres.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.