Install and configure TimescaleDB with PostgreSQL for high-performance time-series data

#!/usr/bin/env bash

set -euo pipefail

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

# Default values
DB_NAME="${1:-timeseries_db}"
DB_USER="${2:-tsdb_user}"
DB_PASSWORD="${3:-$(openssl rand -base64 32)}"

usage() {
    echo "Usage: $0 [database_name] [username] [password]"
    echo "Example: $0 metrics_db app_user mypassword"
    echo "If password is omitted, a random one will be generated"
    exit 1
}

log() {
    echo -e "${GREEN}[INFO]${NC} $1"
}

warn() {
    echo -e "${YELLOW}[WARN]${NC} $1"
}

error() {
    echo -e "${RED}[ERROR]${NC} $1"
    exit 1
}

cleanup() {
    warn "Installation failed. Check logs above for details."
    exit 1
}

trap cleanup ERR

# Check if running with sudo/root
if [[ $EUID -ne 0 && -z "${SUDO_USER:-}" ]]; then
    error "This script must be run with sudo or as root"
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"
            PKG_INSTALL="apt install -y"
            PKG_UPGRADE="apt upgrade -y"
            PG_VERSION="16"
            PG_SERVICE="postgresql"
            PG_CONFIG_DIR="/etc/postgresql/$PG_VERSION/main"
            PG_DATA_DIR="/var/lib/postgresql/$PG_VERSION/main"
            ;;
        almalinux|rocky|centos|rhel|ol|fedora)
            if command -v dnf &> /dev/null; then
                PKG_MGR="dnf"
                PKG_UPDATE="dnf check-update || true"
                PKG_INSTALL="dnf install -y"
                PKG_UPGRADE="dnf update -y"
            else
                PKG_MGR="yum"
                PKG_UPDATE="yum check-update || true"
                PKG_INSTALL="yum install -y"
                PKG_UPGRADE="yum update -y"
            fi
            PG_VERSION="16"
            PG_SERVICE="postgresql"
            PG_CONFIG_DIR="/var/lib/pgsql/data"
            PG_DATA_DIR="/var/lib/pgsql/data"
            ;;
        amzn)
            PKG_MGR="yum"
            PKG_UPDATE="yum check-update || true"
            PKG_INSTALL="yum install -y"
            PKG_UPGRADE="yum update -y"
            PG_VERSION="16"
            PG_SERVICE="postgresql"
            PG_CONFIG_DIR="/var/lib/pgsql/data"
            PG_DATA_DIR="/var/lib/pgsql/data"
            ;;
        *)
            error "Unsupported distribution: $ID"
            ;;
    esac
else
    error "Cannot detect distribution. /etc/os-release not found."
fi

log "Detected distribution: $ID"
log "Package manager: $PKG_MGR"

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

echo "[2/8] Installing required tools..."
case "$ID" in
    ubuntu|debian)
        $PKG_INSTALL wget curl gnupg2 lsb-release ca-certificates openssl
        ;;
    *)
        $PKG_INSTALL wget curl gnupg2 ca-certificates openssl
        ;;
esac

echo "[3/8] Installing PostgreSQL..."
case "$ID" in
    ubuntu|debian)
        # Add PostgreSQL official repository
        curl -fsSL https://www.postgresql.org/media/keys/ACCC4CF8.asc | gpg --dearmor -o /usr/share/keyrings/postgresql-keyring.gpg
        echo "deb [signed-by=/usr/share/keyrings/postgresql-keyring.gpg] http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list
        $PKG_UPDATE
        $PKG_INSTALL postgresql-$PG_VERSION postgresql-client-$PG_VERSION postgresql-contrib-$PG_VERSION
        ;;
    almalinux|rocky|centos|rhel|ol)
        $PKG_INSTALL https://download.postgresql.org/pub/repos/yum/reporpms/EL-$(rpm -E %{rhel})-x86_64/pgdg-redhat-repo-latest.noarch.rpm
        $PKG_INSTALL postgresql$PG_VERSION-server postgresql$PG_VERSION postgresql$PG_VERSION-contrib
        /usr/pgsql-$PG_VERSION/bin/postgresql-$PG_VERSION-setup initdb
        ;;
    fedora)
        $PKG_INSTALL https://download.postgresql.org/pub/repos/yum/reporpms/F-$(rpm -E %{fedora})-x86_64/pgdg-fedora-repo-latest.noarch.rpm
        $PKG_INSTALL postgresql$PG_VERSION-server postgresql$PG_VERSION postgresql$PG_VERSION-contrib
        /usr/pgsql-$PG_VERSION/bin/postgresql-$PG_VERSION-setup initdb
        ;;
    amzn)
        $PKG_INSTALL postgresql-server postgresql postgresql-contrib
        postgresql-setup initdb
        ;;
esac

echo "[4/8] Starting PostgreSQL service..."
systemctl enable $PG_SERVICE
systemctl start $PG_SERVICE
systemctl is-active $PG_SERVICE || error "Failed to start PostgreSQL"

