-- show running queries (pre 9.2)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(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;-- show running sqls more than 2 mins (9.2+)SELECT now() - query_start as "runtime", usename, datname, state, query FROM pg_stat_activity WHERE now() - query_start > ‘2 minutes‘::interval ORDER BY runtime DESC;--show all queries select substr(query,1,100) query,backend_type,application_name,query_start,wait_event_type,wait_event,state from pg_stat_activity;
--get vacuum status and resultsselect relname,last_vacuum, last_autovacuum, last_analyze, last_autoanalyze from pg_stat_user_tables;--query by sigle table of vacuum statusselect relname, n_dead_tup, last_vacuum, last_autovacuum from pg_catalog.pg_stat_all_tables where n_dead_tup > 0 and relname =‘sxacc-devices‘ order by n_dead_tup desc;-- vacuum command VACUUM (VERBOSE, ANALYZE);
-- table index usage rates (should not be less than 0.99)SELECT relname, CASE WHEN (seq_scan + idx_scan) != 0 THEN 100.0 * idx_scan / (seq_scan + idx_scan) ELSE 0 END AS percent_of_times_index_used, n_live_tup AS rows_in_tableFROM pg_stat_user_tables ORDER BY n_live_tup DESC;-- 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; -- 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; -- 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;
-- check the size(as in disk space) of all databases;
SELECT d.datname AS Name, pg_catalog.pg_get_userbyid(d.datdba) AS Owner, CASE WHEN pg_catalog.has_database_privilege(d.datname, ‘CONNECT‘) THEN pg_catalog.pg_size_pretty(pg_catalog.pg_database_size(d.datname)) ELSE ‘No Access‘ END AS SIZE FROM pg_catalog.pg_database d ORDER BY CASE WHEN pg_catalog.has_database_privilege(d.datname, ‘CONNECT‘) THEN pg_catalog.pg_database_size(d.datname) ELSE NULL END;
--check the size(as in space) of each table
SELECT nspname || ‘.‘ || relname AS "relation", pg_size_pretty(pg_total_relation_size(C.oid)) AS "total_size" FROM pg_class C LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace) WHERE nspname NOT IN (‘pg_catalog‘, ‘information_schema‘) AND C.relkind <> ‘i‘ AND nspname !~ ‘^pg_toast‘ ORDER BY pg_total_relation_size(C.oid) DESC;
-- all tables and their size, with/without indexesselect datname, pg_size_pretty(pg_database_size(datname))from pg_databaseorder by pg_database_size(datname) desc;
-- show unused indexes:
SELECT relname AS table_name, indexrelname AS index_name, idx_scan, idx_tup_read, idx_tup_fetch, pg_size_pretty(pg_relation_size(indexrelname::regclass)) FROM pg_stat_all_indexes WHERE schemaname = ‘public‘ AND idx_scan = 0 AND idx_tup_read = 0 AND idx_tup_fetch = 0 ORDER BY pg_relation_size(indexrelname::regclass) DESC;
-- all locksSELECT t.relname, l.locktype, page, virtualtransaction, pid, mode, granted FROM pg_locks l, pg_stat_all_tables t WHERE l.relation = t.relid ORDER BY relation asc;-- Сombination of blocked and blocking activity -- 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;-- Here‘s an alternate view of that same data that includes application_name‘s-- Setting application_name variable in the begging of each transaction allows you to which logical process blocks another one. It can be information which source code line starts transaction or any other information that helps you to match application_name to your code. SET application_name=‘%your_logical_name%‘;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, blocked_activity.application_name AS blocked_application, blocking_activity.application_name AS blocking_application 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;-- Here‘s an alternate view of that same data that includes an idea how old the state is SELECT a.datname, l.relation::regclass, l.transactionid, l.mode, l.GRANTED, a.usename, a.query, a.query_start, age(now(), a.query_start) AS "age", a.pidFROM pg_stat_activity aJOIN pg_locks l ON l.pid = a.pidORDER BY a.query_start;-- Flat view of Blocking-- For PostgreSQL Version < 9.2SELECT waiting.locktype AS waiting_locktype, waiting.relation::regclass AS waiting_table, waiting_stm.current_query AS waiting_query, waiting.mode AS waiting_mode, waiting.pid AS waiting_pid, other.locktype AS other_locktype, other.relation::regclass AS other_table, other_stm.current_query AS other_query, other.mode AS other_mode, other.pid AS other_pid, other.GRANTED AS other_grantedFROM pg_catalog.pg_locks AS waitingJOIN pg_catalog.pg_stat_activity AS waiting_stm ON ( waiting_stm.procpid = waiting.pid )JOIN pg_catalog.pg_locks AS other ON ( ( waiting."database" = other."database" AND waiting.relation = other.relation ) OR waiting.transactionid = other.transactionid )JOIN pg_catalog.pg_stat_activity AS other_stm ON ( other_stm.procpid = other.pid )WHERE NOT waiting.GRANTEDAND waiting.pid <> other.pid-- For PostgreSQL Version >= 9.2SELECT waiting.locktype AS waiting_locktype, waiting.relation::regclass AS waiting_table, waiting_stm.query AS waiting_query, waiting.mode AS waiting_mode, waiting.pid AS waiting_pid, other.locktype AS other_locktype, other.relation::regclass AS other_table, other_stm.query AS other_query, other.mode AS other_mode, other.pid AS other_pid, other.GRANTED AS other_grantedFROM pg_catalog.pg_locks AS waitingJOIN pg_catalog.pg_stat_activity AS waiting_stm ON ( waiting_stm.pid = waiting.pid )JOIN pg_catalog.pg_locks AS other ON ( ( waiting."database" = other."database" AND waiting.relation = other.relation ) OR waiting.transactionid = other.transactionid )JOIN pg_catalog.pg_stat_activity AS other_stm ON ( other_stm.pid = other.pid )WHERE NOT waiting.GRANTEDAND waiting.pid <> other.pid-- Recursive View of BlockingWITH RECURSIVE c(requested, CURRENT) AS ( VALUES (‘AccessShareLock‘::text, ‘AccessExclusiveLock‘::text), (‘RowShareLock‘::text, ‘ExclusiveLock‘::text), (‘RowShareLock‘::text, ‘AccessExclusiveLock‘::text), (‘RowExclusiveLock‘::text, ‘ShareLock‘::text), (‘RowExclusiveLock‘::text, ‘ShareRowExclusiveLock‘::text), (‘RowExclusiveLock‘::text, ‘ExclusiveLock‘::text), (‘RowExclusiveLock‘::text, ‘AccessExclusiveLock‘::text), (‘ShareUpdateExclusiveLock‘::text, ‘ShareUpdateExclusiveLock‘::text), (‘ShareUpdateExclusiveLock‘::text, ‘ShareLock‘::text), (‘ShareUpdateExclusiveLock‘::text, ‘ShareRowExclusiveLock‘::text), (‘ShareUpdateExclusiveLock‘::text, ‘ExclusiveLock‘::text), (‘ShareUpdateExclusiveLock‘::text, ‘AccessExclusiveLock‘::text), (‘ShareLock‘::text, ‘RowExclusiveLock‘::text), (‘ShareLock‘::text, ‘ShareUpdateExclusiveLock‘::text), (‘ShareLock‘::text, ‘ShareRowExclusiveLock‘::text), (‘ShareLock‘::text, ‘ExclusiveLock‘::text), (‘ShareLock‘::text, ‘AccessExclusiveLock‘::text), (‘ShareRowExclusiveLock‘::text, ‘RowExclusiveLock‘::text), (‘ShareRowExclusiveLock‘::text, ‘ShareUpdateExclusiveLock‘::text), (‘ShareRowExclusiveLock‘::text, ‘ShareLock‘::text), (‘ShareRowExclusiveLock‘::text, ‘ShareRowExclusiveLock‘::text), (‘ShareRowExclusiveLock‘::text, ‘ExclusiveLock‘::text), (‘ShareRowExclusiveLock‘::text, ‘AccessExclusiveLock‘::text), (‘ExclusiveLock‘::text, ‘RowShareLock‘::text), (‘ExclusiveLock‘::text, ‘RowExclusiveLock‘::text), (‘ExclusiveLock‘::text, ‘ShareUpdateExclusiveLock‘::text), (‘ExclusiveLock‘::text, ‘ShareLock‘::text), (‘ExclusiveLock‘::text, ‘ShareRowExclusiveLock‘::text), (‘ExclusiveLock‘::text, ‘ExclusiveLock‘::text), (‘ExclusiveLock‘::text, ‘AccessExclusiveLock‘::text), (‘AccessExclusiveLock‘::text, ‘AccessShareLock‘::text), (‘AccessExclusiveLock‘::text, ‘RowShareLock‘::text), (‘AccessExclusiveLock‘::text, ‘RowExclusiveLock‘::text), (‘AccessExclusiveLock‘::text, ‘ShareUpdateExclusiveLock‘::text), (‘AccessExclusiveLock‘::text, ‘ShareLock‘::text), (‘AccessExclusiveLock‘::text, ‘ShareRowExclusiveLock‘::text), (‘AccessExclusiveLock‘::text, ‘ExclusiveLock‘::text), (‘AccessExclusiveLock‘::text, ‘AccessExclusiveLock‘::text) ), l AS ( SELECT (locktype,DATABASE,relation::regclass::text,page,tuple,virtualxid,transactionid,classid,objid,objsubid) AS target, virtualtransaction, pid, mode, GRANTED FROM pg_catalog.pg_locks ), t AS ( SELECT blocker.target AS blocker_target, blocker.pid AS blocker_pid, blocker.mode AS blocker_mode, blocked.target AS target, blocked.pid AS pid, blocked.mode AS mode FROM l blocker JOIN l blocked ON ( NOT blocked.GRANTED AND blocker.GRANTED AND blocked.pid != blocker.pid AND blocked.target IS NOT DISTINCT FROM blocker.target) JOIN c ON (c.requested = blocked.mode AND c.CURRENT = blocker.mode) ), r AS ( SELECT blocker_target, blocker_pid, blocker_mode, ‘1‘::INT AS depth, target, pid, mode, blocker_pid::text || ‘,‘ || pid::text AS seq FROM t UNION ALL SELECT blocker.blocker_target, blocker.blocker_pid, blocker.blocker_mode, blocker.depth + 1, blocked.target, blocked.pid, blocked.mode, blocker.seq || ‘,‘ || blocked.pid::text FROM r blocker JOIN t blocked ON (blocked.blocker_pid = blocker.pid) WHERE blocker.depth < 1000 )SELECT * FROM r ORDER BY seq;-- the following view also adds useful information about the sessions in the blocking tree. CREATE OR REPLACE VIEW blocking_tree ASWITH RECURSIVE LOCK AS ( SELECT pid, virtualtransaction, GRANTED, mode, (locktype, CASE locktype WHEN ‘relation‘ THEN concat_ws(‘;‘, ‘db:‘||datname, ‘rel:‘||relation::regclass::text) WHEN ‘extend‘ THEN concat_ws(‘;‘, ‘db:‘||datname, ‘rel:‘||relation::regclass::text) WHEN ‘page‘ THEN concat_ws(‘;‘, ‘db:‘||datname, ‘rel:‘||relation::regclass::text, ‘page#‘||page::text) WHEN ‘tuple‘ THEN concat_ws(‘;‘, ‘db:‘||datname, ‘rel:‘||relation::regclass::text, ‘page#‘||page::text, ‘tuple#‘||tuple::text) WHEN ‘transactionid‘ THEN transactionid::text WHEN ‘virtualxid‘ THEN virtualxid::text WHEN ‘object‘ THEN concat_ws(‘;‘, ‘class:‘||classid::regclass::text, ‘objid:‘||objid, ‘col#‘||objsubid) ELSE concat(‘db:‘||datname) -- userlock and advisory END::text) AS target FROM pg_catalog.pg_locks LEFT JOIN pg_catalog.pg_database ON (pg_database.oid = pg_locks.DATABASE) ), waiting_lock AS ( SELECT blocker.pid AS blocker_pid, blocked.pid AS pid, concat(blocked.mode,blocked.target) AS lock_target FROM LOCK blocker JOIN LOCK blocked ON ( NOT blocked.GRANTED AND blocker.GRANTED AND blocked.pid != blocker.pid AND blocked.target IS NOT DISTINCT FROM blocker.target) JOIN lock_composite c ON (c.requested = blocked.mode AND c.CURRENT = blocker.mode) ), acquired_lock AS ( WITH waiting AS ( SELECT lock_target, COUNT(lock_target) AS wait_count FROM waiting_lock GROUP BY lock_target ) SELECT pid, array_agg(concat(mode,target,‘ + ‘||wait_count) ORDER BY wait_count DESC NULLS LAST) AS locks_acquired FROM LOCK LEFT JOIN waiting ON waiting.lock_target = concat(mode,target) WHERE GRANTED GROUP BY pid ), blocking_lock AS ( SELECT ARRAY[date_part(‘epoch‘, query_start)::INT, pid] AS seq, 0::INT AS depth, -1::INT AS blocker_pid, pid, concat(‘Connect: ‘,usename,‘ ‘,datname,‘ ‘,COALESCE(host(client_addr)||‘:‘||client_port, ‘local‘) , E‘\nSQL: ‘,REPLACE(substr(COALESCE(query,‘N/A‘), 1, 60), E‘\n‘, ‘ ‘) , E‘\nAcquired:\n ‘ , array_to_string(locks_acquired[1:5] || CASE WHEN array_upper(locks_acquired,1) > 5 THEN ‘... ‘||(array_upper(locks_acquired,1) - 5)::text||‘ more ...‘ END, E‘\n ‘) ) AS lock_info, concat(to_char(query_start, CASE WHEN age(query_start) > ‘24h‘ THEN ‘Day DD Mon‘ ELSE ‘HH24:MI:SS‘ END),E‘ started\n‘ ,CASE WHEN waiting THEN ‘waiting‘ ELSE state END,E‘\n‘ ,date_trunc(‘second‘,age(now(),query_start)),‘ ago‘ ) AS lock_state FROM acquired_lock blocker LEFT JOIN pg_stat_activity act USING (pid) WHERE EXISTS -- The root of the tree should blocks one or more sessions. (SELECT ‘x‘ FROM waiting_lock blocked WHERE blocked.blocker_pid = blocker.pid) AND NOT EXISTS -- The root of the tree should not be a blocked session. (SELECT ‘x‘ FROM waiting_lock blocked WHERE blocked.pid = blocker.pid)UNION ALL SELECT blocker.seq || blocked.pid, blocker.depth + 1, blocker.pid, blocked.pid, concat(‘Connect: ‘,usename,‘ ‘,datname,‘ ‘,COALESCE(host(client_addr)||‘:‘||client_port, ‘local‘) , E‘\nSQL: ‘,REPLACE(substr(COALESCE(query,‘N/A‘), 1, 60), E‘\n‘, ‘ ‘) , E‘\nWaiting: ‘,blocked.lock_target , CASE WHEN locks_acquired IS NOT NULL THEN E‘\nAcquired:\n ‘ || array_to_string(locks_acquired[1:5] || CASE WHEN array_upper(locks_acquired,1) > 5 THEN ‘... ‘||(array_upper(locks_acquired,1) - 5)::text||‘ more ...‘ END, E‘\n ‘) END ) AS lock_info, concat(to_char(query_start, CASE WHEN age(query_start) > ‘24h‘ THEN ‘Day DD Mon‘ ELSE ‘HH24:MI:SS‘ END),E‘ started\n‘ ,CASE WHEN waiting THEN ‘waiting‘ ELSE state END,E‘\n‘ ,date_trunc(‘second‘,age(now(),query_start)),‘ ago‘ ) AS lock_state FROM blocking_lock blocker JOIN waiting_lock blocked ON (blocked.blocker_pid = blocker.pid) LEFT JOIN pg_stat_activity act ON (act.pid = blocked.pid) LEFT JOIN acquired_lock acq ON (acq.pid = blocked.pid) WHERE blocker.depth < 5 )SELECT concat(lpad(‘=> ‘, 4*depth, ‘ ‘),pid::text) AS "PID", lock_info AS "Lock Info", lock_state AS "State"FROM blocking_lockORDER BY seq;
Other Operations
-- 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-- kill running querySELECT pg_cancel_backend(procpid);-- kill idle querySELECT pg_terminate_backend(procpid);
# Taken from http://robots.thoughtbot.com/post/33706558963/migrating-data-from-an-upgraded-postgres## Note: these steps assume installation with Homebrew.# Initialize a new database, adding a .new suffix to the directory that Homebrew recommends.initdb /usr/local/var/postgres.new -E utf8# Run the upgrade script, providing the correct paths for the various flags.pg_upgrade -b /usr/local/Cellar/postgresql/9.1.4/bin -B /usr/local/Cellar/postgresql/9.2.1/bin -d /usr/local/var/postgres -D /usr/local/var/postgres.new# Put the data in the correct place.rm -rf /usr/local/var/postgresmv /usr/local/var/postgres.new /usr/local/var/postgres# If you’ve set up launchd to run Postgres automatically, # everything should be up and running! Otherwise, check out the # documentation with brew info postgres to read how to have OS X # manage postgres for you.######## Alternatively, if you’ve just upgraded Postgres with Homebrew and # Postgres won’t start, as long as you don’t care about any data stored locally:brew remove --force postgresqlrm -rf /usr/local/var/postgres/ brew install postgresql
MemoryOnly four values really matter: shared-buffers: below 2GB: set it to 20% of full memory; below 32GB: 25% of your full memory. work_mem: Start low at 32/64MB. Look for temporary file lines in logs. Then set it to 2-3x the largest temp file that you see. This setting can give a huge speed boost (if set properly). maintenance_work_mem: Set it to 10% of system memory. effective_cache_size: Only a hint to postgres. Just set it to the amount of filesystem cache available.
Reference: