👁47views

EC2 to Raspberry Pi WordPress Migration: Full Guide

services:

  mariadb:
    image: mariadb:11
    container_name: pi_mariadb
    restart: unless-stopped
    environment:
      MYSQL_ROOT_PASSWORD: ${MYSQL_ROOT_PASSWORD}
      MYSQL_DATABASE:      ${MYSQL_DATABASE}
      MYSQL_USER:          ${MYSQL_USER}
      MYSQL_PASSWORD:      ${MYSQL_PASSWORD}
    volumes:
      - db_data:/var/lib/mysql
      - ./mariadb/my.cnf:/etc/mysql/conf.d/pi-tuning.cnf:ro
    networks:
      - backend
    deploy:
      resources:
        limits:
          memory: 2G
        reservations:
          memory: 512M
    healthcheck:
      test: ["CMD", "healthcheck.sh", "--connect", "--innodb_initialized"]
      interval: 10s
      timeout: 5s
      retries: 5
      start_period: 30s

  redis:
    image: redis:7-alpine
    container_name: pi_redis
    restart: unless-stopped
    command: >
      redis-server
        --requirepass ${REDIS_PASSWORD}
        --maxmemory 512mb
        --maxmemory-policy allkeys-lru
        --save ""
        --appendonly no
    networks:
      - backend
    deploy:
      resources:
        limits:
          memory: 640M
        reservations:
          memory: 128M
    healthcheck:
      test: ["CMD", "redis-cli", "-a", "${REDIS_PASSWORD}", "ping"]
      interval: 10s
      timeout: 3s
      retries: 5

  wordpress:
    build:
      context: ./wordpress
      dockerfile: Dockerfile
    image: pi_wordpress_custom
    container_name: pi_wordpress
    restart: unless-stopped
    depends_on:
      mariadb:
        condition: service_healthy
      redis:
        condition: service_healthy
    environment:
      WORDPRESS_DB_HOST:      ${WORDPRESS_DB_HOST}
      WORDPRESS_DB_NAME:      ${WORDPRESS_DB_NAME}
      WORDPRESS_DB_USER:      ${WORDPRESS_DB_USER}
      WORDPRESS_DB_PASSWORD:  ${WORDPRESS_DB_PASSWORD}
      WORDPRESS_TABLE_PREFIX: ${WORDPRESS_TABLE_PREFIX}
      WORDPRESS_AUTH_KEY:         ${WORDPRESS_AUTH_KEY}
      WORDPRESS_SECURE_AUTH_KEY:  ${WORDPRESS_SECURE_AUTH_KEY}
      WORDPRESS_LOGGED_IN_KEY:    ${WORDPRESS_LOGGED_IN_KEY}
      WORDPRESS_NONCE_KEY:        ${WORDPRESS_NONCE_KEY}
      WORDPRESS_AUTH_SALT:        ${WORDPRESS_AUTH_SALT}
      WORDPRESS_SECURE_AUTH_SALT: ${WORDPRESS_SECURE_AUTH_SALT}
      WORDPRESS_LOGGED_IN_SALT:   ${WORDPRESS_LOGGED_IN_SALT}
      WORDPRESS_NONCE_SALT:       ${WORDPRESS_NONCE_SALT}
      WORDPRESS_CONFIG_EXTRA: |
        define('WP_REDIS_HOST', 'redis');
        define('WP_REDIS_PASSWORD', '${REDIS_PASSWORD}');
        define('WP_REDIS_PORT', 6379);
        define('WP_REDIS_DATABASE', 0);
        define('WP_REDIS_TIMEOUT', 1);
        define('WP_REDIS_READ_TIMEOUT', 1);
        define('WP_REDIS_PREFIX', 'ab_');
        define('WP_POST_REVISIONS', 3);
        define('AUTOSAVE_INTERVAL', 120);
        define('EMPTY_TRASH_DAYS', 7);
        define('WP_MEMORY_LIMIT', '256M');
        define('WP_MAX_MEMORY_LIMIT', '512M');
        if (isset($_SERVER['HTTP_X_FORWARDED_PROTO']) && $_SERVER['HTTP_X_FORWARDED_PROTO'] === 'https') {
            $_SERVER['HTTPS'] = 'on';
        }
        define('FORCE_SSL_ADMIN', true);
        define('WP_HOME',    '${WORDPRESS_HOME_URL}');
        define('WP_SITEURL', '${WORDPRESS_SITE_URL}');
    volumes:
      - wp_data:/var/www/html
      - ./php/php.ini:/usr/local/etc/php/conf.d/99-custom.ini:ro
      - ./php/www.conf:/usr/local/etc/php-fpm.d/www.conf:ro
      - ./wordpress/mu-plugins:/var/www/html/wp-content/mu-plugins:ro
    networks:
      - backend
    deploy:
      resources:
        limits:
          memory: 3G
        reservations:
          memory: 256M

  nginx:
    image: nginx:1.27-alpine
    container_name: pi_nginx
    restart: unless-stopped
    depends_on:
      - wordpress
    ports:
      - "8082:80"
    volumes:
      - wp_data:/var/www/html:ro
      - ./nginx/nginx.conf:/etc/nginx/nginx.conf:ro
      - ./nginx/wordpress.conf:/etc/nginx/conf.d/default.conf:ro
      - nginx_cache:/var/cache/nginx/fastcgi
    networks:
      - backend
    deploy:
      resources:
        limits:
          memory: 256M
        reservations:
          memory: 64M
    healthcheck:
      test: ["CMD", "wget", "-qO-", "http://127.0.0.1/nginx-health"]
      interval: 15s
      timeout: 5s
      retries: 3

