284 lines
14 KiB
Bash
Executable File
284 lines
14 KiB
Bash
Executable File
#!/bin/bash
|
|
# ═══════════════════════════════════════════════════════════════════════════════
|
|
# monitor_postgres_tables.sh — Surveillance tailles tables ADS-B PostgreSQL
|
|
# Version : 2.0 — JCA 2026
|
|
# Usage : ./monitor_postgres_tables.sh [--alert THRESHOLD_MB] [--init]
|
|
# --alert N : alerte si positions ou aircraft_history dépasse N Mo (défaut 5000)
|
|
# --init : crée la table adsb.table_size_history si elle n'existe pas
|
|
# ═══════════════════════════════════════════════════════════════════════════════
|
|
|
|
# ── Configuration ─────────────────────────────────────────────────────────────
|
|
PG_USER="pgz_admin"
|
|
PG_DB="wpz_postgres"
|
|
PG_SCHEMA="adsb"
|
|
ALERT_THRESHOLD_MB=5000
|
|
|
|
# ── Couleurs ──────────────────────────────────────────────────────────────────
|
|
GREEN='\033[0;32m'; RED='\033[0;31m'; YELLOW='\033[1;33m'
|
|
CYAN='\033[0;36m'; BLUE='\033[1;34m'; BOLD='\033[1m'; NC='\033[0m'
|
|
|
|
ok() { echo -e " ${GREEN}✓${NC} $1"; }
|
|
fail() { echo -e " ${RED}✗${NC} $1"; }
|
|
warn() { echo -e " ${YELLOW}⚠${NC} $1"; }
|
|
info() { echo -e " ${CYAN}→${NC} $1"; }
|
|
hdr() { echo -e "\n${BLUE}${BOLD}── $1 ──${NC}"; }
|
|
|
|
# ── Parsing des arguments ─────────────────────────────────────────────────────
|
|
DO_INIT=false
|
|
while [[ $# -gt 0 ]]; do
|
|
case "$1" in
|
|
--alert) ALERT_THRESHOLD_MB="$2"; shift 2 ;;
|
|
--init) DO_INIT=true; shift ;;
|
|
*) echo "Usage: $0 [--alert THRESHOLD_MB] [--init]"; exit 1 ;;
|
|
esac
|
|
done
|
|
|
|
# ── Helper psql ───────────────────────────────────────────────────────────────
|
|
pg() { docker exec wordpress-postgres psql -U "$PG_USER" -d "$PG_DB" -t -c "$1" 2>/dev/null | xargs; }
|
|
|
|
# ── Vérification Docker ───────────────────────────────────────────────────────
|
|
if ! docker inspect wordpress-postgres &>/dev/null; then
|
|
fail "Conteneur wordpress-postgres inaccessible"
|
|
exit 1
|
|
fi
|
|
|
|
# ── Init : création de la table d'historique ──────────────────────────────────
|
|
if [ "$DO_INIT" = true ]; then
|
|
echo -e "${BOLD}Création de adsb.table_size_history...${NC}"
|
|
TMP_INIT=$(mktemp /tmp/pg_init_XXXXXX.sql)
|
|
trap "rm -f $TMP_INIT" EXIT
|
|
cat > "$TMP_INIT" << 'ENDINIT'
|
|
CREATE TABLE IF NOT EXISTS adsb.table_size_history (
|
|
id SERIAL PRIMARY KEY,
|
|
captured_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
|
table_name VARCHAR(60) NOT NULL,
|
|
total_bytes BIGINT NOT NULL,
|
|
table_bytes BIGINT NOT NULL,
|
|
index_bytes BIGINT NOT NULL,
|
|
row_estimate BIGINT NOT NULL,
|
|
partition_count INTEGER NOT NULL DEFAULT 0
|
|
);
|
|
CREATE INDEX IF NOT EXISTS tsh_captured_at_idx ON adsb.table_size_history (captured_at DESC);
|
|
CREATE INDEX IF NOT EXISTS tsh_table_name_idx ON adsb.table_size_history (table_name, captured_at DESC);
|
|
COMMENT ON TABLE adsb.table_size_history IS 'Historique tailles tables ADS-B — monitor_postgres_tables.sh';
|
|
ENDINIT
|
|
docker cp "$TMP_INIT" wordpress-postgres:/tmp/pg_init.sql 2>/dev/null
|
|
docker exec wordpress-postgres psql -U "$PG_USER" -d "$PG_DB" -f /tmp/pg_init.sql 2>&1
|
|
RET=$?
|
|
docker exec wordpress-postgres rm -f /tmp/pg_init.sql 2>/dev/null
|
|
rm -f "$TMP_INIT"
|
|
if [ $RET -eq 0 ]; then
|
|
ok "Table adsb.table_size_history créée (ou déjà existante)"
|
|
else
|
|
fail "Erreur lors de la création de la table"
|
|
exit 1
|
|
fi
|
|
echo ""
|
|
fi
|
|
|
|
# ── En-tête ───────────────────────────────────────────────────────────────────
|
|
NOW=$(date '+%Y-%m-%d %H:%M:%S')
|
|
echo -e "${BOLD}╔══════════════════════════════════════════════════════╗${NC}"
|
|
echo -e "${BOLD}║ Surveillance tailles tables ADS-B PostgreSQL ║${NC}"
|
|
echo -e "${BOLD}║ $NOW ║${NC}"
|
|
echo -e "${BOLD}╚══════════════════════════════════════════════════════╝${NC}"
|
|
|
|
# ── Collecte des métriques via une seule requête ──────────────────────────────
|
|
hdr "Collecte des métriques"
|
|
|
|
# Écrire la requête dans un fichier temporaire (heredoc dans $() est instable en bash)
|
|
TMP_SQL=$(mktemp /tmp/pg_monitor_XXXXXX.sql)
|
|
trap "rm -f $TMP_SQL" EXIT
|
|
|
|
cat > "$TMP_SQL" << 'ENDSQL'
|
|
WITH parent_tables AS (
|
|
SELECT
|
|
pt.relname AS table_name,
|
|
pg_total_relation_size(pt.oid) AS total_bytes,
|
|
pg_relation_size(pt.oid) AS table_bytes,
|
|
pg_indexes_size(pt.oid) AS index_bytes,
|
|
COALESCE((
|
|
SELECT SUM(child.reltuples::BIGINT)
|
|
FROM pg_inherits inh
|
|
JOIN pg_class child ON child.oid = inh.inhrelid
|
|
JOIN pg_class parent ON parent.oid = inh.inhparent
|
|
WHERE parent.relname = pt.relname
|
|
AND parent.relnamespace = pt.relnamespace
|
|
), pt.reltuples::BIGINT) AS row_estimate,
|
|
(SELECT COUNT(*) FROM pg_inherits WHERE inhparent = pt.oid) AS partition_count
|
|
FROM pg_class pt
|
|
JOIN pg_namespace ns ON ns.oid = pt.relnamespace
|
|
WHERE ns.nspname = 'adsb'
|
|
AND pt.relkind IN ('r','p')
|
|
AND pt.relname IN ('positions', 'aircraft_history', 'receivers')
|
|
),
|
|
aggregated AS (
|
|
SELECT
|
|
pt.table_name,
|
|
CASE WHEN pt.partition_count > 0 THEN COALESCE((
|
|
SELECT SUM(pg_total_relation_size(child.oid))
|
|
FROM pg_inherits inh
|
|
JOIN pg_class child ON child.oid = inh.inhrelid
|
|
JOIN pg_class parent ON parent.oid = inh.inhparent
|
|
WHERE parent.relname = pt.table_name
|
|
AND parent.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname='adsb')
|
|
), 0) ELSE pt.total_bytes END AS total_bytes,
|
|
CASE WHEN pt.partition_count > 0 THEN COALESCE((
|
|
SELECT SUM(pg_relation_size(child.oid))
|
|
FROM pg_inherits inh
|
|
JOIN pg_class child ON child.oid = inh.inhrelid
|
|
JOIN pg_class parent ON parent.oid = inh.inhparent
|
|
WHERE parent.relname = pt.table_name
|
|
AND parent.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname='adsb')
|
|
), 0) ELSE pt.table_bytes END AS table_bytes,
|
|
CASE WHEN pt.partition_count > 0 THEN COALESCE((
|
|
SELECT SUM(pg_indexes_size(child.oid))
|
|
FROM pg_inherits inh
|
|
JOIN pg_class child ON child.oid = inh.inhrelid
|
|
JOIN pg_class parent ON parent.oid = inh.inhparent
|
|
WHERE parent.relname = pt.table_name
|
|
AND parent.relnamespace = (SELECT oid FROM pg_namespace WHERE nspname='adsb')
|
|
), 0) ELSE pt.index_bytes END AS index_bytes,
|
|
pt.row_estimate,
|
|
pt.partition_count
|
|
FROM parent_tables pt
|
|
)
|
|
SELECT
|
|
table_name,
|
|
total_bytes,
|
|
table_bytes,
|
|
index_bytes,
|
|
row_estimate,
|
|
partition_count,
|
|
pg_size_pretty(total_bytes) AS total_pretty,
|
|
pg_size_pretty(table_bytes) AS data_pretty,
|
|
pg_size_pretty(index_bytes) AS idx_pretty,
|
|
ROUND(total_bytes / 1024.0 / 1024.0, 1) AS total_mb
|
|
FROM aggregated
|
|
ORDER BY total_bytes DESC;
|
|
ENDSQL
|
|
|
|
# Copier le fichier SQL dans le conteneur et exécuter
|
|
docker cp "$TMP_SQL" wordpress-postgres:/tmp/pg_monitor.sql 2>/dev/null
|
|
METRICS=$(docker exec wordpress-postgres psql -U "$PG_USER" -d "$PG_DB" -t -f /tmp/pg_monitor.sql 2>&1)
|
|
docker exec wordpress-postgres rm -f /tmp/pg_monitor.sql 2>/dev/null
|
|
|
|
if [ -z "$METRICS" ]; then
|
|
fail "Impossible de collecter les métriques PostgreSQL"
|
|
exit 1
|
|
fi
|
|
ok "Métriques collectées"
|
|
|
|
# ── Affichage et insertion ────────────────────────────────────────────────────
|
|
hdr "Tailles actuelles"
|
|
|
|
ALERT_MSGS=()
|
|
|
|
while IFS='|' read -r table_name total_bytes table_bytes index_bytes row_estimate partition_count total_pretty data_pretty idx_pretty total_mb; do
|
|
# Nettoyer les espaces
|
|
table_name=$(echo "$table_name" | xargs)
|
|
total_bytes=$(echo "$total_bytes" | xargs)
|
|
table_bytes=$(echo "$table_bytes" | xargs)
|
|
index_bytes=$(echo "$index_bytes" | xargs)
|
|
row_estimate=$(echo "$row_estimate" | xargs)
|
|
partition_count=$(echo "$partition_count" | xargs)
|
|
total_pretty=$(echo "$total_pretty" | xargs)
|
|
data_pretty=$(echo "$data_pretty" | xargs)
|
|
idx_pretty=$(echo "$idx_pretty" | xargs)
|
|
total_mb=$(echo "$total_mb" | xargs)
|
|
|
|
[ -z "$table_name" ] && continue
|
|
|
|
# Récupérer la mesure précédente
|
|
PREV=$(docker exec wordpress-postgres psql -U "$PG_USER" -d "$PG_DB" -t -c "
|
|
SELECT total_bytes, captured_at::TEXT
|
|
FROM adsb.table_size_history
|
|
WHERE table_name = '$table_name'
|
|
ORDER BY captured_at DESC
|
|
LIMIT 1;" 2>/dev/null | xargs)
|
|
|
|
PREV_BYTES=$(echo "$PREV" | awk '{print $1}')
|
|
PREV_DATE=$(echo "$PREV" | awk '{print $2, $3}')
|
|
|
|
# Calculer la croissance
|
|
GROWTH_STR=""
|
|
if [ -n "$PREV_BYTES" ] && [ "$PREV_BYTES" -gt 0 ] 2>/dev/null; then
|
|
GROWTH_BYTES=$(( total_bytes - PREV_BYTES ))
|
|
GROWTH_MB=$(awk "BEGIN{printf \"%.1f\", $GROWTH_BYTES/1024/1024}")
|
|
GROWTH_PCT=$(awk "BEGIN{printf \"%.2f\", ($GROWTH_BYTES/$PREV_BYTES)*100}")
|
|
if [ "$GROWTH_BYTES" -gt 0 ]; then
|
|
GROWTH_STR=" ${GREEN}(+${GROWTH_MB} MB / +${GROWTH_PCT}% depuis $PREV_DATE)${NC}"
|
|
elif [ "$GROWTH_BYTES" -lt 0 ]; then
|
|
ABS_MB=$(awk "BEGIN{printf \"%.1f\", -$GROWTH_BYTES/1024/1024}")
|
|
GROWTH_STR=" ${YELLOW}(-${ABS_MB} MB depuis $PREV_DATE)${NC}"
|
|
else
|
|
GROWTH_STR=" ${CYAN}(stable depuis $PREV_DATE)${NC}"
|
|
fi
|
|
fi
|
|
|
|
# Affichage
|
|
echo -e " ${BOLD}${table_name}${NC}"
|
|
echo -e " Total : ${BOLD}${total_pretty}${NC}${GROWTH_STR}"
|
|
echo -e " Données : ${data_pretty} | Index : ${idx_pretty}"
|
|
echo -e " Lignes : ~$(printf "%'d" "${row_estimate:-0}" 2>/dev/null || echo "$row_estimate") | Partitions : ${partition_count}"
|
|
echo ""
|
|
|
|
# Alerte seuil
|
|
TOTAL_MB_INT=$(echo "$total_mb" | cut -d. -f1)
|
|
if [ "${TOTAL_MB_INT:-0}" -gt "$ALERT_THRESHOLD_MB" ] 2>/dev/null; then
|
|
ALERT_MSGS+=("$table_name : ${total_pretty} dépasse le seuil de ${ALERT_THRESHOLD_MB} MB")
|
|
fi
|
|
|
|
# Insertion dans l'historique
|
|
INSERT_RESULT=$(docker exec wordpress-postgres psql -U "$PG_USER" -d "$PG_DB" -t -c "
|
|
INSERT INTO adsb.table_size_history
|
|
(table_name, total_bytes, table_bytes, index_bytes, row_estimate, partition_count)
|
|
VALUES
|
|
('$table_name', $total_bytes, $table_bytes, $index_bytes,
|
|
${row_estimate:-0}, ${partition_count:-0})
|
|
RETURNING id;" 2>/dev/null | xargs)
|
|
|
|
if [ -n "$INSERT_RESULT" ]; then
|
|
info "Enregistrement #${INSERT_RESULT} inséré dans adsb.table_size_history"
|
|
else
|
|
warn "Impossible d'insérer dans adsb.table_size_history (table créée ? Lancer avec --init)"
|
|
fi
|
|
|
|
done <<< "$METRICS"
|
|
|
|
# ── Évolution sur 7 jours ─────────────────────────────────────────────────────
|
|
hdr "Évolution sur 7 jours"
|
|
|
|
docker exec wordpress-postgres psql -U "$PG_USER" -d "$PG_DB" -c "
|
|
SELECT
|
|
table_name AS \"Table\",
|
|
TO_CHAR(captured_at, 'YYYY-MM-DD HH24:MI') AS \"Horodatage\",
|
|
pg_size_pretty(total_bytes) AS \"Total\",
|
|
pg_size_pretty(total_bytes
|
|
- LAG(total_bytes) OVER (PARTITION BY table_name ORDER BY captured_at))
|
|
AS \"Delta\",
|
|
partition_count AS \"Parts\",
|
|
TO_CHAR(row_estimate, 'FM999,999,999,999') AS \"Lignes estimées\"
|
|
FROM adsb.table_size_history
|
|
WHERE captured_at >= NOW() - INTERVAL '7 days'
|
|
AND table_name IN ('positions','aircraft_history')
|
|
ORDER BY table_name, captured_at DESC
|
|
LIMIT 30;" 2>/dev/null || warn "Table adsb.table_size_history absente — lancer avec --init"
|
|
|
|
# ── Alertes ───────────────────────────────────────────────────────────────────
|
|
if [ ${#ALERT_MSGS[@]} -gt 0 ]; then
|
|
echo ""
|
|
echo -e "${RED}${BOLD}═══ ALERTES ═══${NC}"
|
|
for msg in "${ALERT_MSGS[@]}"; do
|
|
echo -e " ${RED}⚠ $msg${NC}"
|
|
done
|
|
fi
|
|
|
|
# ── Résumé ────────────────────────────────────────────────────────────────────
|
|
echo ""
|
|
echo -e "${BOLD}╔══════════════════════════════════════════════════════╗${NC}"
|
|
echo -e "${BOLD}║ Historique : SELECT * FROM adsb.table_size_history ║${NC}"
|
|
echo -e "${BOLD}║ Seuil alerte : ${ALERT_THRESHOLD_MB} MB ║${NC}"
|
|
echo -e "${BOLD}╚══════════════════════════════════════════════════════╝${NC}"
|
|
|