Install and configure PostgreSQL 17 with performance tuning and security hardening

#!/usr/bin/env bash

set -euo pipefail

# Colors for output
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m' # No Color

# Global variables
POSTGRES_PASSWORD=""
RAM_GB=""
SHARED_BUFFERS=""
EFFECTIVE_CACHE_SIZE=""

# Usage function
usage() {
    echo "Usage: $0 [postgres_password]"
    echo "  postgres_password: Password for PostgreSQL superuser (optional, will prompt if not provided)"
    exit 1
}

# Logging functions
log_info() { echo -e "${BLUE}[INFO]${NC} $1"; }
log_success() { echo -e "${GREEN}[SUCCESS]${NC} $1"; }
log_warning() { echo -e "${YELLOW}[WARNING]${NC} $1"; }
log_error() { echo -e "${RED}[ERROR]${NC} $1"; }

# Cleanup function for rollback
cleanup() {
    log_error "Installation failed. Performing cleanup..."
    systemctl stop postgresql-17 2>/dev/null || systemctl stop postgresql 2>/dev/null || true
    if [[ "$PKG_MGR" == "apt" ]]; then
        apt-get remove --purge -y postgresql-17* 2>/dev/null || true
        rm -f /etc/apt/sources.list.d/pgdg.list /etc/apt/trusted.gpg.d/apt.postgresql.org.gpg
    else
        dnf remove -y postgresql17* 2>/dev/null || yum remove -y postgresql17* 2>/dev/null || true
    fi
}

# Set trap for cleanup on error
trap cleanup ERR

# Check if running as root or with sudo
if [[ $EUID -ne 0 ]]; then
    log_error "This script must be run as root or with sudo"
    exit 1
fi

# Parse arguments
if [[ $# -gt 1 ]]; then
    usage
elif [[ $# -eq 1 ]]; then
    POSTGRES_PASSWORD="$1"
fi

# Auto-detect distribution
if [[ -f /etc/os-release ]]; then
    . /etc/os-release
    case "$ID" in
        ubuntu|debian)
            PKG_MGR="apt"
            PKG_UPDATE="apt update && apt upgrade -y"
            PKG_INSTALL="apt install -y"
            PG_VERSION="17"
            PG_CONFIG_DIR="/etc/postgresql/17/main"
            PG_SERVICE="postgresql"
            ;;
        almalinux|rocky|centos|rhel|ol)
            PKG_MGR="dnf"
            PKG_UPDATE="dnf update -y"
            PKG_INSTALL="dnf install -y"
            PG_VERSION="17"
            PG_CONFIG_DIR="/var/lib/pgsql/17/data"
            PG_SERVICE="postgresql-17"
            ;;
        fedora)
            PKG_MGR="dnf"
            PKG_UPDATE="dnf update -y"
            PKG_INSTALL="dnf install -y"
            PG_VERSION="17"
            PG_CONFIG_DIR="/var/lib/pgsql/17/data"
            PG_SERVICE="postgresql-17"
            ;;
        amzn)
            PKG_MGR="yum"
            PKG_UPDATE="yum update -y"
            PKG_INSTALL="yum install -y"
            PG_VERSION="17"
            PG_CONFIG_DIR="/var/lib/pgsql/17/data"
            PG_SERVICE="postgresql-17"
            ;;
        *)
            log_error "Unsupported distribution: $ID"
            exit 1
            ;;
    esac
else
    log_error "Cannot detect distribution"
    exit 1
fi

log_info "Detected distribution: $PRETTY_NAME"
log_info "Package manager: $PKG_MGR"

# Prompt for password if not provided
if [[ -z "$POSTGRES_PASSWORD" ]]; then
    echo -n "Enter PostgreSQL superuser password: "
    read -s POSTGRES_PASSWORD
    echo
    if [[ -z "$POSTGRES_PASSWORD" ]]; then
        log_error "Password cannot be empty"
        exit 1
    fi
fi

# Calculate memory settings
RAM_KB=$(grep MemTotal /proc/meminfo | awk '{print $2}')
RAM_GB=$((RAM_KB / 1024 / 1024))
SHARED_BUFFERS="${RAM_GB}GB"
EFFECTIVE_CACHE_SIZE="$((RAM_GB * 3))GB"

if [[ $RAM_GB -lt 4 ]]; then
    SHARED_BUFFERS="1GB"
    EFFECTIVE_CACHE_SIZE="2GB"
fi

echo "[1/8] Updating system packages..."
$PKG_UPDATE

echo "[2/8] Adding PostgreSQL official repository..."
if [[ "$PKG_MGR" == "apt" ]]; then
    $PKG_INSTALL wget ca-certificates gnupg
    wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor > /etc/apt/trusted.gpg.d/apt.postgresql.org.gpg
    chmod 644 /etc/apt/trusted.gpg.d/apt.postgresql.org.gpg
    echo "deb http://apt.postgresql.org/pub/repos/apt/ $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list
    chmod 644 /etc/apt/sources.list.d/pgdg.list
    apt update
