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
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.
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.
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:
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.
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
pgmetrics v1.17 and later supports specifying the connection parameters as a
libpq-style key-value connection string. For more information about connection strings,
see the PostgreSQL docs.
When a connection string is supplied, the parameters therein will be used instead
of any values supplied through other options (--host, --port etc).
pgmetrics "host=myserver port=6432 user=alice password=s3cr3t dbname=mydb"
Note that when using a connection string, only one database name may be specified,
using the dbname parameter inside the connection string.
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:
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:
EXECUTE permission for the following functions:
There are options to restrict the amount and scope of information and metrics collected.
You can instruct pgmetrics not to collect information about any of the
following categories using the --omit option:
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.
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
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.
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:
These are not collected when pgmetrics has to connect to a remote PosgreSQL
server.
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.
-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.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.