CLI

pgperf

Python port of Heroku PG Extras with several additions and improvements.

The goal of this project is to provide powerful insights into the PostgreSQL database for Python apps that are not using the Heroku PostgreSQL plugin.

Usage:

main [OPTIONS] COMMAND [ARGS]...

Options:

  --conf TEXT
  --install-completion  Install completion for the current shell.
  --show-completion     Show completion for the current shell, to copy it or
                        customize the installation.

all-locks

List all current locks in your database

Usage:

main all-locks [OPTIONS]

blocking

Get all statements that are currently holding locks in your database

Usage:

main blocking [OPTIONS]

buffercache-stats

Get all Buffercache Stats

Usage:

main buffercache-stats [OPTIONS]

buffercache-usage

Get all Buffercache Usage

Usage:

main buffercache-usage [OPTIONS]

cache-hits

Get all cache hits

Usage:

main cache-hits [OPTIONS]

calls

Get the queries that have highest frequency of execution

Usage:

main calls [OPTIONS]

calls-legacy

Get the queries that have highest frequency of execution

Usage:

main calls-legacy [OPTIONS]

index

Index Informations

Usage:

main index [OPTIONS] COMMAND [ARGS]...

Options:

  --verbose / --no-verbose  [default: no-verbose]
  --debug / --no-debug      [default: no-debug]
  --conf TEXT
all

List all the indexes with their corresponding tables and columns.

Usage:

main index all [OPTIONS]
cache-hit

Calculates your cache hit rate for reading indexes

Usage:

main index cache-hit [OPTIONS]
duplicate

Show multiple indexes that have the same set of columns, same opclass, expression and predicate.

Usage:

main index duplicate [OPTIONS]
null

Find indexes with a high ratio of NULL values

Usage:

main index null [OPTIONS]
scans

Number of scans performed on indexes

Usage:

main index scans [OPTIONS]
size

The size of indexes, descending by size, in MB.

Usage:

main index size [OPTIONS]
total-size

Total size of all indexes in MB

Usage:

main index total-size [OPTIONS]
unused

Unused and almost unused indexes. Ordered by their size relative to the number of index scans. Exclude indexes of very small tables (less than 5 pages), where the planner will almost invariably select a sequential scan, but may not in the future as the table grows

Usage:

main index unused [OPTIONS]
usage

Index hit rate (effective databases are at 99% and up)

Usage:

main index usage [OPTIONS]

locks

Queries with active exclusive locks

Usage:

main locks [OPTIONS]

long-running-queries

All queries longer than five minutes by descending duration

Usage:

main long-running-queries [OPTIONS]

outliers

Queries that have longest execution time in aggregate

Usage:

main outliers [OPTIONS]

server

Server Informations

Usage:

main server [OPTIONS] COMMAND [ARGS]...

Options:

  --verbose / --no-verbose  [default: no-verbose]
  --debug / --no-debug      [default: no-debug]
  --conf TEXT
active-conections

List all active connections in this moments in your database

Usage:

main server active-conections [OPTIONS]
additional

Additional Supplied Modules

Usage:

main server additional [OPTIONS] COMMAND [ARGS]...

Options:

  --verbose / --no-verbose  [default: no-verbose]
  --debug / --no-debug      [default: no-debug]
  --conf TEXT

Adding Extensions [sslinfo, pg_buffercache, pg_stat_statements] to your database.

Usage:

main server additional add-all-recommended-extensions [OPTIONS]
extensions

Get available and installed extensions

Usage:

main server additional extensions [OPTIONS]
configuration

Server configuration information

Usage:

main server configuration [OPTIONS] COMMAND [ARGS]...

Options:

  --verbose / --no-verbose  [default: no-verbose]
  --debug / --no-debug      [default: no-debug]
  --conf TEXT
authentication

Show Authentication configuration

Usage:

main server configuration authentication [OPTIONS]
connection-settings

Show Connection Settings configuration

Usage:

main server configuration connection-settings [OPTIONS]
disk

Show Kernel Resource Usage configuration

Usage:

main server configuration disk [OPTIONS]
file-locations

Show File Locations configuration

Usage:

main server configuration file-locations [OPTIONS]
kernel

Show Kernel configuration

Usage:

main server configuration kernel [OPTIONS]
memory

Show Memory configuration

Usage:

main server configuration memory [OPTIONS]
replication

Server Replication information

Usage:

main server configuration replication [OPTIONS] COMMAND [ARGS]...

Options:

  --verbose / --no-verbose  [default: no-verbose]
  --debug / --no-debug      [default: no-debug]
  --conf TEXT
# primary

Show Primary Server configuration

Usage:

main server configuration replication primary [OPTIONS]
# sending

Show Sending Servers configuration

Usage:

main server configuration replication sending [OPTIONS]
# standby

