pad/docs/backup.md

Backup & Restore Guide

Overview

Pad provides built-in tooling for database backup, restore, and migration between SQLite and PostgreSQL.

Command	Description
pad db backup	Database backup — SQLite (VACUUM INTO, default) or PostgreSQL (pg_dump)
pad db restore <file>	Database restore — SQLite (file copy) or PostgreSQL (psql)
pad db migrate-to-pg	One-time SQLite → PostgreSQL migration
pad workspace export	Application-level JSON export (portable)
pad workspace import	Application-level JSON import

pad db backup / pad db restore auto-detect the driver: PostgreSQL when PAD_DB_DRIVER=postgres (or PAD_DATABASE_URL is set), SQLite otherwise. The SQLite database path is resolved exactly as the server resolves it — PAD_DB_PATH > PAD_DATA_DIR/pad.db > ~/.pad/pad.db — so it works inside the Docker image (which sets PAD_DATA_DIR=/data) without extra flags.

SQLite Backups

SQLite stores everything in a single file (default: ~/.pad/pad.db). The canonical, online-safe way to back it up is pad db backup:

# Online-safe single-file backup via VACUUM INTO (safe while the server runs)
pad db backup -o ~/backups/pad-$(date +%Y%m%d).db

# Omit -o to get a timestamped pad-backup-YYYYMMDD-HHMMSS.db in the cwd
pad db backup

pad db backup uses SQLite's VACUUM INTO, which reads a consistent snapshot through the SQLite engine and writes a single fully-checkpointed file — no -wal/-shm sidecars to juggle, and no torn copy if the server is mid-write. The database path is resolved the same way the server resolves it (see above), so no --from/path flag is needed.

Docker

Inside the official image (PAD_DATA_DIR=/data), run the backup through the container so it resolves /data/pad.db automatically:

# Write the backup to the mounted /data volume, then copy it off-host
docker exec <container> pad db backup -o /data/backup.db
docker cp <container>:/data/backup.db ./pad-backup.db

Avoid cp-ing pad.db out from under a running server — a plain file copy can tear against in-flight WAL writes and silently lose the -wal contents.

Restore

# Stop the server first — restore refuses to run while it detects a live
# server (a running WAL checkpoint could clobber the restored file).
pad server stop
pad db restore ~/backups/pad-20250101.db
pad server start

Restore writes the backup over the resolved database path and clears any stale -wal/-shm sidecars. Use --force to skip the confirmation prompt and override the live-server guard (not recommended while the server is running).

PostgreSQL Backups

Manual Backup

# Create a SQL dump
pad db backup

# Specify output file
pad db backup --output /backups/pad-backup.sql

Requires:

• pg_dump installed
• PAD_DATABASE_URL environment variable set

Automated Backups (Cron)

# Add to crontab: daily backup at 2 AM
0 2 * * * PAD_DATABASE_URL="postgres://pad:secret@localhost:5432/pad" /usr/local/bin/pad db backup --cron --output /backups/pad-$(date +\%Y\%m\%d).sql

The --cron flag uses structured log output suitable for log aggregation systems.

Both PostgreSQL commands accept postgres:// / postgresql:// URIs and libpq keyword/value connection strings in PAD_DATABASE_URL. Database passwords are passed to the native client through its environment, not its arguments. For a PostgreSQL service configuration, keep the password in that service rather than also supplying an inline password; service settings override environment defaults. Likewise, configure an SSL key passphrase (sslpassword) in a service file, since libpq has no corresponding environment variable.

Restore

# Restore from backup (will prompt for confirmation)
pad db restore /backups/pad-backup.sql

# Skip confirmation (for automated restore)
pad db restore --force /backups/pad-backup.sql

Cloud Database Snapshots

For managed PostgreSQL (AWS RDS, Google Cloud SQL, Azure Database):

• AWS RDS: Use automated backups + manual snapshots via the AWS Console or CLI
• Google Cloud SQL: Enable automated backups in instance settings
• Azure: Configure automated backups via the portal

These are generally preferred over pg_dump for large databases as they use filesystem-level snapshots.

Migrating SQLite → PostgreSQL

When graduating from a local SQLite setup to production PostgreSQL:

# 1. Set up PostgreSQL and create the database
createdb pad

# 2. Run Pad once against PostgreSQL to create the schema
PAD_DB_DRIVER=postgres PAD_DATABASE_URL="postgres://pad:secret@localhost:5432/pad" pad server start &
# Wait a few seconds for migrations to run, then stop it
kill %1

# 3. Migrate workspace data
pad db migrate-to-pg \
  --from ~/.pad/pad.db \
  --to "postgres://pad:secret@localhost:5432/pad"

# 4. Create an admin account on the new database
PAD_DB_DRIVER=postgres PAD_DATABASE_URL="postgres://pad:secret@localhost:5432/pad" pad auth setup

# 5. Start the server with PostgreSQL
PAD_DB_DRIVER=postgres PAD_DATABASE_URL="postgres://pad:secret@localhost:5432/pad" pad server start

What gets migrated:

• Workspaces, collections, items, comments
• Item links (dependencies)
• Item versions (history)

What does NOT get migrated:

• User accounts and sessions (re-create with pad auth setup)
• Platform settings (reconfigure in admin panel)
• Activity/audit log (starts fresh)

Application-Level Export/Import

For portable workspace backups that work across SQLite and PostgreSQL:

# Export a workspace to JSON
pad workspace export > my-workspace.json

