SQLite ????????? Litestream ?????????????????????
SQLite ???????????? embedded database ???????????????????????????????????????????????????????????????????????? ?????????????????????????????? ????????????????????? database server ????????????????????????????????? mobile apps, IoT, small-medium web applications ????????? edge computing ??????????????????????????????????????? backup strategy ??????????????? copy ?????????????????????????????????????????????????????????????????????????????????????????????????????????
Litestream ???????????? streaming replication tool ?????????????????? SQLite ??????????????????????????? background process ????????? replicate WAL (Write-Ahead Logging) changes ??????????????? remote storage (S3, GCS, Azure Blob) ????????? near-real-time ??????????????????????????? Zero downtime backup ????????????????????????????????? application, Point-in-time recovery ???????????????????????????????????????????????????????????????????????????, Simple ????????????????????????????????? config ????????????, Low overhead ????????? resources ?????????????????????
Litestream ???????????????????????????????????????????????? SQLite backup ????????????????????? lock database ????????????????????? backup, ?????????????????????????????? VACUUM ???????????? .backup command, Replicate ????????? continuous ????????????????????????????????????????????????????????? (RPO ????????????????????? 1 ??????????????????), ????????????????????? S3 storage costs ??????????????????
????????????????????? Litestream ?????????????????? SQLite Backup
Setup Litestream
# === Litestream Installation ===
# 1. Install Litestream (Linux)
wget https://github.com/benbjohnson/litestream/releases/latest/download/litestream-linux-amd64.tar.gz
tar -xzf litestream-linux-amd64.tar.gz
sudo mv litestream /usr/local/bin/
litestream version
# macOS
brew install benbjohnson/litestream/litestream
# Docker
docker pull litestream/litestream
# 2. Create SQLite Database for Testing
python3 -c "
import sqlite3
conn = sqlite3.connect('/data/myapp.db')
conn.execute('PRAGMA journal_mode=WAL') # Required for Litestream
conn.execute('''CREATE TABLE IF NOT EXISTS users (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
email TEXT UNIQUE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)''')
conn.execute('''CREATE TABLE IF NOT EXISTS orders (
id INTEGER PRIMARY KEY AUTOINCREMENT,
user_id INTEGER REFERENCES users(id),
amount REAL NOT NULL,
status TEXT DEFAULT 'pending',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
)''')
conn.execute(\"INSERT INTO users (name, email) VALUES ('Test User', 'test@example.com')\")
conn.commit()
print(f'Database created: /data/myapp.db')
print(f'Journal mode: {conn.execute(\"PRAGMA journal_mode\").fetchone()[0]}')
conn.close()
"
# 3. Configure Litestream
cat > /etc/litestream.yml << 'EOF'
dbs:
- path: /data/myapp.db
replicas:
# Primary: S3
- type: s3
bucket: myapp-backups
path: sqlite/myapp
region: ap-southeast-1
access-key-id:
secret-access-key:
retention: 720h # 30 days retention
retention-check-interval: 1h
sync-interval: 1s # Sync every 1 second
snapshot-interval: 4h # Full snapshot every 4 hours
# Secondary: Local backup
- type: file
path: /backup/myapp
retention: 168h # 7 days
EOF
# 4. Verify Configuration
litestream databases
# 5. Start Replication
litestream replicate -config /etc/litestream.yml
echo "Litestream installed and configured"????????????????????? Continuous Replication
????????? Litestream ???????????? service
# === Continuous Replication Setup ===
# 1. Systemd Service
cat > /etc/systemd/system/litestream.service << 'EOF'
[Unit]
Description=Litestream SQLite Replication
After=network.target
[Service]
Type=simple
User=www-data
Group=www-data
ExecStart=/usr/local/bin/litestream replicate -config /etc/litestream.yml
Restart=always
RestartSec=5
Environment=AWS_ACCESS_KEY_ID=your-key
Environment=AWS_SECRET_ACCESS_KEY=your-secret
# Security
NoNewPrivileges=true
ProtectSystem=strict
ReadWritePaths=/data /backup
PrivateTmp=true
[Install]
WantedBy=multi-user.target
EOF
systemctl daemon-reload
systemctl enable litestream
systemctl start litestream
systemctl status litestream
# 2. Docker Compose with Litestream Sidecar
cat > docker-compose.yml << 'EOF'
version: '3.8'
services:
app:
image: myapp:latest
volumes:
- sqlite-data:/data
ports:
- "8080:8080"
environment:
DATABASE_PATH: /data/myapp.db
depends_on:
- litestream-restore
litestream-restore:
image: litestream/litestream
command: restore -if-db-not-exists -if-replica-exists -config /etc/litestream.yml /data/myapp.db
volumes:
- sqlite-data:/data
- ./litestream.yml:/etc/litestream.yml
environment:
AWS_ACCESS_KEY_ID:
AWS_SECRET_ACCESS_KEY:
litestream-replicate:
image: litestream/litestream
command: replicate -config /etc/litestream.yml
volumes:
- sqlite-data:/data
- ./litestream.yml:/etc/litestream.yml
environment:
AWS_ACCESS_KEY_ID:
AWS_SECRET_ACCESS_KEY:
restart: always
volumes:
sqlite-data:
EOF
# 3. Verify Replication
litestream snapshots /data/myapp.db
litestream wal /data/myapp.db
echo "Continuous replication configured"Recovery ????????? Restore Procedures
????????????????????????????????????????????? Litestream backup
#!/usr/bin/env python3
# recovery_procedures.py ??? Litestream Recovery Guide
import json
import logging
from typing import Dict, List
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger("recovery")
class LitestreamRecovery:
def __init__(self):
self.procedures = {}
def recovery_commands(self):
return {
"restore_latest": {
"description": "??????????????????????????????????????????????????????",
"command": "litestream restore -config /etc/litestream.yml /data/myapp.db",
"notes": "Restores to latest available state",
"rpo": "< 1 second (last WAL frame)",
},
"restore_point_in_time": {
"description": "?????????????????? ??? ???????????????????????????????????????????????????",
"command": 'litestream restore -config /etc/litestream.yml -timestamp "2024-06-15T10:30:00Z" /data/myapp.db',
"notes": "Restores to specific timestamp",
"rpo": "Exact point in time",
},
"restore_from_s3": {
"description": "??????????????????????????? S3 ??????????????????",
"command": "litestream restore -type s3 -bucket myapp-backups -path sqlite/myapp /data/myapp.db",
"notes": "Direct restore from S3 without config file",
},
"restore_from_generation": {
"description": "??????????????????????????? generation ???????????????",
"command": 'litestream restore -generation "abc123def456" /data/myapp.db',
"notes": "Useful when specific backup point is known",
},
"verify_restore": {
"description": "????????????????????? database ???????????? restore",
"commands": [
"sqlite3 /data/myapp.db 'PRAGMA integrity_check;'",
"sqlite3 /data/myapp.db 'SELECT count(*) FROM users;'",
"sqlite3 /data/myapp.db 'PRAGMA journal_mode;'",
],
},
}
def disaster_recovery_plan(self):
return {
"scenario_1_server_failure": {
"description": "Server ??????????????????????????????????????????",
"steps": [
"Provision new server",
"Install Litestream",
"Run: litestream restore -config /etc/litestream.yml /data/myapp.db",
"Start application",
"Start Litestream replication",
"Verify data integrity",
],
"rto": "10-30 minutes",
"rpo": "< 1 second",
},
"scenario_2_data_corruption": {
"description": "Database file ?????????????????????",
"steps": [
"Stop application",
"Remove corrupted database",
"Restore from latest backup: litestream restore ...",
"Run integrity check: PRAGMA integrity_check",
"Start application",
],
"rto": "5-10 minutes",
"rpo": "< 1 second",
},
"scenario_3_accidental_delete": {
"description": "?????????????????????????????????",
"steps": [
"Identify timestamp before deletion",
"Restore to point-in-time: litestream restore -timestamp ...",
"Extract needed data from restored copy",
"Insert data back to production",
],
"rto": "15-30 minutes",
"rpo": "Exact (point-in-time)",
},
}
recovery = LitestreamRecovery()
commands = recovery.recovery_commands()
print("Recovery Commands:")
for name, info in commands.items():
print(f" {name}: {info['description']}")
if 'rpo' in info:
print(f" RPO: {info['rpo']}")
dr = recovery.disaster_recovery_plan()
print("\nDisaster Recovery Plans:")
for scenario, plan in dr.items():
print(f" {plan['description']}: RTO={plan['rto']}, RPO={plan['rpo']}")Multi-Destination Backup
Backup ?????????????????? destinations (3-2-1 Rule)
# === Multi-Destination Backup (3-2-1 Rule) ===
cat > /etc/litestream-multi.yml << 'EOF'
dbs:
- path: /data/myapp.db
replicas:
# 1. Primary: AWS S3 (ap-southeast-1)
- name: s3-primary
type: s3
bucket: myapp-backups-primary
path: sqlite/myapp
region: ap-southeast-1
retention: 720h
sync-interval: 1s
snapshot-interval: 4h
# 2. Secondary: AWS S3 (different region for DR)
- name: s3-dr
type: s3
bucket: myapp-backups-dr
path: sqlite/myapp
region: us-west-2
retention: 2160h # 90 days
sync-interval: 10s
snapshot-interval: 24h
# 3. Local backup (fast recovery)
- name: local
type: file
path: /backup/myapp
retention: 168h # 7 days
sync-interval: 1s
snapshot-interval: 1h
# Additional database
- path: /data/analytics.db
replicas:
- name: s3-analytics
type: s3
bucket: myapp-backups-primary
path: sqlite/analytics
region: ap-southeast-1
retention: 360h
sync-interval: 60s # Less critical, sync every minute
EOF
# GCS (Google Cloud Storage) example
cat > litestream-gcs.yml << 'EOF'
dbs:
- path: /data/myapp.db
replicas:
- type: gcs
bucket: myapp-backups-gcs
path: sqlite/myapp
retention: 720h
EOF
# Azure Blob Storage example
cat > litestream-azure.yml << 'EOF'
dbs:
- path: /data/myapp.db
replicas:
- type: abs
account-name: myappstorage
bucket: backups
path: sqlite/myapp
retention: 720h
EOF
# Verify all replicas
litestream snapshots -config /etc/litestream-multi.yml /data/myapp.db
litestream generations -config /etc/litestream-multi.yml /data/myapp.db
echo "Multi-destination backup configured"Monitoring ????????? Automation
?????????????????? backup status
#!/usr/bin/env python3
# backup_monitor.py ??? Litestream Backup Monitor
import json
import logging
from typing import Dict
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger("monitor")
class BackupMonitor:
def __init__(self):
self.metrics = {}
def health_check(self):
return {
"litestream_process": {
"check": "systemctl is-active litestream",
"expected": "active",
},
"last_snapshot": {
"check": "litestream snapshots /data/myapp.db | tail -1",
"alert_if": "Older than 5 hours",
},
"wal_lag": {
"check": "Compare local WAL position vs replica",
"alert_if": "Lag > 10 seconds",
},
"s3_connectivity": {
"check": "aws s3 ls s3://myapp-backups-primary/sqlite/",
"alert_if": "Connection failed",
},
"db_integrity": {
"check": "sqlite3 /data/myapp.db 'PRAGMA integrity_check'",
"expected": "ok",
"frequency": "Daily",
},
"disk_space": {
"check": "df -h /data",
"alert_if": "Usage > 80%",
},
}
def dashboard(self):
return {
"backup_status": {
"primary_s3": {"status": "synced", "last_snapshot": "2 hours ago", "lag": "< 1s"},
"dr_s3": {"status": "synced", "last_snapshot": "12 hours ago", "lag": "5s"},
"local": {"status": "synced", "last_snapshot": "30 minutes ago", "lag": "< 1s"},
},
"database_stats": {
"size_mb": 256,
"tables": 15,
"total_rows": 2500000,
"wal_size_mb": 12,
"journal_mode": "WAL",
},
"cost": {
"s3_storage_gb": 5.2,
"s3_cost_monthly": 0.12,
"data_transfer_gb": 2.1,
"total_monthly": 0.17,
},
"recovery_tests": {
"last_test": "2024-06-01",
"result": "passed",
"restore_time": "45 seconds",
"data_verified": True,
},
}
monitor = BackupMonitor()
dash = monitor.dashboard()
print("Backup Status:")
for replica, status in dash["backup_status"].items():
print(f" {replica}: {status['status']}, lag={status['lag']}")
print(f"\nDB: {dash['database_stats']['size_mb']}MB, {dash['database_stats']['total_rows']:,} rows")
print(f"Cost: /month")
print(f"Last Recovery Test: {dash['recovery_tests']['last_test']} ({dash['recovery_tests']['result']})")Best Practices สำหรับนักพัฒนา
การเขียนโค้ดที่ดีไม่ใช่แค่ทำให้โปรแกรมทำงานได้ แต่ต้องเขียนให้อ่านง่าย ดูแลรักษาง่าย และ Scale ได้ หลัก SOLID Principles เป็นพื้นฐานสำคัญที่นักพัฒนาทุกู้คืนควรเข้าใจ ได้แก่ Single Responsibility ที่แต่ละ Class ทำหน้าที่เดียว Open-Closed ที่เปิดให้ขยายแต่ปิดการแก้ไข Liskov Substitution ที่ Subclass ต้องใช้แทน Parent ได้ Interface Segregation ที่แยก Interface ให้เล็ก และ Dependency Inversion ที่พึ่งพา Abstraction ไม่ใช่ Implementation
เรื่อง Testing ก็ขาดไม่ได้ ควรเขียน Unit Test ครอบคลุมอย่างน้อย 80% ของ Code Base ใช้ Integration Test ทดสอบการทำงานร่วมกันของ Module ต่างๆ และ E2E Test สำหรับ Critical User Flow เครื่องมือยอดนิยมเช่น Jest, Pytest, JUnit ช่วยให้การเขียน Test เป็นเรื่องง่าย
เรื่อง Version Control ด้วย Git ใช้ Branch Strategy ที่เหมาะกับทีม เช่น Git Flow สำหรับโปรเจคใหญ่ หรือ Trunk-Based Development สำหรับทีมที่ Deploy บ่อย ทำ Code Review ทุก Pull Request และใช้ CI/CD Pipeline ทำ Automated Testing และ Deployment
เปรียบเทียบข้อดีและข้อเสีย
จากตารางเปรียบเทียบจะเห็นว่าข้อดีมีมากกว่าข้อเสียอย่างชัดเจน โดยเฉพาะในแง่ของประสิทธิภาพและความสามารถในการ Scale สำหรับข้อเสียส่วนใหญ่สามารถแก้ไขได้ด้วยการเรียนรู้อย่างเป็นระบบและวางแผนทรัพยากรให้เหมาะสม
FAQ ??????????????????????????????????????????
Q: Litestream ????????? SQLite .backup command ???????????????????????????????????????????
A: SQLite .backup command ??????????????? snapshot ?????????????????????????????? ???????????? lock database ????????????????????? backup ?????????????????????????????????????????????????????? backup ?????????????????????????????? RPO ????????????????????? backup frequency (????????? backup ????????? 1 ????????????????????? RPO = 1 ?????????????????????) Litestream ?????? continuous replication stream WAL changes ?????? storage ???????????? ????????????????????? lock database RPO < 1 ?????????????????? ?????????????????? point-in-time recovery ????????? resources ???????????? Litestream ???????????????????????????????????????????????????????????? ??????????????????????????????????????? one-time backup ??????????????? ????????? .backup command ?????????????????????
Q: Litestream ?????????????????? production ???????????????????
A: ????????? Litestream ????????????????????????????????????????????? production ?????????????????? Fly.io ?????????????????????????????????????????? ?????????????????? production ????????????????????????????????? SQLite ???????????????????????? journal_mode=WAL (??????????????????), Application ???????????????????????? single-writer (SQLite limitation), ?????????????????????????????????????????? high-concurrency write workloads (????????? PostgreSQL ?????????), ????????????????????????????????? read-heavy workloads, small-medium applications, edge computing Use cases ???????????????????????? Personal projects, SaaS ????????????????????????-????????????, Mobile/IoT backends, Blogs/CMS, Internal tools ??????????????? concurrent writes ???????????? (100+ writes/second) ?????????????????? PostgreSQL/MySQL ?????????
Q: ?????????????????????????????? Litestream backup ??????????????????????
A: ?????????????????? Litestream ???????????? open source ????????? ????????????????????????????????????????????? cloud storage S3 Standard storage $0.023/GB/month, S3 PUT requests $0.005/1000 requests ???????????????????????? SQLite 1GB database, sync ????????? 1 ?????????????????? Storage ~2-5GB (snapshots + WAL) = $0.05-0.12/month, Requests ~2.6M/month = $13/month (??????????????????????????? sync-interval) ???????????????????????? sync-interval ???????????? 10 ?????????????????? requests ????????????????????? ~260K/month = $1.30/month ??????????????????????????????????????? $1-15/month ????????????????????? sync frequency ????????????????????? managed database service ????????? (RDS starts at $15/month)
Q: Litestream ?????????????????? encryption ??????????
A: Litestream ????????? encrypt data ????????? ?????????????????? encryption ????????? storage provider S3 Server-Side Encryption (SSE-S3, SSE-KMS) ????????????????????? S3 bucket settings, GCS encryption enabled by default, Azure Blob encryption enabled by default ?????????????????? encryption at rest ????????? storage provider encryption ????????????????????? ?????????????????? encryption in transit Litestream ????????? HTTPS ???????????????????????????????????????????????????????????? cloud storage ?????????????????????????????? application-level encryption ????????? SQLCipher (encrypted SQLite) ????????????????????? Litestream ????????? ???????????????????????????????????? compatibility
