← Vibe coding database safety

Back up your database before you let an AI agent near it

Updated · DBShifts · commands tested on real PostgreSQL, MySQL and MongoDB servers

Short answer

Take a full backup with pg_dump, mysqldump or mongodump, restore it into a scratch database, and count the rows. A backup you have not restored is a guess. Keep your provider's point-in-time recovery switched on too, because a nightly backup loses everything written since the night before.

Key facts

  • pg_dump and mysqldump take a consistent snapshot without stopping the app (mysqldump needs --single-transaction for that on InnoDB).
  • A custom-format pg_dump archive (--format=custom) can restore one table on its own; a plain .sql file cannot as easily.
  • Restoring into a scratch database is the only proof a backup is complete: it fails loudly on missing roles, extensions or permissions while you still have time to fix them.
  • Point-in-time recovery covers the gap between backups. A daily backup alone can lose up to a day.

1. PostgreSQL

Back up, then restore into a new database and count every table.

ShellPostgreSQL: full backup
pg_dump --format=custom --file=app.dump "$DATABASE_URL"
ShellPostgreSQL: restore it into a scratch database
createdb app_restore
pg_restore --no-owner --dbname=app_restore app.dump
SQLPostgreSQL: exact row count of every table
SELECT table_name,
       (xpath('/row/n/text()',
              query_to_xml(format('SELECT count(*) AS n FROM %I.%I', table_schema, table_name),
                           false, true, '')))[1]::text::bigint AS row_count
FROM information_schema.tables
WHERE table_schema = 'public' AND table_type = 'BASE TABLE'
ORDER BY table_name;

2. MySQL and MariaDB

--single-transaction gives a consistent copy of InnoDB tables while the app keeps running; --routines, --triggers and --events take the code that lives in the database too.

ShellMySQL: full backup
mysqldump --single-transaction --routines --triggers --events app > app.sql
ShellMySQL: restore it into a scratch database
mysql -e "CREATE DATABASE app_restore"
mysql app_restore < app.sql

3. MongoDB

One gzipped archive file, restored under a different database name so it cannot touch the original.

ShellMongoDB: full backup
mongodump --uri="$MONGODB_URI" --archive=app.archive --gzip
ShellMongoDB: restore it under another name
mongorestore --uri="$MONGODB_URI" --archive=app.archive --gzip --nsFrom='app.*' --nsTo='app_restore.*'

4. Managed databases

Turn on point-in-time recovery, find out how far back it goes, and know where the restore button is before you need it. Take your own logical backup as well: it is the copy that does not depend on the provider, and the one you can restore anywhere.

5. Before every risky change

Before an AI-written migration or a big refactor runs against real data, take a fresh backup (or a branch, where your platform has them). It takes a minute and turns a disaster into a restore.

Common mistakes

Never restoring the backup

Backups fail quietly: a missing role, an extension, a table the dump user could not read. You find out on the worst day.

Backing up into the same server

A backup on the machine it protects goes down with it. Copy it somewhere else.

Only counting rows

Equal counts can hide wrong rows. For the tables that matter, compare content as well.

Letting the agent run the backup and the change

If the same session that backs up also wipes the database, a mistake can take both. Back up yourself, first.

How DBShifts helps

  • DBShifts migration copies a database to another server or engine and then checks row counts, checksums and foreign keys — the same check a restore needs.
  • Dry runs and rollback plans show exactly what a migration will create or drop before it runs.

Questions people ask

What is the best way to back up a PostgreSQL database?

pg_dump --format=custom for a portable logical backup, plus continuous WAL archiving or your provider's point-in-time recovery for everything between backups.

Does mysqldump lock the database?

With --single-transaction on InnoDB tables, no: it reads a consistent snapshot. Without it, or on MyISAM tables, it locks tables while it reads them.

How do I know my backup worked?

Restore it into a scratch database and compare row counts — and for important tables, content — with the source.

Copy a database and prove every row arrived

Counts, checksums and foreign keys, checked for you. Free to start.

7 guides in this series. See the checklist.