Production Deployment

Overview

This guide covers production deployment best practices for Duckling, including performance optimization, monitoring, backup strategies, and disaster recovery.

Pre-Deployment Checklist

Security configured

ADMIN_PASSWORD changed from default
DUCKLING_API_KEY generated with openssl rand -hex 32
SESSION_SECRET generated with openssl rand -hex 32
JWT_SECRET set (or using SESSION_SECRET)
Rate limiting enabled (RATE_LIMIT_ENABLED=true)

Environment configured

NODE_ENV=production
MySQL connection string validated
DuckDB path exists with proper permissions
Data directory mounted as persistent volume
Log directory configured

Automation enabled

AUTO_START_SYNC=true (automatic sync on boot)
AUTO_BACKUP=true (daily backups)
AUTO_RESTART=true (auto-recovery on failures)
S3 backups configured (for large databases)

Resources allocated

Minimum 8GB RAM for production workloads
16GB+ RAM for large databases (>100GB)
Sufficient disk space (2-3x source database size)
Docker memory limits set

Deployment Architecture

Single Container (Recommended)

The production Dockerfile builds a single container that serves both the API and frontend:

# Build production image
docker build -t duckling:latest .

# Run production container
docker run -d \
  --name duckling \
  -p 3000:3000 \
  -p 3307:3307 \
  -v ./data:/app/data \
  -v ./logs:/app/logs \
  --env-file .env \
  --restart unless-stopped \
  --memory 16g \
  --cpus 4 \
  duckling:latest

Multi-Instance (High Availability)

For high availability, run multiple Duckling instances behind a load balancer:

version: '3.8'
services:
  duckling-1:
    image: duckling:latest
    environment:
      - READ_REPLICA_ENABLED=false  # Primary
    volumes:
      - ./data:/app/data
      - ./logs-1:/app/logs

  duckling-2:
    image: duckling:latest
    environment:
      - READ_REPLICA_ENABLED=true   # Read replica
      - REPLICA_REFRESH_INTERVAL=300
    volumes:
      - ./data:/app/data  # Read-only mount
      - ./logs-2:/app/logs

  nginx:
    image: nginx:alpine
    ports:
      - "80:80"
      - "443:443"
    volumes:
      - ./nginx.conf:/etc/nginx/nginx.conf
    depends_on:
      - duckling-1
      - duckling-2

Resource Allocation

Memory Guidelines

Small Databases

<10GB

Minimum: 4GB RAM

docker run --memory 4g duckling:latest

Medium Databases

10-100GB

Recommended: 8-16GB RAM

docker run --memory 16g duckling:latest

Large Databases

100GB+

Recommended: 16-32GB RAM

docker run --memory 32g duckling:latest

Also increase Node.js heap size:

ENV NODE_OPTIONS="--max-old-space-size=24576"  # 24GB

CPU Allocation

# 4 cores for production workloads
docker run --cpus 4 duckling:latest

# 8 cores for large databases
docker run --cpus 8 duckling:latest

Disk Space

Estimate required disk space:

DuckDB database: 30-50% of MySQL source size (columnar compression)
Backups: 7 days × database size (with BACKUP_RETENTION_DAYS=7)
S3 restore buffer: 1× database size (temporary during restore)
Logs: 1-10GB depending on LOG_LEVEL

Example for 200GB MySQL database:

DuckDB: ~80GB (40% compression)
Local backups: 560GB (7 days × 80GB)
S3 restore buffer: 80GB (temporary)
Total: 720GB minimum

With S3 backups enabled, you can reduce local backup retention to save disk space.

Performance Optimization

Sync Configuration

For large databases (100GB+):

# Increase batch sizes for faster sync
BATCH_SIZE=5000
INSERT_BATCH_SIZE=5000

# Reduce sync frequency to avoid overhead
SYNC_INTERVAL_MINUTES=30

# Enable incremental sync
ENABLE_INCREMENTAL_SYNC=true

For real-time requirements:

# Use CDC for sub-second latency
CDC_ENABLED=true
CDC_AUTO_START=true

# Or reduce sync interval
SYNC_INTERVAL_MINUTES=5

Query Performance

# Increase concurrent query limit
MAX_CONCURRENT_QUERIES=20

# Increase query timeout for complex analytics
QUERY_TIMEOUT_MS=60000  # 60 seconds

# Enable worker threads for parallel execution
WORKER_THREADS=0  # Auto-detect (CPU cores - 1)

Connection Pooling

# DuckDB connections (one per concurrent query)
DUCKDB_MAX_CONNECTIONS=20

