stefanwalther/pg-backup repository overview

⁠PostgreSQL Backup to S3

Automated PostgreSQL backups to AWS S3. Run the Docker container with your database and S3 credentials to get daily backups with both SQL dumps and JSON exports.

⁠Features

• Dual Backup Strategy: Full SQL dumps + individual table JSON exports
• Zero Configuration: Just set environment variables and run
• Automated Scheduling: Built-in cron for daily backups at 2 AM UTC
• Cloud Native: Supports AWS S3 and S3-compatible storage (Garage for local testing)
• Secure: SSL support for cloud databases (NEON, RDS, etc.)
• Observable: Comprehensive logging with metrics
• Multi-Platform: Available for amd64 and arm64

⁠Quick Start

# Run with your environment variables
docker run -d \
  -e POSTGRES_HOST=your-db.host.com \
  -e POSTGRES_DB=mydb \
  -e POSTGRES_USER=myuser \
  -e POSTGRES_PASSWORD=mypassword \
  -e POSTGRES_SSL=true \
  -e S3_BUCKET=my-backup-bucket \
  -e AWS_ACCESS_KEY_ID=your-key \
  -e AWS_SECRET_ACCESS_KEY=your-secret \
  --name pg-backup \
  stefanwalther/pg-backup:latest

For production use, create an .env file with your configuration (see Configuration⁠ below) and run:

docker run -d --env-file .env --name pg-backup stefanwalther/pg-backup:latest

The container will automatically backup your database daily at 2 AM UTC. You'll get both a complete SQL dump and individual JSON files for each table.

⁠Configuration

All configuration is done through environment variables. Create an .env file or pass them to Docker:

⁠Required Variables

# PostgreSQL Connection
POSTGRES_HOST=your-database.host.com
POSTGRES_PORT=5432             # Default: 5432
POSTGRES_DB=mydb
POSTGRES_USER=myuser
POSTGRES_PASSWORD=mypassword
POSTGRES_SSL=true              # Required for cloud databases
# POSTGRES_SSL_VERIFY=false    # Only for self-signed hosts, default verifies

# AWS S3 Storage
S3_BUCKET=my-backup-bucket
AWS_ACCESS_KEY_ID=your-key
AWS_SECRET_ACCESS_KEY=your-secret
AWS_REGION=us-east-1           # Default: us-east-1

# S3-Compatible Storage (optional, e.g. Garage for local testing)
# S3_ENDPOINT=http://localhost:3900
# S3_FORCE_PATH_STYLE=true

⁠Optional Variables

# Storage Target
STORAGE_TARGET=s3               # s3 (default), local, or both

# Local Filesystem Storage (required for local/both)
LOCAL_BACKUP_DIR=./backups      # base directory, relative resolves against cwd
LOCAL_PGDUMP_FOLDER=sql-backups # SQL dumps location (default: sql-backups)
LOCAL_JSON_FOLDER=json-backups  # JSON exports location (default: json-backups)

# S3 Folder Organization
S3_PGDUMP_FOLDER=sql-backups   # SQL dumps location (default: sql-backups)
S3_JSON_FOLDER=json-backups    # JSON exports location (default: json-backups)

# Backup Settings
EXPORT_TABLES=users,orders     # Comma-separated table list (default: all tables)

# Alternative: Use connection URL instead of individual params
DATABASE_URL=postgresql://user:pass@host:5432/db?sslmode=require

# Logging
LOG_LEVEL=info                 # error, warn, info, debug
LOG_FILE=backup.log           # Optional log file path

See env.example⁠ for a complete configuration template.

⁠Backup Structure

The backup creates the following structure in S3, or on disk when STORAGE_TARGET includes local:

your-bucket/  # or LOCAL_BACKUP_DIR/
├── sql-backups/
│   └── 2024-01-15/
│       └── backup_mydb_20240115T020000000Z.sql.gz
└── json-backups/
    └── 2024-01-15/
        ├── users_2024-01-15.json.gz
        ├── orders_2024-01-15.json.gz
        └── products_2024-01-15.json.gz

Date folders are YYYY-MM-DD. JSON files are named <table>_<YYYY-MM-DD>.json.gz, SQL dumps backup_<db>_<timestamp>.sql.gz (timestamp derived from ISO format).

Local example with Docker:

