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;