vesta/bin/v_upd_sys_rrd_pgsql

#!/bin/bash
# info: updating PostgreSQL rrd

#----------------------------------------------------------#
#                    Variable&Function                     #
#----------------------------------------------------------#

# Importing variables
source $VESTA/conf/vars.conf
source $V_FUNC/shared_func.sh
source $V_FUNC/db_func.sh


#----------------------------------------------------------#
#                       Action                             #
#----------------------------------------------------------#

# Checking directory
if [ ! -d "$V_RRD/db" ]; then
    mkdir $V_RRD/db
fi

# Parsing db hosts
conf="$V_DB/pgsql.conf"
fields='$HOST'
hosts=$(v_clear_list)
check_row=$(echo "$hosts" |wc -l)
if [ 0 -eq "$check_row" ]; then
    exit
fi

# Parsing excludes
for exclude in $(echo ${V_RRD_PGSQL_EXCLUDE//,/ }); do
    hosts=$(echo "$hosts" |grep -vw "$exclude" )
done

for host in $hosts; do
    # Checking database
    if [ ! -e "$V_RRD/db/pgsql_$host.rrd" ]; then
        # Adding database
        rrdtool create $V_RRD/db/pgsql_$host.rrd --step $V_RRD_STEP \
            DS:A:GAUGE:600:U:U \
            DS:T:COUNTER:600:U:U \
            RRA:AVERAGE:0.5:1:600 \
            RRA:AVERAGE:0.5:6:700 \
            RRA:AVERAGE:0.5:24:775 \
            RRA:AVERAGE:0.5:288:797 \
            RRA:MAX:0.5:1:600 \
            RRA:MAX:0.5:6:700 \
            RRA:MAX:0.5:24:775 \
            RRA:MAX:0.5:288:797
    fi

    # Defining host credentials
    host_str=$(grep "HOST='$host'" $conf)
    for key in $host_str; do
        eval ${key%%=*}=${key#*=}
    done

    export PGPASSWORD="$PASSWORD"
    sql="psql -h $HOST -U $USER -p $PORT -c"

    # Checking empty vars
    if [ -z $HOST ] || [ -z $USER ] || [ -z $PASSWORD ] || [ -z $TPL ]; then
        echo "Error: config is broken"
        log_event 'debug' "$E_PARSE_ERROR $V_EVENT"
        exit $E_PARSE_ERROR
    fi


    # Parsing data
    q='select sum(xact_commit + xact_rollback), sum(numbackends) from pg_stat_database;'
    status=$($sql plsql -d postgres -c "$q" 2>/dev/null); code="$?"
    if [ '0' -ne "$code" ]; then
        active=0
        slow=0
    else
        active=$(echo "$status"|head -n 3|tail -n 1|awk '{print $3}')
        trans=$(echo "$status"|head -n 3 |tail -n 1|awk '{print $1}')
    fi

    # Updating rrd
    export PGPASSWORD='pgsql'
    rrdtool update $V_RRD/db/pgsql_$host.rrd N:$active:$trans

    # Updating daily graph
    rrdtool graph  $V_RRD/db/pgsql_$host.png \
        --imgformat PNG \
        --height="120" \
        --width="440" \
        --start -1d \
        --end now \
        --title "PostgreSQL Usage on $host" \
        --vertical-label "Queries" \
        --x-grid MINUTE:30:HOUR:1:HOUR:4:0:%H:%M\
        -c "BACK#484439" \
        -c "SHADEA#484439" \
        -c "SHADEB#484439" \
        -c "FONT#DDDDDD" \
        -c "CANVAS#202020" \
        -c "GRID#666666" \
        -c "MGRID#AAAAAA" \
        -c "FRAME#202020" \
        -c "ARROW#FFFFFF" \
        DEF:a=$V_RRD/db/pgsql_$host.rrd:A:AVERAGE \
        DEF:t=$V_RRD/db/pgsql_$host.rrd:T:AVERAGE \
        COMMENT:'\r' \
        LINE1:a#fefda0:"Queries     "\
        GPRINT:a:'LAST: Current conn\:''%8.0lf'  \
        GPRINT:a:'MIN: Min\:''%8.0lf'  \
        GPRINT:a:'MAX: Max\:''%8.0lf\j'  \
        LINE2:t#f57900:"Transactions" \
        GPRINT:t:'LAST:Transactions\:''%8.0lf' \
        GPRINT:t:'MIN:Min\:''%8.0lf' \
        GPRINT:t:'MAX:Max\:''%8.0lf\j' > /dev/null

done

#----------------------------------------------------------#
#                       Vesta                              #
#----------------------------------------------------------#

# No Logging
#log_event 'system' "$V_EVENT"

exit $OK