pgtop-cli
PGTop
Last updated 2 months ago by tim.alby .
MIT · Repository · Bugs · Original npm · Tarball · package.json
$ cnpm install pgtop-cli 
SYNC missed versions from official npm registry.

pgtop-cli

Version License

Analyse PostgreSQL from your terminal

Overview

PGTop helps you running some specifically crafted queries to analyse the state of your database right from your terminal:

$ pgtop queries:long-running
running query... done
  pid  | duration |           query
-------+----------+-------------------------
 12345 | 00:12:34 | SELECT                 +
       |          |   my_column            +
       |          | FROM large_table       +
       |          | WHERE                  +
       |          |   some_column LIKE 'foo'

Quickly check your database for:

  • long-running queries
  • blocking queries
  • vacuum stats
  • unused indexes
  • and more...

Usage

$ npm install -g pgtop-cli
$ pgtop COMMAND
running command...
$ pgtop (-v|--version|version)
pgtop-cli/0.2.0 darwin-x64 node-v10.0.0
$ pgtop --help [COMMAND]
USAGE
  $ pgtop COMMAND
...

Connecting to the Database

At the moment the only supported way to connect to a database is via a .pg_service.conf file set in the your home folder.

Define your database connection(s) in the ~/.pg_service.conf as follow:

[service_name]
host=
port=
dbname=
user=
password=

If more than one connection is defined PGTop will ask you to choose one of the connections each time you run a command.

You can also pass a --service service_name to connect directly.

Commands

pgtop commands

list all the commands

USAGE
  $ pgtop commands

OPTIONS
  -h, --help  show CLI help
  -j, --json  output in json format
  --hidden    also show hidden commands

See code: @oclif/plugin-commands

pgtop health:table-size

list table sizes

USAGE
  $ pgtop health:table-size

OPTIONS
  -c, --columns=columns  Select columns to output (comma-separated)
  -l, --limit=limit      Limit number of rows returned
  -o, --order=order      Order (column number (Integer) or name (String))
  -s, --service=service  Postgres service to use (must be defined in ~/.pg_service.conf)
  -v, --verbose          Enable verbose output
  --html                 Set output format to HTML

DESCRIPTION
  List all tables and their size with and without indexes.

  Columns:
     * `namespace`: namespace name
     * `table`: table name
     * `kind`: table kind
     * `size`: table size
     * `size_with_index`: total table size including indexes

See code: src/commands/health/table-size.js

pgtop health:total-size

show database size

USAGE
  $ pgtop health:total-size

OPTIONS
  -s, --service=service  Postgres service to use (must be defined in ~/.pg_service.conf)
  -v, --verbose          Enable verbose output
  --html                 Set output format to HTML

DESCRIPTION
  Show database size.

  Columns:
     * `size`: database size

See code: src/commands/health/total-size.js

pgtop health:vacuum-stats

show vacuum statistics

USAGE
  $ pgtop health:vacuum-stats

OPTIONS
  -c, --columns=columns  Select columns to output (comma-separated)
  -l, --limit=limit      Limit number of rows returned
  -o, --order=order      Order (column number (Integer) or name (String))
  -s, --service=service  Postgres service to use (must be defined in ~/.pg_service.conf)
  -v, --verbose          Enable verbose output
  --html                 Set output format to HTML

DESCRIPTION
  Show basic vacuum-related informations.

  Columns:
     * `namespace`: namespace name
     * `table`: table name
     * `last`: last vacuum (manual or auto)
     * `rows`: estimated number of rows
     * `dead_rows`: estimated number of dead rows
     * `threshold`: autovacuum threshold (in number of dead rows)
     * `expected`: is autovacuum expected

See code: src/commands/health/vacuum-stats.js

pgtop help [COMMAND]

display help for pgtop

USAGE
  $ pgtop help [COMMAND]

ARGUMENTS
  COMMAND  command to show help for

OPTIONS
  --all  see all commands in CLI

