Gets running postgresql vacuums and phases ready for influxdb

pgsqlvacuumstat.sh

#!/bin/bash

PGSQL_HOST="localhost"
PGSQL_PORT="5432"
PGSQL_DATABASE="postgres"
PGSQL_USERNAME="postgres"
export PGPASSWORD="passwd"
TMP_FILE="/tmp/pgsqlchk.out"
ERR_FILE="/tmp/pgsqlchk.err"
HOST=$(hostname)

list=`/usr/bin/psql -t -c "select pid,s.relname, v.phase from pg_stat_progress_vacuum v left join pg_catalog.pg_class s on v.relid = s.oid" 2> /dev/null`

if [ -z "$list" ]
then
    exit 1
else
    while IFS= read -r VALUE; do
        PHASE=`echo "$VALUE" | awk -F"|" '{print $3}' | sed 's/^[ \t]*//;s/[ \t]*$//'`
        TABLE=`echo "$VALUE" | awk -F"|" '{print $2}' | sed 's/^[ \t]*//;s/[ \t]*$//'`
        PSPID=`echo "$VALUE" | awk -F"|" '{print $1}' | sed 's/^[ \t]*//;s/[ \t]*$//'`
        if [ "$PHASE" = "initializing" ]
        then
            PHASE=1
        elif [ "$PHASE" = "scanning heap" ]
        then
            PHASE=2
        elif [ "$PHASE" = "vacuuming indexes" ]
        then
            PHASE=3
        elif [ "$PHASE" = "vacuuming heap" ]
        then
            PHASE=4
        elif [ "$PHASE" = "cleaning up indexes" ]
        then
            PHASE=5
        elif [ "$PHASE" = "truncating heap" ]
        then
            PHASE=6
        elif [ "$PHASE" = "performing final cleanup" ]
        then
            PHASE=7
        fi

        influxline="postgresql,host=${HOST},table=${TABLE},pid=${PSPID} vacuum_phase_id=${PHASE}"
        echo $influxline
    done <<< "$list"
fi