nikolays / postgres_dba

The missing set of useful tools for Postgres DBAs and all engineers

postgres_dba (PostgresDBA)

The missing set of useful tools for Postgres DBA and mere mortals.

⚠️ If you have great ideas, feel free to create a pull request or open an issue.

👉 See also postgres-checkup, a tool for automated health checks and SQL performance analysis.

Questions?

Questions? Ideas? Contact me: [email protected], Nikolay Samokhvalov.

Credits

postgres_dba is based on useful queries created and improved by many developers. Here is incomplete list of them:

• Jehan-Guillaume (ioguix) de Rorthais https://github.com/ioguix/pgsql-bloat-estimation
• Alexey Lesovsky, Alexey Ermakov, Maxim Boguk, Ilya Kosmodemiansky et al. from Data Egret (aka PostgreSQL-Consulting) https://github.com/dataegret/pg-utils
• Josh Berkus, Quinn Weaver et al. from PostgreSQL Experts, Inc. https://github.com/pgexperts/pgx_scripts

Requirements

You need to have psql version 10 or newer, but the Postgres server itself can be older – most tools work with it. You can install postgresql-client library version, say, 12 on your machine and use it to work with Postgres server version 9.6 and older – in this case postgres_dba will work. But you do need to have psql from the latest (version 12) Postgres release.

On clean Ubuntu, this is how you can get postgresql-client and have the most recent psql:

sudo sh -c "echo \"deb http://apt.postgresql.org/pub/repos/apt/ `lsb_release -cs`-pgdg main\" >> /etc/apt/sources.list.d/pgdg.list"
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
sudo apt-get update
sudo apt-get install -y postgresql-client-12

Using alternative psql pager called "pspg" is highly recommended (but not required): https://github.com/okbob/pspg.

Installation

The installation is trivial. Clone the repository and put "dba" alias to your .psqlrc file (works in bash, zsh, and csh):

git clone https://github.com/NikolayS/postgres_dba.git
cd postgres_dba
printf "%s %s %s %s\n" \\echo 🧐 🐘 'postgres_dba 6.0 installed. Use ":dba" to see menu' >> ~/.psqlrc
printf "%s %s %s %s\n" \\set dba \'\\\\i $(pwd)/start.psql\' >> ~/.psqlrc

That's it.

Usage

Connect to Local Postgres Server

If you are running psql and Postgres server on the same machine, just launch psql:

psql -U <username> <dbname>

