Invoking

pgmetrics is designed to be invoked just like the psql and other commands that are bundled with PostgreSQL. Most command-line flags (like -h, -p, -U etc) as well as environment variables (like $PGHOST, $PGDATABASE etc) will work as expected.

pgmetrics is invoked against one or more databases. If no database is specified, it will assume the database name to be the currently logged in user’s username. Here is the --help output of pgmetrics:

$ pgmetrics --help
pgmetrics collects PostgreSQL information and metrics.

Usage:
  pgmetrics [OPTION]... [DBNAME]

General options:
  -t, --timeout=SECS           individual query timeout in seconds (default: 5)
  -S, --no-sizes               don't collect tablespace and relation sizes
  -i, --input=FILE             don't connect to db, instead read and display
                                   this previously saved JSON file
  -V, --version                output version information, then exit
  -?, --help[=options]         show this help, then exit
      --help=variables         list environment variables, then exit

Collection options:
  -c, --schema=REGEXP          collect only from schema(s) matching POSIX regexp
  -C, --exclude-schema=REGEXP  do NOT collect from schema(s) matching POSIX regexp
  -a, --table=REGEXP           collect only from table(s) matching POSIX regexp
  -A, --exclude-table=REGEXP   do NOT collect from table(s) matching POSIX regexp
      --omit=WHAT              do NOT collect the items specified as a comma-separated
                                   list of: "tables", "indexes", "sequences",
                                   "functions", "extensions", "triggers", "statements"
      --sql-length=LIMIT       collect only first LIMIT characters of all SQL
                                   queries (default: 500)
      --statements-limit=LIMIT collect only utmost LIMIT number of row from
                                   pg_stat_statements (default: 100)

Output options:
  -f, --format=FORMAT          output format; "human", or "json" (default: "human")
  -l, --toolong=SECS           for human output, transactions running longer than
                                   this are considered too long (default: 60)
  -o, --output=FILE            write output to the specified file
      --no-pager               do not invoke the pager for tty output

Connection options:
  -h, --host=HOSTNAME          database server host or socket directory
                                   (default: "/var/run/postgresql")
  -p, --port=PORT              database server port (default: 5432)
  -U, --username=USERNAME      database user name (default: "mdevan")
      --no-password            never prompt for password

For more information, visit <https://pgmetrics.io>.

The environment variables understody by pgmetrics is printed by the --help=variables option:

$ pgmetrics --help=variables
Environment variables:
Usage:
  NAME=VALUE [NAME=VALUE] pgmetrics ...

  PAGER              name of external pager program
  PGAPPNAME          the application_name connection parameter
  PGDATABASE         the dbname connection parameter
  PGHOST             the host connection parameter
  PGPORT             the port connection parameter
  PGUSER             the user connection parameter
  PGPASSWORD         connection password (not recommended)
  PGPASSFILE         path to the pgpass password file
  PGSSLMODE          "disable", "require", "verify-ca", "verify-full"
  PGSSLCERT          path to client SSL certificate
  PGSSLKEY           path to secret key for client SSL certificate
  PGSSLROOTCERT      path to SSL root CA
  PGCONNECT_TIMEOUT  connection timeout in seconds

Also, the following libpq-related environment variarables are not
required/used by pgmetrics and are IGNORED:

  PGHOSTADDR, PGSERVICE,     PGSERVICEFILE, PGREALM,  PGREQUIRESSL,
  PGSSLCRL,   PGREQUIREPEER, PGKRBSRVNAME,  PGGSSLIB, PGSYSCONFDIR,
  PGLOCALEDIR

Avoiding Passwords and Requiring SSL

pgmetrics by default assumes that: (1) you’re going to be inputting a password, and (2) you want to make a non-SSL connection. These assumptions are different from psql, which will retry the connection with/without password and with/without SSL.

If you’re sure you don’t require a password, or want to use the .pgpass file for supplying the password, use --no-password to avoid the password prompt:

pgmetrics --no-password

Using this flag is equivalent to hitting enter at the Password: prompt without typing anything. Empty passwords are not supported, by both PostgreSQL and pgmetrics.

If you’re sure you want to attempt only SSL connections, use:

PGSSLMODE=require pgmetrics --help

You can also use “verify-ca” or “verify-full” for the PGSSLMODE environment variable, see PostgreSQL docs for more info.

