bin/check-postgres-connections.rb
#!/usr/bin/env ruby
# frozen_string_literal: false
#
# check-postgres-connections
#
# DESCRIPTION:
# This plugin checks the number of connections to a postgresql database and
# alerts when the number or percent crosses a threshold. Defaults to checking
# number of connections unless the --percentage flag is used in which case the
# percentage of max connections is checked.
#
# OUTPUT:
# plain-text
#
# PLATFORMS:
# Linux
#
# DEPENDENCIES:
# gem: pg
# gem: sensu-plugin
#
# USAGE:
# # warn when connections hit 250, critical when 400
# check-postgres-connections.rb -u db_user -p db_pass -h db_host -d db -w 250 -c 400
# # warn when connections hit 80%, critical when 95%
# check-postgres-connections.rb -u db_user -p db_pass -h db_host -d db -w 80 -c 95 --percentage
#
# NOTES:
#
# LICENSE:
# Copyright 2016, Eric Heydrick <eheydrick@gmail.com>
# Released under the same terms as Sensu (the MIT license); see LICENSE
# for details.
#
require 'sensu-plugins-postgres/pgpass'
require 'sensu-plugin/check/cli'
require 'pg'
class CheckPostgresConnections < Sensu::Plugin::Check::CLI
option :pgpass,
description: 'Pgpass file',
short: '-f FILE',
long: '--pgpass',
default: ENV['PGPASSFILE'] || "#{ENV['HOME']}/.pgpass"
option :user,
description: 'Postgres User',
short: '-u USER',
long: '--user USER'
option :password,
description: 'Postgres Password',
short: '-p PASS',
long: '--password PASS'
option :hostname,
description: 'Hostname to login to',
short: '-h HOST',
long: '--hostname HOST'
option :port,
description: 'Database port',
short: '-P PORT',
long: '--port PORT'
option :database,
description: 'Database name',
short: '-d DB',
long: '--db DB'
option :warning,
description: 'Warning threshold number or % of connections. (default: 200 connections)',
short: '-w WARNING',
long: '--warning WARNING',
default: 200,
proc: proc(&:to_i)
option :critical,
description: 'Critical threshold number or % of connections. (default: 250 connections)',
short: '-c CRITICAL',
long: '--critical CRITICAL',
default: 250,
proc: proc(&:to_i)
option :use_percentage,
description: 'Use percentage of max connections used instead of the absolute number of connections',
short: '-a',
long: '--percentage',
boolean: true,
default: false
option :timeout,
description: 'Connection timeout (seconds)',
short: '-T TIMEOUT',
long: '--timeout TIMEOUT',
default: nil
include Pgpass
def run
begin
pgpass
con = PG.connect(host: config[:hostname],
dbname: config[:database],
user: config[:user],
password: config[:password],
port: config[:port],
connect_timeout: config[:timeout])
max_conns = con.exec('SHOW max_connections').getvalue(0, 0).to_i
superuser_conns = con.exec('SHOW superuser_reserved_connections').getvalue(0, 0).to_i
available_conns = max_conns - superuser_conns
current_conns = con.exec('SELECT count(*) from pg_stat_activity').getvalue(0, 0).to_i
rescue PG::Error => e
unknown "Unable to query PostgreSQL: #{e.message}"
end
percent = (current_conns / max_conns.to_f * 100).to_i
if config[:use_percentage]
message = "PostgreSQL connections at #{percent}%, #{current_conns} out of #{available_conns} connections"
if percent >= config[:critical]
critical message
elsif percent >= config[:warning]
warning message
else
ok "PostgreSQL connections under threshold: #{percent}%, #{current_conns} out of #{available_conns} connections"
end
else
message = "PostgreSQL connections at #{current_conns} out of #{available_conns} connections"
if current_conns >= config[:critical]
critical message
elsif current_conns >= config[:warning]
warning message
else
ok "PostgreSQL connections under threshold: #{current_conns} out of #{available_conns} connections"
end
end
end
end