Backup & Restore

A complete Tours backup has three parts: the PostgreSQL database (users, tours, bookings, settings, license, encrypted integration credentials), the object-storage uploads (tour images, branding, documents, gallery — Cloudflare R2 by default), and your .env file (the secrets that make the other two readable). This guide covers the built-in admin backup export, manual pg_dump/psql on Neon, Neon's managed options, storage backups, and restoring everything together.

Table of Contents

• What to Back Up
• The Pooled vs. Direct Endpoint
• In-App Backup Export
• Manual Database Backup (pg_dump)
• Managed Backups on Neon
• Object Storage Backup
• Restore
• Backup Integrity

What to Back Up

SECRET_KEY is part of your backup. Integration credentials in the database are AES-256-GCM encrypted with a key derived from SECRET_KEY. Restore the database under a different SECRET_KEY and those credentials become unreadable and must be re-entered. Because storage (R2) credentials live encrypted in the database, they come across in a database dump — but only decrypt with the matching SECRET_KEY. Always keep .env (especially SECRET_KEY and JWT_SECRET) safe alongside your dumps.

The Pooled vs. Direct Endpoint

The app connects to Neon through the pooled endpoint (the host containing -pooler), which is correct for serverless request fan-out. Bulk database tooling is different: for pg_dump, psql restores, and migrations, use the Neon DIRECT (unpooled) endpoint — the pooler host with -pooler removed. It is the same database and the same credentials, but the PgBouncer pooler stalls large single-transaction restores.

# App (pooled) — do NOT dump/restore through this:
#   ...-pooler.<region>.aws.neon.tech/...
# Direct (unpooled) — USE this for pg_dump / psql:
export DIRECT_NEON_URL="postgresql://USER:PASS@ep-xxxx.<region>.aws.neon.tech/DB?sslmode=require"

Set DIRECT_NEON_URL to the direct host and use it for every command in this guide that dumps or loads data.

In-App Backup Export

The admin panel includes a Backup screen at Settings → Backup (src/app/admin/(panel)/settings/backup/page.tsx), backed by POST /api/v1/admin/backup. It produces a downloadable ZIP on demand. Access requires the settings:read permission (requirePermission("read", "settings")).

What the export contains

The ZIP can include two things, selected in the request body ({ database, buckets }):

// src/app/api/v1/admin/backup/route.ts
if (database) zip.file(`database-${stamp}.sql`, await buildSqlDump()); // data-only INSERTs
// for each selected folder: listAllObjects(`${folder}/`) → add bytes to zip "files/"

The available folders are listed by GET /api/v1/admin/backup/buckets, which enumerates the top-level prefixes in your storage bucket (with file counts and total bytes), defaulting to the app's known folders (branding, products, product-categories, gallery, hero-slider, blogs, documents) when storage isn't reachable.

The in-app feature is export/download only — there is no "restore" button. To restore, import the SQL dump and re-upload the files as described under Restore. Because the dump is data-only, import it into a database whose schema already exists (a fresh npm run db:init, or an existing install).

Manual Database Backup (pg_dump)

For a full, self-contained backup that does not depend on the app to recreate the schema, use pg_dump against the direct endpoint. The recommended form for portability across owners/roles is a plain-text dump with ownership and privilege statements stripped:

pg_dump "$DIRECT_NEON_URL" \
  --no-owner --no-privileges --no-comments \
  -f tours-$(date +%Y%m%d-%H%M%S).sql

