486 lines
21 KiB
Plaintext
486 lines
21 KiB
Plaintext
|
#!/bin/bash
|
||
|
|
# +------------------------------------------------------------------+
|
||
|
|
# | ____ _ _ __ __ _ __ |
|
||
|
|
# | / ___| |__ ___ ___| | __ | \/ | |/ / |
|
||
|
|
# | | | | '_ \ / _ \/ __| |/ / | |\/| | ' / |
|
||
|
|
# | | |___| | | | __/ (__| < | | | | . \ |
|
||
|
|
# | \____|_| |_|\___|\___|_|\_\___|_| |_|_|\_\ |
|
||
|
|
# | |
|
||
|
|
# | Copyright Mathias Kettner 2015 mk@mathias-kettner.de |
|
||
|
|
# +------------------------------------------------------------------+
|
||
|
|
#
|
||
|
|
# This file is part of Check_MK.
|
||
|
|
# The official homepage is at http://mathias-kettner.de/check_mk.
|
||
|
|
#
|
||
|
|
# check_mk is free software; you can redistribute it and/or modify it
|
||
|
|
# under the terms of the GNU General Public License as published by
|
||
|
|
# the Free Software Foundation in version 2. check_mk is distributed
|
||
|
|
# in the hope that it will be useful, but WITHOUT ANY WARRANTY; with-
|
||
|
|
# out even the implied warranty of MERCHANTABILITY or FITNESS FOR A
|
||
|
|
# PARTICULAR PURPOSE. See the GNU General Public License for more de-
|
||
|
|
# tails. You should have received a copy of the GNU General Public
|
||
|
|
# License along with GNU Make; see the file COPYING. If not, write
|
||
|
|
# to the Free Software Foundation, Inc., 51 Franklin St, Fifth Floor,
|
||
|
|
# Boston, MA 02110-1301 USA.
|
||
|
|
|
||
|
|
|
||
|
|
# TODO postgres_connections output format
|
||
|
|
|
||
|
|
|
||
|
|
# .--common funcs--------------------------------------------------------.
|
||
|
|
# | __ |
|
||
|
|
# | ___ ___ _ __ ___ _ __ ___ ___ _ __ / _|_ _ _ __ ___ ___ |
|
||
|
|
# | / __/ _ \| '_ ` _ \| '_ ` _ \ / _ \| '_ \ | |_| | | | '_ \ / __/ __| |
|
||
|
|
# || (_| (_) | | | | | | | | | | | (_) | | | || _| |_| | | | | (__\__ \ |
|
||
|
|
# | \___\___/|_| |_| |_|_| |_| |_|\___/|_| |_||_| \__,_|_| |_|\___|___/ |
|
||
|
|
# | |
|
||
|
|
# '----------------------------------------------------------------------'
|
||
|
|
|
||
|
|
|
||
|
|
function compare_version_greater_equal() {
|
||
|
|
local GREATER_ONE
|
||
|
|
GREATER_ONE=$(echo "$1 $2" | awk '{if ($1 >= $2) print $1; else print $2}')
|
||
|
|
if [ "$GREATER_ONE" == "$1" ] ; then
|
||
|
|
return 0
|
||
|
|
else
|
||
|
|
return 1
|
||
|
|
fi
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
#.
|
||
|
|
# .--section funcs-------------------------------------------------------.
|
||
|
|
# | _ _ __ |
|
||
|
|
# | ___ ___ ___| |_(_) ___ _ __ / _|_ _ _ __ ___ ___ |
|
||
|
|
# | / __|/ _ \/ __| __| |/ _ \| '_ \ | |_| | | | '_ \ / __/ __| |
|
||
|
|
# | \__ \ __/ (__| |_| | (_) | | | | | _| |_| | | | | (__\__ \ |
|
||
|
|
# | |___/\___|\___|\__|_|\___/|_| |_| |_| \__,_|_| |_|\___|___/ |
|
||
|
|
# | |
|
||
|
|
# '----------------------------------------------------------------------'
|
||
|
|
|
||
|
|
|
||
|
|
function postgres_instances() {
|
||
|
|
echo '<<<postgres_instances>>>'
|
||
|
|
# If we have no instances we take db id (pqsql/postgres) because
|
||
|
|
# ps output may be unreadable
|
||
|
|
# In case of instances ps output shows them readable
|
||
|
|
if [ ! -z "${1}" ]; then
|
||
|
|
echo "[[[${1}]]]"
|
||
|
|
fi
|
||
|
|
pgrep -laf bin/postgres
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
function postgres_sessions() {
|
||
|
|
# Postgres 9.2 uses 'query' instead of 'current_query'
|
||
|
|
local OUTPUT
|
||
|
|
OUTPUT="$(echo "\echo '<<<postgres_sessions>>>${INSTANCE_SECTION}'
|
||
|
|
SELECT (
|
||
|
|
SELECT column_name
|
||
|
|
FROM information_schema.columns
|
||
|
|
WHERE table_name='pg_stat_activity' AND column_name in ('query', 'current_query')
|
||
|
|
) = '<IDLE>' as query, count(*)
|
||
|
|
FROM pg_stat_activity
|
||
|
|
GROUP BY (query = '<IDLE>');" |\
|
||
|
|
sudo -u "$DBUSER" $export_PGPASSFILE $psql -X --variable ON_ERROR_STOP=1 -d $PGDATABASE ${EXTRA_ARGS} -A -t -F' ' 2>/dev/null)"
|
||
|
|
|
||
|
|
echo "$OUTPUT"
|
||
|
|
# line with number of idle sessions is sometimes missing on Postgres 8.x. This can lead
|
||
|
|
# to an altogether empty section and thus the check disappearing.
|
||
|
|
echo "$OUTPUT" | grep -q '^t ' || echo "t 0"
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
function postgres_simple_queries() {
|
||
|
|
# Querytime
|
||
|
|
# Supports versions >= 8.3, > 9.1
|
||
|
|
local QUERYTIME_QUERY
|
||
|
|
if compare_version_greater_equal "$POSTGRES_VERSION" "9.2" ; then
|
||
|
|
QUERYTIME_QUERY="SELECT datname, datid, usename, client_addr, state AS state, COALESCE(ROUND(EXTRACT(epoch FROM now()-query_start)),0) AS seconds,
|
||
|
|
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;"
|
||
|
|
else
|
||
|
|
QUERYTIME_QUERY="SELECT datname, datid, usename, client_addr, '' AS state, COALESCE(ROUND(EXTRACT(epoch FROM now()-query_start)),0) AS seconds,
|
||
|
|
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;"
|
||
|
|
fi
|
||
|
|
|
||
|
|
# Number of current connections per database
|
||
|
|
# We need to output the databases, too.
|
||
|
|
# This query does not report databases without an active query
|
||
|
|
local CONNECTIONS_QUERY
|
||
|
|
if compare_version_greater_equal "$POSTGRES_VERSION" "9.2" ; then
|
||
|
|
CONNECTIONS_QUERY="SELECT COUNT(datid) AS current,
|
||
|
|
(SELECT setting AS mc FROM pg_settings WHERE name = 'max_connections') AS mc,
|
||
|
|
d.datname
|
||
|
|
FROM pg_database d
|
||
|
|
LEFT JOIN pg_stat_activity s ON (s.datid = d.oid) WHERE state <> 'idle'
|
||
|
|
GROUP BY 2,3
|
||
|
|
ORDER BY datname;"
|
||
|
|
else
|
||
|
|
CONNECTIONS_QUERY="SELECT COUNT(datid) AS current,
|
||
|
|
(SELECT setting AS mc FROM pg_settings WHERE name = 'max_connections') AS mc,
|
||
|
|
d.datname
|
||
|
|
FROM pg_database d
|
||
|
|
LEFT JOIN pg_stat_activity s ON (s.datid = d.oid) WHERE current_query <> '<IDLE>'
|
||
|
|
GROUP BY 2,3
|
||
|
|
ORDER BY datname;"
|
||
|
|
fi
|
||
|
|
|
||
|
|
echo "\pset footer off
|
||
|
|
\echo '<<<postgres_stat_database:sep(59)>>>${INSTANCE_SECTION}'
|
||
|
|
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;
|
||
|
|
|
||
|
|
\echo '<<<postgres_locks:sep(59)>>>${INSTANCE_SECTION}'
|
||
|
|
\echo '[databases_start]'
|
||
|
|
$ECHO_DATABASES
|
||
|
|
\echo '[databases_end]'
|
||
|
|
SELECT datname, granted, mode FROM pg_locks l RIGHT JOIN pg_database d ON (d.oid=l.database) WHERE d.datallowconn;
|
||
|
|
|
||
|
|
\echo '<<<postgres_query_duration:sep(59)>>>${INSTANCE_SECTION}'
|
||
|
|
\echo '[databases_start]'
|
||
|
|
$ECHO_DATABASES
|
||
|
|
\echo '[databases_end]'
|
||
|
|
$QUERYTIME_QUERY
|
||
|
|
|
||
|
|
\echo '<<<postgres_connections:sep(59)>>>${INSTANCE_SECTION}'
|
||
|
|
\echo '[databases_start]'
|
||
|
|
$ECHO_DATABASES
|
||
|
|
\echo '[databases_end]'
|
||
|
|
$CONNECTIONS_QUERY" \
|
||
|
|
| sudo -u "$DBUSER" $export_PGPASSFILE $psql -X -d $PGDATABASE ${EXTRA_ARGS} -q -A -F';'
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
function postgres_stats() {
|
||
|
|
# Contains last vacuum time and analyze time
|
||
|
|
local LASTVACUUM="SELECT current_database() AS datname, nspname AS sname, relname AS tname,
|
||
|
|
CASE WHEN v IS NULL THEN -1 ELSE round(extract(epoch FROM v)) END AS vtime,
|
||
|
|
CASE WHEN g IS NULL THEN -1 ELSE round(extract(epoch FROM v)) END AS atime
|
||
|
|
FROM (SELECT nspname, relname, GREATEST(pg_stat_get_last_vacuum_time(c.oid), pg_stat_get_last_autovacuum_time(c.oid)) AS v,
|
||
|
|
GREATEST(pg_stat_get_last_analyze_time(c.oid), pg_stat_get_last_autoanalyze_time(c.oid)) AS g
|
||
|
|
FROM pg_class c, pg_namespace n
|
||
|
|
WHERE relkind = 'r' AND n.oid = c.relnamespace AND n.nspname <> 'information_schema'
|
||
|
|
ORDER BY 3) AS foo;"
|
||
|
|
|
||
|
|
local FIRST=
|
||
|
|
local QUERY="\pset footer off
|
||
|
|
BEGIN;
|
||
|
|
SET statement_timeout=30000;
|
||
|
|
COMMIT;
|
||
|
|
|
||
|
|
\echo '<<<postgres_stats:sep(59)>>>${INSTANCE_SECTION}'
|
||
|
|
\echo '[databases_start]'
|
||
|
|
$ECHO_DATABASES
|
||
|
|
\echo '[databases_end]'"
|
||
|
|
|
||
|
|
for db in $DATABASES ; do
|
||
|
|
QUERY="$QUERY
|
||
|
|
\c $db
|
||
|
|
$LASTVACUUM
|
||
|
|
"
|
||
|
|
if [ -z $FIRST ] ; then
|
||
|
|
FIRST=false
|
||
|
|
QUERY="$QUERY
|
||
|
|
\pset tuples_only on
|
||
|
|
"
|
||
|
|
fi
|
||
|
|
done
|
||
|
|
echo "$QUERY" | sudo -u "$DBUSER" $export_PGPASSFILE $psql -X ${EXTRA_ARGS} -q -A -F';' | grep -v -e 'COMMIT$' -e 'SET$' -e 'BEGIN$'
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
function postgres_version() {
|
||
|
|
# Postgres version an connection time
|
||
|
|
echo -e "<<<postgres_version:sep(1)>>>${INSTANCE_SECTION}"
|
||
|
|
(TIMEFORMAT='%3R'; time echo "SELECT version() AS v" |\
|
||
|
|
sudo -u "$DBUSER" $export_PGPASSFILE $psql -X -d $PGDATABASE ${EXTRA_ARGS} -t -A -F';'; echo -e "<<<postgres_conn_time>>>${INSTANCE_SECTION}") 2>&1
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
function postgres_bloat() {
|
||
|
|
# Bloat index and tables
|
||
|
|
# Supports versions <9.0, >=9.0
|
||
|
|
# This huge query has been gratefully taken from Greg Sabino Mullane's check_postgres.pl
|
||
|
|
local BLOAT_QUERY
|
||
|
|
if compare_version_greater_equal "$POSTGRES_VERSION" "9.0" ; then
|
||
|
|
BLOAT_QUERY="SELECT
|
||
|
|
current_database() AS db, schemaname, tablename, reltuples::bigint AS tups, relpages::bigint AS pages, otta,
|
||
|
|
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,
|
||
|
|
CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages,
|
||
|
|
CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes,
|
||
|
|
CASE WHEN relpages < otta THEN 0 ELSE (bs*(relpages-otta))::bigint END AS wastedsize,
|
||
|
|
iname, ituples::bigint AS itups, ipages::bigint AS ipages, iotta,
|
||
|
|
ROUND(CASE WHEN iotta=0 OR ipages=0 OR ipages=iotta THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat,
|
||
|
|
CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wastedipages,
|
||
|
|
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes,
|
||
|
|
CASE WHEN ipages < iotta THEN 0 ELSE (bs*(ipages-iotta))::bigint END AS wastedisize,
|
||
|
|
CASE WHEN relpages < otta THEN
|
||
|
|
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta::bigint) END
|
||
|
|
ELSE CASE WHEN ipages < iotta THEN bs*(relpages-otta::bigint)
|
||
|
|
ELSE bs*(relpages-otta::bigint + ipages-iotta::bigint) END
|
||
|
|
END AS totalwastedbytes
|
||
|
|
FROM (
|
||
|
|
SELECT
|
||
|
|
nn.nspname AS schemaname,
|
||
|
|
cc.relname AS tablename,
|
||
|
|
COALESCE(cc.reltuples,0) AS reltuples,
|
||
|
|
COALESCE(cc.relpages,0) AS relpages,
|
||
|
|
COALESCE(bs,0) AS bs,
|
||
|
|
COALESCE(CEIL((cc.reltuples*((datahdr+ma-
|
||
|
|
(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)),0) AS otta,
|
||
|
|
COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
|
||
|
|
COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
|
||
|
|
FROM
|
||
|
|
pg_class cc
|
||
|
|
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <> 'information_schema'
|
||
|
|
LEFT JOIN
|
||
|
|
(
|
||
|
|
SELECT
|
||
|
|
ma,bs,foo.nspname,foo.relname,
|
||
|
|
(datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
|
||
|
|
(maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
|
||
|
|
FROM (
|
||
|
|
SELECT
|
||
|
|
ns.nspname, tbl.relname, hdr, ma, bs,
|
||
|
|
SUM((1-coalesce(null_frac,0))*coalesce(avg_width, 2048)) AS datawidth,
|
||
|
|
MAX(coalesce(null_frac,0)) AS maxfracsum,
|
||
|
|
hdr+(
|
||
|
|
SELECT 1+count(*)/8
|
||
|
|
FROM pg_stats s2
|
||
|
|
WHERE null_frac<>0 AND s2.schemaname = ns.nspname AND s2.tablename = tbl.relname
|
||
|
|
) AS nullhdr
|
||
|
|
FROM pg_attribute att
|
||
|
|
JOIN pg_class tbl ON att.attrelid = tbl.oid
|
||
|
|
JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
|
||
|
|
LEFT JOIN pg_stats s ON s.schemaname=ns.nspname
|
||
|
|
AND s.tablename = tbl.relname
|
||
|
|
AND s.inherited=false
|
||
|
|
AND s.attname=att.attname,
|
||
|
|
(
|
||
|
|
SELECT
|
||
|
|
(SELECT current_setting('block_size')::numeric) AS bs,
|
||
|
|
CASE WHEN SUBSTRING(SPLIT_PART(v, ' ', 2) FROM '#\[0-9]+.[0-9]+#\%' for '#')
|
||
|
|
IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
|
||
|
|
CASE WHEN v ~ 'mingw32' OR v ~ '64-bit' THEN 8 ELSE 4 END AS ma
|
||
|
|
FROM (SELECT version() AS v) AS foo
|
||
|
|
) AS constants
|
||
|
|
WHERE att.attnum > 0 AND tbl.relkind='r'
|
||
|
|
GROUP BY 1,2,3,4,5
|
||
|
|
) AS foo
|
||
|
|
) AS rs
|
||
|
|
ON cc.relname = rs.relname AND nn.nspname = rs.nspname
|
||
|
|
LEFT JOIN pg_index i ON indrelid = cc.oid
|
||
|
|
LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
|
||
|
|
) AS sml
|
||
|
|
WHERE sml.relpages - otta > 0 OR ipages - iotta > 10 ORDER BY totalwastedbytes DESC LIMIT 10;"
|
||
|
|
else
|
||
|
|
BLOAT_QUERY="SELECT
|
||
|
|
current_database() AS db, schemaname, tablename, reltuples::bigint AS tups, relpages::bigint AS pages, otta,
|
||
|
|
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,
|
||
|
|
CASE WHEN relpages < otta THEN 0 ELSE relpages::bigint - otta END AS wastedpages,
|
||
|
|
CASE WHEN relpages < otta THEN 0 ELSE bs*(sml.relpages-otta)::bigint END AS wastedbytes,
|
||
|
|
CASE WHEN relpages < otta THEN '0 bytes'::text ELSE (bs*(relpages-otta))::bigint || ' bytes' END AS wastedsize,
|
||
|
|
iname, ituples::bigint AS itups, ipages::bigint AS ipages, iotta,
|
||
|
|
ROUND(CASE WHEN iotta=0 OR ipages=0 OR ipages=iotta THEN 0.0 ELSE ipages/iotta::numeric END,1) AS ibloat,
|
||
|
|
CASE WHEN ipages < iotta THEN 0 ELSE ipages::bigint - iotta END AS wastedipages,
|
||
|
|
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta) END AS wastedibytes,
|
||
|
|
CASE WHEN ipages < iotta THEN '0 bytes' ELSE (bs*(ipages-iotta))::bigint || ' bytes' END AS wastedisize,
|
||
|
|
CASE WHEN relpages < otta THEN
|
||
|
|
CASE WHEN ipages < iotta THEN 0 ELSE bs*(ipages-iotta::bigint) END
|
||
|
|
ELSE CASE WHEN ipages < iotta THEN bs*(relpages-otta::bigint)
|
||
|
|
ELSE bs*(relpages-otta::bigint + ipages-iotta::bigint) END
|
||
|
|
END AS totalwastedbytes
|
||
|
|
FROM (
|
||
|
|
SELECT
|
||
|
|
nn.nspname AS schemaname,
|
||
|
|
cc.relname AS tablename,
|
||
|
|
COALESCE(cc.reltuples,0) AS reltuples,
|
||
|
|
COALESCE(cc.relpages,0) AS relpages,
|
||
|
|
COALESCE(bs,0) AS bs,
|
||
|
|
COALESCE(CEIL((cc.reltuples*((datahdr+ma-
|
||
|
|
(CASE WHEN datahdr%ma=0 THEN ma ELSE datahdr%ma END))+nullhdr2+4))/(bs-20::float)),0) AS otta,
|
||
|
|
COALESCE(c2.relname,'?') AS iname, COALESCE(c2.reltuples,0) AS ituples, COALESCE(c2.relpages,0) AS ipages,
|
||
|
|
COALESCE(CEIL((c2.reltuples*(datahdr-12))/(bs-20::float)),0) AS iotta -- very rough approximation, assumes all cols
|
||
|
|
FROM
|
||
|
|
pg_class cc
|
||
|
|
JOIN pg_namespace nn ON cc.relnamespace = nn.oid AND nn.nspname <> 'information_schema'
|
||
|
|
LEFT JOIN
|
||
|
|
(
|
||
|
|
SELECT
|
||
|
|
ma,bs,foo.nspname,foo.relname,
|
||
|
|
(datawidth+(hdr+ma-(case when hdr%ma=0 THEN ma ELSE hdr%ma END)))::numeric AS datahdr,
|
||
|
|
(maxfracsum*(nullhdr+ma-(case when nullhdr%ma=0 THEN ma ELSE nullhdr%ma END))) AS nullhdr2
|
||
|
|
FROM (
|
||
|
|
SELECT
|
||
|
|
ns.nspname, tbl.relname, hdr, ma, bs,
|
||
|
|
SUM((1-coalesce(null_frac,0))*coalesce(avg_width, 2048)) AS datawidth,
|
||
|
|
MAX(coalesce(null_frac,0)) AS maxfracsum,
|
||
|
|
hdr+(
|
||
|
|
SELECT 1+count(*)/8
|
||
|
|
FROM pg_stats s2
|
||
|
|
WHERE null_frac<>0 AND s2.schemaname = ns.nspname AND s2.tablename = tbl.relname
|
||
|
|
) AS nullhdr
|
||
|
|
FROM pg_attribute att
|
||
|
|
JOIN pg_class tbl ON att.attrelid = tbl.oid
|
||
|
|
JOIN pg_namespace ns ON ns.oid = tbl.relnamespace
|
||
|
|
LEFT JOIN pg_stats s ON s.schemaname=ns.nspname
|
||
|
|
AND s.tablename = tbl.relname
|
||
|
|
AND s.attname=att.attname,
|
||
|
|
(
|
||
|
|
SELECT
|
||
|
|
(SELECT current_setting('block_size')::numeric) AS bs,
|
||
|
|
CASE WHEN SUBSTRING(SPLIT_PART(v, ' ', 2) FROM '#\"[0-9]+.[0-9]+#\"%' for '#')
|
||
|
|
IN ('8.0','8.1','8.2') THEN 27 ELSE 23 END AS hdr,
|
||
|
|
CASE WHEN v ~ 'mingw32' OR v ~ '64-bit' THEN 8 ELSE 4 END AS ma
|
||
|
|
FROM (SELECT version() AS v) AS foo
|
||
|
|
) AS constants
|
||
|
|
WHERE att.attnum > 0 AND tbl.relkind='r'
|
||
|
|
GROUP BY 1,2,3,4,5
|
||
|
|
) AS foo
|
||
|
|
) AS rs
|
||
|
|
ON cc.relname = rs.relname AND nn.nspname = rs.nspname
|
||
|
|
LEFT JOIN pg_index i ON indrelid = cc.oid
|
||
|
|
LEFT JOIN pg_class c2 ON c2.oid = i.indexrelid
|
||
|
|
) AS sml
|
||
|
|
WHERE sml.relpages - otta > 0 OR ipages - iotta > 10 ORDER BY totalwastedbytes DESC LIMIT 10;"
|
||
|
|
fi
|
||
|
|
|
||
|
|
local FIRST=
|
||
|
|
local QUERY="\pset footer off
|
||
|
|
\echo '<<<postgres_bloat:sep(59)>>>${INSTANCE_SECTION}'
|
||
|
|
\echo '[databases_start]'
|
||
|
|
$ECHO_DATABASES
|
||
|
|
\echo '[databases_end]'"
|
||
|
|
|
||
|
|
for db in $DATABASES ; do
|
||
|
|
QUERY="$QUERY
|
||
|
|
\c $db
|
||
|
|
$BLOAT_QUERY
|
||
|
|
"
|
||
|
|
if [ -z $FIRST ] ; then
|
||
|
|
FIRST=false
|
||
|
|
QUERY="$QUERY
|
||
|
|
\pset tuples_only on
|
||
|
|
"
|
||
|
|
fi
|
||
|
|
done
|
||
|
|
echo "$QUERY" | sudo -u "$DBUSER" $export_PGPASSFILE $psql -X ${EXTRA_ARGS} -q -A -F';'
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
#.
|
||
|
|
# .--main----------------------------------------------------------------.
|
||
|
|
# | _ |
|
||
|
|
# | _ __ ___ __ _(_)_ __ |
|
||
|
|
# | | '_ ` _ \ / _` | | '_ \ |
|
||
|
|
# | | | | | | | (_| | | | | | |
|
||
|
|
# | |_| |_| |_|\__,_|_|_| |_| |
|
||
|
|
# | |
|
||
|
|
# '----------------------------------------------------------------------'
|
||
|
|
|
||
|
|
|
||
|
|
### postgres.cfg ##
|
||
|
|
# DBUSER=OS_USER_NAME
|
||
|
|
# INSTANCE=/home/postgres/db1.env:USER_NAME:/PATH/TO/.pgpass
|
||
|
|
# INSTANCE=/home/postgres/db2.env:USER_NAME:/PATH/TO/.pgpass
|
||
|
|
|
||
|
|
# TODO @dba USERNAME in .pgpass ?
|
||
|
|
# INSTANCE=/home/postgres/db2.env:/PATH/TO/.pgpass
|
||
|
|
|
||
|
|
|
||
|
|
function postgres_main() {
|
||
|
|
if [ -z "$DBUSER" ] || [ -z "$PGDATABASE" ] ; then
|
||
|
|
exit 0
|
||
|
|
fi
|
||
|
|
|
||
|
|
EXTRA_ARGS=""
|
||
|
|
if [ ! -z "$PGUSER" ]; then
|
||
|
|
EXTRA_ARGS=$EXTRA_ARGS" -U $PGUSER"
|
||
|
|
fi
|
||
|
|
if [ ! -z "$PGPORT" ]; then
|
||
|
|
EXTRA_ARGS=$EXTRA_ARGS" -p $PGPORT"
|
||
|
|
fi
|
||
|
|
|
||
|
|
if [ ! -z "$PGPASSFILE" ]; then
|
||
|
|
export_PGPASSFILE="export PGPASSFILE=$PGPASSFILE; "
|
||
|
|
fi
|
||
|
|
|
||
|
|
DATABASES="$(echo "SELECT datname FROM pg_database WHERE datistemplate = false;" |\
|
||
|
|
sudo -u "$DBUSER" $export_PGPASSFILE $psql -X -d $PGDATABASE ${EXTRA_ARGS} -t -A -F';')"
|
||
|
|
ECHO_DATABASES="$(echo "$DATABASES" | sed 's/^/\\echo /')"
|
||
|
|
|
||
|
|
POSTGRES_VERSION=$(sudo -u "$DBUSER" $psql -X -V -d $PGDATABASE ${EXTRA_ARGS} | egrep -o '[0-9]{1,}\.[0-9]{1,}')
|
||
|
|
|
||
|
|
postgres_sessions
|
||
|
|
postgres_simple_queries
|
||
|
|
#postgres_stats
|
||
|
|
postgres_version
|
||
|
|
postgres_bloat
|
||
|
|
}
|
||
|
|
|
||
|
|
|
||
|
|
MK_CONFFILE=$MK_CONFDIR/postgres.cfg
|
||
|
|
if [ -e "$MK_CONFFILE" ]; then
|
||
|
|
|
||
|
|
postgres_instances
|
||
|
|
|
||
|
|
DBUSER=$(grep DBUSER "$MK_CONFFILE" | sed 's/.*=//g')
|
||
|
|
cat "$MK_CONFFILE" | while read line
|
||
|
|
do
|
||
|
|
case $line in
|
||
|
|
INSTANCE*)
|
||
|
|
instance=$line
|
||
|
|
;;
|
||
|
|
*)
|
||
|
|
instance=
|
||
|
|
;;
|
||
|
|
esac
|
||
|
|
|
||
|
|
if [ ! -z "$instance" ]; then
|
||
|
|
instance_path=$(echo "$instance" | sed 's/.*=\(.*\):.*:.*$/\1/g')
|
||
|
|
instance_name=$(echo "$instance_path" | sed -e 's/.*\/\(.*\)/\1/g' -e 's/\.env$//g')
|
||
|
|
if [ ! -z "$instance_name" ]; then
|
||
|
|
INSTANCE_SECTION="\n[[[$instance_name]]]"
|
||
|
|
else
|
||
|
|
INSTANCE_SECTION=""
|
||
|
|
fi
|
||
|
|
|
||
|
|
psql="/$DBUSER/$(grep "^export PGVERSION=" "$instance_path" |
|
||
|
|
sed -e 's/.*=//g' -e 's/\s*#.*$//g')/bin/psql"
|
||
|
|
|
||
|
|
PGUSER=$(echo "$instance" | sed 's/.*=.*:\(.*\):.*$/\1/g')
|
||
|
|
PGPASSFILE="$(echo "$instance" | sed 's/.*=.*:.*:\(.*\)$/\1/g')"
|
||
|
|
PGDATABASE=$(grep "^export PGDATABASE=" "$instance_path" |
|
||
|
|
sed -e 's/.*=//g' -e 's/\s*#.*$//g')
|
||
|
|
PGPORT=$(grep "^export PGPORT=" "$instance_path" |
|
||
|
|
sed -e 's/.*=//g' -e 's/\s*#.*$//g')
|
||
|
|
|
||
|
|
# Fallback
|
||
|
|
if [ ! -f "$psql" ]; then
|
||
|
|
psql="$(cat $instance_path | grep "^export PGHOME=" |
|
||
|
|
sed -e 's/.*=//g' -e 's/\s*#.*$//g')/psql"
|
||
|
|
fi
|
||
|
|
|
||
|
|
postgres_main
|
||
|
|
|
||
|
|
fi
|
||
|
|
done
|
||
|
|
|
||
|
|
else
|
||
|
|
|
||
|
|
if id pgsql >/dev/null 2>&1; then
|
||
|
|
DBUSER=pgsql
|
||
|
|
elif id postgres >/dev/null 2>&1; then
|
||
|
|
DBUSER=postgres
|
||
|
|
else
|
||
|
|
exit 0
|
||
|
|
fi
|
||
|
|
INSTANCE_SECTION=""
|
||
|
|
|
||
|
|
postgres_instances "$DBUSER"
|
||
|
|
|
||
|
|
psql="psql"
|
||
|
|
PGDATABASE=postgres
|
||
|
|
postgres_main
|
||
|
|
|
||
|
|
fi
|