# Import into any Pad instance (SQLite or PostgreSQL).
# The file is an ARGUMENT, not stdin — `pad workspace import < file` fails
# with "accepts 1 arg(s), received 0".
pad workspace import my-workspace.json

# Import with a new name
pad workspace import --name "imported-workspace" my-workspace.json

Item reference numbers are preserved when the imported items have positive, workspace-unique numbers, including gaps left by deleted items. This keeps references such as [[TASK-2]] pointing to the same task after restoration or SQLite→PostgreSQL migration. Legacy archives with duplicate, missing, or invalid numbers retain the sequential-renumbering fallback; references in those archives may need manual repair.

One case where an export is not importable

A workspace whose stored data contains a NUL character exports fine and is refused on import, with a 400 naming the cause. This is not a corruption of your backup — it is the import applying a rule the write path now applies too (BUG-2803): Pad does not accept a NUL in a text or JSON value. That is an application rule, not a universal storage fact — PostgreSQL does refuse a NUL outright, but SQLite accepts one in a TEXT column, which is why the rule has to be enforced rather than assumed, and why the paragraphs below matter.

The rule is now enforced by the database as well as by the binary. It used to live only in the running build, which meant any window where an older binary served the same SQLite database could still create such rows — a rollback, a staged rollout, a second old instance pointed at the same file. A schema migration now installs triggers that refuse the write in the database itself, so an older binary writing to an upgraded file is refused too (BUG-2813). The window that remains is a SQLite database an upgraded binary has never opened: until its migrations run, it has no triggers.

Only SQLite ever needed this. PostgreSQL refuses a NUL in a text or JSON column itself, at every binary version, so a PostgreSQL instance never stored such a value regardless of which build wrote it.

None of that helps a row that was already stored, which is what the two commands below are for.

Finding and repairing affected rows

pad db scan-nul reports every stored value carrying a NUL — which table and column, which row, and which workspace — and changes nothing:

pad db scan-nul                       # the live database
pad db scan-nul --from /backups/pad-20260901.db   # or a backup file

pad db repair-nul rewrites those values, replacing each NUL with U+FFFD (the Unicode replacement character) and leaving the rest of the value byte for byte as it was. It changes stored content, which is why it is a separate command and never part of a migration — running a schema upgrade should not rewrite your text on your behalf. Run the scan first; it is the dry run. Running the repair twice is safe.

pad server stop
pad db repair-nul                     # lists what it will change, then asks
pad server start

A row whose primary key is the value carrying the NUL is reported and left alone: repairing it would change the row's identity and could collide with another row. email_optouts is the only table where that can happen today.

Migrating to PostgreSQL

pad db migrate-to-pg now runs the same scan as a preflight. If the source database carries any affected rows it lists them, prints the repair command, and exits without moving anything — rather than failing partway through the copy against PostgreSQL's JSONB parser, which is what it used to do.

One shape is checked differently, and it is worth knowing why. A JSON value with LITERAL duplicate keys — {"a":"...","a":"..."} — hides anything in the shadowed copy from every check Pad makes, because the JSON decoder keeps only the last. PostgreSQL still refuses it. Rather than let such a row through, the preflight asks the destination directly: any value that merely mentions a NUL escape is cast on the target database before anything moves, and the migration is refused if PostgreSQL rejects it. That check is exact in both directions — a document that only writes about the escape is accepted, as it should be.

pad db scan-nul lists those values under a separate heading, and pad db repair-nul fixes the fatal shape while leaving the harmless ones byte for byte as they were.

Two things to know about that check:

• It errs toward refusing. If the destination cannot be reached, or a listed row cannot be read back, the migration is refused rather than attempted — an unchecked value is not a passed one. Re-run once the destination is reachable.
• It can refuse a migration that would have worked. The check casts the value as it is stored, and one column — a workspace's settings — is normalised on the way in, which happens to drop the hidden value. Such a row is still a value Pad refuses to write today, so pad db repair-nul clears it and the migration proceeds.

Importing an export that predates the rule

If you have an export file taken from an affected database, the import still refuses it by default and the 400 names the remedy. Passing --repair-nul applies the same U+FFFD substitution to the payload as it is imported:

pad workspace import --repair-nul my-workspace.json

The default stays strict, and the flag is your consent to the rewrite; the command reports how many values it changed. It repairs the payload the way the server reads it, so it reaches a NUL wherever an export can carry one — including inside an item's fields blob, which travels through an export as a quoted document rather than as plain text.

Repairing the source database with pad db repair-nul and re-exporting gives the same result without a rewrite at import time, and is the better option when you still have the source instance.

This format is database-agnostic and can be used to:

• Transfer workspaces between Pad instances
• Create workspace templates
• Back up individual workspaces

Backup Strategy Recommendations

Small Teams (SQLite)

Daily: pad db backup --cron -o /backups/pad-$(date +%Y%m%d).db  (online-safe VACUUM INTO)
Weekly: Rotate old backups (keep 4 weeks)

Production (PostgreSQL)

Continuous: WAL archiving (point-in-time recovery)
Daily: pg_dump via 'pad db backup --cron'
Weekly: Full filesystem snapshot (if using managed DB)
Monthly: Test restore procedure

Disaster Recovery Checklist

• Backups are being created on schedule
• Backups are stored off-site (different region/provider)
• Restore procedure has been tested recently
• Recovery time objective (RTO) is documented
• Recovery point objective (RPO) is documented