# check PostgreSql server
## Introduction
**check_psqlserver** is a plugin execute different checks on a postgreSql server instance.
The kind of check is defined by a paameter `-m METHOD`.
## Requirements
The icinga user needs to connect to the database server.
## Syntax
`$ check_psqlserver [-i|-u|-m METHOD]`
```txt
./check_psqlserver
______________________________________________________________________
CHECK_PSQLSERVER :: v0.6
(c) Institute for Medical Education - University of Bern
Licence: GNU GPL 3
______________________________________________________________________
USAGE:
check_psqlserver [OPTIONS] -m METHOD
OPTIONS:
-h this help
-i install monitoring user (must be executed as root)
-u uninstall monitoring user (must be executed as root)
PARAMETERS:
-m method; valid methods are:
activity running processes and queries
conflicts Detected conflicts from pg_stat_database_conflicts
dbrows Count of database row actions
diskblock Count of diskblocks physically read or coming from cache
problems Problems and troublemakers
replication Replication status (table output only)
transactions Count of transactions over all databases
EXAMPLES:
check_psqlserver -i
check_psqlserver -m activity
```
## Installation
To give access to the database there is the param `-i`(for "install").
This command needs to be run as root.
```txt
./check_psqlserver -i
```
This creates a database user "icingamonitor" with a 64 byte random password.
To store the credentials a file file be created: `/etc/icingaclient/.psql.conf`. It is a shell script that will be sourced by check_psqlserver.
```shell
cat /etc/icingaclient/.psql.conf
#
# generated on Thu Jun 8 03:44:06 CEST 2023
#
export PGUSER=icingamonitor
export PGPASSWORD=87B9jUcRp38DgkLDL3uHzC3V8YjgX6KZMxIxqpWieTWWIBgNoFYt8yvK9Y8RmdL0
export PGHOST=localhost
# set default database because a user db won't be created
export PGDATABASE=postgres
```
## Checks
The checks are done on the server and summarize data from statistic tables for all databases.
see <https://www.postgresql.org/docs/current/monitoring-stats.html>
If you need to troubleshot and want to see which of your databases causes the trouble you can execute the statement which is ggiven in the hint.
### activity
Show running processes and queries
```txt
./check_psqlserver -m activity
OK: Pgsql activity :: Running total: 33 ... active: 2 idle: 25 fastpath: 0 other: 6
Hint: Query for details:
select * from pg_stat_activity.
|running-total=33;; running-active=2;; running-idle=25;; running-fastpath=0;; running-other=6;;
```
### conflicts
Detected conflicts from pg_stat_database_conflicts
```txt
./check_psqlserver -m conflicts
OK: Pgsql conflicts :: ... OK, nothing was found
confl_tablespace : 0 ... delta = 0 per min
confl_lock : 0 ... delta = 0 per min
confl_snapshot : 0 ... delta = 0 per min
confl_bufferpin : 0 ... delta = 0 per min
confl_deadlock : 0 ... delta = 0 per min
Hint: Query for details per database:
select * from pg_stat_database_conflicts.
|confltablespace=0;; confllock=0;; conflsnapshot=0;; conflbufferpin=0;; confldeadlock=0;;
```
### dbrows
Count of database row actions
```txt
./check_psqlserver -m dbrows
OK: Pgsql dbrows :: Count of database row actions
tup_returned : 744761392127 ... delta = 0 per sec
tup_fetched : 21597149760 ... delta = 0 per sec
tup_inserted : 42683720 ... delta = 0 per sec
tup_updated : 1282902 ... delta = 0 per sec
tup_deleted : 1878883 ... delta = 0 per sec
Hint: Query for details per database:
select * from pg_stat_database.
|tupreturned=0;; tupfetched=0;; tupinserted=0;; tupupdated=0;; tupdeleted=0;;
```
### diskblock
Count of diskblocks physically read or coming from cache
```txt
./check_psqlserver -m diskblock
OK: Pgsql diskblock :: Count of diskblocks physically read or coming from cache (from pg_stat_database)
blks_read : 20391240674 ... delta = 439 per sec
blks_hit : 103015908887 ... delta = 6549 per sec
Hint: Query for details per database:
select * from pg_stat_database.
|blksread=439;; blkshit=6549;;
```
### problems
Problems and troublemakers
```txt
./check_psqlserver -m problems
OK: Pgsql problems :: Problems and troublemakers (from pg_stat_database) ... OK, nothing was found
conflicts : 0 ... delta = 0 per min
deadlocks : 0 ... delta = 0 per min
checksumfailures : 0 ... delta = 0 per min
temp_files : 5617739 ... delta = 0 per min
temp_bytes : 2896790521548 ... delta = 0 per min
Hint: Query for details per database:
select * from pg_stat_database.
|conflicts=0;; deadlocks=0;; checksumfailures=0;; tempfiles=0;; tempbytes=0;;
```
### replication
Replication status.
It shows the defined replication and their status.
It switches to state warning if one of the replications is not "streaming".
Aditionally it fetches the maximum lag of write, flush and replay of all replications.
The state switches to warning if it is larger 1 sec (just experimental).
```txt
./check_psqlserver -m replication
OK: Pgsql replication :: status
OK: all replications have the state 'streaming'.
OK: maximum lag is 0.2077 sec (below 1 sec).
application_name : client_addr : state : write_lag : flush_lag : replay_lag : max_lag : sync_state
------------------+----------------+-----------+-----------------+-----------------+---------------+---------------+------------
psqlbackup : 192.168.10.21 : streaming : 00:00:00.000673 : 00:00:00.003771 : 00:00:00.2077 : 00:00:00.2077 : async
(1 row)
Hint: Query for details per replication:
select * from pg_stat_replication.
|total=1;; state-streaming=1;; state-other=0;; max-lag=0.2077;;
```
### transactions
Count of transactions over all databases
```txt
./check_psqlserver -m transactions
OK: Pgsql transactions :: Count of transactions over all databases
commit : 380856524 ... delta = 0 per sec
rollback : 13173 ... delta = 0 per sec
Hint: Query for details per database:
select * from pg_stat_database.
|commit=0;; rollback=0;;
```