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

在数据库维护过程中,定制监控脚本是很平常的工作,以提高工作效率,例如,下面这个 sql 是用来查询数据库当前的非空闲会话。

监控活动会话SQL

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT
pid ,
datname ,
usename ,
client_addr ,
query ,
query_start ,
state ,
waiting
FROM
pg_stat_activity
WHERE
query ! ~ '<IDLE'
AND pid <> pg_backend_pid()
AND state = 'active'
ORDER BY query;

备注:SQL 不算长,需要用时手工编写也没问题,当然也有更快捷的方法,可以将此 SQL 写入 .psqlrc 文件, 首先简单介绍下这个文件。

关于 .psqlrc 文件

.psqlrc 文件用来定制 psql 客户端特性的配置文件,定制后对当前客户端全局生效,在 linux 环境下位于 ~/.psqlrc, windows 平台位于 %APPDATA%\postgresql\psqlrc.conf,下面是 .psqlrc 的一个简单例子。

.psqlrc 文件举例

1
2
3
4
[pg93@redhatB ~]$ cat .psqlrc 
\set active_session ' select pid, datname,usename,client_addr,query,query_start,state,waiting from pg_stat_activity where query !~''<IDLE'' and pid <> pg_backend_pid() and state=''active'' order by query;'

\set wait_session ' select pid, datname,usename,client_addr,query,query_start,state,waiting from pg_stat_activity where waiting and query !~''<IDLE'' and pid <> pg_backend_pid() order by query;'

备注:这里编写了两条SQL,第一条即为本文之前的查看当前会话的 SQL ,第二条 SQL 为查询处于等待状态的 SQL, 注意每条 SQL 为一行,接着测试。

测试一: 测试 active_session

本次实验的 PostgreSQL 版本为 9.3.0。
3.1 session1

1
2
[pg93@redhatB tf]$ psql -c "select pg_sleep(60);" &
[1] 7578

备注:后台开启 sessoin1。

3.2 session2

1
2
3
4
5
6
7
8
[pg93@redhatB tf]$ psql francs postgres
psql (9.3.0)
Type "help" for help.
francs=# :active_session
pid | datname | usename | client_addr | query | query_start | state | waiting
------+----------+----------+-------------+----------------------+-------------------------------+--------+---------
7579 | postgres | postgres | | select pg_sleep(60); | 2014-02-21 11:25:12.993091+08 | active | f
(1 row)

备注:另开启 session2 用来监控,实验环境为虚拟机,非常空闲,所以之前开了个 pg_sleep(60)进程,调用方法很简单,执行 :active_session 即可,冒号用来解析变量,同时支持 TAB 补全功能,感觉非常帅。

测试二: 测试 wait_session

4.1 session1

1
2
3
4
5
6
7
8
9
10
11
[pg93@redhatB tf]$ psql francs francs
psql (9.3.0)
Type "help" for help.
francs=> create table test_wait(id int4);
CREATE TABLE

francs=> begin;
BEGIN
francs=> drop table test_wait;
DROP TABLE
.. 注意事务未提交

备注: session1 创建一张测试表,并在事务中删除这张表,注意事务未结束。

4.2 session2
另开一会话,准备往表 test_wait 中写一条数据,但 insert 操作处于等待状态。

1
2
3
4
5
[pg93@redhatB ~]$ psql francs francs
psql (9.3.0)
Type "help" for help.
francs=> insert into test_wait values (1);
.. 这里处于等待状态。

4.3 session3
再开一会话,用来监控。

1
2
3
4
5
6
7
8
9
[pg93@redhatB ~]$ psql francs postgres
psql (9.3.0)
Type "help" for help.

francs=# :wait_session
pid | datname | usename | client_addr | query | query_start | state | waiting
------+---------+---------+-------------+-----------------------------------+-------------------------------+--------+---------
7692 | francs | francs | | insert into test_wait values (1); | 2014-02-21 11:28:35.914326+08 | active | t
(1 row)

备注: waiting = t 表示此会话处于等待状态,测试成功。

总结

这里仅列出两个简单的监控 SQL ,有兴趣的朋友可以根据自己的工作环境定制 SQL ,提高工作效率。

参考

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

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

PostgreSQL实战
感谢支持!
0%