networks:
  backend:
    driver: bridge
    ipam:
      config:
        - subnet: 172.20.0.0/24

volumes:
  db_data:
    driver: local
  wp_data:
    driver: local
  nginx_cache:
    driver: local

map $http_cookie $skip_cache {
    default 0;
    "~wordpress_logged_in" 1;
    "~comment_author"      1;
    "~woocommerce_cart"    1;
}

map $request_method $skip_cache_method {
    default 0;
    POST    1;
}

server {
    listen 80;
    server_name _;
    root  /var/www/html;
    index index.php;

    location = /nginx-health {
        access_log off;
        return 200 "ok\n";
        add_header Content-Type text/plain;
    }

    location ~* \.(css|js|woff2?|ttf|otf|eot|svg|ico|webp|png|jpg|jpeg|gif|mp4|webm)$ {
        expires     1y;
        add_header  Cache-Control "public, immutable";
        log_not_found off;
        access_log  off;
        try_files   $uri =404;
    }

    location ^~ /wp-content/uploads/ {
        expires     30d;
        add_header  Cache-Control "public";
        try_files   $uri =404;
    }

    location = /xmlrpc.php { deny all; access_log off; log_not_found off; }
    location ~ /\.          { deny all; access_log off; log_not_found off; }
    location / { try_files $uri $uri/ /index.php?$args; }

    location ~ \.php$ {
        if ($uri ~* "^/wp-content/uploads/") { return 403; }
        try_files $uri =404;
        fastcgi_split_path_info ^(.+\.php)(/.+)$;
        fastcgi_pass   wordpress:9000;
        fastcgi_index  index.php;
        include        fastcgi_params;
        fastcgi_param  SCRIPT_FILENAME  $document_root$fastcgi_script_name;
        fastcgi_param  PATH_INFO        $fastcgi_path_info;
        fastcgi_param  HTTPS            on;
        fastcgi_connect_timeout 10s;
        fastcgi_send_timeout    60s;
        fastcgi_read_timeout    60s;
        fastcgi_buffer_size          16k;
        fastcgi_buffers              4 32k;
        fastcgi_busy_buffers_size    64k;
        fastcgi_cache         WP_CACHE;
        fastcgi_cache_valid   200 301 302  1h;
        fastcgi_cache_valid   404          1m;
        fastcgi_cache_use_stale error timeout updating invalid_header http_500;
        fastcgi_cache_lock    on;
        fastcgi_cache_bypass  $skip_cache $skip_cache_method;
        fastcgi_no_cache      $skip_cache $skip_cache_method;
        add_header  X-Cache-Status $upstream_cache_status always;
        add_header  Cache-Control "no-store, private" always;
    }

    location ~* /wp-admin/ {
        try_files $uri $uri/ /index.php?$args;
        location ~ \.php$ {
            try_files $uri =404;
            fastcgi_split_path_info ^(.+\.php)(/.+)$;
            fastcgi_pass  wordpress:9000;
            fastcgi_index index.php;
            include       fastcgi_params;
            fastcgi_param SCRIPT_FILENAME $document_root$fastcgi_script_name;
            fastcgi_param HTTPS on;
            fastcgi_cache off;
        }
    }
}

[mysqld]
character-set-server  = utf8mb4
collation-server      = utf8mb4_unicode_ci
innodb_buffer_pool_size         = 1G
innodb_log_file_size            = 128M
innodb_log_buffer_size          = 32M
innodb_flush_log_at_trx_commit  = 2
innodb_read_io_threads          = 4
innodb_write_io_threads         = 4
innodb_io_capacity              = 2000
innodb_io_capacity_max          = 4000
max_connections         = 100
max_connect_errors      = 1000
wait_timeout            = 180
interactive_timeout     = 180
thread_stack            = 256K
thread_cache_size       = 16
query_cache_type        = 0
query_cache_size        = 0
aria_pagecache_buffer_size = 256M
tmp_table_size          = 64M
max_heap_table_size     = 64M
slow_query_log          = 1
slow_query_log_file     = /var/lib/mysql/slow-queries.log
long_query_time         = 1
skip-log-bin
skip-name-resolve
open_files_limit        = 8192
table_open_cache        = 1000
table_definition_cache  = 1000

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4
The PHP-FPM pool configuration (php/www.conf):

[www]
user  = www-data
group = www-data
listen = 0.0.0.0:9000
pm                    = static
pm.max_children       = 50
pm.max_requests       = 1000
request_terminate_timeout = 60s
access.log            = /dev/null
php_flag[display_errors] = off
php_admin_value[error_log] = /dev/stderr
php_admin_flag[log_errors] = on
pm.status_path        = /fpm-status
ping.path             = /fpm-ping

MYSQL_ROOT_PASSWORD=changeme_root
MYSQL_DATABASE=wordpress
MYSQL_USER=wordpress
MYSQL_PASSWORD=changeme_wp
WORDPRESS_DB_HOST=mariadb:3306
WORDPRESS_DB_NAME=wordpress
WORDPRESS_DB_USER=wordpress
WORDPRESS_DB_PASSWORD=changeme_wp
WORDPRESS_TABLE_PREFIX=wp_