Cluster-level and Database-level Information

pgmetrics will collect information at the PostgreSQL cluster level (like checkpoint, bgwriter, WAL archiving, replication etc) after successfully connecting to the first database specified or implied. It will then collect database-level information and stats (like tables, indexes etc) on the same connection.

If more databases are specified, each will be connected to in turn, and database-level information and stats will be collected from each.

The cluster-level information includes some info about each database (like name, size etc.). This will be present in the output regardless of the databases you connect to. Information about tables, indexes, installed extensions, disabled triggers etc., for a specific database, will be present only if you connected to that database.

Privileges

pgmetrics will be able to extract maximum amount of information if it is able to run all it’s queries successfully. Typically, this will be so only if the user is a superuser. While pgmetrics is designed not to fail and extract whatever it can as the privileges permit, there are standard ways to allow a regular user to select/invoke certain tables/views/functions without needing to be a superuser. Some of these techniques are:

  • row-level security policies (docs, v9.5+)
  • explicit GRANTs
  • pg_monitor role (docs, v10+)
  • SECURITY DEFINER functions (docs)

[TODO: give examples]

[TODO: list all objects pgmetrics tries to access – for now, see collect.go]

Restricting What to Collect

Omitting Objects

By default, pgmetrics collects information about the following objects from each database:

  • tables
  • indexes
  • sequences
  • tracked user functions
  • installed extensions
  • disabled triggers
  • statements from pg_stat_statements

You can instruct pgmetrics not to collect objects of any of these categories using the --omit option. For example, to skip the collection of indexes and sequences, use:

$ pgmetrics --omit=indexes,sequences -h myserver mydb1 mydb2

Note that if you skip the collection of tables, then indexes and disabled triggers are skipped too.

Subsets of Tables and Schema

You can also specify a subset of tables and/or schema for the collection, using the -c/--schema, -C/--exclude-schema, -a/--table and -A/--exclude-table options. These options accept POSIX regular expressions (same as what PostgreSQL functions use) as values. Here are a few examples:

To collect only tables that contain “report_2016” or “report_2017” in their name:

$ pgmetrics -a 'report_201(6|7)' -h myserver mydb

To collect only tables that contain “report” or “audit” in their name:

$ pgmetrics -a '(report|audit)' -h myserver mydb

To collect all tables but the ones that contain “delta” in their name:

$ pgmetrics -A delta -h myserver mydb

To collect everything in the schemas “dept42” and “dept66” alone:

$ pgmetrics -c 'dept(42|66)' -h myserver mydb

To collect tables named “report_2016” or “report_2017” in schemas “dept42”:

$ pgmetrics -c dept42 -a 'report_201(6|7)' -h myserver mydb
Omit Sizes

The -S/--no-sizes option can be used to prevent pgmetrics from calling the pg_{tablespace,database,relation,table}_size() functions for collecing the size of tablespaces, databases, tables and indexes.

Local and Remote Invocation

When pgmetrics is invoked locally – that is, it is run from the same machine where the PostgreSQL server is running – it will collect some additional information:

  • CPU core count, model
  • Load average
  • Disk space and inode used by each tablespace location
  • Memory and swap usage

These are not collected when pgmetrics has to connect to a remote PosgreSQL server.

Output Options

pgmetrics by default generates a human-readable text output and pipes this output to $PAGER. This can be changed:

  • -f json / --format=json will instead generate a JSON output
  • -o FILE / --output=FILE will send the output to FILE instead
  • --no-pager will not pipe the output to $PAGER. If an output file is specified, this option has no effect (output is never piped in that case).

For more information about using the JSON output for scripting, see here.

Other Options

  • -t / --timeout=SECS invidiual queries will timeout in 5 seconds by default. You can change this with this option.
  • -l / --toolong=SECS transactions that have been idling for more than 60 seconds are considered “idling too long” and are included in the text output. You can change this threshold using this option. Note that this affects only the text output.

Reading From a Saved JSON

Instead of connecting to a database, pgmetrics can read from a previously-saved JSON and display it again. To do this, use:

pgmetrics -i existing.json

When the -i / --input option is specified, the output options (-f, -o, --no-pager) are honored, but all the connection options are ignored and pgmetrics does not connect to any database.