# MySQL connections (keep low to avoid overwhelming source)
MYSQL_MAX_CONNECTIONS=10

# MySQL protocol server
MYSQL_PROTOCOL_MAX_CONNECTIONS=100

Zero-Downtime Automation

Duckling includes automatic failsafe features enabled by default:

Automatic Sync

Auto-starts on container boot (AUTO_START_SYNC=true)

Incremental sync every 15 minutes
Watermark-based tracking (no duplicates)
Automatic error recovery with exponential backoff

Automatic Backups

Daily local backups (AUTO_BACKUP=true)

Runs every 24 hours (BACKUP_INTERVAL_HOURS=24)
Keeps 7 days of backups (BACKUP_RETENTION_DAYS=7)
Automatic cleanup of old backups
One-command restore: POST /automation/restore

S3 cloud backups (configure per database)

Automatic upload after each local backup when s3.enabled=true
Client-side AES-256 encryption recommended
Fast disaster recovery (download vs. re-sync from MySQL)
Critical for large databases (200GB+)

Health Monitoring

Auto-restart on failures (AUTO_RESTART=true)

Monitors connections every 60 seconds
Auto-reconnects DuckDB and MySQL on failures
Exponential backoff retry (up to 3 attempts)
Auto-triggers sync to verify recovery

Storage Management

Automatic cleanup (AUTO_CLEANUP=true)

DuckDB handles VACUUM and WAL cleanup automatically
Runs every 24 hours (CLEANUP_INTERVAL_HOURS=24)
No manual intervention required

Multi-Database Configuration

Duckling supports multiple isolated database replicas on a single server instance.

Database Configuration File

Location: ./data/databases.json

[
  {
    "id": "production",
    "name": "Production DB",
    "mysqlConnectionString": "mysql://user:pass@prod-host:3306/prod_db",
    "duckdbPath": "data/production.db",
    "createdAt": "2025-11-06T18:58:36.480Z",
    "updatedAt": "2025-11-06T18:58:36.480Z",
    "s3": {
      "enabled": true,
      "bucket": "duckling-backups",
      "region": "us-east-1",
      "accessKeyId": "AKIAIOSFODNN7EXAMPLE",
      "secretAccessKey": "wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY",
      "pathPrefix": "production/",
      "encryption": "client-aes256",
      "encryptionKey": "a3f1c2d4e5b6a7f8..."  # 64-char hex
    }
  },
  {
    "id": "staging",
    "name": "Staging DB",
    "mysqlConnectionString": "mysql://user:pass@staging-host:3306/staging_db",
    "duckdbPath": "data/staging.db",
    "createdAt": "2025-11-06T18:58:36.480Z",
    "updatedAt": "2025-11-06T18:58:36.480Z"
  }
]

Managing Databases via API

# Create new database
curl -X POST http://localhost:3001/api/databases \
  -H "Authorization: Bearer ${DUCKLING_API_KEY}" \
  -H "Content-Type: application/json" \
  -d '{
    "name": "Analytics DB",
    "mysqlConnectionString": "mysql://...",
    "duckdbPath": "data/analytics.db"
  }'

# Test connection
curl -X POST http://localhost:3001/api/databases/analytics/test \
  -H "Authorization: Bearer ${DUCKLING_API_KEY}"

# Sync specific database
curl -X POST 'http://localhost:3001/api/sync/full?db=analytics' \
  -H "Authorization: Bearer ${DUCKLING_API_KEY}"

S3 Backup Configuration

For production databases, configure S3 backups for disaster recovery.

Why S3 Backups?

Critical for large databases: A 200GB+ DuckDB database takes hours to rebuild from MySQL. S3 backups enable fast recovery by downloading a pre-built .db file.

S3 Setup

1. Generate encryption key:

node -e "console.log(require('crypto').randomBytes(32).toString('hex'))"

2. Configure via API:

curl -X PUT http://localhost:3001/api/databases/production/s3 \
  -H "Authorization: Bearer ${DUCKLING_API_KEY}" \
  -H "Content-Type: application/json" \
  -d '{
    "enabled": true,
    "bucket": "my-duckling-backups",
    "region": "us-east-1",
    "accessKeyId": "AKIAIOSFODNN7EXAMPLE",
    "secretAccessKey": "wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY",
    "pathPrefix": "production/",
    "encryption": "client-aes256",
    "encryptionKey": "a3f1c2d4e5b6a7f8..."
  }'

3. Test S3 connection:

