/pgsentinel

Primary LanguageCGNU Affero General Public License v3.0AGPL-3.0

pgsentinel – sampling active session history

Introduction

PostgreSQL provides session activity. However, in order to gather activity
behavior user have to sample the pg_stat_activity view multiple times. pgsentinel is an extension to record active session history and also link the activity with query statistics (pg_stat_statements).

The module must be loaded by adding pgsentinel to shared_preload_libraries in postgresql.conf, means that a server restart is needed to add or remove the module.

When pgsentinel is enabled, it collects the history of session activty:

  • It's implemented as in-memory ring buffer where samples are written with given (configurable) period. Therefore, user can see some number of recent samples depending on history size (configurable).

In combination with pg_stat_statements this extension can also link the session activity with query statistics.

pgsentinel launches special background worker for gathering the sessions activity.

Availability

pgsentinel is implemented as an extension and not available in default PostgreSQL installation. It is available from github under the same license as GNU AFFERO GENERAL PUBLIC LICENSE and supports PostgreSQL 10+.

Installation

pgsentinel is a PostgreSQL extension which requires PostgreSQL 10 or higher. Before build and install you should ensure the following:

  • PostgreSQL version is 10 or higher.
  • You have development package of PostgreSQL installed or you built PostgreSQL from source.
  • Your PATH variable is configured so that pg_config command available, or set PG_CONFIG variable.

Typical installation procedure may look like this:

As pgsentinel uses the pg_stat_statements extension (officially bundled with PostgreSQL) for tracking which queries get executed in your database, add the following entries to your postgres.conf:

$ shared_preload_libraries = 'pg_stat_statements,pgsentinel'
$ # Icncrease the max size of the query strings Postgres records
$ track_activity_query_size = 2048
$ # Track statements generated by stored procedures as well
$ pg_stat_statements.track = all

restart the postgresql daemon and create the extension:

$ git clone https://github.com/pgsentinel/pgsentinel.git
$ cd pgsentinel
$ make
$ sudo make install
$ psql DB -c "CREATE EXTENSION pgsentinel;"

Usage

pgsentinel reports the active session history activity through the pg_active_session_history view:

Column Type Collation Nullable Default
ash_time timestamp with time zone
datid oid
datname text
pid integer
usesysid oid
usename text
application_name text
client_addr text
client_hostname text
client_port integer
backend_start timestamp with time zone
xact_start timestamp with time zone
query_start timestamp with time zone
state_change timestamp with time zone
wait_event_type text
wait_event text
state text
backend_xid xid
backend_xmin xid
top_level_query text
query text
queryid bigint
backend_type text

You could see it as samplings of pg_stat_activity providing more information:

  • ash_time: the sampling time
  • top_level_query: the top level statement (in case PL/pgSQL is used)
  • query: the statement being executed (not normalised, as it is in pg_stat_statements, means you see the values)
  • queryid: the queryid of the statement which links to pg_stat_statements

The worker is controlled by the following GUCs:.

Parameter name Data type Description Default value Min value
pgsentinel_ash.sampling_period int4 Period for history sampling in seconds 1 1
pgsentinel_ash.max_entries int4 Size of history in-memory ring buffer 1000 1000
pgsentinel.db_name char database the worker should connect to postgres

Contribution

Please, notice, that pgsentinel is still in beta testing so may contain some bugs. Don't hesitate to raise issues at github with your bug report.

If you're lacking of some functionality in pgsentinel
then you're welcome to make pull requests.

Authors