#!/bin/bash

#VERSION="v2026051100"		# initiale Erstellung
VERSION="v2026051300"		# jetzt werden die neu berechneten Werte, automatisch in die DB geschrieben

#CLUSTER_NAME="pgcluster"
CLUSTER_NAME="$(awk '/^scope: /{print $NF}' /etc/patroni/config.yml)"

### RAM: 1547643 MB
# > echo "SELECT name, setting, unit, SOURCE, sourcefile, sourceline FROM pg_settings WHERE name IN ('shared_buffers', 'effective_cache_size', 'work_mem', 'max_connections', 'maintenance_work_mem', 'autovacuum_work_mem', 'shared_memory_size');" | sudo -u postgres psql
#          name         |  setting  | unit |       source       |                 sourcefile                  | sourceline 
# ----------------------+-----------+------+--------------------+---------------------------------------------+------------
#  autovacuum_work_mem  | -1        | kB   | default            |                                             |           
#  effective_cache_size | 148635648 | 8kB  | configuration file | /var/lib/postgresql/16/main/postgresql.conf |          6
#  maintenance_work_mem | 78643200  | kB   | configuration file | /var/lib/postgresql/16/main/postgresql.conf |          9
#  max_connections      | 100       |      | command line       |                                             |           
#  shared_buffers       | 49545216  | 8kB  | configuration file | /var/lib/postgresql/16/main/postgresql.conf |         17
#  shared_memory_size   | 394457    | MB   | default            |                                             |           
#  work_mem             | 8388608   | kB   | configuration file | /var/lib/postgresql/16/main/postgresql.conf |         22
# (7 rows)

RAM_IN_MB="$(free -m | tail -n+2 | head -n1 | awk '{printf "%.0f\n", $2}')"
echo "RAM: ${RAM_IN_MB} MB"

S_PG="$(echo "SELECT name, setting, unit
FROM pg_settings
WHERE name IN (
  'autovacuum_work_mem',
  'effective_cache_size',
  'maintenance_work_mem',
  'max_connections',
  'shared_buffers',
  'work_mem'
);" | sudo -u postgres psql -t | head -n-1 | awk '{print $1,$3,$5}' | grep -Fv -- '-1' | awk '{a=$2; if ($3=="8kB") {a=$2*8}; b=a/1024" MB"; if (a>10485760) b=a/1024/1024" GB"; if ($1=="max_connections") b=$2; print $1"\t\t"b}')"
echo "
# IST-Werte (ggf. autovacuum_work_mem = maintenance_work_mem):
${S_PG}
"

AUTOVACUUM_WORK_MEM="$(echo "${RAM_IN_MB}"  | awk '{a=$1/10;   b="MB"; c=a/1024; d="GB"; e=a; f=b; if (a>10240) {e=c; f=d} printf "%.0f%s\n", e,f}')"
EFFECTIVE_CACHE_SIZE="$(echo "${RAM_IN_MB}" | awk '{a=$1*3/4;  b="MB"; c=a/1024; d="GB"; e=a; f=b; if (a>10240) {e=c; f=d} printf "%.0f%s\n", e,f}')"
MAINTENANCE_WORK_MEM="$(echo "${RAM_IN_MB}" | awk '{a=$1/100;  b="MB"; c=a/1024; d="GB"; e=a; f=b; if (a>10240) {e=c; f=d} printf "%.0f%s\n", e,f}')"
MAX_CONNECTIONS="$(awk '/^processor[[:space:]]+: /{printf "%.0f\n", $NF*11/10}' /proc/cpuinfo | sort -n | tail -n1)"
SHARED_BUFFERS="$(echo "${RAM_IN_MB}"       | awk '{a=$1/4;    b="MB"; c=a/1024; d="GB"; e=a; f=b; if (a>10240) {e=c; f=d} printf "%.0f%s\n", e,f}')"
WORK_MEM="$(echo "${RAM_IN_MB}"             | awk '{a=$1/10000; b="MB"; c=a/1024; d="GB"; e=a; f=b; if (a>10240) {e=c; f=d} printf "%.0f%s\n", e,f}')"
MAX_WAL_SIZE="$(echo "${RAM_IN_MB}"  | awk '{a=$1/10;   b="MB"; c=a/1024; d="GB"; e=a; f=b; if (a>10240) {e=c; f=d} printf "%.0f%s\n", e,f}')"

echo "# empfohlene Werte, die meistens passen:
autovacuum_work_mem:  ${AUTOVACUUM_WORK_MEM} (ca. 10% vom RAM)
effective_cache_size: ${EFFECTIVE_CACHE_SIZE} (ca. 50-75% vom RAM)
maintenance_work_mem: ${MAINTENANCE_WORK_MEM} (ca. 1-10% vom RAM)
max_connections:      ${MAX_CONNECTIONS} (ca. CPU-Kerne + 10%)
shared_buffers:       ${SHARED_BUFFERS} (ca. 10-25% vom RAM)
max_wal_size:         ${MAX_WAL_SIZE} (ca. 10% vom RAM / eigentlich kein Bezug zum RAM, 50-200GB)
work_mem:             ${WORK_MEM} (ca. 0,1-1% vom RAM; Vorsicht, kann zu OOM-Kill führen!)
"

### neu berechnete Werte, direkt setzen
### patronictl -c /etc/patroni/config.yml edit-config pgcluster
echo "#==============================================================================#"
echo
jq -n \
  --arg ecs "${EFFECTIVE_CACHE_SIZE}" \
  --arg mwm "${MAINTENANCE_WORK_MEM}" \
  --arg mc  "${MAX_CONNECTIONS}" \
  --arg sb  "${SHARED_BUFFERS}" \
  --arg mw  "${MAX_WAL_SIZE}" \
  --arg wm  "${WORK_MEM}" \
'{
  postgresql: {
    parameters: {
      effective_cache_size: $ecs,
      maintenance_work_mem: $mwm,
      max_connections: $mc,
      shared_buffers: $sb,
      max_wal_size: $mw,
      wal_buffers: "1GB",
      checkpoint_timeout: "30min",
      work_mem: $wm
    }
  }
}' | curl -s -X PATCH http://localhost:8008/config \
        -H "Content-Type: application/json" \
        --data-binary @-
echo
echo "#==============================================================================#"

### aktuelle Einstellungen anzeigen
patronictl -c /etc/patroni/config.yml show-config ${CLUSTER_NAME}

### neue Einstellungen aktivieren
### shared_buffers benötigt leider einen Restart
service patroni restart