And type :dba <Enter> in psql. (Or \i /path/to/postgres_dba/start.psql if you haven't added shortcut to your ~/.psqlrc file).

– it will open interactive menu.

Connect to Remote Postgres Server

What to do if you need to connect to a remote Postgres server? Usually, Postgres is behind a firewall and/or doesn't listen to a public network interface. So you need to be able to connect to the server using SSH. If you can do it, then just create SSH tunnel (assuming that Postgres listens to default port 5432 on that server:

ssh -fNTML 9432:localhost:5432 [email protected]

Then, just launch psql, connecting to port 9432 at localhost:

psql -h localhost -p 9432 -U <username> <dbname>

And type :dba <Enter> in psql to launch postgres_dba.

Connect to Heroku Postgres

Just open psql as you usually do with Heroku:

heroku pg:psql -a <your_project_name>

And then:

:dba

How to Extend (Add More Queries)

You can add your own useful SQL queries and use them from the main menu. Just add your SQL code to ./sql directory. The filename should start with some 1 or 2-letter code, followed by underscore and some additional arbitrary words. Extension should be .sql. Example:

  sql/f1_cool_query.sql

– this will give you an option "f1" in the main menu. The very first line in the file should be an SQL comment (starts with --) with the query description. It will automatically appear in the menu.

Once you added your queries, regenerate start.psql file:

/bin/bash ./init/generate.sh

Now your have the new start.psql and can use it as described above.

‼️ If your new queries are good consider sharing them with public. The best way to do it is to open a Pull Request (https://help.github.com/articles/creating-a-pull-request/).

Uninstallation

No steps are needed, just delete postgres_dba directory and remove \set dba ... in your ~/.psqlrc if you added it.

postgres_dba's Issues

pg_buffercache report

proposal (quick and dirty draft):

with a as (
SELECT reldatabase, c.relname, count(*) AS buffers
FROM pg_buffercache b INNER JOIN pg_class c
ON b.relfilenode = pg_relation_filenode(c.oid)
GROUP BY 1, 2
ORDER BY 3 DESC
)
select *, sum(buffers) over ()
from a
order by buffers desc
limit 20;

Feature request: List reloptions

Sometimes I need to found tables with individual adjustments like autovacuum_scale_factor ou autovacuum disabled.

I use this query and think it would be useful for other people.

SELECT relname as table, nspname as schema, relkind, reloptions 
FROM
    pg_class c
    JOIN pg_namespace n ON c.relnamespace = n.oid
WHERE reloptions IS NOT NULL AND relname != 'pg_stats'
ORDER BY 2,1;

What do you people think? It is useful enough to open a pr?

pg_stat_statements report for older (<9.5) Postgres versions

i2: use CONCURRENTLY when drop/create indexes

Issue with query b1 on postgres 12

Hello Nicholas,

Thanks for this awesome tool. This issue is with option b1 – Tables Bloat, rough estimation.
I am running on postgres 12.1 and got this error

psql:/var/lib/pgsql/postgres_dba/sql/b1_table_estimation.sql:99: ERROR:  column tbl.relhasoids does not exist
LINE 19:       + case when tbl.relhasoids then 4 else 0 end as tpl_hd...

Can you please include support for postgres 12

New report: check int2 and int4 PK columns to be out-of-capacity soon

Report name: c1_integer_pk_columns_check

Idea: check int4 PKs to be out-of-capacity in the nearest future (is max value is approaching 2^31 soon?).
The same for int2 PKs (limit: 2^15).

Advanced idea: if created or created_at column is present in the table, forecast reaching the limit.

The goal of this report: warn about approaching int4/int2 limits, propose using int8/in4 instead.

Improve menu design

currently:

ошибка в b4_btree_pgstattuple.sql

У меня много схем в БД и поэтому проявилась вот такая ошибка:

ERROR:  cross-database references are not implemented: "bankrupt_bulk.bankrupt_bulk.idx_address_changes"

это связано вот с этим:
schemaname || '.' || p.indexrelid::regclass::text
для объекта находящегося по пути поиска p.indexrelid::regclass::text схема отсутствует, а вот если объект находится вне пути поиска то схема добавляется и поэтому получается задвоение схемы.
Предлагаю вообще убрать schemaname || '.' || . Или же сделать более интеллектуальный анализ.

pg_stat_statements "1st word" report

see https://gist.github.com/NikolayS/21e508cf7cb33657f002077e793f3dd4

"extended" mode

it's there already,
TODO:

• doc
• switcher in the menu

i3 fails when intarray is installed

test=# create extension intarray
test-# ;
CREATE EXTENSION
test=# :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)
  i1 – Unused/Rarely Used Indexes
  i2 – Unused/Redundant Indexes Do & Undo Migration DDL
  i3 – FKs with Missing/Bad Indexes
  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
   x – Turn Wide Mode ON (currently OFF): show more details, more columns
   q – Quit

Type your choice and press <Enter>:
i3
psql:/home/nikolay/postgres_dba/sql/i3_non_indexed_fks.sql:125: ERROR:  operator is not unique: smallint[] @> smallint[]
LINE 60: ...(indkey::int2[])[0:(array_length(key_cols,1) -1)] @> key_col...
                                                              ^
HINT:  Could not choose a best candidate operator. You might need to add explicit type casts.
Press <Enter> to continue…

pg v10.3 ERROR: function pg_last_xlog_receive_location() does not exist

Hello, cool tool - is there a version that works with v10?

Type your choice and press :
1
psql.bin:/instance1/postgres/10/sitewatch/test/postgres_dba/sql/1_basic.sql:53:ERROR: function pg_last_xlog_receive_location() does not exist
LINE 13: when pg_last_xlog_receive_location() = pg_last_xlo...
^
HINT: No function matches the given name and argument types. You might need toadd explicit type casts.
Press to continue…

pg_stat_statements changed name colums

s1 and s2 not working, because of changing in pg_stat_statements columns

NEW column name(pg13,pg_stat_statements 1.8):
min_plan_time
max_plan_time
mean_plan_time
stddev_plan_time
total_exec_time
min_exec_time
max_exec_time
mean_exec_time

OLD column name:
total_time
min_time
max_time
mean_time
stddev_time

Bloat estimation is wrong for tables with alignment padding

Example:

create table bloattest as select
  1::int4 as val1,
  2::int8 as val2,
  3::int4 as val3
from generate_series(1, 500);

vacuum analyze bloattest;

-- all reports show 20-25% bloat for this fresh table. Including pgstattuple's one (report b3).

PGExperts' report https://github.com/pgexperts/pgx_scripts/blob/master/bloat/table_bloat_check.sql also shows ~20% bloat. (Actually, it has a comment "still needs work; is often off by +/- 20%" which presumingly refers to this very issue).

@ioguix explained this alignment-related problem here: http://blog.ioguix.net/postgresql/2014/09/10/Bloat-estimation-for-tables.html

TODO:

• alignment padding report showing how many bytes "wasted" (source for possible optimization for those who care – re-creation of table with different columns order will save space)
• fix b1 (replace with PGExperts' report, aldo fixed?)
• fix b3

division by zero in report 0

psql:/home/nikolay/postgres_dba/sql/0_node.sql:74: ERROR:  division by zero

How about Oracle port ?

Better connection monitoring

example:

select
  state,
  count(*) "Total count",
  count(*) filter (where now() - state_change <= '1 second') as "Fresh (<1 sec)",
  count(*) filter (where now() - state_change > '1 second') as "Old (1+ sec)"
from pg_stat_activity
group by cube(state);

+ per host (clent_addr)
+ per app

Feature request active and idle in transaction query

It's good to have query to see active and idle in transaction query
I propose to make "a2" with next query:

SELECT pid, client_addr, now() - query_start as "runtime", usename, datname, state, query
FROM pg_stat_activity
WHERE now() - query_start > '30 seconds'::interval and state != 'idle'
ORDER BY runtime DESC;

psql:/var/lib/postgresql/postgres_dba/start.psql:22: unrecognized value ":postgres_dba_wide" for "\if expression": Boolean expected

Hi Nikolay,

In a server with postgresql 9.5.12 and postgresql-client 10.3 when I type :dba I get this error in the menu:

my_db=# :dba
SET
RESET
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)
i1 – Unused/Rarely Used Indexes
i2 – Unused/Redundant Indexes Do & Undo Migration DDL
i3 – FKs with Missing/Bad Indexes
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
psql:/var/lib/postgresql/sistemas/postgres_dba/start.psql:22: unrecognized value ":postgres_dba_wide" for "\if expression": Boolean expected
x – Turn Wide Mode ON (currently OFF): show more details, more columns
q – Quit

Type your choice and press :

Regards.

bloat estimate precise question

Hello,
I'm comparing bloate results between pgstattuple and posgres_dba and its varying a lot.
results from same table

pgstattuple: 1.93%
postgrtesq_dba: 38.77%

commands:
pgstattuple:
SELECT pg_size_pretty(pg_relation_size('test_table')) as table_size,
(pgstattuple('test_table')).dead_tuple_percent;

:dba: b1

Report about missing extension nicely

List of SQL reports with `\if` (pre-10 version)

Now, if we psql if 9.6 or older, we cannot use menu.
We also cannot simply copy/paste SQL snippets since they have \if blocks.

TODO: create a separate dir for pre-9.6 psql.

t1 has one error,why?

my postgres version is 12.1:

Type your choice and press :
t1
psql:/home/pg121/postgres_dba/sql/t1_tuning.sql:7: error: unrecognized value ":postgres_dba_interactive_mode" for "\if expression": Boolean expected
Parameter | Value | Default | Category
---------------------------------+---------+---------+------------------------------------------------------
autovacuum_analyze_scale_factor | 0.1 | 0.1 | Autovacuum
autovacuum_max_workers | 3 | 3 | Autovacuum
autovacuum_naptime | 60 | 60 | Autovacuum
autovacuum_vacuum_scale_factor | 0.2 | 0.2 | Autovacuum
max_connections | 100 | 100 | Connections and Authentication / Connection Settings
default_statistics_target | 100 | 100 | Query Tuning / Other Planner Options
effective_cache_size | 4096 MB | 4096 MB | Query Tuning / Planner Cost Constants
random_page_cost | 4 | 4 | Query Tuning / Planner Cost Constants
seq_page_cost | 1 | 1 | Query Tuning / Planner Cost Constants
effective_io_concurrency | 1 | 1 | Resource Usage / Asynchronous Behavior
max_parallel_workers | 8 | 8 | Resource Usage / Asynchronous Behavior
max_parallel_workers_per_gather | 2 | 2 | Resource Usage / Asynchronous Behavior
max_worker_processes | 8 | 8 | Resource Usage / Asynchronous Behavior
autovacuum_work_mem | -1 | -1 | Resource Usage / Memory
maintenance_work_mem | 64 MB | 64 MB | Resource Usage / Memory
shared_buffers | 128 MB | 8192 kB | Resource Usage / Memory
work_mem | 4096 kB | 4096 kB | Resource Usage / Memory
checkpoint_completion_target | 0.5 | 0.5 | Write-Ahead Log / Checkpoints
max_wal_size | 1024 | 1024 | Write-Ahead Log / Checkpoints
min_wal_size | 80 | 80 | Write-Ahead Log / Checkpoints
wal_buffers | 4096 kB | -1 | Write-Ahead Log / Settings
(21 rows)

Press to continue…

Alignment Padding report

Estimate alignment padding bytes in each row. Show an estimation of possible disk space savings and propose a new column order.

TODO:

• typealign=c (see #7)
• Notify about VARLENA columns
• suggestions

Nice to have:

• NULLs
• readme

Issue of p1

Good evening, Nikolay. Thank you for tool.

Report P1 confused me. Where I'm wrong? t1 it my table originally, t2 is offered.

https://dbfiddle.uk/?rdbms=postgres_11&fiddle=cc993fad2ccc51ba6bd410612f920849
https://dbfiddle.uk/?rdbms=postgres_11&fiddle=099342ecdd74af9fd50f680e57f631bb

ERROR: relation "pg_stat_statements" does not exist

psql 10, Postgresql 10

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 – Unused/Redundant Indexes Do & Undo Migration DDL
  i3 – FKs with Missing/Bad Indexes
  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
   q – Quit

Type your choice and press <Enter>:
s1
psql.bin:/home/Develop/postgres_dba/sql/s1_pg_stat_statements_top_total.sql:42: ERROR:  relation "pg_stat_statements" does not exist
LINE 25: from pg_stat_statements

extend pg_stat_statements reports with pg_stat_kcache

0: add info about pg_xlog/pg_wal

It's worth to see how many files / WAL segments are currently located in pg_xlog/pg_wal and what's the total size.

select now()::timestamptz(0), count(1), pg_size_pretty(sum((pg_stat_file('pg_xlog/'||fname)).size)) as total_size
from pg_ls_dir('pg_xlog') as t(fname);


select pg_last_xlog_replay_location(), pg_last_xlog_receive_location(); -- on replica


select slot_name, slot_type, active, active_pid as pid, (select pg_size_pretty(pg_xlog_location_diff(pg_current_xlog_insert_location(), flush_location)) from pg_stat_replication where pid = active_pid) as lag from pg_replication_slots;

select pg_current_xlog_location(); -- on master

s1 problem, needs conditional for the order by

s1 gives the following error on a v12 server:

LINE 31: order by sum(total_exec_time) desc

needs conditional to use total_time

p1: data types with typalign=c

create table x(a "char", b int8, c "char");

– for this table report a1 (new, from branch "alignment") gives:

 Table | Table Size | Bytes Wasted in a Row |      Wasted
-------+------------+-----------------------+------------------
 x     | 536 kB     |                       |
(3 rows)

The alignment padding here is 14 bytes, but it isn't shown.

insert into x select ' ', -2, ' ' from generate_series(1, 10000);
select get_raw_page('x',0);
...2000000000000000feffffffffffffff2000000000000000

Todo:

• deal with data types with typealign=c
• CI tests

(reported by S B over telegram channel)