# Generate fresh values at: https://api.wordpress.org/secret-key/1.1/salt/
WORDPRESS_AUTH_KEY=put-a-random-string-here
WORDPRESS_SECURE_AUTH_KEY=put-a-random-string-here
WORDPRESS_LOGGED_IN_KEY=put-a-random-string-here
WORDPRESS_NONCE_KEY=put-a-random-string-here
WORDPRESS_AUTH_SALT=put-a-random-string-here
WORDPRESS_SECURE_AUTH_SALT=put-a-random-string-here
WORDPRESS_LOGGED_IN_SALT=put-a-random-string-here
WORDPRESS_NONCE_SALT=put-a-random-string-here

WORDPRESS_SITE_URL=https://your-domain.example.com
WORDPRESS_HOME_URL=https://your-domain.example.com

EC2_HOST=ec2-xx-xx-xx-xx.af-south-1.compute.amazonaws.com
EC2_USER=ec2-user
EC2_SSH_KEY=/path/to/your/key.pem
EC2_WP_ROOT=/var/www/html

CF_ZONE_ID=your-zone-id-here
REDIS_PASSWORD=changeme_redis

mkdir -p scripts

cat > scripts/deploy.sh </dev/null || { echo "ERROR: docker not found"; exit 1; }
docker compose version &>/dev/null || { echo "ERROR: docker compose plugin not found"; exit 1; }
[[ -f ".env" ]] || { echo "ERROR: .env not found. Copy .env.example and fill in your values."; exit 1; }
source .env

if [[ -z "${CLOUDFLARE_TUNNEL_TOKEN:-}" || "${CLOUDFLARE_TUNNEL_TOKEN}" == "your-tunnel-token-here" ]]; then
    echo "ERROR: CLOUDFLARE_TUNNEL_TOKEN is not set -- run scripts/setup-cf-tunnel.sh first."
    exit 1
fi

if [[ "${FRESH}" == "true" ]]; then
    echo "WARNING: --fresh will destroy all volumes (database + WordPress files)."
    read -r -p "Are you sure? [y/N] " confirm
    [[ "${confirm,,}" == "y" ]] || { echo "Aborted."; exit 0; }
    docker compose down -v --remove-orphans 2>/dev/null || true
fi

echo "=== Pulling latest images ==="
docker compose pull

mkdir -p /tmp/nginx-cache 2>/dev/null || true

echo "=== Starting stack ==="
docker compose up -d --remove-orphans

echo "=== Waiting for Nginx to be healthy ==="
MAX_WAIT=90; ELAPSED=0
until docker compose exec -T nginx wget -qO- http://localhost/nginx-health &>/dev/null; do
    [[ ${ELAPSED} -ge ${MAX_WAIT} ]] && {
        echo "ERROR: Nginx not healthy after ${MAX_WAIT}s"
        docker compose logs nginx
        exit 1
    }
    echo "  Waiting... (${ELAPSED}s)"; sleep 5; ELAPSED=$((ELAPSED + 5))
done

echo "=== Installing WP-CLI ==="
docker compose exec -T -u root wordpress sh -c '
    if ! command -v wp &>/dev/null; then
        curl -sO https://raw.githubusercontent.com/wp-cli/builds/gh-pages/phar/wp-cli.phar &&
        chmod +x wp-cli.phar && mv wp-cli.phar /usr/local/bin/wp && echo "WP-CLI installed."
    else
        echo "WP-CLI already present: $(wp --version --allow-root 2>/dev/null || true)"
    fi
' 2>/dev/null || true

echo "=== Upgrading WordPress core ==="
docker compose exec -T -u root wordpress sh -c '
    wp core update --allow-root 2>&1 | tail -3 || true
    wp core update-db --allow-root 2>&1 | tail -2 || true
' 2>/dev/null || true

echo "Stack is UP."
docker compose ps
EOF
chmod +x scripts/deploy.sh

bash scripts/deploy.sh

cat > scripts/push-to-pi.sh << 'EOF'
#!/usr/bin/env bash
# push-to-pi.sh -- Sync project directory to the Pi from your Mac.
# Does NOT restart containers. SSH into the Pi and run deploy.sh after.
set -euo pipefail

SCRIPT_DIR="$(cd "$(dirname "${BASH_SOURCE[0]}")/.." && pwd)"
PI_HOST="ssh.your-domain.example.com"
PI_USER="pi"
PI_DIR="~/your-project-dir"
SSH_OPTS="-o ProxyCommand='cloudflared access ssh --hostname ${PI_HOST}' \
    -o StrictHostKeyChecking=no \
    -o ServerAliveInterval=15 \
    -o ServerAliveCountMax=10"

echo "Syncing to ${PI_USER}@${PI_HOST}:${PI_DIR}"
rsync -avz --progress \
    -e "ssh ${SSH_OPTS}" \
    --exclude='.git' \
    --exclude='.DS_Store' \
    --exclude='.env' \
    "${SCRIPT_DIR}/" \
    "${PI_USER}@${PI_HOST}:${PI_DIR}/"

ssh ${SSH_OPTS} "${PI_USER}@${PI_HOST}" "chmod +x ${PI_DIR}/scripts/*.sh"
echo "Done. SSH into the Pi and run: cd ${PI_DIR} && bash scripts/deploy.sh"
EOF
chmod +x scripts/push-to-pi.sh

