Useful Queries to admin postgres

Here are some of my favorite postgres queries to help administrate postgres.

Identify slowest running queries.

 		current_timestamp - xact_start as xact_runtime,
FROM pg_stat_activity
ORDER BY xact_start;

Identify existing locks in postgres, good for finding deadlocks.

SELECT pg_class.relname,pg_locks.* 
FROM pg_class,pg_locks 
WHERE pg_class.relfilenode=pg_locks.relation;

See foreign key constraints.

SELECT c.constraint_name
		, x.table_schema as schema_name
		, x.table_name
		, x.column_name
		, y.table_schema as foreign_schema_name
		, y.table_name as foreign_table_name
		, y.column_name as foreign_column_name
FROM information_schema.referential_constraints c
	join information_schema.key_column_usage x
		on x.constraint_name = c.constraint_name
	join information_schema.key_column_usage y
		on y.ordinal_position = x.position_in_unique_constraint
		and y.constraint_name = c.unique_constraint_name
	order by c.constraint_name, x.ordinal_position;

Show active connections

SELECT count(*) 
FROM pg_stat_activity;
Written on December 31, 2013