# How to Configure Automatic Database Backups in Openship: A Complete Guide > Learn to configure automatic database backups in Openship. Set up storage and policies, then automate staging, snapshotting, uploading, and verification for secure data. - Repository: [oblien/openship](https://github.com/oblien/openship) - Tags: how-to-guide - Published: 2026-07-23 --- **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`](https://github.com/oblien/openship/blob/main/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 ```bash openship backup destination create \ --name "Production R2" \ --kind s3_compatible \ --endpoint https://.r2.cloudflarestorage.com \ --bucket my-backups \ --region auto \ --access-key-id \ --secret-access-key ``` ### Verify Connectivity Run the preflight check to ensure Openship can write test files to the endpoint: ```bash openship backup destination preflight ``` These CLI commands are implemented in [`apps/cli/src/commands/backup.ts`](https://github.com/oblien/openship/blob/main/apps/cli/src/commands/backup.ts), which calls repository functions defined in [`packages/db/src/repos/backup.repo.ts`](https://github.com/oblien/openship/blob/main/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 ```bash openship backup policy create \ --project \ --service \ --destination \ --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`](https://github.com/oblien/openship/blob/main/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 ```bash openship backup policy update \ --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 ```bash openship backup policy run --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: ```bash openship backup run protect ``` 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: ```bash # Stage and verify the restore (generates confirmation token) openship backup run restore # Apply the restore (destructive operation) openship backup restore apply \ --token \ --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`](https://github.com/oblien/openship/blob/main/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`](https://github.com/oblien/openship/blob/main/packages/db/src/repos/backup.repo.ts) – CRUD operations and SQL queries for backup entities. | | **CLI commands** | [`apps/cli/src/commands/backup.ts`](https://github.com/oblien/openship/blob/main/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 `. ### Can I trigger a backup manually outside the schedule? Yes. Use `openship backup policy run --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 ` to stage and verify the backup, which returns a confirmation token. Then execute `openship backup restore apply --token ` to actually overwrite your current data. This two-step process prevents accidental destructive operations.