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 collects PostgreSQL information and metrics.

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

General options:
  -t, --timeout=SECS           individual query timeout in seconds (default: 5)
      --lock-timeout=MILLIS    lock timeout in milliseconds (default: 50)
  -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:
  -S, --no-sizes               don't collect tablespace and relation sizes
  -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", "log", "citus", "indexdefs",
                                   "bloat"
      --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)
      --only-listed            collect info only from the databases listed as
                                   command-line args (use with Heroku)
      --all-dbs                collect info from all user databases
      --log-file               location of PostgreSQL log file
      --log-dir                read all the PostgreSQL log files in this directory
      --log-span=MINS          examine the last MINS minutes of logs (default: 5)
      --aws-rds-dbid           AWS RDS/Aurora database instance identifier
      --az-resource            Azure resource ID
      --pgpool                 collect only Pgpool metrics

Output options:
  -f, --format=FORMAT          output format; "human", "json" or "csv" (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: "alice")
  -w, --no-password            never prompt for password
      --role=ROLE              do SET ROLE before collection

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

The following AWS-related environment variables are understood. For
more details about these refer to the AWS documentation.

  AWS_ACCESS_KEY_ID,   AWS_SECRET_ACCESS_KEY, AWS_REGION,
  AWS_ACCESS_KEY,      AWS_SECRET_KEY,        AWS_SESSION_TOKEN,
  AWS_DEFAULT_REGION,  AWS_PROFILE,           AWS_DEFAULT_PROFILE,
  AWS_SDK_LOAD_CONFIG, AWS_SHARED_CREDENTIALS_FILE,
  AWS_CONFIG_FILE,     AWS_CA_BUNDLE

The following Azure-related environment variables are understood. For
more details about these refer to the Azure documentation.

  AZURE_CLIENT_ID,   AZURE_TENANT_ID,   AZURE_CLIENT_SECRET,
  AZURE_USERNAME,    AZURE_PASSWORD,    AZURE_CLIENT_CERTIFICATE_PATH

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

(You can also use the short flag “-w”, like so: pgmetrics -w.)

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.

Setting a Role

You can specify the --role=ROLE option to tell pgmetrics to first do a “SET ROLE” before running any SQL statements to collect data. This is useful if you want to connect as one user but run the SQL commands as another.

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.

The following two options can be used modify this default behavior:

Only Listed

By default pgmetrics collects basic, minimal information about all available databases, not just the ones listed on the command-line. In most cases this is not a problem, but if you have a lot of databases (like hundreds of databases) this can cause pgmetrics to take a long time to finish. For example, a Heroku Postgres database that you connect to might be one among hundreds or more running on a single server.

The --only-listed option was added to restrict the metrics collection to only those databases listed on the pgmetrics command line. Information and metrics about other databases will neither be queried nor displayed.

All Databases

If you want to collect in-depth information of all databases in a cluster, you can avoid listing out the names of all the databases and use the --all-dbs option instead. This is conceptually equivalent to:

# instead of querying all database names as another step..
DBNAMES=$(psql -Xqtc 'select datname from pg_database where not datistemplate')
pgmetrics {other-args} $DBNAMES

# ..you can use the --all-dbs option:
pgmetrics {other-args} --all-dbs

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+)
  • pg_read_all_stats role (docs, v10+)
  • SECURITY DEFINER functions (docs)

The recommended way is to run pgmetrics as a user or a role that belongs to the pg_read_all_stats and pg_monitor roles:

GRANT pg_read_all_stats, pg_monitor TO the_pgmetrics_user;

Here are the permissions required for pgmetrics to successfully collect all available information:

SELECT permission for the following tables/views:

  • pg_am
  • pg_attribute
  • pg_auth_members
  • pg_available_extensions
  • pg_catalog
  • pg_class
  • pg_database
  • pg_dist_node (citus)
  • pg_dist_partition (citus)
  • pg_index
  • pg_inherits
  • pg_locks
  • pg_namespace
  • pg_notification_queue_usage
  • pg_proc
  • pg_publication
  • pg_publication_tables
  • pg_replication_slots
  • pg_roles
  • pg_settings
  • pg_stat_activity
  • pg_stat_archiver
  • pg_stat_bgwriter
  • pg_stat_database
  • pg_statio_user_sequences
  • pg_statio_user_tables
  • pg_stat_progress_analyze
  • pg_stat_progress_basebackup
  • pg_stat_progress_cluster
  • pg_stat_progress_copy
  • pg_stat_progress_create_index
  • pg_stat_progress_vacuum
  • pg_stat_replication
  • pg_stats
  • pg_stat_statements
  • pg_stat_subscription
  • pg_stat_user_functions
  • pg_stat_user_indexes
  • pg_stat_user_tables
  • pg_stat_wal
  • pg_stat_wal_receiver
  • pg_subscription
  • pg_subscription_rel
  • pg_tablespace
  • pg_trigger

EXECUTE permission for the following functions:

  • pg_blocking_pids
  • pg_control_checkpoint
  • pg_control_system
  • pg_current_logfile
  • pg_current_wal_flush_lsn
  • pg_current_wal_insert_lsn
  • pg_current_wal_lsn
  • pg_current_xlog_flush_location
  • pg_current_xlog_insert_location
  • pg_current_xlog_location
  • pg_database_size
  • pg_get_expr
  • pg_get_indexdef
  • pg_get_userbyid
  • pg_is_in_recovery
  • pg_is_wal_replay_paused
  • pg_is_xlog_replay_paused
  • pg_last_committed_xact
  • pg_last_wal_receive_lsn
  • pg_last_wal_replay_lsn
  • pg_last_xact_replay_timestamp
  • pg_last_xlog_receive_location
  • pg_last_xlog_replay_location
  • pg_ls_archive_statusdir
  • pg_ls_dir
  • pg_ls_waldir
  • pg_notification_queue_usage
  • pg_postmaster_start_time
  • pg_stat_get_blocks_fetched
  • pg_stat_get_blocks_hit
  • pg_table_size
  • pg_tablespace_location
  • pg_tablespace_size
  • pg_total_relation_size

Restricting What to Collect

There are options to restrict the amount and scope of information and metrics collected.

Omitting Information

You can instruct pgmetrics not to collect information about any of the following categories using the --omit option:

  • tables
  • indexes
  • sequences
  • tracked user functions
  • installed extensions
  • disabled triggers
  • statements from pg_stat_statements
  • log file processing
  • citus-related information
  • index definitions
  • bloat estimation

For example, to skip the collection of indexes and sequences, use:

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

The full list of items that can be skipped can be seen in the pgmetrics --help output. Here is the relevant snippet:

      --omit=WHAT              do NOT collect the items specified as a comma-separated
                                   list of: "tables", "indexes", "sequences",
                                   "functions", "extensions", "triggers",
                                   "statements", "log", "citus", "indexdefs",
                                   "bloat"

Note that if you skip the collection of tables, then indexes, index definitions 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
  • -f csv / --format=csv will instead generate a CSV 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 value 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.
  • --lock-timeout=MILLIS by default pgmetrics sets a lock_timeout value of 50 milliseconds in the connection string so as to not acquire any long-running locks that can interfere with application SQL queries. The lock timeout value can be changed with this option.

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.