cat > scripts/setup-cf-tunnel.sh </dev/null \
    || { echo "ERROR: Cannot reach Pi"; exit 1; }
echo "  Pi reachable."

echo "Step 2: Adding DNS CNAME for ${HOSTNAME}..."
ssh ${SSH_OPTS} ${PI_USER}@${PI_HOST} \
    "cloudflared tunnel route dns ${TUNNEL_NAME} ${HOSTNAME} 2>&1 || true"

echo "Step 3: Pushing updated config to Pi..."
scp ${SSH_OPTS} "${CF_CONFIG}" "${PI_USER}@${PI_HOST}:/home/${PI_USER}/.cloudflared/config.yml"

echo "Step 4: Validating config on Pi..."
ssh ${SSH_OPTS} ${PI_USER}@${PI_HOST} "cloudflared tunnel ingress validate 2>&1"

echo "Step 5: Reloading cloudflared..."
ssh ${SSH_OPTS} ${PI_USER}@${PI_HOST} \
    "sudo systemctl reload cloudflared 2>/dev/null || sudo systemctl restart cloudflared"
sleep 3
STATUS=$(ssh ${SSH_OPTS} ${PI_USER}@${PI_HOST} "systemctl is-active cloudflared")
echo "  cloudflared status: ${STATUS}"
echo "Done. DNS propagates in ~30 seconds. Site: https://${HOSTNAME}"
EOF
chmod +x scripts/setup-cf-tunnel.sh

tunnel: your-tunnel-uuid
credentials-file: /home/pi/.cloudflared/your-tunnel-uuid.json

ingress:
  - hostname: yoursite.example.com
    service: http://localhost:8082
  - service: http_status:404

cat > scripts/sync-from-ec2.sh </dev/null || echo 'unknown'")
echo "  Old URL: ${OLD_URL}"
$SSH "cd ${EC2_WP_ROOT} && wp db export /tmp/${DUMP_FILE} --allow-root"

echo "Step 2: Downloading database dump..."
scp -i "${EC2_SSH_KEY}" -o StrictHostKeyChecking=no \
    "${EC2_USER}@${EC2_HOST}:/tmp/${DUMP_FILE}" "${LOCAL_DUMP}"

echo "Step 3: Syncing wp-content/uploads..."
docker compose exec -T wordpress mkdir -p /var/www/html/wp-content/uploads
rsync -avz --progress \
    -e "ssh -i ${EC2_SSH_KEY} -o StrictHostKeyChecking=no" \
    "${EC2_USER}@${EC2_HOST}:${EC2_WP_ROOT}/wp-content/uploads/" \
    /tmp/uploads-sync/
docker compose cp /tmp/uploads-sync/. wordpress:/var/www/html/wp-content/uploads/
docker compose exec -T wordpress chown -R www-data:www-data /var/www/html/wp-content/uploads
echo "  Uploads synced."

echo "Step 4: Importing database..."
docker compose cp "${LOCAL_DUMP}" mariadb:/tmp/import.sql
docker compose exec -T mariadb sh -c \
    "mariadb -u${MYSQL_USER} -p${MYSQL_PASSWORD} ${MYSQL_DATABASE} < /tmp/import.sql"
echo "  Database imported."

echo "Step 5: Replacing old URL with new URL..."
docker compose exec -T wordpress wp search-replace \
    "${OLD_URL}" "${NEW_URL}" --all-tables --allow-root --precise

echo "Step 6: Installing Redis Object Cache plugin..."
docker compose exec -T wordpress wp plugin install redis-cache --activate --allow-root || true
docker compose exec -T wordpress wp redis enable --allow-root || true

echo "Step 7: Syncing theme..."
THEME_DIR="$(dirname "${REPO_ROOT}")/your-theme-repo"
if [[ -d "${THEME_DIR}/your-theme" ]]; then
    docker compose cp "${THEME_DIR}/your-theme/." \
        wordpress:/var/www/html/wp-content/themes/your-theme/
    docker compose exec -T wordpress chown -R www-data:www-data \
        /var/www/html/wp-content/themes/your-theme
    docker compose exec -T wordpress wp theme activate your-theme --allow-root || true
    echo "  Theme synced and activated."
else
    echo "  WARN: Theme dir not found -- skipping."
fi

echo "Step 8: Flushing caches..."
docker compose exec -T wordpress wp cache flush --allow-root || true
docker compose exec -T wordpress wp rewrite flush --allow-root || true

echo "Migration complete. Visit: ${NEW_URL}"
EOF
chmod +x scripts/sync-from-ec2.sh

cat > scripts/resync-from-ec2.sh < "${BACKUP_DIR}/pre-resync_${TIMESTAMP}.sql.gz"
echo "  Saved: ${BACKUP_DIR}/pre-resync_${TIMESTAMP}.sql.gz"

echo "[2/6] Dumping database on EC2..."
OLD_URL=$($SSH_EC2 "cd ${EC2_WP_ROOT} && wp option get siteurl --allow-root 2>/dev/null" || echo "")
$SSH_EC2 "cd ${EC2_WP_ROOT} && wp db export /tmp/${DUMP_FILE} --allow-root"

echo "[3/6] Downloading and importing database..."
scp -i "${EC2_SSH_KEY}" -o StrictHostKeyChecking=no \
    "${EC2_USER}@${EC2_HOST}:/tmp/${DUMP_FILE}" "${LOCAL_DUMP}"