```bash
docker run -d \
  -e STORAGE_TARGET=local \
  -e LOCAL_BACKUP_DIR=/app/backups \
  -v ./backups:/app/backups \
  --env-file .env --name pg-backup stefanwalther/pg-backup:latest

The container runs as a non-root user, so the mounted directory must be writable by it.

⁠Deployment Options

⁠1. Docker (Recommended)

The easiest way to deploy is using our pre-built Docker images with automated daily backups via cron.

⁠2. GitHub Actions

Run backups using GitHub Actions for a serverless approach:

1. Fork this repository
2. Copy the example workflow to enable scheduled backups:

   cp .github/workflows/scheduled-backup.yml.example .github/workflows/scheduled-backup.yml
   

   Copy
3. Add these secrets to your repository (Settings → Secrets → Actions):
   • Database credentials: POSTGRES_HOST, POSTGRES_PORT, POSTGRES_DB, POSTGRES_USER, POSTGRES_PASSWORD, POSTGRES_SSL (or DATABASE_URL)
   • AWS credentials: AWS_ACCESS_KEY_ID, AWS_SECRET_ACCESS_KEY, AWS_REGION, S3_BUCKET
   • Optional: STORAGE_TARGET (s3 default, local, or both) plus LOCAL_BACKUP_DIR / LOCAL_*_FOLDER when the target includes local
4. Commit and push the workflow file

The workflow runs daily at 2 AM UTC and can be triggered manually. See GitHub Actions Setup Guide⁠ for details.

⁠3. Kubernetes

Deploy using a CronJob:

apiVersion: batch/v1
kind: CronJob
metadata:
  name: pg-backup
spec:
  schedule: "0 2 * * *"
  jobTemplate:
    spec:
      template:
        spec:
          containers:
          - name: pg-backup
            image: stefanwalther/pg-backup:latest
            envFrom:
            - secretRef:
                name: pg-backup-config
          restartPolicy: OnFailure

⁠Monitoring

The application provides comprehensive logging:

• Backup completion status with execution time
• Database and table metrics (size, row counts)
• Backup file sizes
• Detailed error traces for debugging

Logs are written to stdout (for Docker logs) and optionally to a file in JSON format.

⁠Security Best Practices

• Never commit credentials - use environment variables
• Use IAM roles when running on AWS infrastructure
• Enable S3 bucket encryption and versioning
• Rotate access credentials regularly
• Apply least-privilege access principles

pnpm run build            # Build TypeScript
pnpm run dev              # Run in development mode
pnpm run lint             # Run ESLint
pnpm run check            # Quick checks: format + lint + typecheck (before every commit)
pnpm run test:unit        # Unit tests only
pnpm run test:integration # Integration tests (requires Docker + PostgreSQL client)
pnpm run test             # All tests (unit + integration)
pnpm run test:ci          # Same as test:unit (what CI runs without Docker)
pnpm run ci:local         # Full local CI simulation incl. Docker build

# Start test environment (PostgreSQL + Garage S3-compatible storage)
docker compose --profile test up -d

# Run tests
pnpm test

# Clean up
docker compose --profile test down

# Build locally
docker build -t pg-backup .

# Test the image
docker run --rm pg-backup node -v
docker run --rm pg-backup pg_dump --version

pg-backup/
├── src/
│   └── backup.ts              # Main backup logic
├── tests/
│   ├── config.test.ts         # Env config unit tests
│   ├── storage-target.unit.test.ts  # STORAGE_TARGET unit tests
│   ├── sql-dump-url.unit.test.ts    # DATABASE_URL pg_dump unit tests
│   ├── backup.integration.test.ts   # Integration tests (testcontainers)
│   └── setup.ts
├── scripts/
│   ├── quick-check.sh         # pnpm run check
│   └── test-ci-local.sh       # pnpm run ci:local
├── docker/
│   └── garage.toml            # Local Garage S3 config (testing only)
├── .github/workflows/        # CI, Docker publish, Release Please, secret scanning
├── docs/                     # Documentation (see docs/README.md)
├── Dockerfile                # Multi-stage Docker build (Node 26, cron, non-root)
├── docker-compose.yml        # Local dev/test services (postgres + Garage)
├── env.example               # Configuration template
└── package.json              # Dependencies and scripts

⁠License

Copyright (c) 2024-present Stefan Walther

MIT License - see LICENSE⁠ file for details.

⁠Support

• Issue Tracker⁠
• Documentation⁠
• Docker Hub⁠