Neon backup and restore: what Neon keeps, and a copy you keep

Neon keeps a restore window rather than backups you hold: 6 hours on the Free plan (up to 1 GB), up to 7 days on Launch and up to 30 days on Scale. For a copy outside Neon, Neon documents pg_dump over the direct connection string and a nightly GitHub Actions job to S3. Neither restores the copy to prove it works.

Free

Restore window of 6 hours (1 GB limit); 1 manual snapshot.

Launch

Restore window up to 7 days; 100 manual snapshots; scheduled snapshots at $0.09 per GB-month.

Scale

Restore window up to 30 days; 100 manual snapshots; scheduled snapshots at $0.09 per GB-month.

pg_dump

Over the direct connection string only: Neon says to avoid pg_dump over a pooled one ("-pooler" in the host). Use a pg_dump of your project's Postgres version or newer.

A restore window brings the same project back to an earlier moment. It does not help when the project, the account or the provider is the thing that is gone, and it ends when the window does. A pg_dump copy in your own storage does, if it restores.

# 1. back up
pg_dump --format=custom --no-owner --no-privileges --file=backup.dump "$DATABASE_URL"

# 2. restore into a scratch database on a local PostgreSQL of the same major version
createdb restore_test
pg_restore --no-owner --no-privileges --exit-on-error --dbname=restore_test backup.dump

# 3. count every table on both sides, and compare
COUNT="SELECT format('SELECT %L, count(*) FROM %I.%I;', schemaname || '.' || relname, schemaname, relname) FROM pg_stat_user_tables ORDER BY 1"
psql "$DATABASE_URL" -XAtc "$COUNT" | psql "$DATABASE_URL" -XAt > live.txt
psql -d restore_test -XAtc "$COUNT" | psql -d restore_test -XAt > restored.txt
diff live.txt restored.txt && echo "every table came back with the same number of rows"

restoreproof: the same test, every week

restoreproof backs the database up in your own GitHub Actions, restores the backup into a throwaway PostgreSQL of the same version on the runner, and fails the job when it would not restore. Each run checks:

• The backup restores: pg_restore --exit-on-error into a throwaway PostgreSQL of the same major version, made on the runner with initdb, on 127.0.0.1 with a random password.
• Every table, column, constraint, index, view, function, trigger, row-level security policy, sequence, enum and domain of the source is in the copy, with the same definition.
• Every table's rows match the live count, taken in one READ ONLY snapshot just after the backup, give or take the rows written meanwhile (at most 10 or 10%). A table with rows that restores empty always fails.
• The database has not lost more than half its rows since the last good run, while last week's backup still holds them.

A recorded run, and what else exists.

restoreproof is a free tool from agentcheck, which runs scheduled checks of your endpoints and alerts you when an answer changes. Facts come from each provider's own pages, read on the day shown.