docker compose cp "${LOCAL_DUMP}" mariadb:/tmp/import.sql
docker compose exec -T mariadb sh -c \
    "mariadb -u${MYSQL_USER} -p${MYSQL_PASSWORD} ${MYSQL_DATABASE} /dev/null || true
docker compose exec -T nginx sh -c 'rm -rf /var/cache/nginx/fastcgi/*' 2>/dev/null || true
echo "Resync complete. Visit: ${NEW_URL}"
EOF
chmod +x scripts/resync-from-ec2.sh

0 2 * * 0 cd ~/your-project-dir && bash scripts/resync-from-ec2.sh --yes >> ~/logs/resync.log 2>&1

cat > scripts/health-check.sh </dev/null | grep -q ok \
    && echo "  Nginx: OK" || echo "  Nginx: FAIL"

echo "--- PHP-FPM ---"
docker compose exec -T wordpress curl -sf http://localhost/fpm-ping 2>/dev/null | grep -q pong \
    && echo "  PHP-FPM: OK" || echo "  PHP-FPM: (ping not exposed -- check container logs)"

echo "--- Redis ---"
REDIS_PONG=$(docker compose exec -T redis redis-cli -a "${REDIS_PASSWORD}" ping 2>/dev/null || echo "FAIL")
echo "  Redis: ${REDIS_PONG}"

echo "--- MariaDB ---"
DB_OK=$(docker compose exec -T mariadb \
    sh -c "mysqladmin -u${MYSQL_USER} -p${MYSQL_PASSWORD} ping 2>/dev/null" || echo "FAIL")
echo "  MariaDB: ${DB_OK}"

