Skip to content

guide.md

Latest commit

 

History

History
263 lines (191 loc) · 7.67 KB

guide.md

File metadata and controls

263 lines (191 loc) · 7.67 KB
Raw

Backup Script Guide

Overview

This script performs two operations:

  1. Backup Creation - Dumps a database (PostgreSQL or MySQL) to a local SQL file
  2. Cloud Upload - Queues the backup for upload to DigitalOcean Spaces, then deletes the local file

The backup and upload are decoupled via a Redis-backed job queue (BullMQ), allowing the worker to run on a different machine or at a different time.

Architecture

┌─────────────────┐     ┌─────────────┐     ┌─────────────────┐
│  Create Backup  │────▶│    Redis    │────▶│     Worker      │
│  (pg_dump or    │     │   (Queue)   │     │  (S3 Upload)    │
│   mysqldump)    │     │             │     │                 │
└─────────────────┘     └─────────────┘     └─────────────────┘
        │                                           │
        ▼                                           ▼
  local .sql file                            DigitalOcean
                                               Spaces

Installation

npm install

Configuration

Copy .env.example to .env and configure the following variables:

Database Connection

Variable Description
DATABASE_CONNECTION Database type: postgresql or mysql (default: postgresql)
DB_HOST Database host
DB_PORT Database port (default: 5432 for PostgreSQL, 3306 for MySQL)
DB_NAME Database name to backup
DB_USER Database username
DB_PASSWORD Database password

Redis

Variable Description
RD_HOST Redis host
RD_PORT Redis port (default: 6379)

DigitalOcean Spaces

Variable Description
DO_SPACES_KEY Spaces access key
DO_SPACES_SECRET Spaces secret key
DO_SPACES_BUCKET Spaces bucket name
DO_SPACES_REGION Spaces region (e.g., nyc3)
DO_SPACES_ENDPOINT Spaces endpoint URL

Optional

Variable Description
BACKUP_STORAGE_DIR Local directory for temp backup files (default: src/storage)
CRON_SCHEDULE Cron schedule expression (default: 0 2 * * * - daily at 2:00 AM)

Usage

Run the Backup (with Cron)

Starts the backup script with a cron schedule. The process runs continuously and executes backups according to CRON_SCHEDULE:

npm run backup

An initial backup runs immediately on startup.

Cron Schedule Examples

Schedule Description
* * * * * Every minute (for testing)
0 * * * * Every hour
0 2 * * * Daily at 2:00 AM
0 */6 * * * Every 6 hours

Run the Worker

Processes queued backup jobs (upload to Spaces + local cleanup):

npm run worker

Run Both

For simple setups, run both in the background or as separate processes:

npm run backup &   # Start backup first
npm run worker     # Then start worker

Choosing Database Type

Set DATABASE_CONNECTION in your .env file:

# For PostgreSQL
DATABASE_CONNECTION=postgresql
DB_PORT=5432

# For MySQL
DATABASE_CONNECTION=mysql
DB_PORT=3306

Make sure the corresponding command-line tool is installed:

  • PostgreSQL: pg_dump must be available
  • MySQL: mysqldump must be available

Testing

npm test

Runs syntax validation on all JavaScript files.

File Structure

src/
├── app.js                    # Main entry point - creates backup + dispatches job
├── config/
│   ├── env.js               # Environment variable exports
│   ├── db-connection.js     # PostgreSQL client
│   ├── redis-connection.js  # Redis client
│   └── space-client.js      # DigitalOcean Spaces (S3) client
├── queue/
│   ├── backup-queue.js      # BullMQ queue setup
│   └── constants.js         # Queue name constant
├── services/
│   └── create-backup.js     # Local database dump execution (routes to pg-dump or mysql-dump)
├── worker/
│   └── worker.js            # Job processor - uploads to Spaces
└── utils/
    ├── backup-paths.js      # Path generation for backup files
    ├── env.js               # Environment validation
    ├── log-cleanup.js      # Log file cleanup utility
    ├── logger.js            # File-based logging
    ├── pg-dump.js          # pg_dump wrapper
    └── mysql-dump.js       # mysqldump wrapper

Output Format

Backups are organized by database type, database name, and date. This allows multiple databases to be stored in the same DigitalOcean Space with a clean, hierarchical structure.

Local Storage

{BACKUP_STORAGE_DIR}/{database_type}/{database_name}/{YYYY-MM-DD}/{dbName}-{timestamp}.sql

Example:

storage/postgresql/mydrive/2026-06-18/mydrive-2026-06-18T08-00-00-024Z.sql
storage/mysql/database1/2026-06-18/database1-2026-06-18T08-30-00-025Z.sql

DigitalOcean Spaces

backups/{database_type}/{database_name}/{YYYY-MM-DD}/{dbName}-{timestamp}.sql

Example:

backups/postgresql/mydrive/2026-06-18/mydrive-2026-06-18T08-00-00-024Z.sql
backups/mysql/database1/2026-06-18/database1-2026-06-18T08-30-00-025Z.sql

Structure Overview

storage/
├── mysql/
│   └── {database_name}/
│       └── {YYYY-MM-DD}/
│           └── {dbName}-{timestamp}.sql
└── postgresql/
    └── {database_name}/
        └── {YYYY-MM-DD}/
            └── {dbName}-{timestamp}.sql

backups/ (in DigitalOcean Spaces)
├── mysql/
│   └── {database_name}/
│       └── {YYYY-MM-DD}/
│           └── {dbName}-{timestamp}.sql
└── postgresql/
    └── {database_name}/
        └── {YYYY-MM-DD}/
            └── {dbName}-{timestamp}.sql

This structure enables:

  • Multiple databases of different types in one Space
  • Easy navigation and manual file retrieval
  • Clear organization by database type, name, and date

Log Files

Logs are stored in the logs/ directory with automatic cleanup (7-day retention):

  • logs/backup-{date}.log - Backup process logs
  • logs/worker-{date}.log - Worker process logs
  • logs/queue-{date}.log - Queue events
  • logs/log-cleanup-{date}.log - Log cleanup process

Troubleshooting

pg_dump or mysqldump not found

Ensure the PostgreSQL or MySQL client tools are installed on your system:

# Ubuntu/Debian
sudo apt install postgresql-client
# or
sudo apt install mysql-client

# macOS
brew install postgresql
# or
brew install mysql

Redis connection failed

Ensure Redis is running:

redis-server

DigitalOcean Spaces upload failed

Check your Spaces credentials in .env and ensure the bucket exists.

License

ISC