See code: @oclif/plugin-help

pgtop index:size

list indexes size

USAGE
  $ pgtop index:size

OPTIONS
  -c, --columns=columns  Select columns to output (comma-separated)
  -l, --limit=limit      Limit number of rows returned
  -o, --order=order      Order (column number (Integer) or name (String))
  -s, --service=service  Postgres service to use (must be defined in ~/.pg_service.conf)
  -v, --verbose          Enable verbose output
  --html                 Set output format to HTML

DESCRIPTION
  List each index with their size

  Columns:
     * `namespace`: namespace name
     * `table`: table name
     * `index`: index name
     * `size`: total index size

See code: src/commands/index/size.js

pgtop index:total-size

show total index size

USAGE
  $ pgtop index:total-size

OPTIONS
  -s, --service=service  Postgres service to use (must be defined in ~/.pg_service.conf)
  -v, --verbose          Enable verbose output
  --html                 Set output format to HTML

DESCRIPTION
  Show total index size.

  Columns:
     * `size`: total index size

See code: src/commands/index/total-size.js

pgtop index:unused

list unused indexes

USAGE
  $ pgtop index:unused

OPTIONS
  -c, --columns=columns  Select columns to output (comma-separated)
  -l, --limit=limit      Limit number of rows returned
  -o, --order=order      Order (column number (Integer) or name (String))
  -s, --service=service  Postgres service to use (must be defined in ~/.pg_service.conf)
  -v, --verbose          Enable verbose output
  --html                 Set output format to HTML

DESCRIPTION
  List all non-unique indexes used less than 50 times.

  Columns:
     * `namespace`: namespace name
     * `table`: table name
     * `index`: index name
     * `index_scans`: number of time the index was used
     * `index_size`: size of the index

See code: src/commands/index/unused.js

pgtop index:usage

show indexes usage

USAGE
  $ pgtop index:usage

OPTIONS
  -c, --columns=columns  Select columns to output (comma-separated)
  -l, --limit=limit      Limit number of rows returned
  -o, --order=order      Order (column number (Integer) or name (String))
  -s, --service=service  Postgres service to use (must be defined in ~/.pg_service.conf)
  -v, --verbose          Enable verbose output
  --html                 Set output format to HTML

DESCRIPTION
  Show how many times each table has been queried by index and by sequential scan.

  Columns:
     * `namespace`: namespace name
     * `table`: table name
     * `index_scans`: number of time the index was used
     * `sequential_scans`: number of time the index was NOT used
     * `percent`: index usage in percent

See code: src/commands/index/usage.js

pgtop queries:blocking

list blocking queries

USAGE
  $ pgtop queries:blocking

OPTIONS
  -c, --columns=columns    Select columns to output (comma-separated)
  -l, --limit=limit        Limit number of rows returned
  -o, --order=order        Order (column number (Integer) or name (String))
  -s, --service=service    Postgres service to use (must be defined in ~/.pg_service.conf)
  -t, --truncate=truncate  Truncate statement to the given number of characters
  -v, --verbose            Enable verbose output
  --html                   Set output format to HTML

DESCRIPTION
  List all blocking queries and the queries being blocked.

  Columns:
     * `blocking_pid`: pid of the blocking query
     * `blocking_duration`: how long the query has been blocking for
     * `blocking_statement`: full statement of the blocking query
     * `blocked_pid`: pid of the blocked query
     * `blocked_duration`: how long the query has been blocked for
     * `blocked_statement`: full statement of the blocked query

See code: src/commands/queries/blocking.js

pgtop queries:cancel PID

cancel query

USAGE
  $ pgtop queries:cancel PID

ARGUMENTS
  PID  query to cancel

OPTIONS
  -s, --service=service  Postgres service to use (must be defined in ~/.pg_service.conf)
  -v, --verbose          Enable verbose output
  --html                 Set output format to HTML

DESCRIPTION
  Cancel the query specified by pid.

  Columns:
     * `cancelled`: true (`t`) if the query was cancelled