echo "--- WordPress HTTP ---"
HTTP_CODE=$(docker compose exec -T nginx \
    wget -qO- --server-response http://localhost/ 2>&1 | grep "HTTP/" | tail -1 | awk '{print $2}' || echo "ERR")
echo "  HTTP response: ${HTTP_CODE}"

echo "--- Disk Usage ---"
docker system df

echo "--- Memory ---"
free -h
EOF
chmod +x scripts/health-check.sh

curl -fsSL https://tailscale.com/install.sh | sh
sudo tailscale up
# Find the Pi's Tailscale IP:
tailscale ip -4

ssh [email protected]

Your Mac or browser
     |
     |  HTTPS (browser) or cloudflared ProxyCommand (CLI)
     v
Cloudflare Access -- authenticates you (email OTP or Google)
     |
     |  CF Tunnel (outbound-only from Pi, no inbound ports)
     v
cloudflared on Pi -- proxies to localhost:22
     |
     v
sshd -- validates a short-lived certificate issued by Cloudflare

cat > scripts/setup-cf-ssh.sh </dev/null || { echo "ERROR: cloudflared not found -- brew install cloudflared"; exit 1; }
[ -f "${SSH_KEY}" ]                || { echo "ERROR: SSH key not found: ${SSH_KEY}"; exit 1; }

echo "Step 1: Checking Pi is reachable on LAN..."
ssh ${SSH_OPTS} -o BatchMode=yes "${PI_USER}@${PI_HOST}" "echo ok" &>/dev/null \
    || { echo "ERROR: Cannot reach Pi. Must be on LAN for initial setup."; exit 1; }

echo "Step 2: Adding SSH ingress to tunnel config..."
grep -q "${SSH_HOSTNAME}" "${CF_CONFIG}" || \
    sed -i '' "s|  # Catch-all|  - hostname: ${SSH_HOSTNAME}\n    service: ssh://localhost:22\n\n  # Catch-all|" "${CF_CONFIG}"

echo "Step 3: Adding DNS CNAME..."
ssh ${SSH_OPTS} "${PI_USER}@${PI_HOST}" \
    "cloudflared tunnel route dns ${TUNNEL_NAME} ${SSH_HOSTNAME} 2>&1 || true"

echo "Step 4: Creating Cloudflare Access application..."
EXISTING_APP=$(curl -s \
    "https://api.cloudflare.com/client/v4/accounts/${CF_ACCOUNT_ID}/access/apps" \
    -H "X-Auth-Email: ${CF_EMAIL}" -H "X-Auth-Key: ${CF_KEY}" \
    | python3 -c "
import sys, json
apps = json.load(sys.stdin).get('result', [])
print(next((a['id'] for a in apps if a.get('domain') == '${SSH_HOSTNAME}'), ''))
" 2>/dev/null || true)

if [ -n "${EXISTING_APP}" ]; then
    APP_ID="${EXISTING_APP}"
else
    APP_RESPONSE=$(curl -s -X POST \
        "https://api.cloudflare.com/client/v4/accounts/${CF_ACCOUNT_ID}/access/apps" \
        -H "X-Auth-Email: ${CF_EMAIL}" -H "X-Auth-Key: ${CF_KEY}" \
        -H "Content-Type: application/json" \
        --data "{\"name\":\"Pi SSH\",\"type\":\"ssh\",\"domain\":\"${SSH_HOSTNAME}\",\"session_duration\":\"24h\"}")
    APP_ID=$(echo "${APP_RESPONSE}" | python3 -c "import sys,json; print(json.load(sys.stdin)['result']['id'])")
fi

echo "Step 5: Creating Access policy..."
curl -s -X POST \
    "https://api.cloudflare.com/client/v4/accounts/${CF_ACCOUNT_ID}/access/apps/${APP_ID}/policies" \
    -H "X-Auth-Email: ${CF_EMAIL}" -H "X-Auth-Key: ${CF_KEY}" \
    -H "Content-Type: application/json" \
    --data "{\"name\":\"Owner only\",\"decision\":\"allow\",\"precedence\":1,\"include\":[{\"email\":{\"email\":\"${ALLOWED_EMAIL}\"}}]}" \
    > /dev/null

echo "Step 6: Fetching Cloudflare SSH CAs..."
GATEWAY_CA=$(curl -s -X POST \
    "https://api.cloudflare.com/client/v4/accounts/${CF_ACCOUNT_ID}/access/gateway_ca" \
    -H "X-Auth-Email: ${CF_EMAIL}" -H "X-Auth-Key: ${CF_KEY}" \
    | python3 -c "import sys,json; print(json.load(sys.stdin)['result']['public_key'])" 2>/dev/null)
SLC_CA=$(curl -s -X POST \
    "https://api.cloudflare.com/client/v4/accounts/${CF_ACCOUNT_ID}/access/apps/${APP_ID}/ca" \
    -H "X-Auth-Email: ${CF_EMAIL}" -H "X-Auth-Key: ${CF_KEY}" \
    | python3 -c "import sys,json; print(json.load(sys.stdin)['result']['public_key'])" 2>/dev/null)
[ -n "${GATEWAY_CA}" ] || { echo "ERROR: Failed to fetch gateway CA"; exit 1; }
[ -n "${SLC_CA}" ]     || { echo "ERROR: Failed to fetch short-lived cert CA"; exit 1; }

echo "Step 7: Configuring sshd and creating CF Linux user on Pi..."
ssh ${SSH_OPTS} "${PI_USER}@${PI_HOST}" "
    echo '${GATEWAY_CA}' | sudo tee /etc/ssh/cloudflare-access-ca.pub > /dev/null
    echo '${SLC_CA}'     | sudo tee /etc/ssh/cloudflare-slc-ca.pub    > /dev/null
    sudo bash -c 'cat /etc/ssh/cloudflare-access-ca.pub /etc/ssh/cloudflare-slc-ca.pub \
        > /etc/ssh/cloudflare-ca-combined.pub'
    sudo sed -i '/TrustedUserCAKeys/d' /etc/ssh/sshd_config
    echo 'TrustedUserCAKeys /etc/ssh/cloudflare-ca-combined.pub' \
        | sudo tee -a /etc/ssh/sshd_config > /dev/null
    sudo mkdir -p /etc/ssh/auth_principals
    grep -q '^AuthorizedPrincipalsFile' /etc/ssh/sshd_config \
        || echo 'AuthorizedPrincipalsFile /etc/ssh/auth_principals/%u' \
           | sudo tee -a /etc/ssh/sshd_config > /dev/null
    id '${CF_SSH_USER}' &>/dev/null || sudo useradd \
        --no-create-home --home-dir /home/${PI_USER} --shell /bin/bash '${CF_SSH_USER}'
    sudo chmod 750 /home/${PI_USER}
    sudo usermod -aG ${PI_USER} '${CF_SSH_USER}'
    printf '${CF_SSH_USER}\n${ALLOWED_EMAIL}\n' \
        | sudo tee /etc/ssh/auth_principals/${CF_SSH_USER} > /dev/null
    sudo systemctl reload ssh
"

echo "Step 8: Pushing tunnel config and restarting cloudflared..."
scp ${SSH_OPTS} "${CF_CONFIG}" "${PI_USER}@${PI_HOST}:/tmp/cloudflared-config.yml"
ssh ${SSH_OPTS} "${PI_USER}@${PI_HOST}" "
    sudo mv /tmp/cloudflared-config.yml /etc/cloudflared/config.yml
    sudo systemctl restart cloudflared
    sleep 2
    systemctl is-active cloudflared
"

echo "Step 9: Authenticating with Cloudflare Access (opens browser)..."
cloudflared access login "https://${SSH_HOSTNAME}"

echo "Step 10: Testing SSH connection end-to-end..."
cloudflared access ssh-gen --hostname "${SSH_HOSTNAME}"
CF_KEY_FILE="${HOME}/.cloudflared/${SSH_HOSTNAME}-cf_key"
ssh \
    -i "${CF_KEY_FILE}" \
    -o "ProxyCommand=cloudflared access ssh --hostname ${SSH_HOSTNAME}" \
    -o StrictHostKeyChecking=no \
    -o ConnectTimeout=15 \
    "${CF_SSH_USER}@${SSH_HOSTNAME}" "echo 'Connected as: \$(whoami)'"

echo "Step 11: Writing ~/.ssh/config entry..."
grep -q "Host pi$" "${HOME}/.ssh/config" 2>/dev/null || cat >> "${HOME}/.ssh/config" << SSHEOF

Host pi
    HostName ${SSH_HOSTNAME}
    User ${CF_SSH_USER}
    IdentityFile ${HOME}/.cloudflared/${SSH_HOSTNAME}-cf_key
    ProxyCommand cloudflared access ssh --hostname %h
    StrictHostKeyChecking no
SSHEOF

echo "Setup complete. Connect with: cloudflared access ssh-gen --hostname ${SSH_HOSTNAME} && ssh pi"
EOF
chmod +x scripts/setup-cf-ssh.sh

cloudflared access ssh-gen --hostname ssh.your-domain.example.com && ssh pi

cloudflared access login https://ssh.your-domain.example.com
cloudflared access ssh-gen --hostname ssh.your-domain.example.com && ssh pi

if ssh -i ~/.ssh/pi_key -o ConnectTimeout=4 -o BatchMode=yes [email protected] exit 2>/dev/null; then
    # on LAN -- direct connection
else
    cloudflared access ssh-gen --hostname ssh.your-domain.example.com
    # CF tunnel -- short-lived cert
fi

TrustedUserCAKeys /etc/ssh/cloudflare-ca-combined.pub
AuthorizedPrincipalsFile /etc/ssh/auth_principals/%u

# Regenerate short-lived cert before each session
cloudflared access ssh-gen --hostname ssh.your-domain.example.com

# Refresh 24h Access token after it expires
cloudflared access login https://ssh.your-domain.example.com

cat > scripts/backup-to-s3.sh < "${DB_FILE}"
log "  DB dump: $(du -sh "${DB_FILE}" | cut -f1)"

log "Step 2: Archiving wp-content/uploads..."
UPLOADS_FILE="${TMP_DIR}/uploads-${TIMESTAMP}.tar.gz"
docker compose exec -T wordpress tar -czf - -C /var/www/html/wp-content uploads \
    2>/dev/null > "${UPLOADS_FILE}"
log "  Uploads: $(du -sh "${UPLOADS_FILE}" | cut -f1)"

log "Step 3: Archiving wp-content/themes..."
THEMES_FILE="${TMP_DIR}/themes-${TIMESTAMP}.tar.gz"
docker compose exec -T wordpress tar -czf - -C /var/www/html/wp-content themes \
    2>/dev/null > "${THEMES_FILE}"
log "  Themes: $(du -sh "${THEMES_FILE}" | cut -f1)"

log "Step 4: Archiving OS config..."
OS_CONFIG_FILE="${TMP_DIR}/os-config-${TIMESTAMP}.tar.gz"
sudo tar -czf "${OS_CONFIG_FILE}" --ignore-failed-read \
    /etc/fstab \
    /etc/docker/daemon.json \
    /boot/firmware/config.txt \
    /etc/systemd/journald.conf.d/volatile.conf \
    /etc/systemd/system.conf.d/watchdog.conf \
    2>/dev/null || true
sudo chown "$(id -u):$(id -g)" "${OS_CONFIG_FILE}"
log "  OS config: $(du -sh "${OS_CONFIG_FILE}" | cut -f1)"

log "Step 5: Archiving stack config..."
STACK_CONFIG_FILE="${TMP_DIR}/stack-config-${TIMESTAMP}.tar.gz"
tar -czf "${STACK_CONFIG_FILE}" --ignore-failed-read \
    -C "${REPO_ROOT}" \
    docker-compose.yml .env php/php.ini php/www.conf nginx/ \
    2>/dev/null || true
log "  Stack config: $(du -sh "${STACK_CONFIG_FILE}" | cut -f1)"

log "Step 6: Uploading to S3..."
S3_PATH="s3://${S3_BUCKET}/${S3_PREFIX}/${DATE}"
aws s3 cp "${DB_FILE}"           "${S3_PATH}/db-${TIMESTAMP}.sql.gz"           --storage-class STANDARD_IA
aws s3 cp "${UPLOADS_FILE}"      "${S3_PATH}/uploads-${TIMESTAMP}.tar.gz"      --storage-class STANDARD_IA
aws s3 cp "${THEMES_FILE}"       "${S3_PATH}/themes-${TIMESTAMP}.tar.gz"       --storage-class STANDARD_IA
aws s3 cp "${OS_CONFIG_FILE}"    "${S3_PATH}/os-config-${TIMESTAMP}.tar.gz"    --storage-class STANDARD_IA
aws s3 cp "${STACK_CONFIG_FILE}" "${S3_PATH}/stack-config-${TIMESTAMP}.tar.gz" --storage-class STANDARD_IA
log "  Uploaded."

log "Step 7: Verifying upload..."
aws s3 ls "${S3_PATH}/" | while read -r line; do log "  $line"; done

log "Step 8: Pruning old backups (keeping ${MAX_BACKUPS})..."
BACKUP_DATES=$(aws s3 ls "s3://${S3_BUCKET}/${S3_PREFIX}/" \
    | grep PRE | awk '{print $2}' | tr -d '/' | sort)
TOTAL=$(echo "${BACKUP_DATES}" | grep -c . || true)
log "  Total backups: ${TOTAL}"
if [[ "${TOTAL}" -gt "${MAX_BACKUPS}" ]]; then
    DELETE_COUNT=$(( TOTAL - MAX_BACKUPS ))
    echo "${BACKUP_DATES}" | head -"${DELETE_COUNT}" | while IFS= read -r old_date; do
        log "  Deleting: ${old_date}"
        aws s3 rm "s3://${S3_BUCKET}/${S3_PREFIX}/${old_date}/" --recursive
    done
    log "  Pruned ${DELETE_COUNT} backup(s)."
else
    log "  No pruning needed."
fi
EOF
chmod +x scripts/backup-to-s3.sh

cat > scripts/restore-from-s3.sh </dev/null | tail -1)
UPLOADS_FILE=$(ls "${TMP_DIR}"/uploads-*.tar.gz 2>/dev/null | tail -1)
THEMES_FILE=$(ls "${TMP_DIR}"/themes-*.tar.gz 2>/dev/null | tail -1)
STACK_CONFIG_FILE=$(ls "${TMP_DIR}"/stack-config-*.tar.gz 2>/dev/null | tail -1)
[[ -z "${DB_FILE}" ]] && { log "ERROR: No DB dump found"; exit 1; }

echo ""
echo "WARNING: This will OVERWRITE the current database and files."
echo "  DB:           ${DB_FILE}"
echo "  Uploads:      ${UPLOADS_FILE:-not found}"
echo "  Themes:       ${THEMES_FILE:-not found}"
echo "  Stack config: ${STACK_CONFIG_FILE:-not found}"
read -r -p "Continue? [y/N] " confirm
[[ "${confirm,,}" == "y" ]] || { echo "Aborted."; exit 0; }

source .env

log "Step 1: Restoring database..."
gunzip -c "${DB_FILE}" | docker compose exec -T mariadb sh -c \
    "mariadb -u${MYSQL_USER} -p${MYSQL_PASSWORD} ${MYSQL_DATABASE}"

if [[ -n "${UPLOADS_FILE:-}" ]]; then
    log "Step 2: Restoring uploads..."
    docker compose exec -T wordpress rm -rf /var/www/html/wp-content/uploads
    cat "${UPLOADS_FILE}" | docker compose exec -T -u root wordpress tar -xzf - \
        -C /var/www/html/wp-content
    docker compose exec -T -u root wordpress chown -R www-data:www-data \
        /var/www/html/wp-content/uploads
fi

if [[ -n "${THEMES_FILE:-}" ]]; then
    log "Step 3: Restoring themes..."
    cat "${THEMES_FILE}" | docker compose exec -T -u root wordpress tar -xzf - \
        -C /var/www/html/wp-content
    docker compose exec -T -u root wordpress chown -R www-data:www-data \
        /var/www/html/wp-content/themes
fi

if [[ -n "${STACK_CONFIG_FILE:-}" ]]; then
    log "Step 4: Restoring stack config..."
    tar -xzf "${STACK_CONFIG_FILE}" -C "${REPO_ROOT}" 2>/dev/null || true
    log "  Run 'docker compose up -d' to apply any config changes."
fi

log "Step 5: Flushing caches..."
docker compose exec -T -u root wordpress wp cache flush --allow-root 2>/dev/null || true
docker compose exec -T nginx sh -c 'rm -rf /var/cache/nginx/fastcgi/*' 2>/dev/null || true

log "Step 6: Smoke test..."
sleep 3
STATUS=$(curl -so /dev/null -w "%{http_code}" http://localhost:8082/)
SIZE=$(curl -s http://localhost:8082/ | wc -c)
log "Homepage: HTTP ${STATUS}, ${SIZE} bytes"

log "Restore complete from ${TARGET_DATE}."
log "Visit: $(docker compose exec -T -u root wordpress wp option get siteurl --allow-root 2>/dev/null)"
EOF
chmod +x scripts/restore-from-s3.sh

0 3 * * * cd ~/your-project-dir && bash scripts/backup-to-s3.sh >> /var/log/wp-backup.log 2>&1

tail -50 /var/log/wp-backup.log
aws s3 ls s3://your-bucket-name/wordpress-pi-nightly/ --recursive --human-readable

cat > scripts/update.sh <&1 | grep -E 'Pull|up to date|Pulled' || true

log "Step 2: Recreating containers..."
docker compose up -d --remove-orphans
sleep 15

log "Step 3: Installing WP-CLI..."
docker compose exec -T -u root wordpress sh -c '
    if ! command -v wp &>/dev/null; then
        curl -sO https://raw.githubusercontent.com/wp-cli/builds/gh-pages/phar/wp-cli.phar &&
        chmod +x wp-cli.phar && mv wp-cli.phar /usr/local/bin/wp
    fi
    echo "WP-CLI: $(wp --version --allow-root)"
' 2>/dev/null

log "Step 4: Updating WordPress core..."
docker compose exec -T -u root wordpress wp core update --allow-root 2>&1 | tail -2 || true
docker compose exec -T -u root wordpress wp core update-db --allow-root 2>&1 | tail -1 || true

log "Step 5: Updating plugins..."
docker compose exec -T -u root wordpress wp plugin update --all --allow-root 2>&1 | tail -5 || true

log "Step 6: Updating themes..."
docker compose exec -T -u root wordpress wp theme update --all --allow-root 2>&1 | tail -3 || true

log "Step 7: Flushing caches..."
docker compose exec -T -u root wordpress wp cache flush --allow-root 2>/dev/null || true
docker compose exec -T nginx sh -c 'rm -rf /var/cache/nginx/fastcgi/*' 2>/dev/null || true

log "Step 8: Smoke test..."
STATUS=$(curl -so /dev/null -w "%{http_code}" http://localhost:8082/)
SIZE=$(curl -s http://localhost:8082/ | wc -c)
log "Homepage: HTTP ${STATUS}, ${SIZE} bytes"
if [[ "${STATUS}" != "200" ]] || [[ "${SIZE}" -lt 10000 ]]; then
    log "ERROR: Homepage check failed -- investigate before declaring update successful"
    exit 1
fi

log "Update complete."
EOF
chmod +x scripts/update.sh

0 3 * * 0 cd ~/your-project-dir && bash scripts/update.sh --yes >> /var/log/wp-update.log 2>&1