简介
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)