See code: src/commands/queries/cancel.js

pgtop queries:idle

list idle queries

USAGE
  $ pgtop queries:idle

OPTIONS
  -c, --columns=columns    Select columns to output (comma-separated)
  -l, --limit=limit        Limit number of rows returned
  -o, --order=order        Order (column number (Integer) or name (String))
  -s, --service=service    Postgres service to use (must be defined in ~/.pg_service.conf)
  -t, --truncate=truncate  Truncate statement to the given number of characters
  -v, --verbose            Enable verbose output
  --html                   Set output format to HTML

DESCRIPTION
  List all idle queries running for more than 10 seconds.

  Columns:
     * `pid`: pid of the query
     * `duration`: how long the query has been running for
     * `statement`: full statement of the query

See code: src/commands/queries/idle.js

pgtop queries:long-running

list long-running queries

USAGE
  $ pgtop queries:long-running

OPTIONS
  -c, --columns=columns    Select columns to output (comma-separated)
  -l, --limit=limit        Limit number of rows returned
  -o, --order=order        Order (column number (Integer) or name (String))
  -s, --service=service    Postgres service to use (must be defined in ~/.pg_service.conf)
  -t, --truncate=truncate  Truncate statement to the given number of characters
  -v, --verbose            Enable verbose output
  --html                   Set output format to HTML

DESCRIPTION
  List all non-idle queries running for more than 10 seconds.

  Columns:
     * `pid`: pid of the query
     * `duration`: how long the query has been running for
     * `statement`: full statement of the query

See code: src/commands/queries/long-running.js

pgtop queries:stats

show queries statistics

USAGE
  $ pgtop queries:stats

OPTIONS
  -c, --columns=columns    Select columns to output (comma-separated)
  -l, --limit=limit        Limit number of rows returned
  -o, --order=order        Order (column number (Integer) or name (String))
  -s, --service=service    Postgres service to use (must be defined in ~/.pg_service.conf)
  -t, --truncate=truncate  Truncate statement to the given number of characters
  -v, --verbose            Enable verbose output
  --html                   Set output format to HTML

DESCRIPTION
  Show queries statistics from `pg_stat_statements`.
  Limited to the first 50 rows by default.

  Columns:
     * `total_time`: total time spent for all queries
     * `percent`: total time as percentage
     * `calls`: total number of calls
     * `avg`: average query duration (ms)
     * `min`: minimum query duration (ms)
     * `max`: maximum query duration (ms)
     * `query`: full statement of the query

See code: src/commands/queries/stats.js

pgtop queries:terminate PID

terminate query

USAGE
  $ pgtop queries:terminate PID

ARGUMENTS
  PID  query to terminate

OPTIONS
  -s, --service=service  Postgres service to use (must be defined in ~/.pg_service.conf)
  -v, --verbose          Enable verbose output
  --html                 Set output format to HTML

DESCRIPTION
  Terminate the query specified by pid.

  Columns:
     * `terminated`: true (`t`) if the query was terminated

See code: src/commands/queries/terminate.js

Contributing

Feel free to contribute by opening a Pull Request.

Testing

Tests are running against a sample database inside Docker.

make test

By default the tests are absorbing all outputs to stdout as this is what's tested. To view all stdout for debugging, run the tests with:

make test_verbose

Linting and formatting

The codebase is linted with eslint and formatted with prettier. Configure your editor accordingly to avoid linting / formatting errors.

Releasing

ROOT=`pwd` npm version patch|minor|major

Current Tags

  • 0.2.0                                ...           latest (2 months ago)

2 Versions

  • 0.2.0                                ...           2 months ago
  • 0.1.0                                ...           2 months ago
Maintainers (1)
Downloads
Today 0
This Week 2
This Month 2
Last Day 2
Last Week 0
Last Month 1
Dependencies (10)
Dependents (0)
None

Copyright 2014 - 2017 © taobao.org |