curl -X POST http://localhost:3001/api/databases/production/s3/test \
  -H "Authorization: Bearer ${DUCKLING_API_KEY}"

S3-Compatible Providers

Provider	Endpoint	Force Path Style
AWS S3	(leave blank)	`false`
Cloudflare R2	`https://<account_id>.r2.cloudflarestorage.com`	`false`
Backblaze B2	`https://s3.<region>.backblazeb2.com`	`false`
DigitalOcean Spaces	`https://<region>.digitaloceanspaces.com`	`false`
MinIO (self-hosted)	`https://minio.internal:9000`	`true`

Encryption Options

Mode	Security	Performance	Recommendation
`none`	No encryption	Fastest	Development only
`sse-s3`	AWS-managed	Fast	Good for most cases
`sse-kms`	AWS KMS + audit trail	~1ms overhead	Compliance requirements
`client-aes256`	Client-side (best)	Streaming, no memory spike	Recommended for production

client-aes256: Encryption key never leaves your server. Protects against compromised AWS credentials and bucket misconfiguration.

Monitoring

Health Checks

# Docker health check
services:
  duckling:
    healthcheck:
      test: ["CMD", "curl", "-f", "http://localhost:3000/health"]
      interval: 30s
      timeout: 10s
      retries: 3
      start_period: 40s

Metrics Endpoints

# System health
curl http://localhost:3001/health

# Detailed status
curl http://localhost:3001/status \
  -H "Authorization: Bearer ${DUCKLING_API_KEY}"

# Sync metrics
curl http://localhost:3001/metrics \
  -H "Authorization: Bearer ${DUCKLING_API_KEY}"

# Automation status
curl http://localhost:3001/automation/status \
  -H "Authorization: Bearer ${DUCKLING_API_KEY}"

Log Management

# Centralized logging with Docker
docker run -d \
  --log-driver=syslog \
  --log-opt syslog-address=udp://logserver:514 \
  duckling:latest

# Or JSON logging
docker run -d \
  --log-driver=json-file \
  --log-opt max-size=100m \
  --log-opt max-file=10 \
  duckling:latest

Sentry Error Tracking

# Enable Sentry in .env
SENTRY_DSN=https://[email protected]/xxx
SENTRY_RELEASE=[email protected]
SENTRY_TRACES_SAMPLE_RATE=0.1

Backup & Disaster Recovery

Backup Strategy

Local Backups (automatic):

Created every 24 hours (BACKUP_INTERVAL_HOURS=24)
Kept for 7 days (BACKUP_RETENTION_DAYS=7)
Stored in ./data/backups/

S3 Backups (automatic when configured):

Uploaded after each local backup
Encrypted with client-side AES-256
No retention limit (managed by S3 lifecycle policies)

Manual Backup

# Trigger immediate backup (local + S3)
curl -X POST http://localhost:3001/automation/backup \
  -H "Authorization: Bearer ${DUCKLING_API_KEY}"

# S3 backup only
curl -X POST 'http://localhost:3001/api/backups/s3?db=production' \
  -H "Authorization: Bearer ${DUCKLING_API_KEY}"

Restore from Backup

From local backup:

# Restore latest local backup
curl -X POST http://localhost:3001/automation/restore \
  -H "Authorization: Bearer ${DUCKLING_API_KEY}"

From S3 backup:

# List S3 backups
curl 'http://localhost:3001/api/backups?db=production' \
  -H "Authorization: Bearer ${DUCKLING_API_KEY}"

# Restore specific backup
curl -X POST 'http://localhost:3001/api/backups/s3/restore?db=production' \
  -H "Authorization: Bearer ${DUCKLING_API_KEY}" \
  -H "Content-Type: application/json" \
  -d '{"key": "production/backup-2025-11-06T12-00-00.db"}'

Disaster Recovery Plan

Scenario 1: Data corruption

Stop sync: docker stop duckling
Restore from S3 backup (faster) or local backup
Verify data integrity: POST /sync/validate
Resume operations: docker start duckling

Scenario 2: Complete server failure

Provision new server
Install Docker and pull Duckling image
Mount backup volume or restore from S3
Start container with same .env configuration
Service resumes automatically

Recovery Time Objectives (RTO):

Local backup restore: 5-30 minutes (depends on database size)
S3 backup restore: 10-60 minutes (includes download time)
Full resync from MySQL: Hours to days (200GB+ databases)

Reverse Proxy Setup

Nginx Configuration

upstream duckling {
  server localhost:3000;
}

