How to Configure Automatic Database Backups in Openship: A Complete Guide

Openship configures automatic database backups by creating a backup destination (storage endpoint) and a backup policy (schedule and retention rules), then executing runs through a finite-state machine that stages, snapshots, uploads, and verifies your data.

Openship provides an adapter-based backup system that protects your PostgreSQL and other databases through automated scheduling. To configure automatic database backups in Openship, you chain together four core database tables—backup_destination, backup_policy, backup_run, and backup_restore—that manage storage credentials, schedules, executions, and restores. This guide walks through the complete setup using both the CLI and Web UI, referencing the actual schema defined in the oblien/openship repository.

Understanding Openship's Backup Architecture

Openship persists backup configuration across four relational tables defined in packages/db/src/schema/backup.ts:

• backup_destination – Stores user-owned storage endpoints (S3, SFTP, existing server, or local disk). Credentials are encrypted using an enc1: envelope in columns like access_key_id_enc and secret_access_key_enc.

• backup_policy – Defines what to back up, where to store it, and when to run. Key columns include cron_expression, retain_count, retain_days, and trigger_on_pre_deploy.

• backup_run – Records each execution (queued → preparing → snapshotting → uploading → verifying → succeeded/failed). Survives policy deletion and includes an artifacts JSON field.

• backup_restore – Tracks restore operations that re-apply previous runs. References are RESTRICT constrained to prevent accidental data loss.

The Backup Orchestrator implements a finite-state machine that drives each backup_run through its lifecycle stages. When a run completes successfully, the system prunes older runs according to the policy's retention limits unless the run is protected via retention_locked_until.

Setting Up a Backup Destination

Before scheduling backups, you must define where Openship stores the data. Destinations support S3-compatible storage, SFTP, or local disk.

Create an S3-Compatible Destination

openship backup destination create \
  --name "Production R2" \
  --kind s3_compatible \
  --endpoint https://<account>.r2.cloudflarestorage.com \
  --bucket my-backups \
  --region auto \
  --access-key-id <ACCESS_KEY_ID> \
  --secret-access-key <SECRET_ACCESS_KEY>

Verify Connectivity

Run the preflight check to ensure Openship can write test files to the endpoint:

openship backup destination preflight <destinationId>

These CLI commands are implemented in apps/cli/src/commands/backup.ts, which calls repository functions defined in packages/db/src/repos/backup.repo.ts to persist encrypted credentials.

Creating Automatic Backup Policies

A backup policy links your service or project to a destination and defines the schedule. Policies support standard 5-field cron expressions for recurring execution.

Schedule Daily Backups with Retention

openship backup policy create \
  --project <projectId> \
  --service <serviceId> \
  --destination <destinationId> \
  --cron "0 3 * * *" \
  --retain-count 7 \
  --retain-days 30 \
  --pre-deploy

Key parameters:

• --cron "0 3 * * *" – Runs daily at 03:00 UTC.
• --retain-count 7 – Keeps the last 7 successful runs.
• --retain-days 30 – Deletes runs older than 30 days.
• --pre-deploy – Triggers a backup immediately before each deployment of the associated service.

The trigger_on_pre_deploy Boolean in packages/db/src/schema/backup.ts creates an implicit run whenever you deploy, providing a safety net against bad releases.

Adding Database Pre-Hooks

For raw database dumps, configure a pre-hook command that runs on the host before the snapshotting stage. If the hook fails, the entire backup run aborts.

Configure a PostgreSQL Dump Hook

openship backup policy update <policyId> \
  --pre-hook "PGPASSWORD=$DB_PASS pg_dump -U $DB_USER $DB_NAME > /tmp/dump.sql"

The hook's output is captured in the run's artifacts field. The subsequent upload stage automatically includes these files in the backup sent to your destination.

Managing Backup Runs and Restores

Once policies are active, Openship automatically creates backup_run records according to your cron schedule. You can also manage runs manually via the CLI.

Trigger a Manual Backup

openship backup policy run <policyId> --follow

The --follow flag streams real-time status updates through the orchestrator's state transitions.

Protect Runs from Pruning

Prevent automatic deletion of critical backups:

openship backup run protect <runId>

This sets retention_locked_until to a future date, bypassing the retain_count and retain_days limits.

Restore from Backup

Restores are deliberately a two-step process to prevent accidental data destruction:

# Stage and verify the restore (generates confirmation token)

openship backup run restore <runId>

# Apply the restore (destructive operation)

openship backup restore apply <restoreId> \
  --token <confirmationToken> \
  --follow

The backup_restore table tracks the operation status and requires the confirmation token generated during staging.

Key Implementation Files

Reference these source files when customizing or debugging your backup configuration:

Component	Path
Backup schema	packages/db/src/schema/backup.ts – Defines backup_destination, backup_policy, backup_run, and backup_restore tables.
Repository layer	packages/db/src/repos/backup.repo.ts – CRUD operations and SQL queries for backup entities.
CLI commands	apps/cli/src/commands/backup.ts – Implements openship backup destination, policy, and run subcommands.
API documentation	apps/web/content/docs/api/backups.mdx – REST endpoints for programmatic policy management.
UI guide	apps/web/content/docs/guides/backups-restore.mdx – Step-by-step Web UI walkthrough with screenshots.

Summary

• Configure automatic database backups in Openship by chaining four tables: destinations define storage, policies define schedules, runs record executions, and restores track recovery.
• Use backup_destination to store encrypted credentials for S3, SFTP, or local storage endpoints.
• Set cron_expression in backup_policy for automated scheduling, and enable trigger_on_pre_deploy for pre-deployment safety nets.
• Add database-specific pre-hooks (like pg_dump) to capture SQL dumps before the upload stage.
• Protect critical runs with openship backup run protect to override retention policies.
• Restore operations require explicit two-step confirmation to prevent accidental data loss.

Frequently Asked Questions

What storage providers does Openship support for backups?

Openship supports S3-compatible storage (including AWS S3 and Cloudflare R2), SFTP servers, existing servers via SSH, and local disk destinations. Credentials are encrypted using the enc1: envelope before storage in the backup_destination table.

How does retention work in Openship backups?

Each backup policy specifies retain_count (number of successful runs to keep) and retain_days (maximum age before deletion). The orchestrator prunes eligible runs after each successful backup, unless a run is protected via retention_locked_until or you run openship backup run protect <runId>.

Can I trigger a backup manually outside the schedule?

Yes. Use openship backup policy run <policyId> --follow to immediately create a backup_run record and stream its progress. This is useful for testing configurations or creating on-demand snapshots before major database migrations.

How do I restore a database from a backup in Openship?

Run openship backup run restore <runId> to stage and verify the backup, which returns a confirmation token. Then execute openship backup restore apply <restoreId> --token <confirmationToken> to actually overwrite your current data. This two-step process prevents accidental destructive operations.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}