南山草舍

0%

PG常用运维语句记录

发表于 更新于 分类于

常用短命令查询

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
\?					#查看所有帮助
\l					#列出数据库
\c [database_name]			#切换数据库
\c - [user_name]			#切换用户
\dt					#查看所有自己创建的表
\dt+					#查看所有自己创建的表，显示表的相关内容及占用磁盘大小
\d [table_name]				#查看指定表结构
\dt(+) ]tablename]			#查看指定表，显示表的相关内容及占用磁盘大小
\dv 					#查看所有自己创建的视图
\dv+ 					#查看所有自己创建的视图，显示大小
\df 					#查看所有自己创建的function
\df+					#查看所有自己创建的function,显示function的内容
\df(+) [func_name]			#显示指定的function
\ef [func_name]				#编辑function
\dy					#查看触发器
\dx					#查看添加的PostgreSQL扩展模块
\du  					#查看所有角色
\dp [viewortable]			#查看表或视图的权限
\sf+ 函数名				#查看函数的创建语句

---
\!  [shell命令]				#执行系统shell命令
\i  [xx.sql]				#执行sql脚本
\conninfo				#查看当前连接信息
\encoding [ENCODING]			#显示/修改客户端编码
\password [USERNAME]			#修改当前用户密码
---
set search_path to [new_schema]  #切换schema
---
批量修改表的所有者
select * from information_schema.tables where table_schema='public';
修改表的所有者（将执行结果复制处理再次执行）
select 'ALTER TABLE ' || table_name || ' OWNER TO yourowner;' from information_schema.tables where table_schema='public';

查看死锁的SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
SELECT blocked_locks.pid     AS blocked_pid,

         blocked_activity.usename  AS blocked_user,

         blocking_locks.pid     AS blocking_pid,

         blocking_activity.usename AS blocking_user,

         blocked_activity.query    AS blocked_statement,

         blocking_activity.query   AS current_statement_in_blocking_process

   FROM  pg_catalog.pg_locks         blocked_locks

    JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid

    JOIN pg_catalog.pg_locks         blocking_locks 

        ON blocking_locks.locktype = blocked_locks.locktype

        AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE

        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation

        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page

        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple

        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid

        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid

        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid

        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid

        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid

        AND blocking_locks.pid != blocked_locks.pid

     JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid

   WHERE NOT blocked_locks.GRANTED;

或者使用这个sql

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT blocked_locks.pid     AS blocked_pid,
         blocked_activity.usename  AS blocked_user,
         blocking_locks.pid     AS blocking_pid,
         blocking_activity.usename AS blocking_user,
         blocked_activity.query    AS blocked_statement,
         blocking_activity.query   AS current_statement_in_blocking_process
   FROM  pg_catalog.pg_locks         blocked_locks
    JOIN pg_catalog.pg_stat_activity blocked_activity  ON blocked_activity.pid = blocked_locks.pid
    JOIN pg_catalog.pg_locks         blocking_locks
        ON blocking_locks.locktype = blocked_locks.locktype
        AND blocking_locks.DATABASE IS NOT DISTINCT FROM blocked_locks.DATABASE
        AND blocking_locks.relation IS NOT DISTINCT FROM blocked_locks.relation
        AND blocking_locks.page IS NOT DISTINCT FROM blocked_locks.page
        AND blocking_locks.tuple IS NOT DISTINCT FROM blocked_locks.tuple
        AND blocking_locks.virtualxid IS NOT DISTINCT FROM blocked_locks.virtualxid
        AND blocking_locks.transactionid IS NOT DISTINCT FROM blocked_locks.transactionid
        AND blocking_locks.classid IS NOT DISTINCT FROM blocked_locks.classid
        AND blocking_locks.objid IS NOT DISTINCT FROM blocked_locks.objid
        AND blocking_locks.objsubid IS NOT DISTINCT FROM blocked_locks.objsubid
        AND blocking_locks.pid != blocked_locks.pid
 
    JOIN pg_catalog.pg_stat_activity blocking_activity ON blocking_activity.pid = blocking_locks.pid
   WHERE NOT blocked_locks.GRANTED;

按建立连接的时间排序的正在运行查询的列表

1
SELECT * FROM pg_stat_activity ORDER BY backend_start;

统计idle，active，null的运行的sql的数量

1
select state, wait_event, wait_event_type, count(*) from pg_stat_activity group by 1,2,3 order by wait_event;

终止pid会话

1
SELECT pg_terminate_backend('pid');

确认当前的连接用户和对应的连接机器

1
SELECT datname,usename,client_addr,client_port FROM pg_stat_activity ;

查看sql使用情况

1
SELECT datname,usename,query FROM pg_stat_activity ;

只查看当前正在运行的sql

1
2
3
SELECT datname,usename,query
   FROM pg_stat_activity
   WHERE state != 'idle'

查看耗时较长的sql

1
2
3
4
select current_timestamp - query_start as runtime, datname, usename, query
    from pg_stat_activity
    where state != 'idle'
    order by 1 desc;

查看数据库的最大链接数

1
show max_connections;

查看当前使用的连接数

1
SELECT COUNT(*) from pg_stat_activity;

查看prepostsql的最大链接数

1
select min_val, max_val from pg_settings where name='max_connections';

查看正在运行的sql，并且带上运行时长

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
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>' 
ORDER BY 
   lap DESC;