SQLite 3.50+ Live Remote Replication Script (sqlite3_rsync)

Supanote Core Team @supanote · 1 min read

plain (bash) 1 day ago · 48 lines · 2 views

Artifact

Verified Blueprint SQLite 3.50+

Automate bandwidth-efficient live SQLite 3.50+ backups over SSH using official sqlite3_rsync page-level delta transfers and quick_check.

✓ Uses official sqlite3_rsync (SQLite 3.50+) to transmit only modified B-tree pages over SSH
✓ Runs safely against live databases without blocking readers or requiring WAL mode on the replica
✓ Prevents overlapping runs via flock and verifies replica integrity with PRAGMA quick_check

Quick Fetch

#!/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"

1#!/usr/bin/env bash

2# Live Bandwidth-Optimized SQLite 3.50+ Remote Replication via sqlite3_rsync

3# Schedule every 1-5 minutes via systemd timer or cron:

4# */2 * * * * /usr/local/bin/sqlite-rsync-replica.sh >> /var/log/sqlite-rsync.log 2>&1

6set -euo pipefail

8ORIGIN_DB="${ORIGIN_DB:-/var/lib/app/production.sqlite}"

9REPLICA_TARGET="${REPLICA_TARGET:-backup-svc@replica-db.internal:/var/backups/sqlite/production.sqlite}"

10LOCK_FILE="/tmp/sqlite3_rsync_production.lock"

11SSH_OPTS="-o BatchMode=yes -o ConnectTimeout=10 -o ServerAliveInterval=15"

13if ! command -v sqlite3_rsync >/dev/null 2>&1; then

14 echo "[ERROR] sqlite3_rsync binary not found (requires SQLite 3.50.0+ tools package)" >&2

15 exit 1

16fi

18if [[ ! -f "${ORIGIN_DB}" ]]; then

19 echo "[ERROR] Origin SQLite database missing: ${ORIGIN_DB}" >&2

20 exit 1

21fi

23# Prevent overlapping sync cycles if a large bulk migration is in flight

24exec 9>"${LOCK_FILE}"

25if ! flock -n 9; then

26 echo "[SKIP] Previous sqlite3_rsync cycle still active; skipping."

27 exit 0

28fi

30START_MS=$(date +%s%3N)

31echo "[INFO] $(date -u +%FT%TZ) Starting page-delta sync for ${ORIGIN_DB}"

33# sqlite3_rsync opens a clean read transaction on origin and sends only changed pages

34sqlite3_rsync -v --ssh "ssh ${SSH_OPTS}" "${ORIGIN_DB}" "${REPLICA_TARGET}"

36# Optional post-sync remote integrity verification on standby host

37REMOTE_HOST="${REPLICA_TARGET%%:*}"

38REMOTE_PATH="${REPLICA_TARGET#*:}"

39CHECK_OUT=$(ssh ${SSH_OPTS} "${REMOTE_HOST}" "sqlite3 '${REMOTE_PATH}' 'PRAGMA quick_check(1);'")

41if [[ "${CHECK_OUT}" != "ok" ]]; then

42 echo "[CRITICAL] Remote replica integrity check failed: ${CHECK_OUT}" >&2

43 exit 2

44fi

46END_MS=$(date +%s%3N)

47echo "[OK] Replica verified in $((END_MS - START_MS)) ms"

Replies 0

Reply with note

No replies yet

Every reply is a note. Start a discussion, ask a question, or attach a code snippet.

Reply with a note

Related SQLite 3.50+ & Database, Cache & SQL Blueprints

Production-ready configurations and diagnostics from the verified Supanote library.

All Database, Cache & SQL →

SQL SQLite 3.50+

SQLite 3.50+ Binary JSONB, STRICT Table & Generated Indexes

Cut SQLite JSON CPU overhead by 50%+ using binary JSONB BLOB storage, STRICT tables, VIRTUAL generated columns, and jsonb_set updates.

SQL SQLite

High-Performance SQLite Production PRAGMAs

Maximum performance SQLite pragmas for WAL mode, concurrency, and memory buffers.

SQL SQLite

SQLite FTS5 Full-Text Search Schema & Sync Triggers

External content FTS5 virtual table with unicode61 tokenizer and insert, update, and delete synchronization triggers.

BASH Redis 8.0+

Redis 8.0 Native Vector Sets (VADD & VSIM with Q8 Quantization)

Run sub-millisecond similarity search in Redis 8.0 using built-in Vector Sets (VADD, VSIM), 8-bit Q8 quantization, and FILTER expressions.

Interactive Developer Workbench: .env, Log & API Key Secret Scanner

Detect and redact leaked AWS keys, JWTs, database URIs, private keys, and Bearer tokens before sharing.

Open Secret Scanner →