DB Backup & Restore Runbook

Operators only

This page is not something RP integration developers need to worry about. It covers logi's own operations — DR (Disaster Recovery), periodic restore drills, and recovery procedures for migration incidents.

1. Automatic snapshots (Render PG)

Render Postgres keeps one automatic snapshot per day. The retention period depends on the instance plan:

Plan	Retention
basic_256mb (current logi-db, PostgreSQL 18)	7 days
standard	30 days
pro	30 days + point-in-time recovery

To check: Render dashboard → logi-db → Backups tab. It shows the timestamp of the most recent snapshot plus the ETA of the next one.

Match the client tool version

Use a pg_dump / pg_restore / psql that is the same major version as the server, or higher. logi-db currently runs PG 18 (verified via Render API 2026-05-15) — dumping with a PG 16 client can silently mishandle new system catalogs and extensions. On macOS:

brew install postgresql@18
export PATH="/opt/homebrew/opt/postgresql@18/bin:$PATH"
pg_dump --version  # → pg_dump (PostgreSQL) 18.x

A Render Postgres major-version upgrade requires an explicit migration via pg_upgrade — see the Render docs.

Backups beyond the retention period

To keep backups longer than 7 days, periodically push a manual pg_dump to S3 or another storage. There is no automation for this yet — it is a TODO item.

2. Manual pg_dump (ad-hoc)

We recommend a manual snapshot right before a migration, or right before any risky data change.

# Get the external connection URL from the Render dashboard → Connect → External Connection.

export LOGI_PG_URL='postgresql://USER:PASS@<LOGI_PG_INSTANCE_ID>.singapore-postgres.render.com/logi_db_783n'

# Full dump (custom format — compressed, supports selective restore)
pg_dump --format=custom --no-owner --no-acl \
        --file=logi_backup_$(date +%Y%m%d_%H%M%S).dump \
        "$LOGI_PG_URL"

# Or plain SQL (easier to inspect)
pg_dump --format=plain --no-owner --no-acl \
        --file=logi_backup_$(date +%Y%m%d_%H%M%S).sql \
        "$LOGI_PG_URL"

Compression ratio: the custom format is roughly 10x smaller than plain. For long-term storage, prefer custom.

3. Partial backup (specific tables only)

Useful when validating a large-scale data migration:

pg_dump --format=custom --no-owner --no-acl \
        --table=logi_identity_links \
        --table=logi_webhook_deliveries \
        --table=logi_event_dlqs \
        --file=logi_partial_$(date +%Y%m%d).dump \
        "$LOGI_PG_URL"

4. Restoring from an automatic snapshot (during an incident)

The most common scenario — a migration was destructive, or a bad code deploy corrupted data.

4.1 Dashboard path (recommended)

1. Render dashboard → logi-db → Backups → select the snapshot to restore.
2. Click "Restore to new database" (this does not overwrite the existing DB; it creates a new instance).
3. You get a new DB ID (for example, dpg-xxxx-a).
4. Verify the data through the new DB's External URL:

   psql "$NEW_DB_URL" -c "SELECT COUNT(*) FROM users;"

5. Once verified, swap logi-server's DATABASE_URL env to the new DB URL (Render dashboard → env vars, individual PUT).
6. This triggers an automatic redeploy of logi-server.
7. After confirming /up returns HTTP 200, suspend the old DB (delete it a few days later).

4.2 Direct overwrite (emergency, risky)

This restores the backup directly into the existing logi-db instance. Risk of data loss — every change made after the backup timestamp is gone. This is a last resort.

# 1. Pause logi-server (Render dashboard → Suspend)
# 2. Drop all tables + restore the backup
psql "$LOGI_PG_URL" -c "DROP SCHEMA public CASCADE; CREATE SCHEMA public;"
pg_restore --no-owner --no-acl --dbname="$LOGI_PG_URL" logi_backup_YYYYMMDD.dump
# 3. Resume logi-server → automatic redeploy

Never skip this

When you use the procedure above for incident response (not a routine backup-restore), you must notify users: "The DB is being rolled back to its state N hours ago. Signups and merges made in that window may be lost." Communicate via the status page, or notify RPs afterward.

5. Periodic restore drills (recommended monthly)

You need to verify that DR actually works before you ever need it. Validate separately, without touching the live instance:

# 1. Restore the latest snapshot to a new DB (Render dashboard, see 4.1 above)
# 2. Connect logi-server code to the new DB (locally or on a staging Render)
RAILS_ENV=production DATABASE_URL="$RESTORED_DB_URL" \
  bundle exec rails runner '
    puts "users: #{User.count}"
    puts "links: #{LogiIdentityLink.count}"
    puts "last_signup: #{User.order(created_at: :desc).first&.created_at}"
  '
# 3. If the results match your expected values (within tolerance), the drill PASSES. Delete the new DB.
# 4. Record the drill result in the internal log.

Criteria: the users and links counts are within ±5% of the backup timestamp, and last_signup is within ±1 hour of the backup timestamp.

6. Off-site backup retention (long-term)

If the 7-day retention of basic_256mb is not enough:

# Run daily via cron (for example, on your own infrastructure or a GH Actions schedule)
pg_dump --format=custom --no-owner --no-acl "$LOGI_PG_URL" \
  | aws s3 cp - "s3://logi-backups/$(date +%Y%m%d).dump" \
      --storage-class STANDARD_IA

Use an S3 lifecycle to transition to Glacier after 30 days → minimizes cost.

7. Caveats

• pg_dump also dumps the audit tables guarded by the WORM trigger as-is. On restore, you may need SET LOCAL logi.allow_audit_purge='yes' (memory: logi-eb-merge-rollout.md §"Anonymous purge audit DELETE").
• Rather than taking a backup immediately after a migration, take it after the migration is verified (5–10 minutes of prod monitoring). This avoids the risk of backing up the result of a bad migration.
• IP whitelist on the External Host: <LOGI_PG_INSTANCE_ID> currently allows 0.0.0.0/0 (check the Render dashboard). Narrowing it to ops-team IPs only is the security best practice.

Related pages

• Deploy Runbook — running migrations + rollback
• Incident Response — deciding the backup-restore branch during sev1/sev2