server {
  listen 80;
  server_name duckling.example.com;

  # Redirect to HTTPS
  return 301 https://$server_name$request_uri;
}

server {
  listen 443 ssl http2;
  server_name duckling.example.com;

  ssl_certificate /etc/ssl/certs/duckling.crt;
  ssl_certificate_key /etc/ssl/private/duckling.key;
  ssl_protocols TLSv1.2 TLSv1.3;

  # API and frontend
  location / {
    proxy_pass http://duckling;
    proxy_http_version 1.1;
    proxy_set_header Upgrade $http_upgrade;
    proxy_set_header Connection 'upgrade';
    proxy_set_header Host $host;
    proxy_cache_bypass $http_upgrade;
    proxy_set_header X-Real-IP $remote_addr;
    proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
    proxy_set_header X-Forwarded-Proto $scheme;
    
    # Increase timeouts for long queries
    proxy_read_timeout 300s;
    proxy_connect_timeout 75s;
  }

  # Rate limiting
  limit_req_zone $binary_remote_addr zone=api:10m rate=10r/s;
  limit_req zone=api burst=20 nodelay;
}

Traefik Configuration

labels:
  - "traefik.enable=true"
  - "traefik.http.routers.duckling.rule=Host(`duckling.example.com`)"
  - "traefik.http.routers.duckling.entrypoints=websecure"
  - "traefik.http.routers.duckling.tls.certresolver=letsencrypt"
  - "traefik.http.services.duckling.loadbalancer.server.port=3000"

Scaling Considerations

Vertical Scaling (Single Instance)

Best for:

Single database replication
<100GB databases
<1000 queries/minute

Limits:

Node.js single-threaded execution
DuckDB file locking (single writer)

Horizontal Scaling (Read Replicas)

Best for:

Read-heavy workloads
Multiple geographic regions
1000 queries/minute

services:
  duckling-primary:
    environment:
      - READ_REPLICA_ENABLED=false

  duckling-replica-1:
    environment:
      - READ_REPLICA_ENABLED=true
      - REPLICA_REFRESH_INTERVAL=300

  duckling-replica-2:
    environment:
      - READ_REPLICA_ENABLED=true
      - REPLICA_REFRESH_INTERVAL=300

Security Hardening

See Security Configuration for detailed security best practices.

Troubleshooting

High Memory Usage

# Check memory stats
docker stats duckling

# Reduce batch sizes
BATCH_SIZE=1000
INSERT_BATCH_SIZE=1000

# Increase heap size
NODE_OPTIONS="--max-old-space-size=16384"

Slow Queries

# Enable query logging
LOG_LEVEL=debug

# Check slow queries in logs
docker logs duckling | grep "Query took"

# Increase worker threads
WORKER_THREADS=4

Sync Failures

# Check sync logs
curl http://localhost:3001/api/sync-logs?status=error \
  -H "Authorization: Bearer ${DUCKLING_API_KEY}"

# View detailed logs
docker logs duckling | grep "sync"

# Trigger manual sync
curl -X POST http://localhost:3001/api/sync/full \
  -H "Authorization: Bearer ${DUCKLING_API_KEY}"

Next Steps

Security

Harden your production deployment

Configuration

Fine-tune environment variables

Docker Guide

Docker deployment details

Get Started

Core Concepts

Setup & Deployment

Operations

Advanced Features

​Overview

​Pre-Deployment Checklist

​Deployment Architecture

​Single Container (Recommended)

​Multi-Instance (High Availability)

​Resource Allocation

​Memory Guidelines

​CPU Allocation

​Disk Space

​Performance Optimization

​Sync Configuration

​Query Performance

​Connection Pooling

​Zero-Downtime Automation

​Automatic Sync

​Automatic Backups

​Health Monitoring

​Storage Management

​Multi-Database Configuration

​Database Configuration File

​Managing Databases via API

​S3 Backup Configuration

​Why S3 Backups?

​S3 Setup

​S3-Compatible Providers

​Encryption Options

​Monitoring

​Health Checks

​Metrics Endpoints

​Log Management

​Sentry Error Tracking

​Backup & Disaster Recovery

​Backup Strategy

​Manual Backup

​Restore from Backup

​Disaster Recovery Plan

​Reverse Proxy Setup

​Nginx Configuration

​Traefik Configuration

​Scaling Considerations

​Vertical Scaling (Single Instance)

​Horizontal Scaling (Read Replicas)

​Security Hardening

​Troubleshooting

​High Memory Usage

​Slow Queries

​Sync Failures

​Next Steps