Skip to content

Instantly share code, notes, and snippets.

@virtadpt

virtadpt/postgres-cheatsheet.md

Forked from Kartones/postgres-cheatsheet.md
Created March 20, 2024 19:24
Show Gist options
  • Save virtadpt/b36a8c08a42faa77f05ca2872f1a7306 to your computer and use it in GitHub Desktop.
Save virtadpt/b36a8c08a42faa77f05ca2872f1a7306 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @Kartones Kartones revised this gist Jan 18, 2024. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions postgres-cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -232,6 +232,7 @@ $ source $HOME/.editrc
    - 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
    - [Operations Cheat Sheet](https://wiki.postgresql.org/wiki/Operations_cheat_sheet): Official PG wiki cheat sheet with an amazing amount of explanations of many topics, features, and many many internal implementation details
    - [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**.
  2. @Kartones Kartones revised this gist Oct 30, 2021. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions postgres-cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -229,6 +229,7 @@ $ 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
  3. @Kartones Kartones revised this gist May 26, 2021. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions postgres-cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -235,4 +235,5 @@ $ source $HOME/.editrc
    - [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))
  4. @Kartones Kartones revised this gist Apr 7, 2021. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions postgres-cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -232,6 +232,7 @@ $ source $HOME/.editrc

    ## 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.
    - `psql -c "\l+" -H -q postgres > out.html`: Generate a html report of your databases (source: [Daniel Westermann](https://twitter.com/westermanndanie/status/1242117182982586372))
  5. @Kartones Kartones revised this gist Jan 11, 2021. 1 changed file with 89 additions and 2 deletions.
    91 changes: 89 additions & 2 deletions postgres-cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -11,6 +11,7 @@ Some interesting flags (to see all, use `-h` or `--help` depending on your psql

    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
    @@ -29,6 +30,7 @@ Most `\d` commands support additional param of `__schema__.name__` and accept wi
    - `\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
    @@ -69,11 +71,14 @@ tail -f /var/log/postgresql/postgresql-9.3-main.log
    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)
    @@ -117,10 +122,12 @@ 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,
    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`
    @@ -134,6 +141,80 @@ Query analysis:
    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

    @@ -145,6 +226,12 @@ Generating random data ([source](https://www.citusdata.com/blog/2019/07/17/postg
    $ 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;`

    ## Resources & Documentation
    - [Postgres Weekly](https://postgresweekly.com/) newsletter: The best way IMHO to keep up to date with PG news
    - [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.
    - `psql -c "\l+" -H -q postgres > out.html`: Generate a html report of your databases (source: [Daniel Westermann](https://twitter.com/westermanndanie/status/1242117182982586372))
  6. @Kartones Kartones revised this gist Apr 9, 2020. 1 changed file with 2 additions and 1 deletion.
    3 changes: 2 additions & 1 deletion postgres-cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -146,4 +146,5 @@ $ echo "bind "^R" em-inc-search-prev" > $HOME/.editrc
    $ source $HOME/.editrc
    ```
    - [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.
    - [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.
    - `psql -c "\l+" -H -q postgres > out.html`: Generate a html report of your databases (source: [Daniel Westermann](https://twitter.com/westermanndanie/status/1242117182982586372))
  7. @Kartones Kartones revised this gist Apr 9, 2020. 1 changed file with 5 additions and 1 deletion.
    6 changes: 5 additions & 1 deletion postgres-cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -13,7 +13,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
    - `\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
    @@ -23,9 +23,12 @@ Most `\d` commands support additional param of `__schema__.name__` and accept wi
    - `\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

    User Related:
    - `\du`: List users
    @@ -34,6 +37,7 @@ User Related:
    - `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

  8. @Kartones Kartones revised this gist Aug 21, 2019. 1 changed file with 3 additions and 0 deletions.
    3 changes: 3 additions & 0 deletions postgres-cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -127,6 +127,9 @@ Query analysis:
    - `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;`

    ## Keyboard shortcuts
    - `CTRL` + `R`: reverse-i-search

  9. @Kartones Kartones revised this gist Jul 2, 2019. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions postgres-cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -14,12 +14,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
    - `\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
  10. @Kartones Kartones revised this gist Sep 24, 2018. 1 changed file with 6 additions and 2 deletions.
    8 changes: 6 additions & 2 deletions postgres-cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -5,7 +5,7 @@ Magic words:
    ```bash
    psql -U postgres
    ```
    Some interesting flags (to see all, use `-h`):
    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)

    @@ -125,8 +125,12 @@ Query analysis:
    - `EXPLAIN ANALYZE __query__`: see and execute the query plan for the given query
    - `ANALYZE [__table__]`: collect statistics

    ## Keyboard shortcuts
    - `CTRL` + `R`: reverse-i-search

    ## Tools
    - [pg-top](http://ptop.projects.pgfoundry.org/): `top` for PG. `sudo apt-get install ptop` + `pg_top`
    - `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
  11. @Kartones Kartones revised this gist Jun 20, 2018. 1 changed file with 2 additions and 1 deletion.
    3 changes: 2 additions & 1 deletion postgres-cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -132,4 +132,5 @@ Query analysis:
    $ echo "bind "^R" em-inc-search-prev" > $HOME/.editrc
    $ source $HOME/.editrc
    ```
    - [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**.
    - [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.
  12. @Kartones Kartones revised this gist Jun 20, 2018. 1 changed file with 4 additions and 3 deletions.
    7 changes: 4 additions & 3 deletions postgres-cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -14,12 +14,13 @@ 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 from all schemas (if `*.*` is omitted will only show SEARCH_PATH ones)
    - `\l`: List databases
    - `\dn`: List schemas
    - `\dy`: List events
    - `\df`: List functions
    - `\dv`: List views
    - `\di`: List indexes
    - `\dn`: List schemas
    - `\dt *.*`: List tables from all schemas (if `*.*` is omitted will only show SEARCH_PATH ones)
    - `\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
  13. @kartones-tkt kartones-tkt revised this gist Feb 25, 2018. 1 changed file with 5 additions and 0 deletions.
    5 changes: 5 additions & 0 deletions postgres-cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -22,6 +22,7 @@ Most `\d` commands support additional param of `__schema__.name__` and accept wi
    - `\di`: List indexes
    - `\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

    User Related:
    - `\du`: List users
    @@ -61,6 +62,10 @@ tail -f /var/log/postgresql/postgresql-9.3-main.log
    log_line_prefix = '%t %u %d %a '
    ```

    ## 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
  14. @kartones-tkt kartones-tkt revised this gist Jan 25, 2018. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions postgres-cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -19,6 +19,7 @@ Most `\d` commands support additional param of `__schema__.name__` and accept wi
    - `\dn`: List schemas
    - `\df`: List functions
    - `\dv`: List views
    - `\di`: List indexes
    - `\df+ __function__` : Show function SQL code.
    - `\x`: Pretty-format query results instead of the not-so-useful ASCII tables

  15. @kartones-tkt kartones-tkt revised this gist Dec 11, 2017. 1 changed file with 2 additions and 1 deletion.
    3 changes: 2 additions & 1 deletion postgres-cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -124,4 +124,5 @@ Query analysis:
    ```bash
    $ echo "bind "^R" em-inc-search-prev" > $HOME/.editrc
    $ source $HOME/.editrc
    ```
    ```
    - [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**.
  16. @kartones-tkt kartones-tkt revised this gist Aug 8, 2017. 1 changed file with 3 additions and 1 deletion.
    4 changes: 3 additions & 1 deletion postgres-cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -5,7 +5,9 @@ Magic words:
    ```bash
    psql -U postgres
    ```
    If run with `-E` flag, it will describe the underlaying queries of the `\` commands (cool for learning!).
    Some interesting flags (to see all, use `-h`):
    - `-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 `*.*`

  17. @kartones-tkt kartones-tkt revised this gist Jun 21, 2017. 1 changed file with 4 additions and 0 deletions.
    4 changes: 4 additions & 0 deletions postgres-cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -111,6 +111,10 @@ 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

    ## Tools
    - [pg-top](http://ptop.projects.pgfoundry.org/): `top` for PG. `sudo apt-get install ptop` + `pg_top`
  18. @Kartones Kartones revised this gist Aug 27, 2016. 1 changed file with 9 additions and 1 deletion.
    10 changes: 9 additions & 1 deletion postgres-cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -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:
  19. @Kartones Kartones revised this gist Mar 2, 2016. 1 changed file with 6 additions and 1 deletion.
    7 changes: 6 additions & 1 deletion postgres-cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -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
    ```
  20. @Kartones Kartones revised this gist Feb 18, 2016. 1 changed file with 3 additions and 2 deletions.
    5 changes: 3 additions & 2 deletions postgres-cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -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
    ```
  21. @Kartones Kartones revised this gist Feb 11, 2016. 1 changed file with 2 additions and 1 deletion.
    3 changes: 2 additions & 1 deletion postgres-cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -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

  22. @Kartones Kartones revised this gist Feb 11, 2016. 1 changed file with 3 additions and 1 deletion.
    4 changes: 3 additions & 1 deletion postgres-cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -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
  23. @Kartones Kartones revised this gist Feb 11, 2016. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion postgres-cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -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
  24. @Kartones Kartones revised this gist Jan 27, 2016. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion postgres-cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -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
  25. @Kartones Kartones revised this gist Jan 27, 2016. 1 changed file with 3 additions and 3 deletions.
    6 changes: 3 additions & 3 deletions postgres-cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -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 '
    ```
  26. @Kartones Kartones revised this gist Jan 27, 2016. 1 changed file with 11 additions and 4 deletions.
    15 changes: 11 additions & 4 deletions postgres-cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -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 '
    ```
  27. @Kartones Kartones revised this gist Jan 27, 2016. 1 changed file with 15 additions and 15 deletions.
    30 changes: 15 additions & 15 deletions postgres-cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -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;
  28. @Kartones Kartones revised this gist Jan 27, 2016. 1 changed file with 10 additions and 3 deletions.
    13 changes: 10 additions & 3 deletions postgres-cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -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
  29. @Kartones Kartones revised this gist Jan 27, 2016. 1 changed file with 14 additions and 5 deletions.
    19 changes: 14 additions & 5 deletions postgres-cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -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`
  30. @Kartones Kartones revised this gist Oct 23, 2015. 1 changed file with 2 additions and 1 deletion.
    3 changes: 2 additions & 1 deletion postgres-cheatsheet.md
    View file
    Original file line number Diff line number Diff line change
    @@ -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;
    ```