Index: /branches/amp_4_0/platform/tools/install_pgbouncer.sh
===================================================================
--- /branches/amp_4_0/platform/tools/install_pgbouncer.sh	(nonexistent)
+++ /branches/amp_4_0/platform/tools/install_pgbouncer.sh	(working copy)
@@ -0,0 +1,121 @@
+#!/bin/bash
+
+PG_HOST="127.0.0.1"
+PG_PORT="5432"
+PG_DB="cm"
+PG_USER="amp_admin"
+PG_PASS="Array@123$"
+
+PGBOUNCER_PORT="6432"
+PGBOUNCER_LISTEN_ADDR="0.0.0.0"
+POOL_MODE="transaction"
+DEFAULT_POOL_SIZE="50"
+RESERVE_POOL_SIZE="10"
+MAX_CLIENT_CONN="1000"
+
+# Paths
+PGBOUNCER_INI="/etc/pgbouncer/pgbouncer.ini"
+USERLIST="/etc/pgbouncer/userlist.txt"
+PGBOUNCER_LOG_DIR="/var/log/pgbouncer"
+LOG_FILE="${PGBOUNCER_LOG_DIR}/pgbouncer.log"
+
+# --- Script Start ---
+echo "[+] Starting PgBouncer setup script (single user mode)..."
+
+# Ensure script is run as root
+if [ "$EUID" -ne 0 ]; then
+    echo "ERROR: This script must be run as root."
+    exit 1
+fi
+
+# Install PgBouncer and Firewalld
+echo "[+] Installing PgBouncer and Firewalld..."
+dnf install -y pgbouncer firewalld || { echo "ERROR: Installation failed. Exiting."; exit 1; }
+
+# Create log directory and set permissions
+echo "[+] Creating PgBouncer log directory: ${PGBOUNCER_LOG_DIR}"
+mkdir -p "$PGBOUNCER_LOG_DIR"
+chown pgbouncer:pgbouncer "$PGBOUNCER_LOG_DIR"
+chmod 750 "$PGBOUNCER_LOG_DIR"
+touch "$LOG_FILE"
+chown pgbouncer:pgbouncer "$LOG_FILE"
+chmod 640 "$LOG_FILE"
+
+# --- Create pgbouncer.ini ---
+echo "[+] Creating pgbouncer.ini..."
+cat > "$PGBOUNCER_INI" <<EOF
+[databases]
+${PG_DB} = host=${PG_HOST} port=${PG_PORT} dbname=${PG_DB} user=${PG_USER} password=${PG_PASS}
+
+[pgbouncer]
+listen_addr = ${PGBOUNCER_LISTEN_ADDR}
+listen_port = ${PGBOUNCER_PORT}
+auth_type = md5
+auth_file = ${USERLIST}
+pool_mode = ${POOL_MODE}
+max_client_conn = ${MAX_CLIENT_CONN}
+default_pool_size = ${DEFAULT_POOL_SIZE}
+reserve_pool_size = ${RESERVE_POOL_SIZE}
+log_connections = 1
+log_disconnections = 1
+log_pooler_errors = 1
+stats_period = 60
+verbose = 0
+logfile = ${LOG_FILE}
+pidfile = /run/pgbouncer/pgbouncer.pid
+admin_users = ${PG_USER}
+stats_users = ${PG_USER}
+; Timeout settings (in seconds)
+server_check_delay = 10
+server_connect_timeout = 5
+server_lifetime = 3600
+server_idle_timeout = 60
+client_idle_timeout = 300
+query_timeout = 30
+EOF
+
+# --- Generate MD5 passwords for userlist.txt ---
+echo "[+] Generating MD5 password for userlist.txt (single user: ${PG_USER})..."
+# Format: "username" "md5hash"
+MD5_HASH_APP=$(echo -n "${PG_PASS}${PG_USER}" | md5sum | awk '{print "md5"$1}')
+
+echo "[+] Creating userlist.txt..."
+cat > "$USERLIST" <<EOF
+"${PG_USER}" "${MD5_HASH_APP}"
+EOF
+
+# --- Set secure permissions ---
+echo "[+] Setting secure permissions for configuration files..."
+chown pgbouncer:pgbouncer "$PGBOUNCER_INI" "$USERLIST"
+chmod 600 "$PGBOUNCER_INI" "$USERLIST"
+
+# --- Configure firewalld ---
+echo "[+] Configuring firewalld to allow PgBouncer traffic on port ${PGBOUNCER_PORT}..."
+systemctl enable --now firewalld || { echo "WARNING: Failed to enable/start firewalld. Check status manually."; }
+firewall-cmd --permanent --add-port=${PGBOUNCER_PORT}/tcp || { echo "WARNING: Failed to add firewall rule. Check status manually."; }
+firewall-cmd --reload || { echo "WARNING: Failed to reload firewalld. Check status manually."; }
+
+# --- Enable and start PgBouncer ---
+echo "[+] Enabling and starting PgBouncer service..."
+systemctl enable pgbouncer || { echo "ERROR: Failed to enable pgbouncer service. Exiting."; exit 1; }
+systemctl restart pgbouncer || { echo "ERROR: Failed to restart pgbouncer service. Exiting."; exit 1; }
+
+# --- Verify PgBouncer status ---
+echo "[+] Verifying PgBouncer status..."
+systemctl status pgbouncer --no-pager
+
+echo "[+] PgBouncer log file: $LOG_FILE"
+echo "  Use: journalctl -u pgbouncer -f        (for real-time system log)"
+echo "  Or:  tail -f $LOG_FILE                   (for dedicated log file)"
+
+echo "[+] Monitoring commands:"
+echo "  To connect to PgBouncer (for both app and admin access): psql -h 127.0.0.1 -p ${PGBOUNCER_PORT} -U ${PG_USER} -d ${PG_DB}"
+echo "  Then run (inside psql to pgbouncer):"
+echo "    SHOW POOLS;"
+echo "    SHOW STATS;"
+echo "    SHOW CLIENTS;"
+echo "    SHOW SERVERS;"
+echo "    -- For admin commands, you might need to connect to the special 'pgbouncer' database:"
+echo "    -- psql -h 127.0.0.1 -p ${PGBOUNCER_PORT} -U ${PG_USER} -d pgbouncer"
+
+echo "[✅] PgBouncer installation and setup complete (single user mode). Remember to secure your passwords!"