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

简述

本文主要是我日常使用的一些运维SQL和整理于互联网上的SQL，为了方便日常的使用，特把其汇总起来，遇到常用的时将会进行补充该文，欢迎大家在评论区进行提出一些常用的SQL。

正文

1、查询全库所有的表

select * from pg_tables;

2、获取表名及注释

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

3、查询所有库的连接情况

select * from pg_stat_activity;

4、查询数据库大小

SELECT pg_size_pretty(pg_database_size('postExpress'));

5、查询指定域中表的大小

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

6、杀死指定用户的所有进程

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

7、查询最消耗CPU的sql语句

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;

8、查出使用表扫描最多的表

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

9、通过pg_stat_statements插件定位涉及到第8条中表的查询

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

10、授权数据库所有权限给用户

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

11、查询数据库锁情况

select * from pg_locks;

12、查询锁对应的表跟库

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

13、创建用户

create user username with login password 'password';

14、修改用户密码

ALTER USER username WITH PASSWORD 'passwd';

15、查看表上存在哪些索引以及大小

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; 

参考文章 https://www.kancloud.cn/zzgjb/pgsql/76192

本文声明：

知识共享许可协议
本作品由 cn華少 采用 知识共享署名-非商业性使用 4.0 国际许可协议 进行许可。