可以通过以下SQL查询出所有的catalog view：
SELECT
n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
r.rolname as "Owner"
FROM pg_catalog.pg_class c
JOIN pg_catalog.pg_roles r ON r.oid = c.relowner
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('v','')
AND n.nspname = 'pg_catalog'
AND n.nspname !~ '^pg_toast'
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
查看当前活动的客户端连接数：
SELECT count(*) FROM pg_stat_activity WHERE NOT procpid=pg_backend_pid();
查询客户端连接的情况：
SELECT procpid,waiting,current_timestamp - least(query_start,xact_start) AS runtime,substr(current_query,1,25) AS current_query FROM pg_stat_activity WHERE NOT procpid=pg_backend_pid();
查看当前锁的一些信息：
SELECT
locktype,
virtualtransaction,
transactionid,
nspname,
relname,
mode,
granted,
cast(date_trunc('second',query_start) AS timestamp) AS query_start,
substr(current_query,25) AS query
FROM
pg_locks
LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
LEFT OUTER JOIN pg_namespace ON (pg_namespace.oid = pg_class.relnamespace),
pg_stat_activity
WHERE
NOT pg_locks.pid=pg_backend_pid() AND
pg_locks.pid=pg_stat_activity.procpid;
查看持有锁的一些信息：
SELECT
locked.pid AS locked_pid,
locker.pid AS locker_pid,
locked_act.usename AS locked_user,
locker_act.usename AS locker_user,
locked.virtualtransaction,
locked.transactionid,
locked.locktype
FROM
pg_locks locked,
pg_locks locker,
pg_stat_activity locked_act,
pg_stat_activity locker_act
WHERE
locker.granted=true AND
locked.granted=false AND
locked.pid=locked_act.procpid AND
locker.pid=locker_act.procpid AND
(locked.virtualtransaction=locker.virtualtransaction OR
locked.transactionid=locker.transactionid);
还是查看锁的一些信息：
SELECT
locked.pid AS locked_pid,
relname
FROM
pg_locks locked
LEFT OUTER JOIN pg_class ON (locked.relation = pg_class.oid),
pg_stat_activity locker_act
WHERE
locker.granted=true AND
locked.granted=false AND
locked.pid=locked_act.procpid AND
locker.pid=locker_act.procpid AND
locked.relation=locker.relation;
查看当前库表和索引的的大小并排序显示前20条：
SELECT
nspname,
pg_size_pretty(pg_relation_size(C.oid)) AS "size"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog','information_schema')
ORDER BY pg_relation_size(C.oid) DESC
LIMIT 20;
pg_total_relation_size = pg_table_size + pg_indexes_size
pg_table_size = pg_relation_size + toast table + toast indexes + FSM
9.0以后：
SELECT
nspname,
relkind as "type",
pg_size_pretty(pg_table_size(C.oid)) AS size,
pg_size_pretty(pg_indexes_size(C.oid)) AS idxsize,
pg_size_pretty(pg_total_relation_size(C.oid)) as "total"
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE nspname NOT IN ('pg_catalog','information_schema') AND
nspname !~ '^pg_toast' AND
relkind IN ('r','i')
ORDER BY pg_total_relation_size(C.oid) DESC
LIMIT 20;
