PostgreSQL:通过 .psqlrc 定制监控脚本- 续

之前blog PostgreSQL:通过 .psqlrc 定制监控脚本 介绍了通过 .psqlrc 制定监控脚本的例子, 今天接着补充下。

先来看几个不带参数的监控脚本, 以下实验均在 PostgreSQL 9.2.4 下测试。

不带参数的监控脚本

修改 ~/.psqlrc 文件,加入以下内容

1
set active_session 'select pid, datname,usename,query_start,client_addr,query ,waiting from pg_stat_activity where state='active' and pid <> pg_backend_pid() order by query;'

备注: 此查询用来查询当前活动会话。

执行后的结果如下:

再来看一个查询,在 .psqlrc 文件加入以下行:

查询连接数

1
set connections 'select datname,usename,client_addr,count(*) from pg_stat_activity where pid <> pg_backend_pid() group by 1,2,3 order by 1,2,4 desc;'

执行后的结果

备注: 这里是根据数据库名,用户名,应用端 IP 进行统计 。 不带参数的监控脚本比较容易定制,接着演示带参数的监控脚本。

带参数的监控脚本: 参数类型为整型

在 .psqlrc 文件加入以下行
根据 oid 查询对像信息

1
set get_oid 'select oid,relname,relkind,reltuples,relfilenode from pg_class where oid=:v_oid;'

备注: get_oid 脚本根据数据库对像 oid 查询对像信息,这里使用了参数 v_oid 为传入参数。

调用 get_oid 脚本

1
2
3
4
5
6
7
8
9
10
11
[pg92@db1 ~]$ psql francs francs  
psql (9.2.4)
Type "help" for help.

francs=> set v_oid 39285

francs=> select oid,relname,relkind,reltuples,relfilenode from pg_class where oid=39285;
oid |relname | relkind | reltuples | relfilenode
-------+-------------------------------------+---------+-----------+-------------
39285 | tbl_wo_account_permission_user_pkey | i | 257 | 39285
(1 row)

备注: 参数类型为整型容易处理,那么参数类型为字符型应该如何处理呢? 这里研究了好久。

带参数的监控脚本: 参数类型为字符型

在 .psqlrc 文件加入以下行
查询指定表空间表占用大小 top10

1
set top10_ts_table 'select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner from pg_class a, pg_tablespace tb where a.relkind in ('r', 'i') and a.reltablespace=tb.oid and tb.spcname=':v_spcname' order by a.relpages desc limit 10;'

备注:变量 v_spcname 用来传入表空间的名称。

测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
[pg92@db1 ~]$ psql francs francs  
psql (9.2.4)
Type "help" for help.

francs=> \db
List of tablespaces
Name | Owner | Location
-------------------+----------+-----------------------------------------
pg_default | postgres |
pg_global | postgres |
tbs_db_francs | postgres | /database/pg92/pg_tbs/tbs_db_francs
tbs_francs | postgres | /database/pg92/pg_tbs/tbs_francs
tbs_francs_idx | postgres | /database/pg92/pg_tbs/tbs_francs_idx
(5 rows)

francs=> set v_spcname tbs_francs_idx

francs=> echo :v_spcname
tbs_francs_idx

francs=> :top10_ts_table
relname | relkind | relpages | pg_size_pretty | reltablespace | relowner
---------+---------+----------+----------------+---------------+----------
(0 rows)

备注:调用为空,测试前我已经手工将多张表移到了 tbs_francs_idx 表空间上,为什么查询出来为空呢? 看下数据库日志:

数据库日志

1
2014-07-25  02:28:53.831 GMT,"francs","francs",6722,"[local]",53d1c0db.1a42,4,"idle",2014-07-25  02:28:43 GMT,2/24,0,LOG,00000,"statement: select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner from pg_class a, pg_tablespace tb where a.relkind in ('r', 'i') and a.reltablespace=tb.oid and tb.spcname=':v_spcname' order by a.relpages desc limit 10;",,,,,,,,,"psql"

备注:从日志看出, 变量 v_spcname 的值根本没有传进来,接着换一种测试方式。

修改 .psqlrc 文件

1
set top10_ts_table 'select relname, relkind, relpages,pg_size_pretty(pg_relation_size(a.oid)),reltablespace,relowner from pg_class a, pg_tablespace tb where a.relkind in ('r', 'i') and a.reltablespace=tb.oid and tb.spcname=:v_spcname order by a.relpages desc limit 10;'

备注: 修改了 v_spcname 变量调用部分,去掉了两边的单引号。

调用测试

1
2
3
4
5
6
7
8
9
10
11
12
[pg92@db1 ~]$ psql francs francs  
psql (9.2.4)
Type "help" for help.

francs=> set v_spcname 'tbs_francs_idx'

francs=> :top10_ts_table
ERROR:column "tbs_francs_idx" does not exist
LINE 1: ...', 'i') and a.reltablespace=tb.oid and tb.spcname=tbs_skypcs...
^
francs=> echo :v_spcname
tbs_francs_idx

备注:调用报错,报错原因很明显, 表空间名为字符类型,单引号没传进去,接着测试。

再次测试

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
[pg92@db1 ~]$ psql francs francs  
psql (9.2.4)
Type "help" for help.

francs=> set v_spcname ''tbs_francs_idx''

francs=> echo :v_spcname
'tbs_francs_idx'
--上面说明已将两个单引号传进来了
francs=> :top10_
:top10_index :top10_table :top10_ts_table

francs=> :top10_ts_table
relname | relkind | relpages | pg_size_pretty | reltablespace | relowner
-----------------------+---------+----------+----------------+---------------+----------
tbl_wo_channel | r | 8520 | 67 MB | 49152 | 16388
tbl_wo_channel_status | r | 2655 | 21 MB | 49152 | 16388
tbl_wo_channel_change | r | 9 | 72 kB | 49152 | 16388
pg_toast_16427_index | i | 1 | 8192 bytes | 49152 | 16388
pg_toast_16528_index | i | 1 | 8192 bytes | 49152 | 16388
pg_toast_16512_index | i | 1 | 8192 bytes | 49152 | 16388
(6 rows)

备注:从上看出,需要通过 转义将两个单引号传入,同时 .psqlrc 写好的脚本支持 Tab 补全,非常好用。这时得到了我们想要的结果。

总结

通过 .psqlrc 定制自己日常工作中经常使用的监控脚本可以提高工作效率,遇到问题时不需要手工敲打大量脚本。这里只是抛砖引玉,其它监控脚本需要大家在日常工作中挖掘。

之所以研究这些,是因为之前在维护 Oracle 数据库习惯了将大量维护脚本写在本地,连上数据库后通过@脚本名的方式调用 ,感觉很帅。

参考

最后推荐和张文升共同编写的《PostgreSQL实战》,本书基于PostgreSQL 10 编写,共18章,重点介绍SQL高级特性、并行查询、分区表、物理复制、逻辑复制、备份恢复、高可用、性能优化、PostGIS等,涵盖大量实战用例!

购买链接:https://item.jd.com/12405774.html

PostgreSQL实战
感谢支持!
0%