Skip to content

Instantly share code, notes, and snippets.

@stephenaument

stephenaument/postgres_queries_and_commands.sql

Forked from rgreenjr/postgres_queries_and_commands.sql
Created July 14, 2020 20:13
Show Gist options
  • Save stephenaument/c813a3e2940ba0102d42d9ecd0f628f4 to your computer and use it in GitHub Desktop.
Save stephenaument/c813a3e2940ba0102d42d9ecd0f628f4 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. @rgreenjr rgreenjr revised this gist Aug 12, 2018. 1 changed file with 2 additions and 2 deletions.
    4 changes: 2 additions & 2 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,11 +1,11 @@
    -- show running queries (pre 9.2)
    SELECT procpid, age(query_start, clock_timestamp()), usename, current_query
    SELECT procpid, age(clock_timestamp(), query_start), usename, current_query
    FROM pg_stat_activity
    WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%'
    ORDER BY query_start desc;

    -- show running queries (9.2)
    SELECT pid, age(query_start, clock_timestamp()), usename, query
    SELECT pid, age(clock_timestamp(), query_start), usename, query
    FROM pg_stat_activity
    WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'
    ORDER BY query_start desc;
  2. @rgreenjr rgreenjr renamed this gist Sep 8, 2015. 1 changed file with 0 additions and 0 deletions.
    0 Useful PostgreSQL Queries and Commands → postgres_queries_and_commands.sql
    View file
    File renamed without changes.
  3. @rgreenjr rgreenjr renamed this gist Sep 8, 2013. 1 changed file with 0 additions and 0 deletions.
    0 postgres.sql → Useful PostgreSQL Queries and Commands
    View file
    File renamed without changes.
  4. @rgreenjr rgreenjr revised this gist Jun 20, 2013. 1 changed file with 6 additions and 0 deletions.
    6 changes: 6 additions & 0 deletions postgres.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -42,3 +42,9 @@ ORDER BY n_live_tup DESC;
    -- how many indexes are in cache
    SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit) as idx_hit, (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
    FROM pg_statio_user_indexes;

    -- Dump database on remote host to file
    $ pg_dump -U username -h hostname databasename > dump.sql

    -- Import dump into existing database
    $ psql -d newdb -f dump.sql
  5. @rgreenjr rgreenjr revised this gist Jun 20, 2013. 1 changed file with 0 additions and 4 deletions.
    4 changes: 0 additions & 4 deletions postgres.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -42,7 +42,3 @@ ORDER BY n_live_tup DESC;
    -- how many indexes are in cache
    SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit) as idx_hit, (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
    FROM pg_statio_user_indexes;

    -- Dump database on remote host to file
    pg_dump -U username -h hostname databasename > dump.sql

  6. @rgreenjr rgreenjr revised this gist Jun 20, 2013. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion postgres.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -44,5 +44,5 @@ SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit) as idx_hit, (sum(idx_b
    FROM pg_statio_user_indexes;

    -- Dump database on remote host to file
    pg_dump -U sideshow_user_production -h hideout.ziften.local sideshow_production > sideshow_production.sql
    pg_dump -U username -h hostname databasename > dump.sql

  7. @rgreenjr rgreenjr revised this gist Jun 20, 2013. 1 changed file with 4 additions and 0 deletions.
    4 changes: 4 additions & 0 deletions postgres.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -42,3 +42,7 @@ ORDER BY n_live_tup DESC;
    -- how many indexes are in cache
    SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit) as idx_hit, (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
    FROM pg_statio_user_indexes;

    -- Dump database on remote host to file
    pg_dump -U sideshow_user_production -h hideout.ziften.local sideshow_production > sideshow_production.sql

  8. @rgreenjr rgreenjr revised this gist Apr 16, 2013. 1 changed file with 3 additions and 0 deletions.
    3 changes: 3 additions & 0 deletions postgres.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -16,6 +16,9 @@ SELECT pg_cancel_backend(procpid);
    -- kill idle query
    SELECT pg_terminate_backend(procpid);

    -- vacuum command
    VACUUM (VERBOSE, ANALYZE);

    -- all database users
    select * from pg_stat_activity where current_query not like '<%';

  9. @rgreenjr rgreenjr revised this gist Apr 16, 2013. 1 changed file with 1 addition and 4 deletions.
    5 changes: 1 addition & 4 deletions postgres.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -28,10 +28,7 @@ from pg_database
    order by pg_database_size(datname) desc;

    -- cache hit rates (should not be less than 0.99)
    SELECT
    sum(heap_blks_read) as heap_read,
    sum(heap_blks_hit) as heap_hit,
    (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
    SELECT sum(heap_blks_read) as heap_read, sum(heap_blks_hit) as heap_hit, (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
    FROM pg_statio_user_tables;

    -- table index usage rates (should not be less than 0.99)
  10. @rgreenjr rgreenjr revised this gist Apr 16, 2013. 1 changed file with 9 additions and 21 deletions.
    30 changes: 9 additions & 21 deletions postgres.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -23,34 +23,22 @@ select * from pg_stat_activity where current_query not like '<%';
    select * from pg_user;

    -- all tables and their size, with/without indexes
    select datname,
    pg_size_pretty(pg_database_size(datname))
    from pg_database
    order by pg_database_size(datname) desc;

    select datname, pg_size_pretty(pg_database_size(datname))
    from pg_database
    order by pg_database_size(datname) desc;

    -- cache hit rates (should not be less than 0.99)
    SELECT
    sum(heap_blks_read) as heap_read,
    sum(heap_blks_hit) as heap_hit,
    (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
    FROM
    pg_statio_user_tables;
    FROM pg_statio_user_tables;

    -- table index usage rates (should not be less than 0.99)
    SELECT
    relname,
    100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used,
    n_live_tup rows_in_table
    FROM
    pg_stat_user_tables
    ORDER BY
    n_live_tup DESC;
    SELECT relname, 100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used, n_live_tup rows_in_table
    FROM pg_stat_user_tables
    ORDER BY n_live_tup DESC;

    -- how many indexes are in cache
    SELECT
    sum(idx_blks_read) as idx_read,
    sum(idx_blks_hit) as idx_hit,
    (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
    FROM
    pg_statio_user_indexes;
    SELECT sum(idx_blks_read) as idx_read, sum(idx_blks_hit) as idx_hit, (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
    FROM pg_statio_user_indexes;
  11. @rgreenjr rgreenjr revised this gist Apr 16, 2013. 1 changed file with 7 additions and 1 deletion.
    8 changes: 7 additions & 1 deletion postgres.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,9 +1,15 @@
    -- show running queries
    -- show running queries (pre 9.2)
    SELECT procpid, age(query_start, clock_timestamp()), usename, current_query
    FROM pg_stat_activity
    WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%'
    ORDER BY query_start desc;

    -- show running queries (9.2)
    SELECT pid, age(query_start, clock_timestamp()), usename, query
    FROM pg_stat_activity
    WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'
    ORDER BY query_start desc;

    -- kill running query
    SELECT pg_cancel_backend(procpid);

  12. @rgreenjr rgreenjr revised this gist Oct 6, 2012. 1 changed file with 3 additions and 0 deletions.
    3 changes: 3 additions & 0 deletions postgres.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -7,6 +7,9 @@ ORDER BY query_start desc;
    -- kill running query
    SELECT pg_cancel_backend(procpid);

    -- kill idle query
    SELECT pg_terminate_backend(procpid);

    -- all database users
    select * from pg_stat_activity where current_query not like '<%';

  13. @rgreenjr rgreenjr revised this gist Oct 3, 2012. 1 changed file with 9 additions and 1 deletion.
    10 changes: 9 additions & 1 deletion postgres.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -36,4 +36,12 @@ SELECT
    FROM
    pg_stat_user_tables
    ORDER BY
    n_live_tup DESC;
    n_live_tup DESC;

    -- how many indexes are in cache
    SELECT
    sum(idx_blks_read) as idx_read,
    sum(idx_blks_hit) as idx_hit,
    (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) as ratio
    FROM
    pg_statio_user_indexes;
  14. @rgreenjr rgreenjr revised this gist Oct 3, 2012. 1 changed file with 19 additions and 0 deletions.
    19 changes: 19 additions & 0 deletions postgres.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -18,3 +18,22 @@ select datname,
    pg_size_pretty(pg_database_size(datname))
    from pg_database
    order by pg_database_size(datname) desc;


    -- cache hit rates (should not be less than 0.99)
    SELECT
    sum(heap_blks_read) as heap_read,
    sum(heap_blks_hit) as heap_hit,
    (sum(heap_blks_hit) - sum(heap_blks_read)) / sum(heap_blks_hit) as ratio
    FROM
    pg_statio_user_tables;

    -- table index usage rates (should not be less than 0.99)
    SELECT
    relname,
    100 * idx_scan / (seq_scan + idx_scan) percent_of_times_index_used,
    n_live_tup rows_in_table
    FROM
    pg_stat_user_tables
    ORDER BY
    n_live_tup DESC;
  15. @rgreenjr rgreenjr created this gist Sep 5, 2012.
    20 changes: 20 additions & 0 deletions postgres.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,20 @@
    -- show running queries
    SELECT procpid, age(query_start, clock_timestamp()), usename, current_query
    FROM pg_stat_activity
    WHERE current_query != '<IDLE>' AND current_query NOT ILIKE '%pg_stat_activity%'
    ORDER BY query_start desc;

    -- kill running query
    SELECT pg_cancel_backend(procpid);

    -- all database users
    select * from pg_stat_activity where current_query not like '<%';

    -- all databases and their sizes
    select * from pg_user;

    -- all tables and their size, with/without indexes
    select datname,
    pg_size_pretty(pg_database_size(datname))
    from pg_database
    order by pg_database_size(datname) desc;