agents/plugins/mk_postgres

   1 #!/bin/bash
   2 # +------------------------------------------------------------------+
   3 # |             ____ _               _        __  __ _  __           |
   4 # |            / ___| |__   ___  ___| | __   |  \/  | |/ /           |
   5 # |           | |   | '_ \ / _ \/ __| |/ /   | |\/| | ' /            |
   6 # |           | |___| | | |  __/ (__|   <    | |  | | . \            |
   7 # |            \____|_| |_|\___|\___|_|\_\___|_|  |_|_|\_\           |
   8 # |                                                                  |
   9 # | Copyright Mathias Kettner 2015             mk@mathias-kettner.de |
  10 # +------------------------------------------------------------------+
  11 #
  12 # This file is part of Check_MK.
  13 # The official homepage is at http://mathias-kettner.de/check_mk.
  14 #
  15 # check_mk is free software;  you can redistribute it and/or modify it
  16 # under the  terms of the  GNU General Public License  as published by
  17 # the Free Software Foundation in version 2.  check_mk is  distributed
  18 # in the hope that it will be useful, but WITHOUT ANY WARRANTY;  with-
  19 # out even the implied warranty of  MERCHANTABILITY  or  FITNESS FOR A
  20 # PARTICULAR PURPOSE. See the  GNU General Public License for more de-
  21 # tails. You should have  received  a copy of the  GNU  General Public
  22 # License along with GNU Make; see the file  COPYING.  If  not,  write
  23 # to the Free Software Foundation, Inc., 51 Franklin St,  Fifth Floor,
  24 # Boston, MA 02110-1301 USA.
  25
  26
  27 # TODO postgres_connections output format
  28
  29
  30 #   .--common funcs--------------------------------------------------------.
  31 #   |                                             __                       |
  32 #   |  ___ ___  _ __ ___  _ __ ___   ___  _ __   / _|_   _ _ __   ___ ___  |
  33 #   | / __/ _ \| '_ ` _ \| '_ ` _ \ / _ \| '_ \ | |_| | | | '_ \ / __/ __| |
  34 #   || (_| (_) | | | | | | | | | | | (_) | | | ||  _| |_| | | | | (__\__ \ |
  35 #   | \___\___/|_| |_| |_|_| |_| |_|\___/|_| |_||_|  \__,_|_| |_|\___|___/ |
  36 #   |                                                                      |
  37 #   '----------------------------------------------------------------------'
  38
  39
  40 function compare_version_greater_equal() {
  41     local GREATER_ONE
  42     GREATER_ONE=$(echo "$1 $2" | awk '{if ($1 >= $2) print $1; else print $2}')
  43     if [ "$GREATER_ONE" == "$1" ] ; then
  44         return 0
  45     else
  46         return 1
  47     fi
  48 }
  49
  50
  51 #.
  52 #   .--section funcs-------------------------------------------------------.
  53 #   |                   _   _                __                            |
  54 #   |     ___  ___  ___| |_(_) ___  _ __    / _|_   _ _ __   ___ ___       |
  55 #   |    / __|/ _ \/ __| __| |/ _ \| '_ \  | |_| | | | '_ \ / __/ __|      |
  56 #   |    \__ \  __/ (__| |_| | (_) | | | | |  _| |_| | | | | (__\__ \      |
  57 #   |    |___/\___|\___|\__|_|\___/|_| |_| |_|  \__,_|_| |_|\___|___/      |
  58 #   |                                                                      |
  59 #   '----------------------------------------------------------------------'
  60
  61
  62 function postgres_instances() {
  63     echo '<<<postgres_instances>>>'
  64     # If we have no instances we take db id (pqsql/postgres) because
  65     # ps output may be unreadable
  66     # In case of instances ps output shows them readable
  67     if [ ! -z "${1}" ]; then
  68         echo "[[[${1}]]]"
  69     fi
  70     pgrep -laf bin/postgres
  71 }
  72
  73
  74 function postgres_sessions() {
  75     # Postgres 9.2 uses 'query' instead of 'current_query'
  76     local OUTPUT
  77     OUTPUT="$(echo "\echo '<<<postgres_sessions>>>${INSTANCE_SECTION}'
  78                     SELECT (
  79                         SELECT column_name
  80                         FROM information_schema.columns
  81                         WHERE table_name='pg_stat_activity' AND column_name in ('query', 'current_query')
  82                     ) = '<IDLE>' as query, count(*)
  83                     FROM pg_stat_activity
  84                     GROUP BY (query = '<IDLE>');" |\
  85               su - "$DBUSER" -c "$export_PGPASSFILE $psql -X --variable ON_ERROR_STOP=1 -d $PGDATABASE ${EXTRA_ARGS} -A -t -F' '" 2>/dev/null)"
  86
  87     echo "$OUTPUT"
  88     # line with number of idle sessions is sometimes missing on Postgres 8.x. This can lead
  89     # to an altogether empty section and thus the check disappearing.
  90     echo "$OUTPUT" | grep -q '^t ' || echo "t 0"
  91 }
  92
  93
  94 function postgres_simple_queries() {
  95     # Querytime
  96     # Supports versions >= 8.3, > 9.1
  97     local QUERYTIME_QUERY
  98     if compare_version_greater_equal "$POSTGRES_VERSION" "9.2" ; then
  99         QUERYTIME_QUERY="SELECT datname, datid, usename, client_addr, state AS state, COALESCE(ROUND(EXTRACT(epoch FROM now()-query_start)),0) AS seconds,
 100         pid, regexp_replace(query, E'[\\n\\r\\u2028]+', ' ', 'g' ) AS current_query FROM pg_stat_activity WHERE (query_start IS NOT NULL AND (state NOT LIKE 'idle%' OR state IS NULL)) ORDER BY query_start, pid DESC;"
 101     else
 102         QUERYTIME_QUERY="SELECT datname, datid, usename, client_addr, '' AS state,    COALESCE(ROUND(EXTRACT(epoch FROM now()-query_start)),0) AS seconds,
 103         procpid as pid, regexp_replace(current_query, E'[\\n\\r\\u2028]+', ' ', 'g' ) AS current_query FROM pg_stat_activity WHERE (query_start IS NOT NULL AND current_query NOT LIKE '<IDLE>%') ORDER BY query_start, procpid DESC;"
 104     fi
 105
 106     # Number of current connections per database
 107     # We need to output the databases, too.
 108     # This query does not report databases without an active query
 109     local CONNECTIONS_QUERY
 110     if compare_version_greater_equal "$POSTGRES_VERSION" "9.2" ; then
 111         CONNECTIONS_QUERY="SELECT COUNT(datid) AS current,
 112           (SELECT setting AS mc FROM pg_settings WHERE name = 'max_connections') AS mc,
 113           d.datname
 114         FROM pg_database d
 115         LEFT JOIN pg_stat_activity s ON (s.datid = d.oid) WHERE state <> 'idle'
 116         GROUP BY 2,3
 117         ORDER BY datname;"
 118     else
 119         CONNECTIONS_QUERY="SELECT COUNT(datid) AS current,
 120           (SELECT setting AS mc FROM pg_settings WHERE name = 'max_connections') AS mc,
 121           d.datname
 122         FROM pg_database d
 123         LEFT JOIN pg_stat_activity s ON (s.datid = d.oid) WHERE current_query <> '<IDLE>'
 124         GROUP BY 2,3
 125         ORDER BY datname;"
 126     fi
 127
 128     echo "\pset footer off
 129           \echo '<<<postgres_stat_database:sep(59)>>>${INSTANCE_SECTION}'
 130           SELECT datid, datname, numbackends, xact_commit, xact_rollback, blks_read, blks_hit, tup_returned, tup_fetched, tup_inserted, tup_updated, tup_deleted, pg_database_size(datname) AS datsize FROM pg_stat_database;
 131
 132           \echo '<<<postgres_locks:sep(59)>>>${INSTANCE_SECTION}'
 133           \echo '[databases_start]'
 134           $ECHO_DATABASES
 135           \echo '[databases_end]'
 136           SELECT datname, granted, mode FROM pg_locks l RIGHT JOIN pg_database d ON (d.oid=l.database) WHERE d.datallowconn;
 137
 138           \echo '<<<postgres_query_duration:sep(59)>>>${INSTANCE_SECTION}'
 139           \echo '[databases_start]'
 140           $ECHO_DATABASES
 141           \echo '[databases_end]'
 142           $QUERYTIME_QUERY
 143
 144           \echo '<<<postgres_connections:sep(59)>>>${INSTANCE_SECTION}'
 145           \echo '[databases_start]'
 146           $ECHO_DATABASES
 147           \echo '[databases_end]'
 148           $CONNECTIONS_QUERY" \
 149         | su - "$DBUSER" -c "$export_PGPASSFILE $psql -X -d $PGDATABASE ${EXTRA_ARGS} -q -A -F';'"
 150 }
 151
 152
 153 function postgres_stats() {
 154     # Contains last vacuum time and analyze time
 155     local LASTVACUUM="SELECT current_database() AS datname, nspname AS sname, relname AS tname,
 156                           CASE WHEN v IS NULL THEN -1 ELSE round(extract(epoch FROM v)) END AS vtime,
 157                           CASE WHEN g IS NULL THEN -1 ELSE round(extract(epoch FROM v)) END AS atime
 158                       FROM (SELECT nspname, relname, GREATEST(pg_stat_get_last_vacuum_time(c.oid), pg_stat_get_last_autovacuum_time(c.oid)) AS v,
 159                                 GREATEST(pg_stat_get_last_analyze_time(c.oid), pg_stat_get_last_autoanalyze_time(c.oid)) AS g
 160                             FROM pg_class c, pg_namespace n
 161                             WHERE relkind = 'r' AND n.oid = c.relnamespace AND n.nspname <> 'information_schema'
 162                       ORDER BY 3) AS foo;"
 163
 164     local FIRST=
 165     local QUERY="\pset footer off
 166                  BEGIN;
 167                  SET statement_timeout=30000;
 168                  COMMIT;
 169
 170                  \echo '<<<postgres_stats:sep(59)>>>${INSTANCE_SECTION}'
 171                  \echo '[databases_start]'
 172                  $ECHO_DATABASES
 173                  \echo '[databases_end]'"
 174
 175     for db in $DATABASES ; do
 176         QUERY="$QUERY
 177                \c $db
 178                $LASTVACUUM
 179               "
 180         if [ -z $FIRST ] ; then
 181             FIRST=false
 182             QUERY="$QUERY
 183                    \pset tuples_only on
 184                   "
 185         fi
 186     done
 187     echo "$QUERY" | su - "$DBUSER" -c "$export_PGPASSFILE $psql -X ${EXTRA_ARGS} -q -A -F';'" | grep -v -e 'COMMIT$' -e 'SET$' -e 'BEGIN$'
 188 }
 189
 190
 191 function postgres_version() {
 192     # Postgres version an connection time
 193     echo -e "<<<postgres_version:sep(1)>>>${INSTANCE_SECTION}"
 194     (TIMEFORMAT='%3R'; time echo "SELECT version() AS v" |\
 195     su - "$DBUSER" -c "$export_PGPASSFILE $psql -X -d $PGDATABASE ${EXTRA_ARGS} -t -A -F';'; echo -e '<<<postgres_conn_time>>>${INSTANCE_SECTION}'") 2>&1
 196 }
 197
 198
 199 function postgres_bloat() {
 200     # Bloat index and tables
 201     # Supports versions <9.0, >=9.0
 202     # This huge query has been gratefully taken from Greg Sabino Mullane's check_postgres.pl
 203     local BLOAT_QUERY
 204     if compare_version_greater_equal "$POSTGRES_VERSION" "9.0" ; then
 205         BLOAT_QUERY="SELECT
 206           current_database() AS db, schemaname, tablename, reltuples::bigint AS tups, relpages::bigint AS pages, otta,
 207           ROUND(CASE WHEN otta=0 OR sml.relpages=0 OR sml.relpages=otta THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat,
 208           CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages,
 209           CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes,
 210           CASE WHEN relpages < otta THEN 0 ELSE (bs*(relpages-otta))::bigint END AS wastedsize,
 211           iname, ituples::bigint AS itups, ipages::bigint AS ipages, iotta,
 212           ROUND(CASE WHEN iotta=0 OR ipages=0 OR ipages=iotta THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat,
 213           CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wastedipages,
 214           CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes,
 215           CASE WHEN ipages < iotta THEN 0 ELSE (bs*(ipages-iotta))::bigint END AS wastedisize,
 216           CASE WHEN relpages < otta THEN
 217             CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta::bigint) END
 218             ELSE CASE WHEN ipages < iotta THEN bs*(relpages-otta::bigint)
 219               ELSE bs*(relpages-otta::bigint + ipages-iotta::bigint) END
 220           END AS totalwastedbytes
 221         FROM (
 222           SELECT
 223             nn.nspname AS schemaname,
 224             cc.relname AS tablename,
 225             COALESCE(cc.reltuples,0) AS reltuples,
 226             COALESCE(cc.relpages,0) AS relpages,
 227             COALESCE(bs,0) AS bs,
 228             COALESCE(CEIL((cc.reltuples*((datahdr+ma-
 229               (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)),0) AS otta,
 230             COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
 231             COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
 232           FROM
 233              pg_class cc
 234           JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <> 'information_schema'
 235           LEFT JOIN
 236           (
 237             SELECT
 238               ma,bs,foo.nspname,foo.relname,
 239               (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
 240               (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
 241             FROM (
 242               SELECT
 243                 ns.nspname, tbl.relname, hdr, ma, bs,
 244                 SUM((1-coalesce(null_frac,0))*coalesce(avg_width, 2048)) AS datawidth,
 245                 MAX(coalesce(null_frac,0)) AS maxfracsum,
 246                 hdr+(
 247                   SELECT 1+count(*)/8
 248                   FROM pg_stats s2
 249                   WHERE null_frac<>0 AND s2.schemaname = ns.nspname AND s2.tablename = tbl.relname
 250                 ) AS nullhdr
 251               FROM pg_attribute att
 252               JOIN pg_class tbl ON att.attrelid = tbl.oid
 253               JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
 254               LEFT JOIN pg_stats s ON s.schemaname=ns.nspname
 255               AND s.tablename = tbl.relname
 256               AND s.inherited=false
 257               AND s.attname=att.attname,
 258               (
 259                 SELECT
 260                   (SELECT current_setting('block_size')::numeric) AS bs,
 261                     CASE WHEN SUBSTRING(SPLIT_PART(v, ' ', 2) FROM '#\[0-9]+.[0-9]+#\%' for '#')
 262                       IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
 263                   CASE WHEN v ~ 'mingw32' OR v ~ '64-bit' THEN 8 ELSE 4 END AS ma
 264                 FROM (SELECT version() AS v) AS foo
 265               ) AS constants
 266               WHERE att.attnum > 0 AND tbl.relkind='r'
 267               GROUP BY 1,2,3,4,5
 268             ) AS foo
 269           ) AS rs
 270           ON cc.relname = rs.relname AND nn.nspname = rs.nspname
 271           LEFT JOIN pg_index i ON indrelid = cc.oid
 272           LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
 273         ) AS sml
 274          WHERE sml.relpages - otta > 0 OR ipages - iotta > 10 ORDER BY totalwastedbytes DESC LIMIT 10;"
 275     else
 276         BLOAT_QUERY="SELECT
 277           current_database() AS db, schemaname, tablename, reltuples::bigint AS tups, relpages::bigint AS pages, otta,
 278           ROUND(CASE WHEN otta=0 OR sml.relpages=0 OR sml.relpages=otta THEN 0.0 ELSE sml.relpages/otta::numeric END,1) AS tbloat,
 279           CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages,
 280           CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes,
 281           CASE WHEN relpages < otta THEN '0 bytes'::text ELSE (bs*(relpages-otta))::bigint || ' bytes' END AS wastedsize,
 282           iname, ituples::bigint AS itups, ipages::bigint AS ipages, iotta,
 283           ROUND(CASE WHEN iotta=0 OR ipages=0 OR ipages=iotta THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat,
 284           CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wastedipages,
 285           CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes,
 286           CASE WHEN ipages < iotta THEN '0 bytes' ELSE (bs*(ipages-iotta))::bigint || ' bytes' END AS wastedisize,
 287           CASE WHEN relpages < otta THEN
 288             CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta::bigint) END
 289             ELSE CASE WHEN ipages < iotta THEN bs*(relpages-otta::bigint)
 290               ELSE bs*(relpages-otta::bigint + ipages-iotta::bigint) END
 291           END AS totalwastedbytes
 292         FROM (
 293           SELECT
 294             nn.nspname AS schemaname,
 295             cc.relname AS tablename,
 296             COALESCE(cc.reltuples,0) AS reltuples,
 297             COALESCE(cc.relpages,0) AS relpages,
 298             COALESCE(bs,0) AS bs,
 299             COALESCE(CEIL((cc.reltuples*((datahdr+ma-
 300               (CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)),0) AS otta,
 301             COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
 302             COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
 303           FROM
 304              pg_class cc
 305           JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <> 'information_schema'
 306           LEFT JOIN
 307           (
 308             SELECT
 309               ma,bs,foo.nspname,foo.relname,
 310               (datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
 311               (maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
 312             FROM (
 313               SELECT
 314                 ns.nspname, tbl.relname, hdr, ma, bs,
 315                 SUM((1-coalesce(null_frac,0))*coalesce(avg_width, 2048)) AS datawidth,
 316                 MAX(coalesce(null_frac,0)) AS maxfracsum,
 317                 hdr+(
 318                   SELECT 1+count(*)/8
 319                   FROM pg_stats s2
 320                   WHERE null_frac<>0 AND s2.schemaname = ns.nspname AND s2.tablename = tbl.relname
 321                 ) AS nullhdr
 322               FROM pg_attribute att
 323               JOIN pg_class tbl ON att.attrelid = tbl.oid
 324               JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
 325               LEFT JOIN pg_stats s ON s.schemaname=ns.nspname
 326               AND s.tablename = tbl.relname
 327               AND s.attname=att.attname,
 328               (
 329                 SELECT
 330                   (SELECT current_setting('block_size')::numeric) AS bs,
 331                     CASE WHEN SUBSTRING(SPLIT_PART(v, ' ', 2) FROM '#\"[0-9]+.[0-9]+#\"%' for '#')
 332                       IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
 333                   CASE WHEN v ~ 'mingw32' OR v ~ '64-bit' THEN 8 ELSE 4 END AS ma
 334                 FROM (SELECT version() AS v) AS foo
 335               ) AS constants
 336               WHERE att.attnum > 0 AND tbl.relkind='r'
 337               GROUP BY 1,2,3,4,5
 338             ) AS foo
 339           ) AS rs
 340           ON cc.relname = rs.relname AND nn.nspname = rs.nspname
 341           LEFT JOIN pg_index i ON indrelid = cc.oid
 342           LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
 343         ) AS sml
 344          WHERE sml.relpages - otta > 0 OR ipages - iotta > 10 ORDER BY totalwastedbytes DESC LIMIT 10;"
 345     fi
 346
 347     local FIRST=
 348     local QUERY="\pset footer off
 349                  \echo '<<<postgres_bloat:sep(59)>>>${INSTANCE_SECTION}'
 350                  \echo '[databases_start]'
 351                  $ECHO_DATABASES
 352                  \echo '[databases_end]'"
 353
 354     for db in $DATABASES ; do
 355         QUERY="$QUERY
 356                \c $db
 357                $BLOAT_QUERY
 358               "
 359         if [ -z $FIRST ] ; then
 360             FIRST=false
 361             QUERY="$QUERY
 362                    \pset tuples_only on
 363                   "
 364         fi
 365     done
 366     echo "$QUERY" | su - "$DBUSER" -c "$export_PGPASSFILE $psql -X ${EXTRA_ARGS} -q -A -F';'"
 367 }
 368
 369
 370 #.
 371 #   .--main----------------------------------------------------------------.
 372 #   |                                       _                              |
 373 #   |                       _ __ ___   __ _(_)_ __                         |
 374 #   |                      | '_ ` _ \ / _` | | '_ \                        |
 375 #   |                      | | | | | | (_| | | | | |                       |
 376 #   |                      |_| |_| |_|\__,_|_|_| |_|                       |
 377 #   |                                                                      |
 378 #   '----------------------------------------------------------------------'
 379
 380
 381 ### postgres.cfg ##
 382 # DBUSER=OS_USER_NAME
 383 # INSTANCE=/home/postgres/db1.env:USER_NAME:/PATH/TO/.pgpass
 384 # INSTANCE=/home/postgres/db2.env:USER_NAME:/PATH/TO/.pgpass
 385
 386 # TODO @dba USERNAME in .pgpass ?
 387 # INSTANCE=/home/postgres/db2.env:/PATH/TO/.pgpass
 388
 389
 390 function postgres_main() {
 391     if [ -z "$DBUSER" ] || [ -z "$PGDATABASE" ] ; then
 392         exit 0
 393     fi
 394
 395     EXTRA_ARGS=""
 396     if [ ! -z "$PGUSER" ]; then
 397         EXTRA_ARGS=$EXTRA_ARGS" -U $PGUSER"
 398     fi
 399     if [ ! -z "$PGPORT" ]; then
 400         EXTRA_ARGS=$EXTRA_ARGS" -p $PGPORT"
 401     fi
 402
 403     if [ ! -z "$PGPASSFILE" ]; then
 404         export_PGPASSFILE="export PGPASSFILE=$PGPASSFILE; "
 405     fi
 406
 407     DATABASES="$(echo "SELECT datname FROM pg_database WHERE datistemplate = false;" |\
 408                  su - "$DBUSER" -c "$export_PGPASSFILE $psql -X -d $PGDATABASE ${EXTRA_ARGS} -t -A -F';'")"
 409     ECHO_DATABASES="$(echo "$DATABASES" | sed 's/^/\\echo /')"
 410
 411     POSTGRES_VERSION=$(su - "$DBUSER" -c "$psql -X -V -d $PGDATABASE ${EXTRA_ARGS} | egrep -o '[0-9]{1,}\.[0-9]{1,}'")
 412
 413     postgres_sessions
 414     postgres_simple_queries
 415     postgres_stats
 416     postgres_version
 417     postgres_bloat
 418 }
 419
 420
 421 MK_CONFFILE=$MK_CONFDIR/postgres.cfg
 422 if [ -e "$MK_CONFFILE" ]; then
 423
 424     postgres_instances
 425
 426     DBUSER=$(grep DBUSER "$MK_CONFFILE" | sed 's/.*=//g')
 427     cat "$MK_CONFFILE" | while read line
 428     do
 429         case $line in
 430             INSTANCE*)
 431                 instance=$line
 432                 ;;
 433             *)
 434                 instance=
 435                 ;;
 436         esac
 437
 438         if [ ! -z "$instance" ]; then
 439             instance_path=$(echo "$instance" | sed 's/.*=\(.*\):.*:.*$/\1/g')
 440             instance_name=$(echo "$instance_path" | sed -e 's/.*\/\(.*\)/\1/g' -e 's/\.env$//g')
 441             if [ ! -z "$instance_name" ]; then
 442                 INSTANCE_SECTION="\n[[[$instance_name]]]"
 443             else
 444                 INSTANCE_SECTION=""
 445             fi
 446
 447             psql="/$DBUSER/$(grep "^export PGVERSION=" "$instance_path" |
 448                  sed -e 's/.*=//g' -e 's/\s*#.*$//g')/bin/psql"
 449
 450             PGUSER=$(echo "$instance" | sed 's/.*=.*:\(.*\):.*$/\1/g')
 451             PGPASSFILE="$(echo "$instance" | sed 's/.*=.*:.*:\(.*\)$/\1/g')"
 452             PGDATABASE=$(grep "^export PGDATABASE=" "$instance_path" |
 453                          sed -e 's/.*=//g' -e 's/\s*#.*$//g')
 454             PGPORT=$(grep "^export PGPORT=" "$instance_path" |
 455                      sed -e 's/.*=//g' -e 's/\s*#.*$//g')
 456
 457             # Fallback
 458             if [ ! -f "$psql" ]; then
 459                 psql="$(cat $instance_path | grep "^export PGHOME=" |
 460                      sed -e 's/.*=//g' -e 's/\s*#.*$//g')/psql"
 461             fi
 462
 463             postgres_main
 464
 465         fi
 466     done
 467
 468 else
 469
 470     if id pgsql >/dev/null 2>&1; then
 471         DBUSER=pgsql
 472     elif id postgres >/dev/null 2>&1; then
 473         DBUSER=postgres
 474     else
 475         exit 0
 476     fi
 477     INSTANCE_SECTION=""
 478
 479     postgres_instances "$DBUSER"
 480
 481     psql="psql"
 482     PGDATABASE=postgres
 483     postgres_main
 484
 485 fi