postgres_dba——构建自己的运维脚本库

我们在平时的运维中会用到各种脚本来处理问题，但是每次使用的时候都需要去找一下自己的脚本然后复制粘贴过来执行。你可能会想如果能跳过这些繁琐的步骤，直接在数据库中找到这些脚本一键执行就好了。

推荐一个小工具：postgres_dba，可以在psql命令行中随时调用脚本，也可以将我们常用的脚本自己加进去，根据需求定制。

安装使用：

直接下载完解压就可以：https://github.com/NikolayS/postgres_dba

unzip postgres_dba-master.zip
mv postgres_dba-master/ postgres_dba/

然后将脚本添加到psqlrc文件中：

echo "\\set dba '\\\\i `pwd`/postgres_dba/start.psql'" >> ~/.psqlrc

接下来就可以使用了！

pg13@cnndwpmesp3T-> psql
psql (13beta3)
Type "help" for help.

postgres=# :dba
Menu:
   0 – Node & Current DB Information: master/replica, lag, DB size, tmp files, etc
   1 – Databases: Size, Statistics
   2 – Table Sizes
   3 – Load Profile
  a1 – Current Activity: count of current connections grouped by database, user name, state
  b1 – Tables Bloat, rough estimation
  b2 – B-tree Indexes Bloat, rough estimation
  b3 – Tables Bloat, more precise (requires pgstattuple extension; expensive)
  b4 – B-tree Indexes Bloat, more precise (requires pgstattuple extension; expensive)
  b5 – Tables and Columns Without Stats (so bloat cannot be estimated)
  e1 – List of extensions installed in the current DB
  i1 – Unused/Rarely Used Indexes
  i2 – List of redundant indexes
  i3 – FKs with Missing/Bad Indexes
  i4 – List of invalid indexes
  i5 – Unused/Redundant Indexes Do & Undo Migration DDL
  l1 – Locks: analysis of "locking trees"
  p1 – [EXPERIMENTAL] Alignment Padding. How many bytes can be saved if columns are ordered better?
  s1 – Slowest Queries, by Total Time (requires pg_stat_statements extension)
  s2 – Slowest Queries Report (requires pg_stat_statements)
  t1 – Postgres parameters tuning
  v1 – Vacuum: Current Activity
  v2 – Vacuum: VACUUM progress and autovacuum queue
   q – Quit

Type your choice and press <Enter>:

以上是自带的一些脚本，我们也可以自己添加需要的脚本进去。

脚本扩展方法：

我们只需要将自己的sql脚本添加到sql/这个目录下面即可，文件名建议以1或2个字母开头即可。

例如我这里添加一个统计不同schema下对象个数的脚本：

vim c1_count_objects.sql

SELECT
        n.nspname as schema_name
        ,CASE c.relkind
           WHEN 'r' THEN 'table'
           WHEN 'v' THEN 'view'
           WHEN 'i' THEN 'index'
           WHEN 'S' THEN 'sequence'
           WHEN 's' THEN 'special'
        END as object_type
        ,count(1) as object_count
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','v','i','S','s')
GROUP BY  n.nspname,
        CASE c.relkind
           WHEN 'r' THEN 'table'
           WHEN 'v' THEN 'view'
           WHEN 'i' THEN 'index'
           WHEN 'S' THEN 'sequence'
           WHEN 's' THEN 'special'
        END
ORDER BY n.nspname,
        CASE c.relkind
           WHEN 'r' THEN 'table'
           WHEN 'v' THEN 'view'
           WHEN 'i' THEN 'index'
           WHEN 'S' THEN 'sequence'
           WHEN 's' THEN 'special'
        END;

然后执行generate.sh脚本初始化：

./init/generate.sh

这里注意要在postgres_dba的目录下执行，这样才能在该目录下生成start.psql和warmup.psql两个脚本。

然后我们去start.psql中可以为我们新增的选项加点注释：

...
\echo '  c1 – List the count of objects for each Database Schema'
...

再次执行，可以看到已经新增了我们刚刚添加的脚本。

Type your choice and press <Enter>:
c1
    schema_name     | object_type | object_count
--------------------+-------------+--------------
 __monitor__        | index       |            1
 __monitor__        | sequence    |            1
 __monitor__        | table       |           32
 cron               | index       |            1
 cron               | sequence    |            1
 cron               | table       |            1
 information_schema | table       |            4
 information_schema | view        |           61
 pg_catalog         | index       |          116
 pg_catalog         | table       |           62
 pg_catalog         | view        |           67
 pg_toast           | index       |           62
 public             | index       |            2
 public             | sequence    |            1
 public             | table       |            7
 public             | view        |            6
 repack             | view        |            2
(17 rows)

参考链接：
https://github.com/NikolayS/postgres_dba