Show Standby Server configuration

Usage:

main server configuration replication standby [OPTIONS]
# subscribers

Show Subscribers configuration

Usage:

main server configuration replication subscribers [OPTIONS]
show-all

Show all Server configuration

Usage:

main server configuration show-all [OPTIONS]
ssl

Show Authentication configuration

Usage:

main server configuration ssl [OPTIONS]
vacuum

Show Cost-based Vacuum Delay configuration

Usage:

main server configuration vacuum [OPTIONS]
writer

Show Background Writer configuration

Usage:

main server configuration writer [OPTIONS]
db-settings

Get the DB Settings

Usage:

main server db-settings [OPTIONS]
kill-all

Kill all the active database connections

Usage:

main server kill-all [OPTIONS]

Options:

  --confirm / --no-confirm  [default: no-confirm]
ssl-used

Check if SSL connection is used

Usage:

main server ssl-used [OPTIONS]
stats

The Statistics Collector. PostgreSQL's statistics collector is a subsystem that supports collection and reporting of information about server activity.

Usage:

main server stats [OPTIONS] COMMAND [ARGS]...

Options:

  --verbose / --no-verbose  [default: no-verbose]
  --debug / --no-debug      [default: no-debug]
  --conf TEXT
collected

Collected Statistics Views

Usage:

main server stats collected [OPTIONS] COMMAND [ARGS]...

Options:

  --verbose / --no-verbose  [default: no-verbose]
  --debug / --no-debug      [default: no-debug]
  --conf TEXT
# all-indexes

One row for each index in the current database, showing statistics about accesses to that specific index. See pg_stat_all_indexes for details. (Compatible with PostgresSQL >= 11.0 )

Usage:

main server stats collected all-indexes [OPTIONS]
# all-tables

One row for each table in the current database, showing statistics about accesses to that specific table. See pg_stat_all_tables for details. (Compatible with PostgresSQL >= 11.0 )

Usage:

main server stats collected all-tables [OPTIONS]
# archiver

One row only, showing statistics about the WAL archiver process's activity. See pg_stat_archiver for details. ( Compatible with PostgresSQL >= 11.0 )

Usage:

main server stats collected archiver [OPTIONS]
# bgwriter

One row only, showing statistics about the background writer process's activity. See pg_stat_bgwriter for details. ( Compatible with PostgresSQL >= 11.0 )

Usage:

main server stats collected bgwriter [OPTIONS]
# database

One row per database, showing database-wide statistics. See pg_stat_database for details. (Compatible with PostgresSQL >= 11.0 )

Usage:

main server stats collected database [OPTIONS]
# database-conflicts

One row per database, showing database-wide statistics about query cancels due to conflict with recovery on standby servers. See pg_stat_database_conflicts for details. (Compatible with PostgresSQL >= 11.0 )

Usage:

main server stats collected database-conflicts [OPTIONS]
# io-all-indexes

One row for each index in the current database, showing statistics about I/O on that specific index. See pg_statio_all_indexes for details. (Compatible with PostgresSQL >= 11.0 )

Usage:

main server stats collected io-all-indexes [OPTIONS]
# io-all-sequences

One row for each sequence in the current database, showing statistics about I/O on that specific sequence. See pg_statio_all_sequences for details. (Compatible with PostgresSQL >= 11.0 )

Usage:

main server stats collected io-all-sequences [OPTIONS]
# io-all-tables

One row for each table in the current database, showing statistics about I/O on that specific table. See pg_statio_all_tables for details. (Compatible with PostgresSQL >= 11.0 )

Usage:

main server stats collected io-all-tables [OPTIONS]
# io-sys-indexes

Same as pg_statio_all_indexes, except that only indexes on system tables are shown. (Compatible with PostgresSQL >= 11.0 )

Usage:

main server stats collected io-sys-indexes [OPTIONS]
# io-sys-sequences

Same as pg_statio_all_sequences, except that only system sequences are shown. (Presently, no system sequences are defined, so this view is always empty.) (Compatible with PostgresSQL >= 11.0 )

Usage:

main server stats collected io-sys-sequences [OPTIONS]
# io-sys-tables

Same as pg_statio_all_tables, except that only system tables are shown. (Compatible with PostgresSQL >= 11.0 )

Usage:

main server stats collected io-sys-tables [OPTIONS]
# io-user-indexes

Same as pg_statio_all_indexes, except that only indexes on user tables are shown. (Compatible with PostgresSQL >= 11.0 )

Usage:

main server stats collected io-user-indexes [OPTIONS]
# io-user-sequences

Same as pg_statio_all_sequences, except that only user sequences are shown. (Compatible with PostgresSQL >= 11.0 )

Usage:

main server stats collected io-user-sequences [OPTIONS]
# io-user-tables

