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;
December 31, 2013| Tags : postgres postgresql databases

Comments Section

Feel free to comment on the post but keep it clean and on topic.

comments powered by Disqus

About Me

Tommie Jones Software Development for Linux/Unix since 1995. I've done everything from Perl,C/C++,Java,Flash,php, ruby and currently node.js. Always interested in pushing technology one step further than expected.