postgresql 常用命令

根据需求自己通过网 写的sql，不完美，欢迎提供建议

查看表注释信息：

select relname,description from pg_description d,pg_class c,pg_namespace n where d.objoid=c.oid and c.relnamespace=n.oid and objsubid=0 and nspname = 'schemaname';

查看字段注释信息

select c.oid,table_schema,table_name,column_name,description from pg_class c,pg_description d,pg_namespace n,information_schema.columns i where d.objoid = c.oid and i.table_name=c.relname and i.ordinal_position=d.objsubid and c.relnamespace=n.oid and nspname = 'schemaname';

查看一个模式下所有表及索引的大小：

select r.relname, pg_size_pretty(pg_relation_size(r.relid)), i.indexrelname,pg_size_pretty(pg_relation_size(i.indexrelid))
from pg_stat_user_tables r,pg_stat_user_indexes i
where r.schemaname='schemaname' and r.relname=i.relname
order by pg_relation_size(r.relid) desc;

监控锁等进程：

9.3版本前：

select distinct locker.pid as locker_pid,
       locker_act.client_addr as locker_addr,
       locker_act.usename as locker_username,
       locked.pid as locked_pid,
       locker.mode as locker_mode,
       locker.locktype as locker_locktype,
       locker_act.usename as locker_user,
       locker_act.query_start as locker_query_start,
       locker_act.current_query as locker_query,
       locked_act.current_query as locked_query
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 locker_act.current_query not like '%select distinct locker.pid %'
and locker.pid <> locked.pid
and locker.mode not like 'AccessShareLock' and locker.mode not like 'ExclusiveLock'
order by locker_act.query_start asc limit 10;

psql 9.3版本之后: 
select distinct locker.pid as locker_pid,
       locker_act.client_addr as locker_addr,
       locker_act.usename as locker_username,
       locked.pid as locked_pid,
       locker.mode as locker_mode,
       locker.locktype as locker_locktype,
       locker_act.usename as locker_user,
       locker_act.query_start as locker_query_start,
       locker_act.query as locker_query,
       locked_act.query as locked_query
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.pid
and locker.pid=locker_act.pid
and locker_act.query not like '%select distinct locker.pid %'
and locker.pid <> locked.pid
and locker.mode not like 'AccessShareLock' and locker.mode not like 'ExclusiveLock'
order by locker_act.query_start asc limit 10;

当前连接数：

select max_conn, now_conn, max_conn-now_conn rest_conn from (select setting::int8 as max_conn,(select count(*) from pg_stat_activity) as now_conn from pg_settings where name = 'max_connections') t;

每个用户的连接数个数

select usename,client_addr,count(client_addr) from pg_stat_activity group by usename,client_addr order by count(client_addr) desc;

当前的执行的sql：

SELECT client_addr,waiting,datname,procpid,query_start, current_query FROM pg_stat_activity where now()-query_start>= '00:00:10' and current_query != '' order by query_start ;

psql 9.3版本以后:

select pid,usename,query_start,client_addr,count(client_addr),query,state from pg_stat_activity where now()-query_start>= '00:00:10' and state  !='idle' group by pid,usename,client_addr,query,query_start,state order by query_start;

查看当前的锁进程：

9.3版本以前

SELECT client_addr,waiting,procpid,query_start, current_query FROM pg_stat_activity where now()-query_start>= '00:00:10' and current_query not like '%' and waiting='t' order by query_start;

psql 9.3版本以后:

SELECT client_addr,waiting,pid,query_start, query FROM pg_stat_activity where now()-query_start>= '00:00:10' and query not like '%' and waiting='t' order by query_start;

查看锁类型

select locktype,granted,database,relation,pid,mode from pg_locks where pid=;

产生锁的操作

select datname,procpid,usename,client_addr,xact_start,query_start,current_query from pg_stat_activity where procpid=