How to back up a production database and test the restore
The three kinds of database backup, what your host already keeps and what it doesn’t, the pg_dump and pg_restore commands for a Postgres copy you control, and a 30-minute restore drill that proves the backup works.
Key Takeaways
- A backup you haven’t restored is a hope, not a backup. Restore into a separate database on a schedule and check the data. That is the only way to know the backup is complete and that you know the steps.
- Different failures need different backups. Point-in-time recovery undoes a bad change from ten minutes ago. A daily snapshot covers yesterday. An off-site dump you control covers losing access to the provider itself.
- Know your host’s actual retention. On Supabase, for example, Pro projects keep 7 days of daily backups, free projects have none, and point-in-time recovery is a paid add-on. Check your plan, not the marketing page.
- Files are usually separate from the database. Uploaded images and documents typically live in object storage, and database backups often don’t include them. Back them up separately.
- Restore beside, not on top. Restore into a new database first, inspect it, and copy back only what you need. Overwriting production with an old backup throws away every good change made since.
Quick-Start Prioritization Framework
| Backup type | Protects against | Typical recovery point | Who usually runs it |
|---|---|---|---|
| Point-in-time recovery (PITR) | Bad migration, mistaken delete or update | Any moment in the window, often to the minute | Your host, often a paid feature |
| Daily snapshot | Corruption or mistakes noticed later | Last daily backup, up to a day old | Your host |
| Logical dump you download (pg_dump) | Losing the account, provider outage, moving hosts | Whenever you last ran it | You |
| Object storage copy | Deleted or overwritten uploaded files | Whenever you last synced | You or your host |
Most small apps have a backup. Far fewer have a backup that someone has restored. The difference shows up on the worst day: an AI agent runs a migration that drops a column, a bulk update hits every row instead of one, or an account gets locked, and only then do you learn that the backups are kept for fewer days than you thought, don’t include uploaded files, or can only be restored by overwriting everything. This guide covers what kinds of backup exist, what popular hosts include, how to make your own copy of a Postgres database, and a restore drill you can run in half an hour. The commands are for Postgres, the most common database for apps built with AI tools, but the plan applies to any database.
What a backup has to survive
Start from the failures, not the tools. Each of these happens to small apps, and each needs a slightly different answer.
| What goes wrong | Example | What saves you |
|---|---|---|
| A change you made | A migration drops the wrong column; an UPDATE without a WHERE clause | Point-in-time recovery to just before it |
| A mistake noticed late | A bug has been saving the wrong price for a week | Daily backups kept longer than the delay |
| Losing the provider | Billing issue, suspended account, regional outage, a project paused on a free plan | A copy outside that provider |
| Someone with bad intent | Leaked admin credentials used to delete data | A copy the leaked credentials can’t reach |
| Files, not rows | User uploads deleted from storage | A separate copy of the storage bucket |
Two numbers worth deciding
Recovery point: how much recent data you can afford to lose. For a to-do app, a day might be acceptable. For an app taking orders, ten minutes might be too much. Recovery time: how long you can be down while restoring. Write both down; they tell you which backup types you need and how much they are worth paying for.
The three kinds of database backup
PostgreSQL’s own documentation describes three fundamentally different approaches: SQL dumps, file-system-level backups and continuous archiving. Managed hosts wrap these in friendlier names, but it helps to know what is underneath.
Logical dumps
A dump is a file of SQL, or a compressed archive, that can rebuild your tables and data. pg_dump produces one. The PostgreSQL documentation notes that it makes a consistent export even while the database is in use and doesn’t block other readers or writers. Dumps are portable: you can restore them on another host, another Postgres version, or your laptop. Their drawback is that they are only as recent as the last time you ran them.
Snapshots (physical backups)
A copy of the database’s files at a moment, usually taken daily by your host. They are fast to restore on the same platform but often can’t be downloaded. Supabase, for instance, says its physical backups aren’t available for direct download.
Point-in-time recovery
The database continuously archives its change log, so the host can rebuild it as it was at any second within a window. Supabase backs up these log files every two minutes by default, giving a worst-case recovery point of about two minutes. Neon calls the feature instant restore and keeps a history window that differs by plan.
What your host keeps, and what it leaves to you
Before writing any scripts, find out exactly what your current host does. Look for four things: whether backups exist on your plan, how many days are kept, whether you can restore to a specific time, and whether you can download a copy.
| Provider and plan | Automatic backups | Point-in-time restore | Notes |
|---|---|---|---|
| Supabase Free | None | No | Supabase suggests exporting regularly with the CLI’s db dump command |
| Supabase Pro ($25/mo) | Daily, kept 7 days | Add-on, about $100/mo for 7 days | Daily backups don’t include files in Supabase Storage |
| Neon Free | Via history window | 6 hours of history (1 GB limit) | Restores overwrite the branch; a backup branch keeps the old state |
| Neon Launch (usage-based) | Via history window | Up to 7 days | Instant restore billed at $0.20/GB-month |
| DigitalOcean Droplet you manage | Optional add-on | No (you set it up) | Weekly backups cost 20% of the Droplet price, daily 30% |
Read the restore behavior, not just the retention
Supabase notes that restoring to an earlier point replaces the current database state, and that enabling point-in-time recovery stops the daily backups. Neon says a restore overwrites the branch but saves the previous state as a separate branch. These details decide whether a restore is a scalpel or a sledgehammer, so learn them before you need them.
Free plans and pausing
Free database plans are fine for building, but many have no backups and some pause or expire. Render’s free Postgres databases, for example, expire 30 days after creation. If real users depend on the data, move to a plan with backups or keep your own dumps.
Making your own copy with pg_dump
Even with good host backups, keep a copy you control. It protects against the one failure your host can’t: losing access to the host. All you need is the connection string (from your host’s dashboard) and the PostgreSQL client tools installed on your computer or a server.
The command
Use the custom format, which is compressed and lets you restore selectively later: pg_dump --format=custom --no-owner --file=app-2026-11-11.dump "$DATABASE_URL". The --no-owner flag leaves out ownership commands, so the dump restores cleanly under a different database user on another host.
Some hosts route ordinary connections through a connection pooler. For dumps, use the host’s direct connection string if it offers one; check its documentation.
Where to keep it
Not only on your laptop, and not only at the same provider as the database. A common rule of thumb is three copies, on two kinds of storage, with one somewhere else: for example the host’s backups, a dump in cloud storage at a different company, and a recent copy on an encrypted drive. Restrict who can read the bucket; a dump contains every user’s data.
Automate it
A manual dump you forget to run doesn’t help. Schedule it nightly with whatever you already use: a cron job on a small server, or a scheduled workflow in your CI service, with the connection string stored as a secret. Have the job fail loudly (an email or chat message) if the dump is empty or much smaller than yesterday’s, and delete dumps older than your retention target.
Don’t forget uploaded files
If users upload images or documents, those usually sit in object storage with only their file paths in the database. Supabase states this explicitly: restoring an old database backup doesn’t bring back objects deleted after it was taken. Copy the bucket on a schedule too, or turn on the storage provider’s versioning if it has one.
The restore drill: 30 minutes that prove it works
Run this once now and then every quarter, or after any big schema change. Do it on a weekday, not during an incident.
1. Create an empty database to restore into
Use a local Postgres, a new free project, or a branch if your host supports branching. Never restore a test into production. Locally: createdb restore_test.
2. Restore the dump
pg_restore --no-owner --dbname=restore_test app-2026-11-11.dump. For a larger database, add --jobs=4 to restore in parallel; this works with the custom and directory formats. Watch for errors about missing extensions or roles: they mean the target needs the same extensions enabled before the restore.
3. Check the data, not just the exit code
Compare row counts for your important tables against production (SELECT count(*) FROM orders), look at the newest row in each to confirm how recent the backup is, and spot-check a few records you know. If you use a host’s restore feature instead of a dump, do the same checks on the restored copy.
4. Point a copy of the app at it
Run the app locally with DATABASE_URL set to the restored database and sign in as a test user. This catches the problems row counts miss: missing functions, broken permissions, sequences that would hand out duplicate IDs.
5. Write down what you did
Record the commands, how long each step took and anything that went wrong, in a short runbook next to the code. On a bad day, you or your AI assistant will follow it instead of improvising.
When you actually need to restore
Most real restores are partial. Someone deleted 40 orders, not the whole database. Overwriting everything would lose the hours of good orders placed since.
Stop the bleeding first
If a bug or a script is still damaging data, take the feature offline or put the app in maintenance mode. Note the exact time the damage started; it is the timestamp you will restore to.
Restore beside production, then copy back
Restore the backup or point-in-time copy into a separate database. Query it for the rows you lost and insert them into production, or export them with \copy and import them. pg_restore can also restore a single table from a custom-format dump with --table, and --list shows what a dump contains.
Full restores are for disasters
Restore over production only when the current database is unusable. Tell users what time their data now reflects, and keep the damaged copy for investigation.
Common mistakes
These are the gaps that show up when a restore is finally needed.
Assuming the free plan has backups
Many free database plans include none. Check, and start your own dumps before real users arrive.
Keeping the only copy at the same provider
Host backups don’t help if you lose access to the account. Keep at least one copy elsewhere.
Letting an AI agent run migrations with no checkpoint
Before an agent changes the schema of a database with real data, take a dump or note the time so you can restore to just before it. Review destructive statements (DROP, DELETE, TRUNCATE, ALTER ... DROP COLUMN) before they run.
Storing dumps unencrypted and widely readable
A dump holds every email address and password hash in your app. Treat it with the same care as the production database.
Never timing a restore
A restore that takes four hours is a four-hour outage. Time it in your drill so you know.
If you’d rather not do this yourself
If you’d rather not script dumps and drills, Floot gives each app its own Postgres database that you can download on every plan, and paid plans add SQL dump import and restore to any moment in the last 7 days.
Frequently Asked Questions
How often should I back up my database?
Match it to how much data you can afford to lose. Daily is a sensible minimum for an app with real users, plus point-in-time recovery if losing a few hours of orders or bookings would hurt.
Is my host’s backup enough?
It covers most mistakes, but not losing access to the host itself. Keep a regular dump somewhere else as well, and check how long the host keeps backups on your plan.
What is the difference between pg_dump and point-in-time recovery?
pg_dump makes a portable file of your data at the moment you run it. Point-in-time recovery is a host feature that can rebuild the database as it was at any moment in a window, using continuously archived changes.
How do I test a database backup?
Restore it into a separate, empty database, compare row counts and the newest records against production, then run a copy of the app against it and sign in. Time the whole process.
Do database backups include uploaded files?
Usually not. Files typically live in object storage and only their paths are in the database. Supabase, for example, says its database backups don’t include objects in Supabase Storage.
Can I restore just one table?
Yes. With a custom-format dump, pg_restore --table restores a single table. Restore it into a separate database first, then copy the rows you need into production.
Sources
- PostgreSQL, Backup and Restore (Chapter 25), 2026 https://www.postgresql.org/docs/current/backup.html
- PostgreSQL, pg_dump, 2026 https://www.postgresql.org/docs/current/app-pgdump.html
- PostgreSQL, pg_restore, 2026 https://www.postgresql.org/docs/current/app-pgrestore.html
- Supabase, Database backups, 2026 https://supabase.com/docs/guides/platform/backups
- Neon, Instant restore, 2026 https://neon.com/docs/introduction/branch-restore
- Neon, Pricing, 2026 https://neon.com/pricing
- DigitalOcean, Droplet pricing, 2026 https://www.digitalocean.com/pricing/droplets
- Render, Deploy for free, 2026 https://render.com/docs/free