Configure TimescaleDB backup and recovery with pgBackRest for automated PostgreSQL protection

#!/usr/bin/env bash

set -euo pipefail

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

# Configuration
PG_VERSION="${PG_VERSION:-14}"
DB_NAME="${1:-main}"
BACKUP_RETENTION_FULL="${BACKUP_RETENTION_FULL:-7}"
BACKUP_RETENTION_DIFF="${BACKUP_RETENTION_DIFF:-14}"

# Function definitions
log_info() { echo -e "${GREEN}[INFO]${NC} $1"; }
log_warn() { echo -e "${YELLOW}[WARN]${NC} $1"; }
log_error() { echo -e "${RED}[ERROR]${NC} $1"; }

usage() {
    echo "Usage: $0 [stanza_name]"
    echo "  stanza_name: pgBackRest stanza name (default: main)"
    exit 1
}

cleanup() {
    log_error "Installation failed. Check logs above."
    exit 1
}

trap cleanup ERR

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

# Detect distribution
if [ ! -f /etc/os-release ]; then
    log_error "/etc/os-release not found. Cannot detect distribution."
    exit 1
fi

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

log_info "Detected distribution: $ID"
log_info "PostgreSQL version: $PG_VERSION"

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

echo "[2/12] Installing pgBackRest..."
$PKG_INSTALL pgbackrest

echo "[3/12] Creating pgBackRest repository directory..."
mkdir -p /var/lib/pgbackrest
chown postgres:postgres /var/lib/pgbackrest
chmod 750 /var/lib/pgbackrest

echo "[4/12] Creating pgBackRest configuration directory..."
mkdir -p /etc/pgbackrest

echo "[5/12] Creating pgBackRest configuration file..."
cat > /etc/pgbackrest/pgbackrest.conf << EOF
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=$BACKUP_RETENTION_FULL
repo1-retention-diff=$BACKUP_RETENTION_DIFF
repo1-retention-archive=$BACKUP_RETENTION_DIFF
log-level-console=info
log-level-file=debug
log-path=/var/log/pgbackrest
log-timestamp=n
process-max=4
start-fast=y
stop-auto=y
archive-async=y
spool-path=/var/spool/pgbackrest
lock-path=/tmp/pgbackrest

[$DB_NAME]
pg1-path=$PG_DATA_DIR
pg1-port=5432
EOF

# Add socket path for Debian-based systems
if [[ "$ID" == "ubuntu" || "$ID" == "debian" ]]; then
    echo "pg1-socket-path=/var/run/postgresql" >> /etc/pgbackrest/pgbackrest.conf
fi

echo "[6/12] Creating pgBackRest directories..."
mkdir -p /var/log/pgbackrest /var/spool/pgbackrest
chown postgres:postgres /var/log/pgbackrest /var/spool/pgbackrest
chmod 750 /var/log/pgbackrest /var/spool/pgbackrest

echo "[7/12] Configuring PostgreSQL for archiving..."
PG_CONF="$PG_CONFIG_DIR/postgresql.conf"

if [ ! -f "$PG_CONF" ]; then
    log_error "PostgreSQL configuration file not found at $PG_CONF"
    log_error "Please ensure PostgreSQL $PG_VERSION is installed"
    exit 1
fi

# Backup original config
cp "$PG_CONF" "$PG_CONF.backup.$(date +%Y%m%d_%H%M%S)"

# Configure PostgreSQL parameters
sed -i "s/#*wal_level = .*/wal_level = replica/" "$PG_CONF"
sed -i "s/#*archive_mode = .*/archive_mode = on/" "$PG_CONF"
sed -i "s/#*archive_command = .*/archive_command = 'pgbackrest --stanza=$DB_NAME archive-push %p'/" "$PG_CONF"
sed -i "s/#*max_wal_senders = .*/max_wal_senders = 3/" "$PG_CONF"

# Add parameters if they don't exist
if ! grep -q "^wal_level" "$PG_CONF"; then
    echo "wal_level = replica" >> "$PG_CONF"
fi
if ! grep -q "^archive_mode" "$PG_CONF"; then
    echo "archive_mode = on" >> "$PG_CONF"
fi
if ! grep -q "^archive_command" "$PG_CONF"; then
    echo "archive_command = 'pgbackrest --stanza=$DB_NAME archive-push %p'" >> "$PG_CONF"
fi
if ! grep -q "^max_wal_senders" "$PG_CONF"; then
    echo "max_wal_senders = 3" >> "$PG_CONF"
fi

# Add wal_keep_size for PostgreSQL 13+
if [[ $PG_VERSION -ge 13 ]]; then
    if ! grep -q "^wal_keep_size" "$PG_CONF"; then
        echo "wal_keep_size = 1024MB" >> "$PG_CONF"
    fi
fi

echo "[8/12] Setting pgBackRest configuration permissions..."
chown postgres:postgres /etc/pgbackrest/pgbackrest.conf
chmod 640 /etc/pgbackrest/pgbackrest.conf

echo "[9/12] Restarting PostgreSQL..."
systemctl restart $PG_SERVICE
systemctl enable $PG_SERVICE
sleep 5

if ! systemctl is-active --quiet $PG_SERVICE; then
    log_error "PostgreSQL failed to start. Check logs: journalctl -u $PG_SERVICE"
    exit 1
fi

echo "[10/12] Initializing pgBackRest stanza..."
sudo -u postgres pgbackrest --stanza=$DB_NAME stanza-create

echo "[11/12] Performing initial full backup..."
sudo -u postgres pgbackrest --stanza=$DB_NAME backup --type=full

echo "[12/12] Setting up automated backup scheduling..."
cat > /etc/systemd/system/pgbackrest-backup.service << EOF
[Unit]
Description=pgBackRest TimescaleDB Backup
Wants=$PG_SERVICE.service
After=$PG_SERVICE.service

[Service]
Type=oneshot
User=postgres
Group=postgres
ExecStart=/usr/bin/pgbackrest --stanza=$DB_NAME backup --type=diff
StandardOutput=journal
StandardError=journal
EOF

cat > /etc/systemd/system/pgbackrest-backup.timer << EOF
[Unit]
Description=Run pgBackRest backup daily
Requires=pgbackrest-backup.service

[Timer]
OnCalendar=daily
Persistent=true
RandomizedDelaySec=300

[Install]
WantedBy=timers.target
EOF

systemctl daemon-reload
systemctl enable pgbackrest-backup.timer
systemctl start pgbackrest-backup.timer

# Verification
echo
log_info "Verifying installation..."
if sudo -u postgres pgbackrest --stanza=$DB_NAME info > /dev/null 2>&1; then
    log_info "✓ pgBackRest stanza created successfully"
else
    log_error "✗ pgBackRest stanza verification failed"
fi

if systemctl is-active --quiet pgbackrest-backup.timer; then
    log_info "✓ Backup timer is active"
else
    log_warn "✗ Backup timer is not active"
fi

echo
log_info "pgBackRest installation completed successfully!"
log_info "Configuration file: /etc/pgbackrest/pgbackrest.conf"
log_info "Repository path: /var/lib/pgbackrest"
log_info "Log path: /var/log/pgbackrest"
log_info ""
log_info "Common commands:"
log_info "  Check backup info: sudo -u postgres pgbackrest --stanza=$DB_NAME info"
log_info "  Manual full backup: sudo -u postgres pgbackrest --stanza=$DB_NAME backup --type=full"
log_info "  Manual diff backup: sudo -u postgres pgbackrest --stanza=$DB_NAME backup --type=diff"
log_info "  Timer status: systemctl status pgbackrest-backup.timer"