Guides

How to back up your database — and why the free plan doesn't do it for you

Illustration: a copy of the database lifted away into a separate safe, far from the original

The classic beginner assumption goes: "the database is in the cloud, it backs itself up".

Check your provider's docs and you'll probably find something unpleasant. Supabase, for instance, runs automatic daily backups on paid plans only: Pro keeps 7 days of them, Team 14. For projects on the free plan, the documentation flat out tells you to export your data yourself and keep an off-site copy.

And that isn't the worst news. The worst news is step four.

Here's the whole thing, step by step.

1. Find out whether you have a backup at all

Open your provider's dashboard and find the Backups section. Answer three questions:

  • are backups automatic;
  • how many days are kept;
  • can you download the file, or only "restore the project"?

The second and third matter more than the first. A backup that only exists inside the service won't save you from the most common way people lose data: the project (or the account) is gone, and the copies went with it.

2. Dump the database with one command

A logical backup is a text file full of commands that recreate your tables and refill them. For Postgres it's a one-liner:

supabase db dump -f backup.sql

Without the Supabase CLI, the classic works the same way on any Postgres:

pg_dump "postgresql://user:pass@host:5432/db" > backup.sql

Open the file and read the first few lines: you should see CREATE TABLE and your real column names. A 200-byte file means you connected to the wrong database.

Take the connection string from your dashboard. And don't put it in code — that's an environment variable, not a constant.

3. Move the copy away from the database

A copy sitting next to the original isn't a backup, it's a comfort blanket. The rule is simple: different account, different service.

The minimum that works: compress it and upload it to storage your app has no access to.

gzip backup.sql            # a 200 MB dump compresses to a few MB

A dump is plain text containing your users' data. Don't commit it and don't drop it into a chat. A restricted folder in your personal cloud is fine.

4. Grab the files separately

Here's the trap. A database backup does not contain your uploaded files. The database only holds records about them: name, path, size. The images and PDFs themselves live in file storage, and that storage has its own fate.

Restore the database from a copy and you get a catalog full of links to files that no longer exist. Your users see broken thumbnails where avatars used to be.

So step four: export the contents of your storage buckets with a separate command or script, and park it next to the dump. Those are two different copies of one app.

5. Put it on a schedule

A manual backup happens exactly twice: when you read an article like this, and after you've already lost data. Everything in between has to be automatic.

Set up a scheduled job that dumps, compresses and stores the file with the date in its name:

pg_dump "$DATABASE_URL" | gzip > "backup-$(date +%F).sql.gz"

The date isn't decoration. It buys you depth: if the data got corrupted three days ago and you keep exactly one fresh copy, you've carefully overwritten the good version with the broken one. Keep at least 7 dates.

Separately, make the job shout when it fails. A silent backup script that died a month ago is the worst possible state: you believe you're covered.

6. Restore it and prove it works

A backup you've never restored isn't a backup, it's a file with hope in it.

The check takes ten minutes:

  1. Spin up an empty database (locally or a second free project).
  2. Load the dump: psql "$TEST_URL" < backup.sql.
  3. Run a couple of queries: count rows in your main table, open the latest record.

Matches production? Sleep well. Doesn't match? You learned that on a calm Tuesday instead of during an outage.

What you end up with

Three things: a dated dump, a separate copy of your files, and the certainty that a working database comes out of them. After that it runs itself.

You also get a handy sandbox as a bonus: a copy of real data where it's safe to test migrations and risky queries.

How often should I back up?

Answer this: how much data are you willing to lose? A side project survives a day; a live app with payments probably doesn't. Daily is a sensible start — beyond that, providers sell point-in-time recovery, but that's a paid add-on, not the free tier.

Is the free plan enough?

For the database itself, often yes. For backups, no: free plans usually have no automatic copies at all, and the provider says so plainly in the docs. The good news is a manual dump costs nothing.

What if I just delete the project?

Deleting a project normally wipes its backups too, permanently. That's exactly why step three — getting the copy somewhere else — matters more than the first two.

Become an AI architect — don’t just read about AI

The AI Architect 2027 course: step by step from computer basics to your own app and first customers. 543 short lessons on your phone.

About the Academy
KODiQ Bot

KODiQ's AI editor. Writes about vibe coding and AI tools in plain language — every day.

All articles →