#!/bin/bash
################################################################################
# Copyright (c) 2016-2019 VMware, Inc. All rights reserved.
################################################################################
# Wrapper script aimed at querying periodically PostgreSQL via a cron job.
# This script should be launched as root.

# Load environment variables needed for script
source /etc/profile.d/vmware-vpostgres-config.sh
source /etc/profile.d/VMware-visl-integration.sh

if [ -z $VMWARE_POSTGRES_BASE ]; then
   echo "VMWARE_POSTGRES_BASE is not set."
   echo "Check your installation."
   exit 1
fi
SANITY_FILE=$VMWARE_POSTGRES_BASE/scripts/vpostgres_sanity_checks
if [ -f $SANITY_FILE ]; then
   source $SANITY_FILE
else
   echo "Sanity check file for environment variables of VMware Postgres"
   echo "is not available. Check your installation."
   exit 1
fi

if [ -z $VMWARE_CIS_HOME ]; then
   echo "VMWARE_CIS_HOME is not set."
   echo "Check your installation."
   exit 1
fi

# Show utility help
show_help()
{
   ERROR_NUM=$1
   echo "Usage: `basename $0`"
   echo "Example: `basename $0`"
   exit $ERROR_NUM
}

EXPECTED_ARGS=0
if [ $# -ne $EXPECTED_ARGS ]; then
   # Leave with an error code
   show_help 1
fi

LOGFILE=${VMWARE_POSTGRES_LOG}/postgres_cron.log

# Issue a set of queries to get a status of activity on the PostgreSQL
# instance running. Note that only read-only queries should be used
# to not interact with the system running as this script is aiming
# at filling only a monitoring role.
echo "========== Start of Postgres status query $(date -u +"%Y-%m-%dT%H:%M:%SZ") ==========" >> "$LOGFILE"

# Query periodically pg_stat_activity about queries running fore more than
# TX_MAX_MIN minutes, or transactions idle for more than TX_MAX_MIN minutes on
# database VCDB.
TX_MAX_MIN=0.1
echo -e "Querying status of queries and transactions longer than ${TX_MAX_MIN} minutes" >> "$LOGFILE"
PGOPTIONS="-c default_transaction_read_only=on" \
   ${VMWARE_POSTGRES_BIN}/psql --no-psqlrc -U postgres VCDB <<EOF >> $LOGFILE
SELECT pid, usename, datname, state, state_change,
       (statement_timestamp() - state_change) AS state_age,
       (statement_timestamp() - backend_start) AS backend_age,
       (statement_timestamp() - xact_start) AS xact_age,
       (statement_timestamp() - query_start) AS query_age,
       backend_xid, backend_xmin, query
  FROM pg_stat_activity
  WHERE state_change < (statement_timestamp() - interval '${TX_MAX_MIN} minutes')
        AND backend_type = 'client backend'
        AND state IN('idle in transaction', 'idle in transaction (aborted)', 'active');
EOF
echo -e "========== End of PostgreSQL status query ==========" >> "$LOGFILE"

# Ensure proper permissions on the log file
chmod 600 ${LOGFILE}
chown ${VMWARE_POSTGRES_OS_ADMIN}:${VMWARE_POSTGRES_OS_GROUP} \
            ${LOGFILE}
exit 0
