导读：之前看了一篇《PostgreSQL DBA常用SQL查询语句》文章。与文章作者讨论了一下元命令\dt，结合平时工作感觉这个命令确实有些不太智能，应该根据search_path把所有的表都列出来才是，于是尝试使用快捷命令解决，也顺道将平时工作中用到的查询总结一下。
1. 如何制作快捷命令
编辑家目录下.psqlrc文件，格式如下：
\set short_command 'SQL;'
- short_commad为自定义的快捷命令名称
- SQL里的单引号需要转义
- 需要带分号
2. 快捷命令my_dt之按search_path查看表
test用户有一个同名schma和public schema
test=> \dn
List of schemas
Name | Owner
--------+-------
public | test
test | test
(2 rows)
两个模式下分别创建一张同名的t表
test=> create table public.t(id int);
test=> create table test.t(id int,info text);
test=> show search_path;
search_path
-----------------
"$user", public
(1 row)
test=> \dt
List of relations
Schema | Name | Type | Owner
--------+------+-------+-------
test | t | table | test
(1 row)
test=> :my_dt
Schema | Name | Owner
--------+--------+-------
public | t | test
test | t | test
(5 rows)
快捷命令my_dt，sql语句如下：
SELECT n.nspname as "Schema",
c.relname as "Name",
pg_catalog.pg_get_userbyid(c.relowner) as "Owner"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind ='r'
AND n.nspname in('public',current_user)
ORDER BY 2,1;
语句修改来源于\dt，根据search_path中的配置，查询public和当前用户，同时先按表名称排序，便于同名表问题排查分析。
3. 快捷命令之连接数统计
test=> :my_stat_conn
client_addr | count
--------------+-------
| 6
192.168.99.1 | 2
(2 rows)
select client_addr,count(1)
from pg_stat_activity
group by 1
经常遇到搞java开发的同学用eclipse不断重启web应用把数据库连接占满的情况。
4. 快捷命令之查询活动会话
test=> :my_active_session
--SQL
select pid,usename,datname,query,client_addr
from pg_stat_activity
where pid<>pg_backend_pid()
and state='active'
order by query;
5. 快捷命令之查询等待事件
test=> :my_wait_event
--SQL
select pid,usename,datname,query,client_addr,wait_event_type,wait_event
from pg_stat_activity
where pid<>pg_backend_pid()
and wait_event is not null
order by wait_event;
6. 快捷命令之查找那些表的字段使用了timestamptz类型（带时区）
test=> :my_timestamptz
--SQL
SELECT relname, attname
FROM pg_class c,
pg_attribute attr
WHERE c.oid = attr.attrelid
and attisdropped = 'f'
and atttypid=1184
and relname !~ '^pg_';
7. 快捷命令之查找数据库维度的CTc
test=> :my_db_ctc
datname | connect_granted | create_granted | temp_granted
-----------+-------------------------------------+-----------------------------+-------------------------------------
postgres | {postgres,repuser,test,test1,test2} | {postgres} | {postgres,repuser,test,test1,test2}
template0 | {postgres,repuser,test,test1,test2} | {postgres} | {postgres}
test | {postgres,test} | {postgres,test,test1,test2} | {postgres,test,test2}
template1 | {postgres,repuser,test,test1,test2} | {postgres} | {postgres}
(4 rows)
--SQL
select
datname,
array_agg(usename) filter (where pri='CONNECT' and has_database_privilege (usename, datname, pri)=true) as connect_granted ,
array_agg(usename) filter (where pri='CREATE' and has_database_privilege (usename, datname, pri)=true) as create_granted ,
array_agg(usename) filter (where pri='TEMP' and has_database_privilege (usename, datname, pri)=true) as temp_granted
from pg_user, pg_database, (values ('CREATE'),('CONNECT'),('TEMP')) t (pri) group by datname;
参考https://github.com/digoal/blog/blob/master/201905/20190505_01.md
其实\l元命令已经足够，只是显示内容较多，没有上面显示的清爽。
8. 快捷命令之查找非当前用户创建的对象(表、视图、序列等)
test=> :my_obj_create_by_other
Schema | Name | Type | Owner | Size | Description
--------±-----±------±------±--------±------------
public | t2 | table | test2 | 0 bytes |
(1 row)
–SQL
SELECT n.nspname as “Schema”,
c.relname as “Name”,
CASE c.relkind WHEN ‘r’ THEN ‘table’ WHEN ‘v’ THEN ‘view’ WHEN ‘m’ THEN ‘materialized view’ WHEN ‘i’ THEN ‘index’ WHEN ‘S’ THEN ‘sequence’ WHEN ‘s’ THEN ‘special’ WHEN ‘f’ THEN ‘foreign table’ WHEN ‘p’ THEN ‘partitioned table’ WHEN ‘I’ THEN ‘partitioned index’ END as “Type”,
pg_catalog.pg_get_userbyid(c.relowner) as “Owner”,
pg_catalog.pg_size_pretty(pg_catalog.pg_table_size(c.oid)) as “Size”,
pg_catalog.obj_description(c.oid, ‘pg_class’) as “Description”
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN (‘r’,‘p’,‘v’,‘m’,‘S’,‘f’,’’)
AND n.nspname <> ‘pg_catalog’
AND n.nspname <> ‘information_schema’
AND n.nspname !~ ‘^pg_toast’
AND pg_catalog.pg_table_is_visible(c.oid)
and c.relowner!=to_regrole(current_user)
ORDER BY 1,2;
9. 快捷命令之查找当前用户对象的权限状态
test=> :my_obj_auth
Schema | Name | Type | Access privileges | Column privileges | Policies
--------+------+-------+--------------------+-------------------+----------
public | t | table | test=arwdDxt/test +| |
| | | test1=arwdDxt/test+| |
| | | test2=r/test | |
public | t2 | table | | |
public | t3 | table | | |
test | t1 | table | test=arwdDxt/test +| |
| | | test1=rw/test +| |
| | | test2=d/test | |
(4 rows)
select relname,relacl
from pg_class
where relkind='r'
and relname !~ '^pg_';
或者
SELECT n.nspname as "Schema",
c.relname as "Name",
CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'm' THEN 'materialized view' WHEN 'S' THEN 'sequence' WHEN 'f' THEN 'foreign table' END as "Type",
pg_catalog.array_to_string(c.relacl, E'\n') AS "Access privileges",
pg_catalog.array_to_string(ARRAY(
SELECT attname || E':\n ' || pg_catalog.array_to_string(attacl, E'\n ')
FROM pg_catalog.pg_attribute a
WHERE attrelid = c.oid AND NOT attisdropped AND attacl IS NOT NULL
), E'\n') AS "Column privileges",
pg_catalog.array_to_string(ARRAY(
SELECT polname
|| CASE WHEN polcmd != '*' THEN
E' (' || polcmd || E'):'
ELSE E':'
END
|| CASE WHEN polqual IS NOT NULL THEN
E'\n (u): ' || pg_catalog.pg_get_expr(polqual, polrelid)
ELSE E''
END
|| CASE WHEN polwithcheck IS NOT NULL THEN
E'\n (c): ' || pg_catalog.pg_get_expr(polwithcheck, polrelid)
ELSE E''
END || CASE WHEN polroles <> '{0}' THEN
E'\n to: ' || pg_catalog.array_to_string(
ARRAY(
SELECT rolname
FROM pg_catalog.pg_roles
WHERE oid = ANY (polroles)
ORDER BY 1
), E', ')
ELSE E''
END
FROM pg_catalog.pg_policy pol
WHERE polrelid = c.oid), E'\n')
AS "Policies"
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r', 'v', 'm', 'S', 'f');
https://github.com/digoal/blog/blob/master/201905/20190505_01.md
其他快捷命令(待整理)
往期回顾
- PostgreSQL超级用户忘记密码了怎么办
- 源码编译安装PostgreSQL
- PostgreSQL外部表插件oracle_fdw
- PG实践学习笔记
- PostgreSQL元命令之connect
- PostgreSQL递归查询
- PostgreSQL源码编译dblink
- PostgreSQL11及以上集成orafce
保持联系
从2019年12月开始写第一篇文章，分享的初心一直在坚持，本人现在组建了一个PG乐知乐享交流群，欢迎关注我文章的小伙伴进群吹牛唠嗑，交流技术，互赞文章。
如果群二维码失效可以加我微信。
