pgstef's blog

SELECT * FROM pgstef

Home About me Talks View on GitHub

check_pgactivity (https://github.com/OPMDG/check_pgactivity) is designed to monitor PostgreSQL clusters from Nagios. It offers many options to measure and monitor useful performance metrics.

Imagine you have a very large database and pg_dump produce abnormal query time alerts. The upcoming release of check_pgactivity offers a way to filter out those alerts !


In the 2.4 version, it will be possible to filter out pg_dump from the oldest_idlexact service. Indeed, above PostgreSQL 9.2, the service supports --exclude to filter out connections.

The longest_query service will also, above PostgreSQL 9.0, support --exclude to filter out application names.

Let’s see an example.

Launch a pg_dump of a “not-empty” database :

$ pgbench -i -s 300 blog
$ pg_dump -d blog -f tmp.dump --exclude-table=pgbench_tellers

We can see the pg_dump progress with pg_stat_activity :

postgres=# SELECT application_name, query, (current_timestamp-state_change) AS elapsed, state, wait_event from pg_stat_activity where application_name = 'pg_dump';
-[ RECORD 1 ]----+---------------------------------------------------------------------
application_name | pg_dump
query            | COPY public.pgbench_accounts (aid, bid, abalance, filler) TO stdout;
elapsed          | 00:01:26.435591
state            | active
wait_event       | ClientWrite

The longest_query service will raise an alert :

$ check_pgactivity --service longest_query -w 1m -c 5m
POSTGRES_LONGEST_QUERY WARNING: blog: 1m31s | 
'blog max'=91s;60;300 'blog avg'=91s;60;300 'blog #queries'=1 
'postgres max'=0s;60;300 'postgres avg'=0s;60;300 'postgres #queries'=1 
'template1 max'=NaNs;60;300 'template1 avg'=NaNs;60;300 'template1 #queries'=0

We can remove that alert by adding the filter :

$ check_pgactivity --service longest_query -w 1m -c 5m --exclude ^pg_dump
POSTGRES_LONGEST_QUERY OK: 1 running querie(s) | 
'postgres max'=0s;60;300 'postgres avg'=0s;60;300 'postgres #queries'=1 
'template1 max'=NaNs;60;300 'template1 avg'=NaNs;60;300 'template1 #queries'=0 
'blog max'=NaNs;60;300 'blog avg'=NaNs;60;300 'blog #queries'=0

After some time, pg_dump goes further :

postgres=# SELECT application_name, query, (current_timestamp-state_change) AS elapsed, state, wait_event from pg_stat_activity where application_name = 'pg_dump';
-[ RECORD 1 ]----+-----------------------------------------------------------------------------
application_name | pg_dump
query            | COPY public.pgbench_history (tid, bid, aid, delta, mtime, filler) TO stdout;
elapsed          | 00:02:38.581393
state            | idle in transaction
wait_event       | ClientRead

It’s now the oldest_idlexact service that raise an alert :

$ check_pgactivity  --service oldest_idlexact -w 1m -c 5m
POSTGRES_OLDEST_IDLEXACT WARNING: 1 idle transaction(s), oldest idle xact on blog: 2m47s | 
'blog max'=167s;60;300 'blog avg'=167s;60;300 'blog # idle xact'=1 
'postgres max'=NaNs;60;300 'postgres avg'=NaNs;60;300 'postgres # idle xact'=0 
'template0 max'=NaNs;60;300 'template0 avg'=NaNs;60;300 'template0 # idle xact'=0 
'template1 max'=NaNs;60;300 'template1 avg'=NaNs;60;300 'template1 # idle xact'=0

We can also remove that alert by adding the filter :

$ check_pgactivity  --service oldest_idlexact -w 1m -c 5m --exclude 'pg_dump'
POSTGRES_OLDEST_IDLEXACT OK: 0 idle transaction(s)

In addition to those features, the 2.4 release will also contain some bug fixes in sequences_exhausted and backends_status services.

The new release candidate should be available soon !