Skip to content

Instantly share code, notes, and snippets.

@hanksudo

hanksudo/PostgreSQL_Note.md

Last active August 7, 2023 07:41
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save hanksudo/cc8ac532509a2ae1e4c5 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save hanksudo/cc8ac532509a2ae1e4c5 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. hanksudo revised this gist Jul 11, 2022. 1 changed file with 14 additions and 13 deletions.
    27 changes: 14 additions & 13 deletions PostgreSQL_Note.md
    View file
    Original file line number Diff line number Diff line change
    @@ -2,7 +2,7 @@

    ## Commands

    https://www.postgresql.org/docs/9.5/static/reference-client.html
    <https://www.postgresql.org/docs/9.5/static/reference-client.html>

    ### Show work memory

    @@ -50,8 +50,8 @@ pg_dump db_name --data-only -h localhost -p 15432 | psql -h localhost -p 15433
    ### backup & recovery with compression

    ```bash
    $ pg_dump dbname | gzip > filename.gz
    $ gunzip -c filename.gz | psql dbname
    pg_dump dbname | gzip > filename.gz
    gunzip -c filename.gz | psql dbname
    ```

    ### Monitoring
    @@ -74,13 +74,13 @@ SELECT pg_cancel_backend(PID);
    ### Dump all databases and reload databases

    ```bash
    $ pg_dumpall > db.out
    $ psql -f db.out postgres
    pg_dumpall > db.out
    psql -f db.out postgres
    ```

    ### Database

    - `\l` - list all databases
    - `\l` - list all databases

    - `\c db_name` - connect to database

    @@ -107,6 +107,7 @@ GRANT CONNECT ON DATABASE database_name TO username;
    - `\dt` - list tables

    - list columns on tables

    ```
    \d table_name
    ```
    @@ -119,7 +120,7 @@ pg_dump -t table_to_copy source_db | psql target_db

    #### reassign all tables owner

    https://gist.github.com/hanksudo/f1365f3a764d3c8519cc
    <https://gist.github.com/hanksudo/f1365f3a764d3c8519cc>

    ### Permission

    @@ -133,6 +134,7 @@ GRANT SELECT ON table_name TO ROLE_NAME;
    ```

    multiple tables

    ```
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO username;
    ```
    @@ -192,7 +194,6 @@ CREATE INDEX CONCURRENTLY diary_user_idx ON diary(user_id, id);
    DROP INDEX diary_user_idx;
    ```


    ### Other

    - Show config file path
    @@ -214,7 +215,6 @@ SHOW ALL;
    SHOW SERVER_VERSION;
    ```


    ### SQLs

    - alter foreign key
    @@ -349,12 +349,14 @@ SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
    FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
    ```

    ## pgpass
    ## .pgpass

    ```bash
    127.0.0.1:5432:*:username:password
    ```

    - [PostgreSQL: Documentation: 14: 34.16. The Password File](https://www.postgresql.org/docs/current/libpq-pgpass.html)

    ## compare two databases

    ```bash
    @@ -389,7 +391,7 @@ psql <REMOTE POSTGRESQL URL> \
    - [Number Of Database Connections - PostgreSQL wiki](https://wiki.postgresql.org/wiki/Number_Of_Database_Connections)
    - [PgBouncer - lightweight connection pooler for PostgreSQL](https://pgbouncer.github.io/)
    - [How much RAM is PostgreSQL using?](http://www.depesz.com/2012/06/09/how-much-ram-is-postgresql-using/)
    - [Database Isolation Levels And Their Effects on Performance and Scalability - High Scalability - ](http://highscalability.com/blog/2011/2/10/database-isolation-levels-and-their-effects-on-performance-a.html)
    - [Database Isolation Levels And Their Effects on Performance and Scalability - High Scalability -](http://highscalability.com/blog/2011/2/10/database-isolation-levels-and-their-effects-on-performance-a.html)
    - [PostgreSQL: Documentation: 9.4: Continuous Archiving and Point-in-Time Recovery (PITR)](http://www.postgresql.org/docs/9.4/static/continuous-archiving.html)
    - [Zero to PostgreSQL streaming replication in 10 mins | Greg Reinacker's Weblog](http://www.rassoc.com/gregr/weblog/2013/02/16/zero-to-postgresql-streaming-replication-in-10-mins/)
    - [12-Step Program for Scaling Web Applications on PostgreSQL](http://www.slideshare.net/kigster/12step-program-for-scaling-web-applications-on-postgresql)
    @@ -399,7 +401,6 @@ psql <REMOTE POSTGRESQL URL> \

    - [Re: How to monitor locks (max_pred_locks_per_transaction)?](http://www.postgresql.org/message-id/CADKuZZCgfVoRJ1UgUkiLNjfOh54-Hq5kkrAsiTBZPKz+pvYwjQ@mail.gmail.com)


    ```
    out of shared memory
    HINT: You might need to increase max_pred_locks_per_transaction.
    @@ -421,4 +422,4 @@ grep -nEo '([0-9]+) rows' SIReadLock.log | sort -rn | head -n 1
    ```sql
    select usename, client_addr, count(*) from pg_stat_activity
    group by client_addr, usename;
    ```
    ```
  2. hanksudo revised this gist Nov 23, 2019. 1 changed file with 11 additions and 0 deletions.
    11 changes: 11 additions & 0 deletions PostgreSQL_Note.md
    View file
    Original file line number Diff line number Diff line change
    @@ -60,6 +60,17 @@ $ gunzip -c filename.gz | psql dbname
    htop -u postgres
    ```

    ### Force kill hanging query

    ```
    # check locked
    SELECT relation::regclass, * FROM pg_locks WHERE NOT GRANTED;
    SELECT * FROM pg_stat_activity WHERE status = 'active';
    SELECT pg_terminate_backend(PID);
    SELECT pg_cancel_backend(PID);
    ```

    ### Dump all databases and reload databases

    ```bash
  3. hanksudo revised this gist Oct 29, 2019. 1 changed file with 25 additions and 3 deletions.
    28 changes: 25 additions & 3 deletions PostgreSQL_Note.md
    View file
    Original file line number Diff line number Diff line change
    @@ -4,6 +4,13 @@

    https://www.postgresql.org/docs/9.5/static/reference-client.html

    ### Show work memory

    ```bash
    SHOW work_mem;
    SHOW maintenance_work_mem;
    ```

    ### Create Database with UTF-8 encoding

    ```bash
    @@ -14,9 +21,9 @@ createdb -E UTF8 -T template0 --locale=en_US.utf8 <dbname> -O owner

    ```bash
    psql
    psql -U user -W
    psql -U user
    psql -U postgres
    psql -U postgres -h locahlost
    psql -U postgres -h locahlost -d db
    ```

    ### backup database
    @@ -38,6 +45,8 @@ pg_dump db_name -h localhost -p 15432 | psql -h localhost -p 15433
    pg_dump db_name --data-only -h localhost -p 15432 | psql -h localhost -p 15433
    ```

    -T exclude table

    ### backup & recovery with compression

    ```bash
    @@ -329,6 +338,12 @@ SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
    FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
    ```

    ## pgpass

    ```bash
    127.0.0.1:5432:*:username:password
    ```

    ## compare two databases

    ```bash
    @@ -379,7 +394,7 @@ out of shared memory
    HINT: You might need to increase max_pred_locks_per_transaction.
    ```

    The total 'SIReadLock' count must be less than max_pred_locks_per_transaction * max_connections.
    **The total 'SIReadLock' count must be less than max_pred_locks_per_transaction * max_connections.**

    ```sql
    SELECT * FROM pg_locks WHERE mode = 'SIReadLock';
    @@ -389,3 +404,10 @@ SELECT * FROM pg_locks WHERE mode = 'SIReadLock';
    watch -n 3 "psql -c \"SELECT * FROM pg_locks WHERE mode='SIReadLock';\" -L SIReadLock.log"
    grep -nEo '([0-9]+) rows' SIReadLock.log | sort -rn | head -n 1
    ```

    **remaining connection slots are reserved for non-replication superuser connections**

    ```sql
    select usename, client_addr, count(*) from pg_stat_activity
    group by client_addr, usename;
    ```
  4. hanksudo revised this gist May 24, 2019. 1 changed file with 20 additions and 2 deletions.
    22 changes: 20 additions & 2 deletions PostgreSQL_Note.md
    View file
    Original file line number Diff line number Diff line change
    @@ -76,6 +76,12 @@ CREATE DATABASE newdb WITH OWNER demorole ENCODING 'UTF8';
    ALTER DATABASE h2 OWNER TO h2;
    ```

    - grant access

    ```sql
    GRANT CONNECT ON DATABASE database_name TO username;
    ```

    ### Table

    - `\dt` - list tables
    @@ -106,6 +112,11 @@ GRANT SELECT, UPDATE, INSERT ON table_name TO role_name;
    GRANT SELECT ON table_name TO ROLE_NAME;
    ```

    multiple tables
    ```
    GRANT SELECT ON ALL TABLES IN SCHEMA public TO username;
    ```

    #### Revoke Permission

    ```
    @@ -130,9 +141,8 @@ ALTER ROLE demorole CREATEROLE CREATEDB REPLICATION SUPERUSER;
    ALTER ROLE demorole WITH ENCRYPTED PASSWORD '123456';
    ```

    - drop role

    ```sql
    -- drop role
    DROP ROLE demorole;
    ```

    @@ -285,6 +295,14 @@ WHERE table_schema = 'public'
    ORDER BY pg_relation_size(TABLE_NAME) DESC;
    ```

    #### Find unused index

    ```sql
    SELECT relname, indexrelname, idx_scan
    FROM pg_catalog.pg_stat_user_indexes
    WHERE schemaname = 'public';
    ```

    ### Extensions

    #### pg_stat_statements
  5. hanksudo revised this gist Sep 1, 2018. 1 changed file with 12 additions and 0 deletions.
    12 changes: 12 additions & 0 deletions PostgreSQL_Note.md
    View file
    Original file line number Diff line number Diff line change
    @@ -273,6 +273,18 @@ WHERE
    duplicates.user_row_number > 1
    ```

    #### list all tables and indexes size

    ```sql
    SELECT TABLE_NAME,
    pg_size_pretty(pg_relation_size(TABLE_NAME)) AS relation_size,
    pg_size_pretty(pg_total_relation_size(TABLE_NAME)) AS total_relation_size,
    pg_size_pretty(pg_indexes_size(TABLE_NAME)) AS indexes_size
    FROM information_schema.tables
    WHERE table_schema = 'public'
    ORDER BY pg_relation_size(TABLE_NAME) DESC;
    ```

    ### Extensions

    #### pg_stat_statements
  6. hanksudo revised this gist Mar 17, 2018. 1 changed file with 21 additions and 8 deletions.
    29 changes: 21 additions & 8 deletions PostgreSQL_Note.md
    View file
    Original file line number Diff line number Diff line change
    @@ -10,25 +10,32 @@ https://www.postgresql.org/docs/9.5/static/reference-client.html
    createdb -E UTF8 -T template0 --locale=en_US.utf8 <dbname> -O owner
    ```

    ### connect to database
    ### Connect to database

    ```bash
    $ psql
    $ psql -U user -W
    $ psql -U postgres
    $ psql -U postgres -h locahlost
    psql
    psql -U user -W
    psql -U postgres
    psql -U postgres -h locahlost
    ```

    ### backup database

    ```bash
    $ pg_dump db_name > file
    pg_dump db_name > file
    ```

    ### restore

    ```bash
    $ psql db_name < file
    psql db_name < file
    ```

    ### backup and restore to another server

    ```bash
    pg_dump db_name -h localhost -p 15432 | psql -h localhost -p 15433
    pg_dump db_name --data-only -h localhost -p 15432 | psql -h localhost -p 15433
    ```

    ### backup & recovery with compression
    @@ -59,10 +66,16 @@ $ psql -f db.out postgres

    - create database

    ```
    ```sql
    CREATE DATABASE newdb WITH OWNER demorole ENCODING 'UTF8';
    ```

    - change owner

    ```sql
    ALTER DATABASE h2 OWNER TO h2;
    ```

    ### Table

    - `\dt` - list tables
  7. hanksudo revised this gist Nov 1, 2017. 1 changed file with 61 additions and 23 deletions.
    84 changes: 61 additions & 23 deletions PostgreSQL_Note.md
    View file
    Original file line number Diff line number Diff line change
    @@ -38,7 +38,7 @@ $ pg_dump dbname | gzip > filename.gz
    $ gunzip -c filename.gz | psql dbname
    ```

    ### monitoring
    ### Monitoring

    ```bash
    htop -u postgres
    @@ -53,27 +53,19 @@ $ psql -f db.out postgres

    ### Database

    - list all databases
    ```
    \l
    ```
    - `\l` - list all databases

    - connect to database
    ```
    \c db_name
    ```
    - `\c db_name` - connect to database

    - create database

    ```
    CREATE DATABASE newdb WITH OWNER demorole ENCODING 'UTF8';
    ```

    ### Table

    - list tables
    ```
    \dt
    ```
    - `\dt` - list tables

    - list columns on tables
    ```
    @@ -90,26 +82,36 @@ pg_dump -t table_to_copy source_db | psql target_db

    https://gist.github.com/hanksudo/f1365f3a764d3c8519cc

    ### Grant
    ### Permission

    - `\z table_name` - Permission of table

    #### Grant Permission

    ```
    GRANT SELECT, UPDATE, INSERT ON table_name TO role_name;
    GRANT SELECT ON table_name TO ROLE_NAME;
    ```

    ### Role
    #### Revoke Permission

    - list roles
    ```
    \du
    REVOKE ALL ON table_name FROM role_name;
    ```

    ### Role

    - `\du` - list roles

    - create role

    ```
    CREATE ROLE demorole WITH LOGIN ENCRYPTED PASSWORD 'password' CREATEDB;
    CREATE ROLE replicator WITH REPLICATION LOGIN ENCRYPTED PASSWORD 'password';
    ```

    - alter role

    ```
    ALTER ROLE demorole CREATEROLE CREATEDB REPLICATION SUPERUSER;
    ALTER ROLE demorole WITH ENCRYPTED PASSWORD '123456';
    @@ -124,34 +126,40 @@ DROP ROLE demorole;
    ### Index

    - list all indexes

    ```
    SELECT * FROM pg_stat_all_indexes WHERE schemaname = 'public' ORDER BY indexrelname;
    ```

    - list index of table

    ```
    \d table_name
    ```

    - create index

    ```
    CREATE INDEX CONCURRENTLY diary_user_id_id_idx ON diary(user_id, id);
    CREATE INDEX CONCURRENTLY diary_user_idx ON diary(user_id, id);
    ```

    - drop index

    ```
    DROP INDEX diary_user_id_id_idx;
    DROP INDEX diary_user_idx;
    ```


    ### Other

    - Show config file path

    ```
    SHOW config_file;
    ```

    - Show current setting

    ```
    \set
    ```
    @@ -167,7 +175,8 @@ SHOW SERVER_VERSION;
    ### SQLs

    - alter foreign key
    ```

    ```sql
    ALTER TABLE diary ADD FOREIGN KEY (meter_id) REFERENCES user_meter(id);
    ```

    @@ -234,11 +243,28 @@ $$ LANGUAGE SQL;
    SELECT * FROM indexes_stat_for_current_database() ORDER BY idx_scan DESC LIMIT 5;
    ```

    - Finding duplicate rows

    ```sql
    SELECT id from (
    SELECT id,
    ROW_NUMBER() OVER(
    PARTITION BY first_name,
    last_name,
    email
    ORDER BY id
    ) AS user_row_number
    FROM users
    ) duplicates
    WHERE
    duplicates.user_row_number > 1
    ```

    ### Extensions

    #### pg_stat_statements

    add into /etc/postgresql/9.4/main/postgresql.conf
    add into `/etc/postgresql/9.4/main/postgresql.conf`

    ```
    shared_preload_libraries = 'pg_stat_statements' # (change requires restart)
    @@ -262,12 +288,25 @@ SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /

    ## compare two databases

    ```
    ```bash
    sudo -u postgres pg_dump -f original.sql db_name
    sudo -u postgres pg_dump -h 192.168.33.32 -f new.sql db_name
    java -jar apgdiff-2.4.jar --ignore-start-with original.sql new.sql
    ```

    ## Remote query execution

    ```bash
    psql <REMOTE POSTGRESQL URL> \
    -c "copy (
    SELECT *
    FROM users
    WHERE date_trunc('month', created_at) = '2016-01-01'
    ) to stdout" \
    | psql <LOCAL POSTGRESQL DATABASE> \
    -c "copy users from stdin"
    ```

    ## Other References

    - [PostgreSQL - Memory](http://michael.otacoo.com/manuals/postgresql/settings/memory/)
    @@ -277,7 +316,6 @@ java -jar apgdiff-2.4.jar --ignore-start-with original.sql new.sql
    - [PostgreSQL: Documentation: 9.4: The Statistics Collector](http://www.postgresql.org/docs/current/static/monitoring-stats.html)
    - [Lock Monitoring - PostgreSQL wiki](https://wiki.postgresql.org/wiki/Lock_Monitoring)
    - [PgTune](http://pgtune.leopard.in.ua/)

    - [Improving PostgreSQL performance on AWS EC2](http://blog.2ndquadrant.com/postgresql_performance_on_ec2/)
    - [Number Of Database Connections - PostgreSQL wiki](https://wiki.postgresql.org/wiki/Number_Of_Database_Connections)
    - [PgBouncer - lightweight connection pooler for PostgreSQL](https://pgbouncer.github.io/)
  8. hanksudo revised this gist Sep 18, 2017. 1 changed file with 27 additions and 6 deletions.
    33 changes: 27 additions & 6 deletions PostgreSQL_Note.md
    View file
    Original file line number Diff line number Diff line change
    @@ -80,9 +80,22 @@ CREATE DATABASE newdb WITH OWNER demorole ENCODING 'UTF8';
    \d table_name
    ```

    - copy table to another db

    ```bash
    pg_dump -t table_to_copy source_db | psql target_db
    ```

    #### reassign all tables owner

    https://gist.github.com/hanksudo/f1365f3a764d3c8519cc

    ### Grant

    ```
    GRANT SELECT, UPDATE, INSERT ON table_name TO role_name;
    ```

    ### Role

    - list roles
    @@ -103,7 +116,8 @@ ALTER ROLE demorole WITH ENCRYPTED PASSWORD '123456';
    ```

    - drop role
    ```

    ```sql
    DROP ROLE demorole;
    ```

    @@ -143,6 +157,7 @@ SHOW config_file;
    ```

    - show the value of a run-time parameter

    ```
    SHOW ALL;
    SHOW SERVER_VERSION;
    @@ -157,31 +172,34 @@ ALTER TABLE diary ADD FOREIGN KEY (meter_id) REFERENCES user_meter(id);
    ```

    - list tables name
    ```

    ```sql
    SELECT table_name FROM information_schema.tables
    WHERE table_schema='public' AND table_type='BASE TABLE';
    ```

    - update datetime
    ```

    ```sql
    UPDATE diary SET recorded_at = recorded_at + INTERVAL '17 days'
    ```

    - Distinct ON Multi columns

    ```
    ```sql
    SELECT DISTINCT ON(glucose_value, recorded_at) glucose_value, recorded_at FROM diary WHERE user_id = 1 ORDER BY recorded_at;
    ```

    - Regular Expression

    ```
    ```sql
    SELECT * FROM product WHERE serial_number !~ '\w{15}';
    SELECT * FROM product WHERE serial_number ~ '\w{15}';
    ```

    - Explain Analyze
    ```

    ```sql
    EXPLAIN ANALYZE select * from api_log;
    ```

    @@ -192,6 +210,7 @@ SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
    ```

    - Sql function that returns a table with I/O infomation (obtained from pg_statio_user_indexes view) and filtered by current database and "public" schema

    ```sql
    CREATE OR REPLACE FUNCTION indexes_io_for_current_database()
    RETURNS TABLE(indexname name, idx_blks_read bigint, idx_blks_hit bigint) AS $$
    @@ -203,6 +222,7 @@ SELECT * FROM indexes_io_for_current_database() ORDER BY idx_blks_hit DESC LIMIT
    ```

    - Sql function that returns a table with index usage infomation (obtained from pg_stat_user_indexes view) and filtered by current database and "public" schema:

    ```sql
    CREATE OR REPLACE FUNCTION indexes_stat_for_current_database()
    RETURNS TABLE(indexname name, idx_scan bigint, idx_tup_read bigint, idx_tup_fetch bigint) AS $$
    @@ -219,6 +239,7 @@ SELECT * FROM indexes_stat_for_current_database() ORDER BY idx_scan DESC LIMIT 5
    #### pg_stat_statements

    add into /etc/postgresql/9.4/main/postgresql.conf

    ```
    shared_preload_libraries = 'pg_stat_statements' # (change requires restart)
    pg_stat_statements.max = 10000
  9. hanksudo revised this gist Jun 28, 2016. 1 changed file with 19 additions and 9 deletions.
    28 changes: 19 additions & 9 deletions PostgreSQL_Note.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,34 +1,44 @@
    ## PostgreSQL Note
    # PostgreSQL Note

    ### Commands
    ## Commands

    https://www.postgresql.org/docs/9.5/static/reference-client.html

    - basic
    ### Create Database with UTF-8 encoding

    ```bash
    createdb -E UTF8 -T template0 --locale=en_US.utf8 <dbname> -O owner
    ```

    ### connect to database

    ```bash
    $ psql
    $ psql -U user -W
    $ psql -U postgres
    $ psql -U postgres -h locahlost
    ```

    - backup database
    ```
    ### backup database

    ```bash
    $ pg_dump db_name > file
    ```

    - restore
    ```
    ### restore

    ```bash
    $ psql db_name < file
    ```

    - backup & recovery with compression
    ### backup & recovery with compression

    ```bash
    $ pg_dump dbname | gzip > filename.gz
    $ gunzip -c filename.gz | psql dbname
    ```

    - monitoring
    ### monitoring

    ```bash
    htop -u postgres
  10. hanksudo revised this gist Jun 27, 2016. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions PostgreSQL_Note.md
    View file
    Original file line number Diff line number Diff line change
    @@ -2,6 +2,8 @@

    ### Commands

    https://www.postgresql.org/docs/9.5/static/reference-client.html

    - basic
    ```
    $ psql
  11. hanksudo revised this gist Apr 23, 2016. 1 changed file with 6 additions and 0 deletions.
    6 changes: 6 additions & 0 deletions PostgreSQL_Note.md
    View file
    Original file line number Diff line number Diff line change
    @@ -173,6 +173,12 @@ SELECT * FROM product WHERE serial_number ~ '\w{15}';
    EXPLAIN ANALYZE select * from api_log;
    ```

    - Find Largest table in the db

    ```sql
    SELECT relname, relpages FROM pg_class ORDER BY relpages DESC;
    ```

    - Sql function that returns a table with I/O infomation (obtained from pg_statio_user_indexes view) and filtered by current database and "public" schema
    ```sql
    CREATE OR REPLACE FUNCTION indexes_io_for_current_database()
  12. hanksudo revised this gist Nov 29, 2015. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion PostgreSQL_Note.md
    View file
    Original file line number Diff line number Diff line change
    @@ -20,7 +20,7 @@ $ pg_dump db_name > file
    $ psql db_name < file
    ```

    - backand & recovery with compression
    - backup & recovery with compression
    ```bash
    $ pg_dump dbname | gzip > filename.gz
    $ gunzip -c filename.gz | psql dbname
  13. hanksudo revised this gist Oct 19, 2015. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion PostgreSQL_Note.md
    View file
    Original file line number Diff line number Diff line change
    @@ -247,7 +247,7 @@ java -jar apgdiff-2.4.jar --ignore-start-with original.sql new.sql
    - [PostgreSQL: Documentation: 9.4: Continuous Archiving and Point-in-Time Recovery (PITR)](http://www.postgresql.org/docs/9.4/static/continuous-archiving.html)
    - [Zero to PostgreSQL streaming replication in 10 mins | Greg Reinacker's Weblog](http://www.rassoc.com/gregr/weblog/2013/02/16/zero-to-postgresql-streaming-replication-in-10-mins/)
    - [12-Step Program for Scaling Web Applications on PostgreSQL](http://www.slideshare.net/kigster/12step-program-for-scaling-web-applications-on-postgresql)

    - [omniti-labs/pgtreats · GitHub](https://github.com/omniti-labs/pgtreats)

    ## Trouble shooting

  14. hanksudo revised this gist Oct 19, 2015. 1 changed file with 3 additions and 2 deletions.
    5 changes: 3 additions & 2 deletions PostgreSQL_Note.md
    View file
    Original file line number Diff line number Diff line change
    @@ -238,14 +238,15 @@ java -jar apgdiff-2.4.jar --ignore-start-with original.sql new.sql
    - [PostgreSQL: Documentation: 9.4: The Statistics Collector](http://www.postgresql.org/docs/current/static/monitoring-stats.html)
    - [Lock Monitoring - PostgreSQL wiki](https://wiki.postgresql.org/wiki/Lock_Monitoring)
    - [PgTune](http://pgtune.leopard.in.ua/)

    - [Improving PostgreSQL performance on AWS EC2](http://blog.2ndquadrant.com/postgresql_performance_on_ec2/)
    - [Number Of Database Connections - PostgreSQL wiki](https://wiki.postgresql.org/wiki/Number_Of_Database_Connections)
    - [PgBouncer - lightweight connection pooler for PostgreSQL](https://pgbouncer.github.io/)
    - [How much RAM is PostgreSQL using?](http://www.depesz.com/2012/06/09/how-much-ram-is-postgresql-using/)
    - [Database Isolation Levels And Their Effects on Performance and Scalability - High Scalability - ](http://highscalability.com/blog/2011/2/10/database-isolation-levels-and-their-effects-on-performance-a.html)
    - [PostgreSQL: Documentation: 9.4: Continuous Archiving and Point-in-Time Recovery (PITR)](http://www.postgresql.org/docs/9.4/static/continuous-archiving.html)
    - [Another PostgreSQL Diff Tool (apgdiff) - Official Homepage](http://apgdiff.com/)

    - [Zero to PostgreSQL streaming replication in 10 mins | Greg Reinacker's Weblog](http://www.rassoc.com/gregr/weblog/2013/02/16/zero-to-postgresql-streaming-replication-in-10-mins/)
    - [12-Step Program for Scaling Web Applications on PostgreSQL](http://www.slideshare.net/kigster/12step-program-for-scaling-web-applications-on-postgresql)


    ## Trouble shooting
  15. hanksudo revised this gist Oct 14, 2015. 1 changed file with 12 additions and 2 deletions.
    14 changes: 12 additions & 2 deletions PostgreSQL_Note.md
    View file
    Original file line number Diff line number Diff line change
    @@ -27,6 +27,7 @@ $ gunzip -c filename.gz | psql dbname
    ```

    - monitoring

    ```bash
    htop -u postgres
    ```
    @@ -220,6 +221,13 @@ SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
    FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
    ```

    ## compare two databases

    ```
    sudo -u postgres pg_dump -f original.sql db_name
    sudo -u postgres pg_dump -h 192.168.33.32 -f new.sql db_name
    java -jar apgdiff-2.4.jar --ignore-start-with original.sql new.sql
    ```

    ## Other References

    @@ -236,6 +244,8 @@ SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
    - [How much RAM is PostgreSQL using?](http://www.depesz.com/2012/06/09/how-much-ram-is-postgresql-using/)
    - [Database Isolation Levels And Their Effects on Performance and Scalability - High Scalability - ](http://highscalability.com/blog/2011/2/10/database-isolation-levels-and-their-effects-on-performance-a.html)
    - [PostgreSQL: Documentation: 9.4: Continuous Archiving and Point-in-Time Recovery (PITR)](http://www.postgresql.org/docs/9.4/static/continuous-archiving.html)
    - [Another PostgreSQL Diff Tool (apgdiff) - Official Homepage](http://apgdiff.com/)



    ## Trouble shooting
    @@ -255,6 +265,6 @@ SELECT * FROM pg_locks WHERE mode = 'SIReadLock';
    ```

    ```bash
    watch -n 1 "psql -c \"SELECT * FROM pg_locks WHERE mode='SIReadLock';\" -L SIReadLock.log"
    grep -Eo '([0-9]+) rows' SIReadLock.log | sort -rn | head -n 1
    watch -n 3 "psql -c \"SELECT * FROM pg_locks WHERE mode='SIReadLock';\" -L SIReadLock.log"
    grep -nEo '([0-9]+) rows' SIReadLock.log | sort -rn | head -n 1
    ```
  16. hanksudo revised this gist Oct 11, 2015. 1 changed file with 3 additions and 1 deletion.
    4 changes: 3 additions & 1 deletion PostgreSQL_Note.md
    View file
    Original file line number Diff line number Diff line change
    @@ -1,10 +1,11 @@
    ## PostgreSQL Cheat sheet
    ## PostgreSQL Note

    ### Commands

    - basic
    ```
    $ psql
    $ psql -U user -W
    $ psql -U postgres
    $ psql -U postgres -h locahlost
    ```
    @@ -225,6 +226,7 @@ SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
    - [PostgreSQL - Memory](http://michael.otacoo.com/manuals/postgresql/settings/memory/)
    - [PostgreSQL: Documentation: 9.4: Transaction Isolation](http://www.postgresql.org/docs/9.4/static/transaction-iso.html)
    - [PostgreSQL: Documentation: 9.4: Performance Tips](http://www.postgresql.org/docs/9.4/static/performance-tips.html)
    - [PostgreSQL: Documentation: 9.4: Replication](http://www.postgresql.org/docs/9.4/static/runtime-config-replication.html)
    - [PostgreSQL: Documentation: 9.4: The Statistics Collector](http://www.postgresql.org/docs/current/static/monitoring-stats.html)
    - [Lock Monitoring - PostgreSQL wiki](https://wiki.postgresql.org/wiki/Lock_Monitoring)
    - [PgTune](http://pgtune.leopard.in.ua/)
  17. hanksudo renamed this gist Oct 8, 2015. 1 changed file with 0 additions and 0 deletions.
    0 postgreSQLnote.md → PostgreSQL_Note.md
    View file
    File renamed without changes.
  18. hanksudo revised this gist Oct 8, 2015. 1 changed file with 3 additions and 0 deletions.
    3 changes: 3 additions & 0 deletions postgreSQLnote.md
    View file
    Original file line number Diff line number Diff line change
    @@ -79,6 +79,7 @@ https://gist.github.com/hanksudo/f1365f3a764d3c8519cc
    - create role
    ```
    CREATE ROLE demorole WITH LOGIN ENCRYPTED PASSWORD 'password' CREATEDB;
    CREATE ROLE replicator WITH REPLICATION LOGIN ENCRYPTED PASSWORD 'password';
    ```

    - alter role
    @@ -232,6 +233,8 @@ SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
    - [PgBouncer - lightweight connection pooler for PostgreSQL](https://pgbouncer.github.io/)
    - [How much RAM is PostgreSQL using?](http://www.depesz.com/2012/06/09/how-much-ram-is-postgresql-using/)
    - [Database Isolation Levels And Their Effects on Performance and Scalability - High Scalability - ](http://highscalability.com/blog/2011/2/10/database-isolation-levels-and-their-effects-on-performance-a.html)
    - [PostgreSQL: Documentation: 9.4: Continuous Archiving and Point-in-Time Recovery (PITR)](http://www.postgresql.org/docs/9.4/static/continuous-archiving.html)


    ## Trouble shooting

  19. hanksudo revised this gist Oct 5, 2015. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion postgreSQLnote.md
    View file
    Original file line number Diff line number Diff line change
    @@ -231,7 +231,7 @@ SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
    - [Number Of Database Connections - PostgreSQL wiki](https://wiki.postgresql.org/wiki/Number_Of_Database_Connections)
    - [PgBouncer - lightweight connection pooler for PostgreSQL](https://pgbouncer.github.io/)
    - [How much RAM is PostgreSQL using?](http://www.depesz.com/2012/06/09/how-much-ram-is-postgresql-using/)

    - [Database Isolation Levels And Their Effects on Performance and Scalability - High Scalability - ](http://highscalability.com/blog/2011/2/10/database-isolation-levels-and-their-effects-on-performance-a.html)

    ## Trouble shooting

  20. hanksudo revised this gist Oct 5, 2015. 1 changed file with 3 additions and 0 deletions.
    3 changes: 3 additions & 0 deletions postgreSQLnote.md
    View file
    Original file line number Diff line number Diff line change
    @@ -235,6 +235,9 @@ SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /

    ## Trouble shooting

    - [Re: How to monitor locks (max_pred_locks_per_transaction)?](http://www.postgresql.org/message-id/CADKuZZCgfVoRJ1UgUkiLNjfOh54-Hq5kkrAsiTBZPKz+pvYwjQ@mail.gmail.com)


    ```
    out of shared memory
    HINT: You might need to increase max_pred_locks_per_transaction.
  21. hanksudo revised this gist Oct 2, 2015. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions postgreSQLnote.md
    View file
    Original file line number Diff line number Diff line change
    @@ -222,6 +222,7 @@ SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
    ## Other References

    - [PostgreSQL - Memory](http://michael.otacoo.com/manuals/postgresql/settings/memory/)
    - [PostgreSQL: Documentation: 9.4: Transaction Isolation](http://www.postgresql.org/docs/9.4/static/transaction-iso.html)
    - [PostgreSQL: Documentation: 9.4: Performance Tips](http://www.postgresql.org/docs/9.4/static/performance-tips.html)
    - [PostgreSQL: Documentation: 9.4: The Statistics Collector](http://www.postgresql.org/docs/current/static/monitoring-stats.html)
    - [Lock Monitoring - PostgreSQL wiki](https://wiki.postgresql.org/wiki/Lock_Monitoring)
  22. hanksudo revised this gist Oct 2, 2015. 1 changed file with 20 additions and 0 deletions.
    20 changes: 20 additions & 0 deletions postgreSQLnote.md
    View file
    Original file line number Diff line number Diff line change
    @@ -229,3 +229,23 @@ SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
    - [Improving PostgreSQL performance on AWS EC2](http://blog.2ndquadrant.com/postgresql_performance_on_ec2/)
    - [Number Of Database Connections - PostgreSQL wiki](https://wiki.postgresql.org/wiki/Number_Of_Database_Connections)
    - [PgBouncer - lightweight connection pooler for PostgreSQL](https://pgbouncer.github.io/)
    - [How much RAM is PostgreSQL using?](http://www.depesz.com/2012/06/09/how-much-ram-is-postgresql-using/)


    ## Trouble shooting

    ```
    out of shared memory
    HINT: You might need to increase max_pred_locks_per_transaction.
    ```

    The total 'SIReadLock' count must be less than max_pred_locks_per_transaction * max_connections.

    ```sql
    SELECT * FROM pg_locks WHERE mode = 'SIReadLock';
    ```

    ```bash
    watch -n 1 "psql -c \"SELECT * FROM pg_locks WHERE mode='SIReadLock';\" -L SIReadLock.log"
    grep -Eo '([0-9]+) rows' SIReadLock.log | sort -rn | head -n 1
    ```
  23. hanksudo revised this gist Sep 29, 2015. 1 changed file with 34 additions and 1 deletion.
    35 changes: 34 additions & 1 deletion postgreSQLnote.md
    View file
    Original file line number Diff line number Diff line change
    @@ -19,6 +19,24 @@ $ pg_dump db_name > file
    $ psql db_name < file
    ```

    - backand & recovery with compression
    ```bash
    $ pg_dump dbname | gzip > filename.gz
    $ gunzip -c filename.gz | psql dbname
    ```

    - monitoring
    ```bash
    htop -u postgres
    ```

    ### Dump all databases and reload databases

    ```bash
    $ pg_dumpall > db.out
    $ psql -f db.out postgres
    ```

    ### Database

    - list all databases
    @@ -48,6 +66,9 @@ CREATE DATABASE newdb WITH OWNER demorole ENCODING 'UTF8';
    \d table_name
    ```

    #### reassign all tables owner
    https://gist.github.com/hanksudo/f1365f3a764d3c8519cc

    ### Role

    - list roles
    @@ -63,6 +84,7 @@ CREATE ROLE demorole WITH LOGIN ENCRYPTED PASSWORD 'password' CREATEDB;
    - alter role
    ```
    ALTER ROLE demorole CREATEROLE CREATEDB REPLICATION SUPERUSER;
    ALTER ROLE demorole WITH ENCRYPTED PASSWORD '123456';
    ```

    - drop role
    @@ -93,7 +115,6 @@ DROP INDEX diary_user_id_id_idx;
    ```



    ### Other

    - Show config file path
    @@ -196,3 +217,15 @@ SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
    nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
    FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
    ```


    ## Other References

    - [PostgreSQL - Memory](http://michael.otacoo.com/manuals/postgresql/settings/memory/)
    - [PostgreSQL: Documentation: 9.4: Performance Tips](http://www.postgresql.org/docs/9.4/static/performance-tips.html)
    - [PostgreSQL: Documentation: 9.4: The Statistics Collector](http://www.postgresql.org/docs/current/static/monitoring-stats.html)
    - [Lock Monitoring - PostgreSQL wiki](https://wiki.postgresql.org/wiki/Lock_Monitoring)
    - [PgTune](http://pgtune.leopard.in.ua/)
    - [Improving PostgreSQL performance on AWS EC2](http://blog.2ndquadrant.com/postgresql_performance_on_ec2/)
    - [Number Of Database Connections - PostgreSQL wiki](https://wiki.postgresql.org/wiki/Number_Of_Database_Connections)
    - [PgBouncer - lightweight connection pooler for PostgreSQL](https://pgbouncer.github.io/)
  24. hanksudo revised this gist Jul 7, 2015. 1 changed file with 23 additions and 0 deletions.
    23 changes: 23 additions & 0 deletions postgreSQLnote.md
    View file
    Original file line number Diff line number Diff line change
    @@ -149,6 +149,29 @@ SELECT * FROM product WHERE serial_number ~ '\w{15}';
    EXPLAIN ANALYZE select * from api_log;
    ```

    - Sql function that returns a table with I/O infomation (obtained from pg_statio_user_indexes view) and filtered by current database and "public" schema
    ```sql
    CREATE OR REPLACE FUNCTION indexes_io_for_current_database()
    RETURNS TABLE(indexname name, idx_blks_read bigint, idx_blks_hit bigint) AS $$
    SELECT i.indexrelname, i.idx_blks_read, i.idx_blks_hit
    FROM pg_statio_user_indexes AS i
    WHERE i.relname IN (SELECT table_name FROM information_schema.tables WHERE table_schema = 'public');
    $$ LANGUAGE SQL;
    SELECT * FROM indexes_io_for_current_database() ORDER BY idx_blks_hit DESC LIMIT 5;
    ```

    - Sql function that returns a table with index usage infomation (obtained from pg_stat_user_indexes view) and filtered by current database and "public" schema:
    ```sql
    CREATE OR REPLACE FUNCTION indexes_stat_for_current_database()
    RETURNS TABLE(indexname name, idx_scan bigint, idx_tup_read bigint, idx_tup_fetch bigint) AS $$
    SELECT i.indexrelname, i.idx_scan, i.idx_tup_read, i.idx_tup_fetch
    FROM pg_stat_user_indexes AS i
    WHERE i.relname IN (SELECT table_name FROM information_schema.tables WHERE table_schema = 'public');
    $$ LANGUAGE SQL;

    SELECT * FROM indexes_stat_for_current_database() ORDER BY idx_scan DESC LIMIT 5;
    ```

    ### Extensions

    #### pg_stat_statements
  25. hanksudo revised this gist Jul 7, 2015. 1 changed file with 25 additions and 0 deletions.
    25 changes: 25 additions & 0 deletions postgreSQLnote.md
    View file
    Original file line number Diff line number Diff line change
    @@ -148,3 +148,28 @@ SELECT * FROM product WHERE serial_number ~ '\w{15}';
    ```
    EXPLAIN ANALYZE select * from api_log;
    ```

    ### Extensions

    #### pg_stat_statements

    add into /etc/postgresql/9.4/main/postgresql.conf
    ```
    shared_preload_libraries = 'pg_stat_statements' # (change requires restart)
    pg_stat_statements.max = 10000
    pg_stat_statements.track = all
    ```

    ```bash
    sudo su - postgres
    psql
    ```

    ```sql
    \x
    CREATE extension pg_stat_statements;
    \dx
    SELECT query, calls, total_time, rows, 100.0 * shared_blks_hit /
    nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent
    FROM pg_stat_statements ORDER BY total_time DESC LIMIT 5;
    ```
  26. hanksudo revised this gist May 25, 2015. 1 changed file with 15 additions and 8 deletions.
    23 changes: 15 additions & 8 deletions postgreSQLnote.md
    View file
    Original file line number Diff line number Diff line change
    @@ -101,6 +101,18 @@ DROP INDEX diary_user_id_id_idx;
    SHOW config_file;
    ```

    - Show current setting
    ```
    \set
    ```

    - show the value of a run-time parameter
    ```
    SHOW ALL;
    SHOW SERVER_VERSION;
    ```


    ### SQLs

    - alter foreign key
    @@ -116,7 +128,7 @@ WHERE table_schema='public' AND table_type='BASE TABLE';

    - update datetime
    ```
    update diary set recorded_at = recorded_at + INTERVAL '17 days'
    UPDATE diary SET recorded_at = recorded_at + INTERVAL '17 days'
    ```

    - Distinct ON Multi columns
    @@ -128,16 +140,11 @@ SELECT DISTINCT ON(glucose_value, recorded_at) glucose_value, recorded_at FROM d
    - Regular Expression

    ```
    select * from product where serial_number !~ '\w{15}';
    select * from product where serial_number ~ '\w{15}';
    SELECT * FROM product WHERE serial_number !~ '\w{15}';
    SELECT * FROM product WHERE serial_number ~ '\w{15}';
    ```

    - Explain Analyze
    ```
    EXPLAIN ANALYZE select * from api_log;
    ```

    - Show current setting
    ```
    # \set
    ```
  27. hanksudo revised this gist Apr 22, 2015. 1 changed file with 31 additions and 6 deletions.
    37 changes: 31 additions & 6 deletions postgreSQLnote.md
    View file
    Original file line number Diff line number Diff line change
    @@ -70,6 +70,30 @@ ALTER ROLE demorole CREATEROLE CREATEDB REPLICATION SUPERUSER;
    DROP ROLE demorole;
    ```

    ### Index

    - list all indexes
    ```
    SELECT * FROM pg_stat_all_indexes WHERE schemaname = 'public' ORDER BY indexrelname;
    ```

    - list index of table
    ```
    \d table_name
    ```

    - create index
    ```
    CREATE INDEX CONCURRENTLY diary_user_id_id_idx ON diary(user_id, id);
    ```

    - drop index
    ```
    DROP INDEX diary_user_id_id_idx;
    ```



    ### Other

    - Show config file path
    @@ -80,21 +104,17 @@ SHOW config_file;
    ### SQLs

    - alter foreign key

    ```
    ALTER TABLE diary ADD FOREIGN KEY (meter_id) REFERENCES user_meter(id);
    ```

    - list tables name
    ```
    SELECT table_name
    FROM information_schema.tables
    WHERE table_schema='public'
    AND table_type='BASE TABLE';
    SELECT table_name FROM information_schema.tables
    WHERE table_schema='public' AND table_type='BASE TABLE';
    ```

    - update datetime

    ```
    update diary set recorded_at = recorded_at + INTERVAL '17 days'
    ```
    @@ -112,6 +132,11 @@ select * from product where serial_number !~ '\w{15}';
    select * from product where serial_number ~ '\w{15}';
    ```

    - Explain Analyze
    ```
    EXPLAIN ANALYZE select * from api_log;
    ```

    - Show current setting
    ```
    # \set
  28. hanksudo revised this gist Apr 21, 2015. 1 changed file with 6 additions and 31 deletions.
    37 changes: 6 additions & 31 deletions postgreSQLnote.md
    View file
    Original file line number Diff line number Diff line change
    @@ -70,30 +70,6 @@ ALTER ROLE demorole CREATEROLE CREATEDB REPLICATION SUPERUSER;
    DROP ROLE demorole;
    ```

    ### Index

    - list all indexes
    ```
    SELECT * FROM pg_stat_all_indexes WHERE schemaname = 'public' ORDER BY indexrelname;
    ```

    - list index of table
    ```
    \d table_name
    ```

    - create index
    ```
    CREATE INDEX CONCURRENTLY diary_user_id_id_idx ON diary(user_id, id);
    ```

    - drop index
    ```
    DROP INDEX diary_user_id_id_idx;
    ```



    ### Other

    - Show config file path
    @@ -104,17 +80,21 @@ SHOW config_file;
    ### SQLs

    - alter foreign key

    ```
    ALTER TABLE diary ADD FOREIGN KEY (meter_id) REFERENCES user_meter(id);
    ```

    - list tables name
    ```
    SELECT table_name FROM information_schema.tables
    WHERE table_schema='public' AND table_type='BASE TABLE';
    SELECT table_name
    FROM information_schema.tables
    WHERE table_schema='public'
    AND table_type='BASE TABLE';
    ```

    - update datetime

    ```
    update diary set recorded_at = recorded_at + INTERVAL '17 days'
    ```
    @@ -132,11 +112,6 @@ select * from product where serial_number !~ '\w{15}';
    select * from product where serial_number ~ '\w{15}';
    ```

    - Explain Analyze
    ```
    EXPLAIN ANALYZE select * from api_log;
    ```

    - Show current setting
    ```
    # \set
  29. hanksudo revised this gist Apr 21, 2015. 1 changed file with 31 additions and 4 deletions.
    35 changes: 31 additions & 4 deletions postgreSQLnote.md
    View file
    Original file line number Diff line number Diff line change
    @@ -70,6 +70,30 @@ ALTER ROLE demorole CREATEROLE CREATEDB REPLICATION SUPERUSER;
    DROP ROLE demorole;
    ```

    ### Index

    - list all indexes
    ```
    SELECT * FROM pg_stat_all_indexes WHERE schemaname = 'public' ORDER BY indexrelname;
    ```

    - list index of table
    ```
    \d table_name
    ```

    - create index
    ```
    CREATE INDEX CONCURRENTLY diary_user_id_id_idx ON diary(user_id, id);
    ```

    - drop index
    ```
    DROP INDEX diary_user_id_id_idx;
    ```



    ### Other

    - Show config file path
    @@ -86,10 +110,8 @@ ALTER TABLE diary ADD FOREIGN KEY (meter_id) REFERENCES user_meter(id);

    - list tables name
    ```
    SELECT table_name
    FROM information_schema.tables
    WHERE table_schema='public'
    AND table_type='BASE TABLE';
    SELECT table_name FROM information_schema.tables
    WHERE table_schema='public' AND table_type='BASE TABLE';
    ```

    - update datetime
    @@ -110,6 +132,11 @@ select * from product where serial_number !~ '\w{15}';
    select * from product where serial_number ~ '\w{15}';
    ```

    - Explain Analyze
    ```
    EXPLAIN ANALYZE select * from api_log;
    ```

    - Show current setting
    ```
    # \set
  30. hanksudo revised this gist Sep 10, 2014. 1 changed file with 8 additions and 0 deletions.
    8 changes: 8 additions & 0 deletions postgreSQLnote.md
    View file
    Original file line number Diff line number Diff line change
    @@ -98,11 +98,19 @@ update diary set recorded_at = recorded_at + INTERVAL '17 days'
    ```

    - Distinct ON Multi columns

    ```
    SELECT DISTINCT ON(glucose_value, recorded_at) glucose_value, recorded_at FROM diary WHERE user_id = 1 ORDER BY recorded_at;
    ```

    - Regular Expression

    ```
    select * from product where serial_number !~ '\w{15}';
    select * from product where serial_number ~ '\w{15}';
    ```

    - Show current setting
    ```
    # \set
    ```