PostgreSQL集群篇——常用的运维SQL

select * from pg_tables;

select relname as tabname,cast(obj_description(relfilenode,'pg_class') as varchar) as comment from pg_class c 
where  relkind = 'r' and relname not like 'pg_%' and relname not like 'sql_%' order by relname

select * from pg_stat_activity;

SELECT pg_size_pretty(pg_database_size('postExpress'));

select relname, pg_size_pretty(pg_relation_size(relid)) from pg_stat_user_tables where schemaname='指定域' order by pg_relation_size(relid) desc;

select pg_terminate_backend(pid) from pg_stat_activity where usename='用户名';

ps aux|head -1;ps aux|grep -v PID|sort -rn -k +3|head
 select procpid, start, now() - start as lap, current_query
from
(select backendid, pg_stat_get_backend_pid(s.backendid) as procpid, pg_stat_get_backend_activity_start(s.backendid) as start, pg_stat_get_backend_activity(s.backendid) as current_query
from
(select pg_stat_get_backend_idset() as backendid) as s
) as s
where
current_query <> '<IDLE>' and procpid IN （17637,123,321） --加入查找到的进程ID
order by
lap desc;

select * from pg_stat_user_tables where n_live_tup > 100000 and seq_scan > 0 order by seq_tup_read desc limit 10;

select * from pg_stat_statements where query ilike '%表名%'order by shared_blks_hit+shared_blks_read desc limit 3;

GRANT ALL PRIVILEGES ON DATABASE 数据库名称 TO 用户名;

select * from pg_locks;

select a.locktype,a.database,a.pid,a.mode,a.relation from pg_locks as a where a.mode='ExclusiveLock'

create user username with login password 'password';

ALTER USER username WITH PASSWORD 'passwd';

select relname,n.amname as index_type from pg_class m,pg_am n where m.relam = n.oid and m.oid in (
select b.indexrelid from pg_class a,pg_index b where a.oid = b.indrelid and a.relname = 'cc');

SELECT c.relname,c2.relname, c2.relpages*8 as size_kb
FROM pg_class c, pg_class c2, pg_index i
WHERE c.relname = 'cc' AND
c.oid = i.indrelid AND
c2.oid = i.indexrelid
ORDER BY c2.relname;