Monthly Archives: July 2016

Show postgres lock

https://wiki.postgresql.org/wiki/Lock_Monitoring 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 … Continue reading

Posted in Database, PostgreSQL | Leave a comment

Check all table size in Postgres

The “tables ” is in information_schema The “pg_class” is in System Catalogs 1234567SELECT ist.TABLE_NAME, reltuples AS "entries", pg_size_pretty(relpages::BIGINT*8*1024) AS SIZE FROM information_schema.TABLES ist INNER JOIN pg_class c ON (ist.TABLE_NAME = c.relname) WHERE ist.table_catalog=’ip2_sync_master’ AND ist.table_schema=’public’ ORDER BY relpages DESC, ist.TABLE_NAME … Continue reading

Posted in Database, PostgreSQL | Leave a comment