else
    if command -v dnf &> /dev/null; then
        $PKG_INSTALL https://download.postgresql.org/pub/repos/yum/reporpms/EL-9-x86_64/pgdg-redhat-repo-latest.noarch.rpm
    else
        $PKG_INSTALL https://download.postgresql.org/pub/repos/yum/reporpms/EL-7-x86_64/pgdg-redhat-repo-latest.noarch.rpm
    fi
fi

echo "[3/8] Installing PostgreSQL 17..."
if [[ "$PKG_MGR" == "apt" ]]; then
    $PKG_INSTALL postgresql-17 postgresql-client-17 postgresql-contrib-17
else
    $PKG_INSTALL postgresql17-server postgresql17 postgresql17-contrib
fi

echo "[4/8] Initializing PostgreSQL database..."
if [[ "$PKG_MGR" == "apt" ]]; then
    systemctl start $PG_SERVICE
    systemctl enable $PG_SERVICE
else
    /usr/pgsql-17/bin/postgresql-17-setup initdb
    systemctl enable --now $PG_SERVICE
fi

echo "[5/8] Setting PostgreSQL superuser password..."
sleep 2
sudo -u postgres psql -c "ALTER USER postgres PASSWORD '$POSTGRES_PASSWORD';"

echo "[6/8] Configuring performance settings..."
PG_CONF="$PG_CONFIG_DIR/postgresql.conf"
cp "$PG_CONF" "$PG_CONF.backup"

cat >> "$PG_CONF" << EOF

# Performance Configuration Added by Install Script
shared_buffers = $SHARED_BUFFERS
effective_cache_size = $EFFECTIVE_CACHE_SIZE
work_mem = 32MB
maintenance_work_mem = 512MB

# Checkpoint Configuration
checkpoint_completion_target = 0.9
wal_buffers = 64MB
max_wal_size = 4GB
min_wal_size = 1GB

# Query Planner
random_page_cost = 1.1
effective_io_concurrency = 200

# Connection Settings
max_connections = 200

# Logging
log_destination = 'stderr'
logging_collector = on
log_directory = 'log'
log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log'
log_min_duration_statement = 1000

# Extensions
shared_preload_libraries = 'pg_stat_statements'
EOF

echo "[7/8] Configuring security hardening..."
PG_HBA="$PG_CONFIG_DIR/pg_hba.conf"
cp "$PG_HBA" "$PG_HBA.backup"

cat > "$PG_HBA" << EOF
# PostgreSQL Client Authentication Configuration File
# TYPE  DATABASE        USER            ADDRESS                 METHOD

# Local connections
local   all             postgres                                peer
local   all             all                                     md5

# IPv4 local connections
host    all             all             127.0.0.1/32            md5

# IPv6 local connections
host    all             all             ::1/128                 md5
EOF

chmod 640 "$PG_HBA"
if [[ "$PKG_MGR" == "apt" ]]; then
    chown postgres:postgres "$PG_HBA"
else
    chown postgres:postgres "$PG_HBA"
fi

# Configure firewall
if command -v ufw &> /dev/null; then
    ufw --force enable
    ufw allow 22/tcp
elif command -v firewall-cmd &> /dev/null; then
    systemctl enable --now firewalld
    firewall-cmd --permanent --add-service=ssh
    firewall-cmd --reload
fi

echo "[8/8] Restarting PostgreSQL and verifying installation..."
systemctl restart $PG_SERVICE

# Verification checks
sleep 3
if systemctl is-active --quiet $PG_SERVICE; then
    log_success "PostgreSQL service is running"
else
    log_error "PostgreSQL service is not running"
    exit 1
fi

# Test database connection
if sudo -u postgres psql -c "SELECT version();" &>/dev/null; then
    log_success "Database connection test passed"
else
    log_error "Database connection test failed"
    exit 1
fi

# Display final information
log_success "PostgreSQL 17 installation completed successfully!"
echo
log_info "Configuration details:"
echo "  - PostgreSQL version: $(sudo -u postgres psql -t -c 'SELECT version();' | head -1 | xargs)"
echo "  - Service name: $PG_SERVICE"
echo "  - Config directory: $PG_CONFIG_DIR"
echo "  - RAM detected: ${RAM_GB}GB"
echo "  - Shared buffers: $SHARED_BUFFERS"
echo "  - Effective cache size: $EFFECTIVE_CACHE_SIZE"
echo
log_info "Next steps:"
echo "  1. Create application databases and users"
echo "  2. Configure network access in pg_hba.conf if needed"
echo "  3. Set up regular backups"
echo "  4. Monitor performance and adjust settings as needed"
echo
log_warning "Backup files created:"
echo "  - $PG_CONF.backup"
echo "  - $PG_HBA.backup"