Skip to content

Instantly share code, notes, and snippets.

@gplv2

gplv2/postgres_queries_and_commands.sql

Forked from rgreenjr/postgres_queries_and_commands.sql
Last active July 3, 2018 11:44
Show Gist options
  • Save gplv2/3625736273e40095eeabf470117881a5 to your computer and use it in GitHub Desktop.
Save gplv2/3625736273e40095eeabf470117881a5 to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. gplv2 revised this gist Jul 3, 2018. 1 changed file with 9 additions and 0 deletions.
    9 changes: 9 additions & 0 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -69,6 +69,15 @@ JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
    WHERE NOT blockedl.granted
    AND blockinga.datname = current_database()
    );

    -- virtual transaction lock id search
    SELECT locktype, relation::regclass, mode, transactionid AS tid,
    virtualtransaction AS vtid, pid, granted
    FROM pg_catalog.pg_locks l LEFT JOIN pg_catalog.pg_database db
    ON db.oid = l.database WHERE (db.datname = 'sandbox' OR db.datname IS NULL)
    AND NOT pid = pg_backend_pid();

    -- Note that each transaction also hold an `ExclusiveLock on its own virtualxid that is their virtual transaction ID.

    -- missing indexes
    SELECT schemaname, relname, seq_scan-idx_scan AS too_much_seq, case when seq_scan-idx_scan>0 THEN 'Missing Index?' ELSE 'OK' END, pg_relation_size(format('%I.%I', schemaname, relname)::regclass) AS rel_size, seq_scan, idx_scan
  2. gplv2 revised this gist Jul 3, 2018. 1 changed file with 32 additions and 0 deletions.
    32 changes: 32 additions & 0 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -38,6 +38,38 @@ SELECT a.datname,
    JOIN pg_class c ON c.oid = l.relation
    ORDER BY a.query_start;

    -- lock
    SELECT bl.pid AS blocked_pid,
    a.usename AS blocked_user,
    kl.pid AS blocking_pid,
    ka.usename AS blocking_user,
    a.query AS blocked_statement
    FROM pg_catalog.pg_locks bl
    JOIN pg_catalog.pg_stat_activity a ON a.pid = bl.pid
    JOIN pg_catalog.pg_locks kl ON kl.transactionid = bl.transactionid AND kl.pid != bl.pid
    JOIN pg_catalog.pg_stat_activity ka ON ka.pid = kl.pid
    WHERE NOT bl.granted;

    -- more lock detection
    CREATE VIEW lock_monitor AS(
    SELECT
    COALESCE(blockingl.relation::regclass::text,blockingl.locktype) as locked_item,
    now() - blockeda.query_start AS waiting_duration, blockeda.pid AS blocked_pid,
    blockeda.query as blocked_query, blockedl.mode as blocked_mode,
    blockinga.pid AS blocking_pid, blockinga.query as blocking_query,
    blockingl.mode as blocking_mode
    FROM pg_catalog.pg_locks blockedl
    JOIN pg_stat_activity blockeda ON blockedl.pid = blockeda.pid
    JOIN pg_catalog.pg_locks blockingl ON(
    ( (blockingl.transactionid=blockedl.transactionid) OR
    (blockingl.relation=blockedl.relation AND blockingl.locktype=blockedl.locktype)
    ) AND blockedl.pid != blockingl.pid)
    JOIN pg_stat_activity blockinga ON blockingl.pid = blockinga.pid
    AND blockinga.datid = blockeda.datid
    WHERE NOT blockedl.granted
    AND blockinga.datname = current_database()
    );

    -- missing indexes
    SELECT schemaname, relname, seq_scan-idx_scan AS too_much_seq, case when seq_scan-idx_scan>0 THEN 'Missing Index?' ELSE 'OK' END, pg_relation_size(format('%I.%I', schemaname, relname)::regclass) AS rel_size, seq_scan, idx_scan
    FROM pg_stat_user_tables
  3. gplv2 revised this gist Jul 3, 2018. 1 changed file with 89 additions and 0 deletions.
    89 changes: 89 additions & 0 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -87,7 +87,96 @@ SELECT blocked_locks.pid AS blocked_pid,

    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
    WHERE NOT blocked_locks.GRANTED;

    -- INSERT/UPDATE/DELETE statistics for tables:

    SELECT relname,cast(n_tup_ins AS numeric) / (n_tup_ins + n_tup_upd + n_tup_del) AS ins_pct,cast(n_tup_upd AS numeric) / (n_tup_ins + n_tup_upd + n_tup_del) AS upd_pct, cast(n_tup_del AS numeric) / (n_tup_ins
    + n_tup_upd + n_tup_del) AS del_pct
    FROM pg_stat_user_tables
    WHERE (n_tup_ins + n_tup_upd + n_tup_del) > 0
    ORDER BY relname;

    -- Find commmonly accessed tables and their use of indexes:
    SELECT relname,seq_tup_read,idx_tup_fetch,cast(idx_tup_fetch AS numeric) / (idx_tup_fetch + seq_tup_read) AS idx_tup_pct
    FROM pg_stat_user_tables WHERE (idx_tup_fetch + seq_tup_read)>0
    ORDER BY idx_tup_pct;

    -- Analysis: For each row, because "idx_tup_pct" is low than it means that essentially no indexes are being used.
    -- In the case of "facebook_oauths" it turns out we are commonly running a query like "SELECT * FROM facebook_oauths WHERE fb_user_id = X" and it turns out there isnt an index on "fb_user_id"

    -- Table I/O
    SELECT relname,cast(heap_blks_hit as numeric) / (heap_blks_hit + heap_blks_read) AS hit_pct,
    heap_blks_hit,heap_blks_read
    FROM pg_statio_user_tables WHERE (heap_blks_hit + heap_blks_read)>0 ORDER BY hit_pct;

    -- 'heap_blks_hit' = the number of blocks that were satisfied from the page cache
    -- 'heap_blks_read' = the number of blocks that had to hit disk/IO layer for reads

    -- When 'heap_blks_hit' is significantly greater than 'heap_blks_read' than it means we have a well-cached DB and most of the queries can be satisfied from the cache

    -- Table & Index sizes
    SELECT
    t.tablename,
    indexname,
    c.reltuples::integer AS num_rows,
    pg_size_pretty(pg_relation_size(quote_ident(t.tablename)::text)) AS table_size,
    pg_size_pretty(pg_relation_size(quote_ident(indexrelname)::text)) AS index_size,
    CASE WHEN x.is_unique = 1 THEN 'Y'
    ELSE 'N'
    END AS UNIQUE,
    idx_scan AS number_of_scans,
    idx_tup_read AS tuples_read,
    idx_tup_fetch AS tuples_fetched
    FROM pg_tables t
    LEFT OUTER JOIN pg_class c ON t.tablename=c.relname
    LEFT OUTER JOIN
    (SELECT indrelid,
    max(CAST(indisunique AS integer)) AS is_unique
    FROM pg_index
    GROUP BY indrelid) x
    ON c.oid = x.indrelid
    LEFT OUTER JOIN
    ( SELECT c.relname AS ctablename, ipg.relname AS indexname, x.indnatts AS number_of_columns, idx_scan, idx_tup_read, idx_tup_fetch,indexrelname FROM pg_index x
    JOIN pg_class c ON c.oid = x.indrelid
    JOIN pg_class ipg ON ipg.oid = x.indexrelid
    JOIN pg_stat_all_indexes psai ON x.indexrelid = psai.indexrelid )
    AS foo
    ON t.tablename = foo.ctablename
    WHERE t.schemaname='public'
    ORDER BY pg_relation_size(quote_ident(indexrelname)::text) desc;

    -- Index Health
    SELECT indexrelname,cast(idx_tup_read AS numeric) / idx_scan AS avg_tuples,idx_scan,idx_tup_read FROM pg_stat_user_indexes WHERE idx_scan > 0;
    -- Index Size
    SELECT
    schemaname,
    relname,
    indexrelname,
    idx_scan,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size
    FROM
    pg_stat_user_indexes i
    JOIN pg_index USING (indexrelid)
    WHERE
    indisunique IS false
    ORDER BY idx_scan,relname;

    -- Index I/O - Same idea as Table I/O above
    SELECT indexrelname,cast(idx_blks_hit as numeric) / (idx_blks_hit + idx_blks_read) AS hit_pct,
    idx_blks_hit,idx_blks_read FROM pg_statio_user_indexes WHERE
    (idx_blks_hit + idx_blks_read)>0 ORDER BY hit_pct;

    -- Show sizes & usage of indexes that are not used very often:
    -- NOTE: we define 'usage' by # of times used, in this case we use '200' - change accordingly

    SELECT idstat.relname AS table_name, indexrelname AS index_name, idstat.idx_scan AS times_used,
    pg_size_pretty(pg_relation_size(tabstat.relid)) AS table_size, pg_size_pretty(pg_relation_size(indexrelid)) AS index_size,
    n_tup_upd + n_tup_ins + n_tup_del as num_writes, indexdef AS definition
    FROM pg_stat_user_indexes AS idstat JOIN pg_indexes ON indexrelname = indexname
    JOIN pg_stat_user_tables AS tabstat ON idstat.relname = tabstat.relname
    WHERE idstat.idx_scan < 200 AND indexdef !~* 'unique'
    ORDER BY idstat.relname, indexrelname;

    -- kill running query (this will be a nice kill to the process)
    SELECT pg_cancel_backend(procpid);

  4. gplv2 revised this gist Mar 29, 2018. 1 changed file with 5 additions and 0 deletions.
    5 changes: 5 additions & 0 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -174,3 +174,8 @@ REASSIGN OWNED BY old_name TO new_name;
    -- within a single DB

    ALTER DATABASE old_owner OWNER TO new_owner;


    -- Sniffing pgsql queries

    $ tshark -i lo -p -f "(tcp[13] != 0x10) and dst port 5432" -t ad -w /tmp/output
  5. gplv2 revised this gist Mar 21, 2018. 1 changed file with 1 addition and 4 deletions.
    5 changes: 1 addition & 4 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -100,10 +100,7 @@ VACUUM (VERBOSE, ANALYZE);
    -- 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
    -- all tables and their size, with indexes
    select datname, pg_size_pretty(pg_database_size(datname))
    from pg_database
    order by pg_database_size(datname) desc;
  6. gplv2 revised this gist Mar 21, 2018. 1 changed file with 11 additions and 1 deletion.
    12 changes: 11 additions & 1 deletion postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -166,4 +166,14 @@ WHERE
    AND a.attnum = ANY(ix.indkey)
    AND t.relkind = 'r'
    AND t.relname LIKE '<tablename>'
    -- src : https://stackoverflow.com/questions/2204058/list-columns-with-indexes-in-postgresql
    -- src : https://stackoverflow.com/questions/2204058/list-columns-with-indexes-in-postgresql

    -- Change owner globaly
    -- This is a global equivalent of ALTER DATABASE command provided in Frank's answer, but instead
    -- of updating a particular DB, it change ownership of all DBs owned by 'old_name'.

    REASSIGN OWNED BY old_name TO new_name;

    -- within a single DB

    ALTER DATABASE old_owner OWNER TO new_owner;
  7. gplv2 revised this gist Mar 21, 2018. 1 changed file with 1 addition and 0 deletions.
    1 change: 1 addition & 0 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -166,3 +166,4 @@ WHERE
    AND a.attnum = ANY(ix.indkey)
    AND t.relkind = 'r'
    AND t.relname LIKE '<tablename>'
    -- src : https://stackoverflow.com/questions/2204058/list-columns-with-indexes-in-postgresql
  8. gplv2 revised this gist Mar 21, 2018. 1 changed file with 17 additions and 1 deletion.
    18 changes: 17 additions & 1 deletion postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -149,4 +149,20 @@ $ iptables -D INPUT -p tcp -m tcp --dport 5432 -m state --state NEW -j LOG --lo
    pg_dump -st planet_osm_polygon grb_temp | grep -i INDEX | grep -v '\-\-'



    -- Finding indexes belonging to table using metadata
    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
    WHERE
    t.oid = ix.indrelid
    AND i.oid = ix.indexrelid
    AND a.attrelid = t.oid
    AND a.attnum = ANY(ix.indkey)
    AND t.relkind = 'r'
    AND t.relname LIKE '<tablename>'
  9. gplv2 revised this gist Mar 20, 2018. 1 changed file with 9 additions and 0 deletions.
    9 changes: 9 additions & 0 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -141,3 +141,12 @@ $ fuser $(locate pg_log | grep $(date +'%Y-%m'))
    $ iptables -I INPUT -p tcp -m tcp --dport 5432 -m state --state NEW -j LOG --log-level 1 --log-prefix "New Connection"
    -- delete
    $ iptables -D INPUT -p tcp -m tcp --dport 5432 -m state --state NEW -j LOG --log-level 1 --log-prefix "New Connection"

    -- Schema stuff

    -- equivalent to mysql 'show create' , dump scheme, grep for INDEXES

    pg_dump -st planet_osm_polygon grb_temp | grep -i INDEX | grep -v '\-\-'



  10. gplv2 revised this gist Mar 12, 2018. 1 changed file with 20 additions and 1 deletion.
    21 changes: 20 additions & 1 deletion postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -39,11 +39,30 @@ SELECT a.datname,
    ORDER BY a.query_start;

    -- missing indexes

    SELECT schemaname, relname, seq_scan-idx_scan AS too_much_seq, case when seq_scan-idx_scan>0 THEN 'Missing Index?' ELSE 'OK' END, pg_relation_size(format('%I.%I', schemaname, relname)::regclass) AS rel_size, seq_scan, idx_scan
    FROM pg_stat_user_tables
    WHERE pg_relation_size(format('%I.%I', schemaname, relname)::regclass)>80000 ORDER BY too_much_seq DESC;

    -- Unused indexes
    SELECT
    PSUI.indexrelid::regclass AS IndexName
    ,PSUI.relid::regclass AS TableName
    FROM pg_stat_user_indexes AS PSUI
    JOIN pg_index AS PI
    ON PSUI.IndexRelid = PI.IndexRelid
    WHERE PSUI.idx_scan = 0
    AND PI.indisunique IS FALSE;

    -- duplicate indexes
    SELECT
    indrelid::regclass AS TableName
    ,array_agg(indexrelid::regclass) AS Indexes
    FROM pg_index
    GROUP BY
    indrelid
    ,indkey
    HAVING COUNT(*) > 1;

    -- The following query may be helpful to see what processes are blocking SQL statements (these only find row-level locks, not object-level locks).
    SELECT blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
  11. gplv2 revised this gist Mar 12, 2018. 1 changed file with 6 additions and 0 deletions.
    6 changes: 6 additions & 0 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -38,6 +38,12 @@ SELECT a.datname,
    JOIN pg_class c ON c.oid = l.relation
    ORDER BY a.query_start;

    -- missing indexes

    SELECT schemaname, relname, seq_scan-idx_scan AS too_much_seq, case when seq_scan-idx_scan>0 THEN 'Missing Index?' ELSE 'OK' END, pg_relation_size(format('%I.%I', schemaname, relname)::regclass) AS rel_size, seq_scan, idx_scan
    FROM pg_stat_user_tables
    WHERE pg_relation_size(format('%I.%I', schemaname, relname)::regclass)>80000 ORDER BY too_much_seq DESC;

    -- The following query may be helpful to see what processes are blocking SQL statements (these only find row-level locks, not object-level locks).
    SELECT blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
  12. gplv2 revised this gist Jan 11, 2018. 1 changed file with 41 additions and 0 deletions.
    41 changes: 41 additions & 0 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -22,6 +22,47 @@ SELECT
    FROM pg_stat_activity
    WHERE now() — pg_stat_activity.query_start > interval '5 minutes';

    -- lock information (9.3 +)
    SELECT a.datname,
    c.relname,
    l.transactionid,
    l.mode,
    l.GRANTED,
    a.usename,
    a.query,
    a.query_start,
    age(now(), a.query_start) AS "age",
    a.pid
    FROM pg_stat_activity a
    JOIN pg_locks l ON l.pid = a.pid
    JOIN pg_class c ON c.oid = l.relation
    ORDER BY a.query_start;

    -- The following query may be helpful to see what processes are blocking SQL statements (these only find row-level locks, not object-level locks).
    SELECT blocked_locks.pid AS blocked_pid,
    blocked_activity.usename AS blocked_user,
    blocking_locks.pid AS blocking_pid,
    blocking_activity.usename AS blocking_user,
    blocked_activity.query AS blocked_statement,
    blocking_activity.query AS current_statement_in_blocking_process
    FROM pg_catalog.pg_locks blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks blocking_locks
    ON blocking_locks.locktype = blocked_locks.locktype
    AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
    AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
    AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
    AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
    AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
    AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
    AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
    AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
    AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
    AND blocking_locks.pid != blocked_locks.pid

    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
    WHERE NOT blocked_locks.GRANTED;

    -- kill running query (this will be a nice kill to the process)
    SELECT pg_cancel_backend(procpid);

  13. gplv2 revised this gist Dec 1, 2017. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -7,7 +7,7 @@ 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)
    -- show running queries (post 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%'
  14. gplv2 revised this gist Nov 30, 2017. 1 changed file with 3 additions and 0 deletions.
    3 changes: 3 additions & 0 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -1,3 +1,6 @@
    -- performance tools
    -- https://www.vividcortex.com/resources/network-analyzer-for-postgresql

    -- show running queries (pre 9.2)
    SELECT procpid, age(query_start, clock_timestamp()), usename, current_query
    FROM pg_stat_activity
  15. gplv2 revised this gist Nov 21, 2017. 1 changed file with 6 additions and 0 deletions.
    6 changes: 6 additions & 0 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -66,3 +66,9 @@ SELECT * FROM pg_stat_replication;

    -- On Linux - Find the open pg_log files for this month (needs updatedb/locate package)
    $ fuser $(locate pg_log | grep $(date +'%Y-%m'))

    -- Firewall rule to log incomming connections to PSQL port
    -- enable
    $ iptables -I INPUT -p tcp -m tcp --dport 5432 -m state --state NEW -j LOG --log-level 1 --log-prefix "New Connection"
    -- delete
    $ iptables -D INPUT -p tcp -m tcp --dport 5432 -m state --state NEW -j LOG --log-level 1 --log-prefix "New Connection"
  16. gplv2 revised this gist Nov 10, 2017. 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
    @@ -64,5 +64,5 @@ SELECT * FROM pg_settings WHERE category LIKE 'File Locat%';
    -- Find the connected slaves
    SELECT * FROM pg_stat_replication;

    -- On Linux - Find the open pg_log files (needs updatedb/locate package)
    $ fuser $(locate pg_log | grep '2017-11')
    -- On Linux - Find the open pg_log files for this month (needs updatedb/locate package)
    $ fuser $(locate pg_log | grep $(date +'%Y-%m'))
  17. gplv2 revised this gist Nov 10, 2017. 1 changed file with 5 additions and 2 deletions.
    7 changes: 5 additions & 2 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -59,7 +59,10 @@ $ pg_dump -U username -h hostname databasename > dump.sql
    $ psql -d newdb -f dump.sql

    -- Multiple instances, find the configuration file of the connected instance
    $ SELECT * FROM pg_settings WHERE category LIKE 'File Locat%';
    SELECT * FROM pg_settings WHERE category LIKE 'File Locat%';

    -- Find the connected slaves
    $ SELECT * FROM pg_stat_replication;
    SELECT * FROM pg_stat_replication;

    -- On Linux - Find the open pg_log files (needs updatedb/locate package)
    $ fuser $(locate pg_log | grep '2017-11')
  18. gplv2 revised this gist Nov 9, 2017. 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
    @@ -19,10 +19,10 @@ SELECT
    FROM pg_stat_activity
    WHERE now() — pg_stat_activity.query_start > interval '5 minutes';

    -- kill running query
    -- kill running query (this will be a nice kill to the process)
    SELECT pg_cancel_backend(procpid);

    -- kill idle query
    -- kill idle query (this is a nasty kill, possibly the database will restart, emergency use only)
    SELECT pg_terminate_backend(procpid);

    -- vacuum command
  19. gplv2 revised this gist Nov 9, 2017. 1 changed file with 9 additions and 0 deletions.
    9 changes: 9 additions & 0 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -10,6 +10,15 @@ FROM pg_stat_activity
    WHERE query != '<IDLE>' AND query NOT ILIKE '%pg_stat_activity%'
    ORDER BY query_start desc;

    -- Show queries taking more than 5 minutes
    SELECT
    pid,
    now() — pg_stat_activity.query_start AS duration,
    query,
    state
    FROM pg_stat_activity
    WHERE now() — pg_stat_activity.query_start > interval '5 minutes';

    -- kill running query
    SELECT pg_cancel_backend(procpid);

  20. gplv2 revised this gist Nov 9, 2017. 1 changed file with 2 additions and 0 deletions.
    2 changes: 2 additions & 0 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -52,3 +52,5 @@ $ psql -d newdb -f dump.sql
    -- Multiple instances, find the configuration file of the connected instance
    $ SELECT * FROM pg_settings WHERE category LIKE 'File Locat%';

    -- Find the connected slaves
    $ SELECT * FROM pg_stat_replication;
  21. gplv2 revised this gist Nov 9, 2017. 1 changed file with 4 additions and 0 deletions.
    4 changes: 4 additions & 0 deletions postgres_queries_and_commands.sql
    View file
    Original file line number Diff line number Diff line change
    @@ -48,3 +48,7 @@ $ pg_dump -U username -h hostname databasename > dump.sql

    -- Import dump into existing database
    $ psql -d newdb -f dump.sql

    -- Multiple instances, find the configuration file of the connected instance
    $ SELECT * FROM pg_settings WHERE category LIKE 'File Locat%';

  22. @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.
  23. @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.
  24. @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
  25. @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

  26. @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

  27. @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

  28. @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 '<%';

  29. @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)
  30. @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;