echo "[5/8] Adding TimescaleDB repository..."
case "$ID" in
    ubuntu|debian)
        curl -s https://packagecloud.io/install/repositories/timescale/timescaledb/script.deb.sh | bash
        $PKG_INSTALL timescaledb-2-postgresql-$PG_VERSION
        ;;
    *)
        curl -s https://packagecloud.io/install/repositories/timescale/timescaledb/script.rpm.sh | bash
        $PKG_INSTALL timescaledb-2-postgresql$PG_VERSION
        ;;
esac

echo "[6/8] Configuring TimescaleDB..."
# Configure PostgreSQL for TimescaleDB
case "$ID" in
    ubuntu|debian)
        PG_CONFIG="$PG_CONFIG_DIR/postgresql.conf"
        PG_HBA="$PG_CONFIG_DIR/pg_hba.conf"
        ;;
    *)
        PG_CONFIG="$PG_CONFIG_DIR/postgresql.conf"
        PG_HBA="$PG_CONFIG_DIR/pg_hba.conf"
        ;;
esac

# Backup original config
cp "$PG_CONFIG" "$PG_CONFIG.backup"

# Add TimescaleDB to shared_preload_libraries
if ! grep -q "timescaledb" "$PG_CONFIG"; then
    echo "shared_preload_libraries = 'timescaledb'" >> "$PG_CONFIG"
fi

# Optimize for time-series workload
cat >> "$PG_CONFIG" << EOF

# TimescaleDB optimizations
max_connections = 100
shared_buffers = 256MB
effective_cache_size = 1GB
maintenance_work_mem = 64MB
checkpoint_completion_target = 0.7
wal_buffers = 16MB
default_statistics_target = 100
random_page_cost = 1.1
effective_io_concurrency = 200
work_mem = 4MB
min_wal_size = 1GB
max_wal_size = 4GB
max_worker_processes = 8
max_parallel_workers_per_gather = 2
max_parallel_workers = 8
max_parallel_maintenance_workers = 2
EOF

chown postgres:postgres "$PG_CONFIG"
chmod 644 "$PG_CONFIG"

echo "[7/8] Restarting PostgreSQL and creating database..."
systemctl restart $PG_SERVICE

# Create database and user
sudo -u postgres psql << EOF
CREATE DATABASE $DB_NAME;
CREATE USER $DB_USER WITH ENCRYPTED PASSWORD '$DB_PASSWORD';
GRANT ALL PRIVILEGES ON DATABASE $DB_NAME TO $DB_USER;
\q
EOF

# Enable TimescaleDB extension
sudo -u postgres psql -d "$DB_NAME" << EOF
CREATE EXTENSION IF NOT EXISTS timescaledb CASCADE;
\q
EOF

echo "[8/8] Verifying installation..."

# Test TimescaleDB installation
TSDB_VERSION=$(sudo -u postgres psql -d "$DB_NAME" -t -c "SELECT extversion FROM pg_extension WHERE extname='timescaledb';" | xargs)

if [[ -n "$TSDB_VERSION" ]]; then
    log "TimescaleDB $TSDB_VERSION installed successfully!"
else
    error "TimescaleDB installation verification failed"
fi

# Configure firewall if active
if systemctl is-active firewalld &>/dev/null; then
    firewall-cmd --permanent --add-port=5432/tcp
    firewall-cmd --reload
    log "Firewall configured to allow PostgreSQL connections"
elif systemctl is-active ufw &>/dev/null; then
    ufw allow 5432/tcp
    log "UFW configured to allow PostgreSQL connections"
fi

# Generate sample hypertable creation script
cat > /tmp/timescaledb_sample.sql << EOF
-- Sample TimescaleDB hypertable creation
-- Run as: psql -d $DB_NAME -f /tmp/timescaledb_sample.sql

-- Create a sample metrics table
CREATE TABLE metrics (
  time TIMESTAMPTZ NOT NULL,
  device_id INTEGER,
  temperature DOUBLE PRECISION,
  humidity DOUBLE PRECISION
);

-- Convert to hypertable (partitioned by time)
SELECT create_hypertable('metrics', 'time');

-- Add compression policy (compress data older than 7 days)
SELECT add_compression_policy('metrics', INTERVAL '7 days');

-- Add retention policy (drop data older than 1 year)
SELECT add_retention_policy('metrics', INTERVAL '1 year');

-- Create index for better query performance
CREATE INDEX idx_metrics_device_time ON metrics (device_id, time DESC);
EOF

chown postgres:postgres /tmp/timescaledb_sample.sql
chmod 644 /tmp/timescaledb_sample.sql

log "Installation completed successfully!"
echo
echo "Database Configuration:"
echo "  Database: $DB_NAME"
echo "  Username: $DB_USER"
echo "  Password: $DB_PASSWORD"
echo
echo "Connection string:"
echo "  postgresql://$DB_USER:$DB_PASSWORD@localhost:5432/$DB_NAME"
echo
echo "Sample hypertable script available at: /tmp/timescaledb_sample.sql"
echo "Run: sudo -u postgres psql -d $DB_NAME -f /tmp/timescaledb_sample.sql"