--no-owner --no-privileges keeps the dump loadable into any target role (Neon's role names differ from a local Postgres), and a plain .sql file is easy to inspect, diff, and load with psql.

If you prefer a compressed, selectively-restorable archive instead, use the custom format:

pg_dump "$DIRECT_NEON_URL" \
  --format=custom --no-owner --no-privileges \
  --file=tours-$(date +%Y%m%d-%H%M%S).dump

Managed Backups on Neon

Tours runs on Neon Postgres, which offers two managed safety nets in addition to pg_dump:

• Branching — a copy-on-write clone of your database at a point in time. Ideal as a pre-upgrade snapshot.

  Terminal

  neonctl branches create --name backup-pre-upgrade --parent main
  

• Point-in-time restore (PITR) — Neon retains history, letting you restore the database to a past timestamp from the Neon console. Use it to recover from accidental deletes or bad imports.

Branching and PITR cover the database only. Object-storage uploads and .env are still your responsibility.

Object Storage Backup

Uploads live in object storage, separate from the database, so they must be backed up independently. The storage layer is S3-compatible (src/lib/r2.ts supports Cloudflare R2, DigitalOcean Spaces, AWS S3, and Google Cloud Storage), so the AWS CLI or rclone can mirror the bucket. Providers like R2 are highly durable, but durability is not a substitute for an off-account copy.

# Mirror the bucket to a local/backup destination (R2 example)
aws s3 sync s3://your-bucket ./uploads-backup \
  --endpoint-url https://<accountid>.r2.cloudflarestorage.com

The in-app export can also bundle selected folders into the ZIP (see above) — convenient for ad-hoc backups, but the AWS CLI / rclone mirror is better for large buckets and scheduled jobs.

Restore

A restore must bring back all three parts. Database rows reference uploads by public URL, so the database and the bucket must be consistent, and SECRET_KEY must match for encrypted credentials to decrypt. Always take a fresh backup of the live database before any destructive restore.

1. Restore the database

Restore over the direct endpoint, never the pooler.

From a plain-text dump — load it inside a single transaction so an interrupted restore rolls back cleanly instead of leaving a half-migrated database:

psql "$DIRECT_NEON_URL" -v ON_ERROR_STOP=1 --single-transaction -f tours-20260626-020000.sql

From a pg_dump custom-format archive (schema + data):

pg_restore --dbname="$DIRECT_NEON_URL" \
  --clean --if-exists --no-owner --no-privileges --single-transaction \
  tours-20260626-020000.dump

From the in-app data-only SQL dump (schema must already exist — run npm run db:init on a fresh database first):

# 1) create schema on the empty database (idempotent ensure*() DDL)
npm run db:init
# 2) load the data-only dump
psql "$DIRECT_NEON_URL" -v ON_ERROR_STOP=1 --single-transaction -f database-2026-06-26T02-00-00.sql

From a Neon branch / PITR: point DATABASE_URL at the restored branch endpoint, verify the data, then promote it or dump-and-load it back into main.

The network round-trip to Neon is slow, so run large restores in the background and verify afterward with a table + row-count comparison against the source. --single-transaction guarantees an interrupted load leaves the target unchanged.

2. Restore the uploads

Sync your backup back into the live bucket, preserving the same keys, so every stored public URL resolves:

aws s3 sync ./uploads-backup s3://your-bucket \
  --endpoint-url https://<accountid>.r2.cloudflarestorage.com

3. Confirm .env

Ensure the restored environment uses the same SECRET_KEY as when the data was backed up (otherwise encrypted integration credentials won't decrypt and must be re-entered) and a valid DATABASE_URL / storage configuration.

Restoring the database without the matching uploads leaves broken media links, and vice-versa. Always restore the DB, the bucket, and the matching .env together.

Backup Integrity

• Prefer plain-text --no-owner --no-privileges dumps for portability, or --format=custom for compressed, selectively-restorable archives.
• Always dump and restore through the direct (unpooled) endpoint.
• Store dumps off-site (a different region/provider from the live data).
• Periodically test-restore into a throwaway Neon branch or database to confirm a dump is valid, verifying row counts.
• Treat dumps and connection strings as secrets — they contain bcrypt password hashes and encrypted credentials.
• Keep a copy of .env (at least SECRET_KEY and JWT_SECRET) with your backups, stored securely.

© CreativeCape Solutions · creative-cape.com · support@creative-cape.com