Useful Postgres Queries

Getting a list of dbnames

/usr/local/bin/psql -q -t -A -h -d template1 -c "SELECT datname FROM pg_database WHERE datname not in ('template0','template1')"

Getting a list of name spaces for a given DB

/usr/local/bin/psql -q -t -A -h -d < DB > -c " select schemaname from pg_tables where schemaname not in ('pg_catalog','information_schema') group by schemaname"

Getting a list of tables (including name spaces)

/usr/local/bin/psql -q -t -A -h -d < DB > --field-separator "." -c "select schemaname,tablename from pg_tables where schemaname not in ('pg_catalog','information_schema')"

Getting a list of locks on the DB server (from within a psql prompt)

SELECT procpid, usename ,datname, (now() - query_start) as age, c.relname , l.mode, l.granted FROM pg_stat_activity a LEFT OUTER JOIN pg_locks l ON (a.procpid = l.pid) LEFT OUTER JOIN pg_class c ON (l.relation = c.oid);

Looking at what is connected to a given Database name (from within a psql prompt)

select * from pg_stat_activity where datname='< DB >';