Backups and restore

Neon's free tier keeps about six hours of history. That covers "I ran the wrong query ten minutes ago", and nothing older. So the hosted version keeps its own backups: every night a GitHub Actions workflow dumps the whole database and stores it in an R2 bucket.

What is in a backupCopy Anchor Link

R2 stores objects redundantly, but nothing here keeps a second copy of that bucket. If it were emptied, the text of every note would survive and older versions and attached files would not.

A dump holds everything in the database, including email addresses, password hashes and every manuscript. Keep the bucket private, and delete any copy you download once you are done with it.

How it runsCopy Anchor Link

• .github/workflows/backup.yml runs at 02:23 UTC every night, and whenever you start it by hand (gh workflow run backup.yml).
• It runs scripts/backup.ts, which calls pg_dump (custom format, compressed), uploads the file as backups/plotra-<date>T<time>Z.dump, checks the upload's size, and only then deletes old dumps so that the newest 14 remain (BACKUP_KEEP).
• If the dump fails or comes out nearly empty, nothing is uploaded and nothing is deleted, and the run fails. A failed scheduled run emails whoever last edited the workflow.
• The secrets and variables it needs are listed in the deploy checklist.

Two ways it can stop without anyone touching it: GitHub switches scheduled workflows off after 60 days without a commit to a public repository, and pg_dump refuses to dump a database newer than itself, which happens when Neon moves to a new Postgres major version and PG_MAJOR hasn't followed. Look at the Actions tab now and then.

Rehearse a restoreCopy Anchor Link

Do this once before anyone's work depends on the hosted version, and again after a Postgres major upgrade. It restores the latest backup into a scratch database next to the real one. It changes nothing in production.

You need the Postgres client tools at the database's major version or newer (pg_restore --version), and Bun.

1. Make a fresh backup and download it. Use the backup bucket's credentials:

gh workflow run backup.yml && gh run watch

export R2_ACCOUNT_ID=...           # the Cloudflare account ID
export R2_ACCESS_KEY_ID=...        # the backup token
export R2_SECRET_ACCESS_KEY=...
export BACKUP_BUCKET=plotra-backups

bun scripts/backup.ts --list
bun scripts/backup.ts --download   # the newest, into the current directory

2. Check the file is a readable dump.

pg_restore --list plotra-*.dump | head -40

It prints a table of contents: tables, indexes, policies. If it prints an error instead, the backup is bad. Stop here and find out why before anything else.

3. Create an empty database beside the real one. In the Neon console: Databases → New database, named restore_test, in the same project and branch. A database in the same project already has the plotra_app role the dump refers to. Copy its direct connection string.

export RESTORE_URL="<direct connection string of restore_test>"

4. Restore.

pg_restore --no-owner --dbname="$RESTORE_URL" plotra-*.dump

It should finish without printing anything. If it ends with "errors ignored on restore", read each error above that line: an error about a role or a permission means the restored database would not work with the app, and this page needs correcting.

5. Compare with production. Run this against both databases (psql "$RESTORE_URL" and psql "<production direct string>"). The restored numbers should equal production's as of the backup:

select
  (select count(*) from "user") as users,
  (select count(*) from project) as projects,
  (select count(*) from node) as notes,
  (select coalesce(sum(word_count), 0) from node) as words,
  (select count(*) from pg_policies) as policies,
  has_table_privilege('plotra_app', 'node', 'select') as app_role_can_read;

policies must be more than zero and app_role_can_read must be t. Those two are the row-level security the app runs under; a restore that loses them produces an app where nobody can see their own work.

6. Open it in the app. Numbers can match while the app still can't read the data. Run the app locally against the restored database:

cd apps/app
DATABASE_URL="$RESTORE_URL" bun run dev

Start it from apps/app as shown, not with bun dev:app from the root: Turborepo doesn't pass DATABASE_URL through, and the app would quietly open your development database instead. To open a note in the editor, run the collab worker too (bun run dev in apps/collab, in a second terminal).

Sign in with your production email and password and open a project you know. You should see the production projects, not your development ones, and your notes with their text. (An account that only ever signed in with Google has no password; use one that has. Version history and attachments open only if your local .env.local points at the production bucket, which this test doesn't need.)

7. Clean up. Delete the restore_test database in the Neon console, delete the downloaded dump, and unset the variables. Then replace the warning at the top of this page with the date and anything that differed from what is written here.

When the database is lost or damagedCopy Anchor Link

First decide which restore you need.

• Something went wrong in the last few hours (a bad migration, a wrong delete): use Neon's own point-in-time restore from its console. It is faster and loses less than a nightly dump.
• The damage is older than Neon's history, or the Neon project is gone: restore the nightly dump, as follows.

Anything written after the dump was taken is not in it. The collab worker narrows that gap for text, as step 5 explains.

1. Download the dump you want. bun scripts/backup.ts --list, then bun scripts/backup.ts --download <key>, with the variables from the rehearsal. Pick the newest dump from before the damage.

2. Create the database to restore into. Never restore over the damaged one: you may still need it.

• A new database in the same Neon project already has the role the dump needs.

• In a new Neon project, create that role first, as the project's owner:

  Copy Text

  CREATE ROLE plotra_app NOLOGIN NOBYPASSRLS;
  GRANT plotra_app TO CURRENT_USER;

3. Restore, then bring the schema up to date.

pg_restore --no-owner --dbname="$RESTORE_URL" plotra-<date>.dump
DATABASE_URL="$RESTORE_URL" bun run db:migrate

The dump carries the list of migrations it was taken at, so db:migrate applies only the ones added since. Run the comparison query from the rehearsal and check policies and app_role_can_read.

4. Point the app at it. In the Vercel project for apps/app, set DATABASE_URL to the new database's pooled connection string and redeploy. Set the BACKUP_DATABASE_URL repository secret to its direct string, or tonight's backup dumps the old database.

5. Know what the collab worker does next. The worker keeps its own copy of every document that has been opened, and that copy is as new as the last keystroke. When someone opens a note after the restore, the worker's copy is what they see, and it is written back to the database. So for notes that exist in the restored database, text typed after the backup comes back by itself.

What does not come back: notes created after the backup (the restored database has no row for them, so the worker has nowhere to write), and everything that isn't document text, such as the binder's structure, comments, properties and sharing.

The same behaviour means a restore can't be used to roll a document's text back: the worker's newer copy wins. For one document, use its version history instead.

6. Expect two loose ends. People are signed out if their session was created after the backup. And a version or attachment that was deleted after the backup is listed again but its file is gone from R2; opening it says the snapshot is missing.

7. Tell people. Say what was lost and from when. Then write down what happened and fix this page where it was wrong.