SQLite 3.50+ Live Remote Replication Script (sqlite3_rsync)

#!/usr/bin/env bash # Live Bandwidth-Optimized SQLite 3.50+ Remote Replication via sqlite3_rsync # Schedule every 1-5 minutes via systemd timer or cron: # */2 * * * * /usr/local/bin/sqlite-rsync-replica.sh >> /var/log/sqlite-rsync.log 2>&1 set -euo pipefail ORIGIN_DB="${ORIGIN_DB:-/var/lib/app/production.sqlite}" REPLICA_TARGET="${REPLICA_TARGET:-backup-svc@replica-db.internal:/var/backups/sqlite/production.sqlite}" LOCK_FILE="/tmp/sqlite3_rsync_production.lock" SSH_OPTS="-o BatchMode=yes -o ConnectTimeout=10 -o ServerAliveInterval=15" if ! command -v sqlite3_rsync >/dev/null 2>&1; then echo "[ERROR] sqlite3_rsync binary not found (requires SQLite 3.50.0+ tools package)" >&2 exit 1 fi if [[ ! -f "${ORIGIN_DB}" ]]; then echo "[ERROR] Origin SQLite database missing: ${ORIGIN_DB}" >&2 exit 1 fi # Prevent overlapping sync cycles if a large bulk migration is in flight exec 9>"${LOCK_FILE}" if ! flock -n 9; then echo "[SKIP] Previous sqlite3_rsync cycle still active; skipping." exit 0 fi START_MS=$(date +%s%3N) echo "[INFO] $(date -u +%FT%TZ) Starting page-delta sync for ${ORIGIN_DB}" # sqlite3_rsync opens a clean read transaction on origin and sends only changed pages sqlite3_rsync -v --ssh "ssh ${SSH_OPTS}" "${ORIGIN_DB}" "${REPLICA_TARGET}" # Optional post-sync remote integrity verification on standby host REMOTE_HOST="${REPLICA_TARGET%%:*}" REMOTE_PATH="${REPLICA_TARGET#*:}" CHECK_OUT=$(ssh ${SSH_OPTS} "${REMOTE_HOST}" "sqlite3 '${REMOTE_PATH}' 'PRAGMA quick_check(1);'") if [[ "${CHECK_OUT}" != "ok" ]]; then echo "[CRITICAL] Remote replica integrity check failed: ${CHECK_OUT}" >&2 exit 2 fi END_MS=$(date +%s%3N) echo "[OK] Replica verified in $((END_MS - START_MS)) ms"