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.
pg_dump --format=custom --file=app.dump "$DATABASE_URL"createdb app_restore
pg_restore --no-owner --dbname=app_restore app.dumpSELECT 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.
mysqldump --single-transaction --routines --triggers --events app > app.sqlmysql -e "CREATE DATABASE app_restore"
mysql app_restore < app.sql3. MongoDB
One gzipped archive file, restored under a different database name so it cannot touch the original.
mongodump --uri="$MONGODB_URI" --archive=app.archive --gzipmongorestore --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.