简介

postgres_dba工具使用方法：在psql中敲入快捷键来执行对应的SQL脚本，以便进行问题的分析与定位,postgres_dba工具使用的SQL脚本位于postgres_dba/sql目录下

安装方法

git clone https://github.com/NikolayS/postgres_dba.git
echo "\\set dba '\\\\i `pwd`/postgres_dba/start.psql'" >> ~/.psqlrc  # bash version; won't work in zsh

使用

[postgres@node_206 ~/postgres_dba]$psql -Upostgres 
psql (12.3)
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>:
0
              metric               |                                                  value                                 
                 
-----------------------------------+----------------------------------------------------------------------------------------
-----------------
 Postgres Version                  | PostgreSQL 12.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4
.8.5-44), 64-bit
 Config file                       | /postgresql/pgsql/data/postgresql.conf
 Role                              | Master
 Replicas                          | async/streaming: 192.168.0.205
 Started At                        | 2020-11-15 18:08:00+08
 Uptime                            | 1 day 08:59:53
 Checkpoints                       | 68
 Forced Checkpoints                | 4.4%
 Checkpoint MB/sec                 | 0.003560
 --------------------------------- | ---------------------------------------------------------------------------------------
-
 Database Name                     | postgres
 Database Size                     | 11 MB
 Stats Since                       | 2020-11-16 16:03:03+08
 Stats Age                         | 11:04:51
 Installed Extensions              | pg_stat_statements 1.7, plpgsql 1.0
 Cache Effectiveness               | 99.99%
 Successful Commits                | 100.00%
 Conflicts                         | 0
 Temp Files: total size            | 0 bytes
 Temp Files: total number of files | 0
 Temp Files: avg file size         | 
 Deadlocks                         | 0
(22 rows)

Press <Enter> to continue…
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>:
1
   Database    |       Size       |   Stats Age    | Cache eff. | Committed | Conflicts | Deadlocks | Temp. Files 
---------------+------------------+----------------+------------+-----------+-----------+-----------+-------------
 *** TOTAL *** | 456 MB (100.00%) |                | 99.51%     | 99.99%    |         0 |         0 | 2 (267 MB)
               |                  |                |            |           |           |           | 
 test          | 430 MB (94.28%)  | 15:10:26       | 99.48%     | 99.84%    |         0 |         0 | 2 (267 MB)
 postgres      | 11 MB (2.35%)    | 11:05:12       | 99.99%     | 100.00%   |         0 |         0 | 0 (0 bytes)
 template0     | 7953 kB (1.70%)  | 1 day 08:43:29 | 99.99%     | 99.99%    |         0 |         0 | 0 (0 bytes)
 template1     | 7809 kB (1.67%)  |                |            |           |         0 |         0 | 0 (0 bytes)
(6 rows)