Same as pg_statio_all_tables, except that only user tables are shown. (Compatible with PostgresSQL >= 11.0 )

Usage:

main server stats collected io-user-tables [OPTIONS]
# sys-indexes

Same as pg_stat_all_indexes, except that only indexes on system tables are shown. (Compatible with PostgresSQL >= 11.0 )

Usage:

main server stats collected sys-indexes [OPTIONS]
# user-functions

One row for each tracked function, showing statistics about executions of that function. See pg_stat_user_functions for details. (Compatible with PostgresSQL >= 11.0 )

Usage:

main server stats collected user-functions [OPTIONS]
# user-tables

Same as pg_stat_all_tables, except that only user tables are shown. (Compatible with PostgresSQL >= 11.0 )

Usage:

main server stats collected user-tables [OPTIONS]
# xact-all-tables

Similar to pg_stat_all_tables, but counts actions taken so far within the current transaction (which are not yet included in pg_stat_all_tables and related views). The columns for numbers of live and dead rows and vacuum and analyze actions are not present in this view. (Compatible with PostgresSQL >= 11.0 )

Usage:

main server stats collected xact-all-tables [OPTIONS]
# xact-user-functions

Similar to pg_stat_user_functions, but counts only calls during the current transaction (which are not yet included in pg_stat_user_functions). (Compatible with PostgresSQL >= 11.0 )

Usage:

main server stats collected xact-user-functions [OPTIONS]
# xact-user-tables

Same as pg_stat_xact_all_tables, except that only user tables are shown. (Compatible with PostgresSQL >= 11.0 )

Usage:

main server stats collected xact-user-tables [OPTIONS]
dinamic

Dynamic Statistics Views

Usage:

main server stats dinamic [OPTIONS] COMMAND [ARGS]...

Options:

  --verbose / --no-verbose  [default: no-verbose]
  --debug / --no-debug      [default: no-debug]
  --conf TEXT
# activity

One row per server process, showing information related to the current activity of that process, such as state and current query. See pg_stat_activity for details. ( >= PostgresSQL 11.0 )

Usage:

main server stats dinamic activity [OPTIONS]
# progress-vacuum

One row for each backend (including autovacuum worker processes) running VACUUM, showing current progress. ( >= PostgresSQL 11.0 )

Usage:

main server stats dinamic progress-vacuum [OPTIONS]
# replication

One row per WAL sender process, showing statistics about replication to that sender's connected standby server. See pg_stat_replication for details. ( >= PostgresSQL 11.0 )

Usage:

main server stats dinamic replication [OPTIONS]
# ssl

One row per connection (regular and replication), showing information about SSL used on this connection. ( >= PostgresSQL 11.0 ) See pg_stat_ssl for details.

Usage:

main server stats dinamic ssl [OPTIONS]
# statements-reset

pg_stat_statements_reset discards statistics gathered so far by pg_stat_statements ( >= PostgresSQL 11.0 )

Usage:

main server stats dinamic statements-reset [OPTIONS]
# subscription

At least one row per subscription, showing information about the subscription workers. See pg_stat_subscription for details. ( >= PostgresSQL 11.0 )

Usage:

main server stats dinamic subscription [OPTIONS]
# wal-receiver

Only one row, showing statistics about the WAL receiver from that receiver's connected server. See pg_stat_wal_receiver for details. ( >= PostgresSQL 11.0 )

Usage:

main server stats dinamic wal-receiver [OPTIONS]
vacuum-stats

Dead rows and whether an automatic vacuum is expected to be triggered

Usage:

main server vacuum-stats [OPTIONS]

table

Table Informations

Usage:

main table [OPTIONS] COMMAND [ARGS]...

Options:

  --verbose / --no-verbose  [default: no-verbose]
  --debug / --no-debug      [default: no-debug]
  --conf TEXT
bloat

Estimation of table 'bloat' space allocated to a relation that is full of dead tuples, that has yet to be reclaimed.

Usage:

main table bloat [OPTIONS]
cache-hit

Calculates your cache hit rate for reading tables

Usage:

main table cache-hit [OPTIONS]
index-scans

Count of index scans by table descending by order

Usage:

main table index-scans [OPTIONS]
index-size

Total size of all the indexes on each table, descending by size

Usage:

main table index-size [OPTIONS]
records-rank

All tables and the number of rows in each ordered by number of rows descending

Usage:

main table records-rank [OPTIONS]
seq-scans

Count of sequential scans by table descending by order

Usage:

main table seq-scans [OPTIONS]
size

Size of the tables (excluding indexes), descending by size

Usage:

main table size [OPTIONS]
size-with-index

Size of the tables (including indexes), descending by size

Usage:

main table size-with-index [OPTIONS]

version

Usage:

main version [OPTIONS]