Guides / Prompting and shipping

How to Back Up Your App Database (and Actually Restore It)

A practical guide to database backups for small apps: what to back up, how pg_dump works, how often, where to keep copies, and how to test a restore.

Mythex Team · 2026-09-29 · 6 min read

To back up your app database, export it to a file on a schedule (for Postgres, the standard tool is pg_dump), store copies somewhere separate from the database, and regularly restore one into an empty database to prove it works. Take an extra backup right before any risky change. Your code backup, whether that's a GitHub repository or an export, does not include your data.

This guide is for people running a small app who want to stop worrying about losing it. It uses Postgres for the examples, since most AI-built apps use it, but the principles apply to any database.

What a backup has to cover

An app is more than its database. Know which part each backup protects.

The data is the part you can't recreate. Code can be rewritten and settings re-entered; your customers' orders cannot.

Step 1: Decide how much you can lose

Two questions set your backup plan:

• How much recent data can you afford to lose? If you back up once a day and the database fails in the evening, you lose that day's changes. For a hobby app that's fine. For an app taking orders, it may not be.
• How long can the app be down while you restore? A small database restores in minutes. A large one can take much longer, and you want to know that before you need it.

For most small apps, a daily automatic backup plus a manual one before risky changes is a sensible start. Apps with money or health data involved deserve more frequent backups, or a managed database with point-in-time recovery (the ability to restore to any moment, not just to the last backup).

Step 2: Check what your host already does

Many managed database providers take automatic backups. Find out:

• Are backups on for your plan, and how often do they run?
• How long are they kept?
• Can you restore to a point in time, or only to a daily snapshot?
• Can you download a backup, or only restore it inside their system?

Provider backups are useful, but they share a single point of failure with your database: the account. If the account is closed, suspended or the project is deleted, the backups may go with it. That's why you also want your own copy.

Step 3: Make a dump with pg_dump

pg_dump exports a Postgres database to a file. It reads a consistent snapshot, so the app can keep running while it works.

A typical command, using the connection string your app already has in an environment variable:

pg_dump "$DATABASE_URL" --format=custom --no-owner --file=backup-2026-09-29.dump

What the options do:

• --format=custom writes a compressed file that pg_restore can read, and lets you restore single tables later. Use --format=plain if you want a readable .sql file instead.
• --no-owner leaves out ownership commands, which makes restoring into a different database simpler.
• The date in the file name keeps backups apart.

The version of pg_dump should be the same as, or newer than, your database's Postgres version. An older pg_dump may refuse to run.

If you build with AI and don't want to touch the command line, you can ask:

Create a backup of the app's database using pg_dump in custom format, with today's date in the file name. Don't print the connection string or any secret. Tell me the file name and its size when it's done.

Step 4: Automate it

Manual backups get forgotten. Automate them with whatever your setup supports:

• Your database provider's scheduled backups, if it offers them.
• A scheduled job on a server or CI service that runs pg_dump and uploads the file to storage.
• A hosted backup service that connects to your database.

Whichever you pick, make it tell you when a backup fails. A backup job that has quietly failed for three months is the classic horror story.

Step 5: Store copies somewhere else

The rule of thumb is often called 3-2-1: three copies of your data, on two different kinds of storage, with one copy off-site. For a small app, a practical version is:

• The live database
• The provider's own automatic backups
• Your own dumps in separate storage, such as an object storage bucket in a different account

Also:

• Restrict access. A backup holds every user's data. Treat it like the live database.
• Keep a retention policy. For example, daily backups for two weeks and monthly ones for a year. Delete old ones on purpose, and remember that privacy laws may require you to delete personal data on request.
• Never commit backups to Git. A dump in a repository is a data leak waiting to happen.

Step 6: Test a restore

A backup is only as good as your last successful restore. At least every few months, and after any change to the backup process:

1. Create a new, empty database. Never restore over the live one to test.
2. Restore the latest backup into it:

pg_restore --no-owner --dbname="$TEST_DATABASE_URL" backup-2026-09-29.dump

For a plain .sql file, use psql "$TEST_DATABASE_URL" -f backup.sql instead.

1. Check that every table exists, compare row counts with the live database, and look at a few recent records.
2. Point a copy of the app at it and log in, if you can.
3. Write down how long the restore took. That's your real recovery time.

Step 7: Back up before risky changes

Take a fresh backup right before:

• Running a database migration that drops or renames columns
• Importing data in bulk
• Letting an AI agent make large changes to the data model
• Deleting old records

In an AI-built app, the agent can change table structure as part of a feature. Checkpoints and version control roll back your code, but they don't un-delete rows. A thirty-second backup before a big change is cheap insurance.

Common mistakes

• Assuming Git or a code export is a backup. It has the code, not the data.
• Never testing a restore. Many backups turn out to be empty, partial or unreadable only when needed.
• Keeping backups in the same account as the database. One compromised or closed account loses both.
• Forgetting uploaded files. The database stores the file's path; the image itself lives in storage.
• Leaving secrets in backup scripts. Read the connection string from an environment variable, never from the script's text. See how to keep API keys safe.

Backups and Mythex

Mythex projects can have a dedicated Postgres database, and its connection details are injected into the workspace and the published app. The Mythex docs don't describe a database backup or restore feature, so plan your own: ask the agent to make a dump, download it, and keep it somewhere safe, especially before big changes. A project export (Pro) contains your code, with secrets and node_modules left out; it does not contain database rows. See Database and Data ownership in the docs. For more about the database itself, read what is Postgres.

Backup checklist

• Decided how much data you can afford to lose and how long you can be down
• Checked what automatic backups your database host provides
• Your own dump runs on a schedule, and failures alert you
• Copies stored separately from the database, with restricted access
• Retention policy written down
• Uploaded files backed up as well as the database
• Restore tested into an empty database, with row counts checked
• Backup taken before every risky change