SQL / TRANSACTIONS AND CONCURRENCY
Backups, restores, and copying a database safely
Take a backup that is one consistent snapshot, verify it with counts and checksums, and swap a restore in without exposing a half-loaded table.
What you will learn
- Wrap every table's read in one transaction so the whole backup shares one snapshot
- Compare row counts and column checksums between source and copy before trusting it
- Load a restore into a side table, verify it, then swap names in one transaction
- Expect a long dump snapshot to hold back vacuum and grow undo on the live server
Understanding Backups, restores, and copying a database safely
A backup is one very long read, so its correctness question is the same as any other read's: which committed state did it see? Split that read across several transactions, one per table or one file at a time, and each part lands on a different state, so the copy as a whole describes a moment that never existed: orders whose customer row is missing, a balance that disagrees with the ledger rows explaining it. That is why pg_dump runs its entire dump inside one REPEATABLE READ transaction, and why mysqldump --single-transaction opens a consistent-snapshot transaction on InnoDB before it reads anything.
The snapshot blocks nobody on an MVCC engine, but it is not free. Postgres cannot recycle row versions your snapshot might still need, and InnoDB keeps the matching undo records, so a two-hour dump against a busy database leaves bloat and slower scans behind it, which is the real argument for dumping from a replica. Engines that do not keep old versions fail the other way: copying a live SQLite file with cp can catch a page mid-write or miss the WAL entirely, so you use VACUUM INTO or .backup, which read the database through a transaction instead of behind its back.
Restoring is a separate operation with its own failure modes. A logical dump reloads table by table, so child rows arrive before their parents, and loaders handle that by deferring or disabling constraint checks until the load finishes; deferring is safer because the check still happens, at COMMIT. Never load on top of the live table: restore into a scratch table or scratch database, compare counts and checksums against what you expect, then swap the verified copy in with one transaction and keep the replaced data until you are sure. A backup you have never restored is a guess about a file, and the restore is also where you find out how long recovery really takes.
CREATE TABLE accounts(id INTEGER PRIMARY KEY, balance INTEGER NOT NULL);
CREATE TABLE ledger(id INTEGER PRIMARY KEY, account_id INTEGER NOT NULL, amount INTEGER NOT NULL);
INSERT INTO accounts(id, balance) VALUES (1, 100), (2, 50);
INSERT INTO ledger(account_id, amount) VALUES (1, 100), (2, 50);
-- One transaction, so every table below is read from the same snapshot.
BEGIN;
CREATE TABLE accounts_bak AS SELECT * FROM accounts;
CREATE TABLE ledger_bak AS SELECT * FROM ledger;
COMMIT;
-- Never trust a copy you have not checked.
SELECT 'accounts: ' || count(*) || ' rows, checksum ' || sum(balance) FROM accounts_bak
UNION ALL
SELECT 'ledger: ' || count(*) || ' rows, checksum ' || sum(amount) FROM ledger_bak;A backup is one long read, and it is only trustworthy if every table in it comes from the same transactional snapshot.
Worked examples
What a torn backup looks like
Copying two related tables in separate transactions captures a state the database was never in.
CREATE TABLE accounts(id INTEGER PRIMARY KEY, balance INTEGER NOT NULL);
CREATE TABLE ledger(id INTEGER PRIMARY KEY, account_id INTEGER NOT NULL, amount INTEGER NOT NULL);
INSERT INTO accounts(id, balance) VALUES (1, 100), (2, 50);
INSERT INTO ledger(account_id, amount) VALUES (1, 100), (2, 50);
-- Copy 1 of 2: accounts.
CREATE TABLE accounts_bak AS SELECT * FROM accounts;
-- A transfer of 30 commits while the backup is still running.
UPDATE accounts SET balance = balance - 30 WHERE id = 1;
UPDATE accounts SET balance = balance + 30 WHERE id = 2;
INSERT INTO ledger(account_id, amount) VALUES (1, -30), (2, 30);
-- Copy 2 of 2: ledger, from a newer state.
CREATE TABLE ledger_bak AS SELECT * FROM ledger;
SELECT 'account ' || a.id || ': copied balance ' || a.balance
|| ', copied ledger ' || sum(l.amount)
FROM accounts_bak a JOIN ledger_bak l ON l.account_id = a.id
GROUP BY a.id, a.balance
ORDER BY a.id;Example explained
Line 1The first CREATE TABLE ... AS SELECT commits on its own, freezing accounts at one instant and releasing its snapshot.
Line 2The two UPDATEs and the INSERT stand in for another session committing a transfer between the two copies.
Line 3The second CREATE TABLE ... AS SELECT gets a fresh snapshot, so ledger_bak contains rows accounts_bak knows nothing about.
Line 4Each copy is well formed alone, but together they break the rule that a balance equals the sum of its ledger rows, and the restore would make that permanent.
Restoring rows in the wrong order
Deferring foreign key checks to COMMIT lets a dump reload children before parents and still end up consistent.
PRAGMA foreign_keys = ON;
CREATE TABLE customers(id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders(
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customers(id)
);
-- The dump file happens to list orders before customers.
BEGIN;
PRAGMA defer_foreign_keys = ON;
INSERT INTO orders VALUES (500, 1);
INSERT INTO customers VALUES (1, 'Ada');
COMMIT;
SELECT 'orders restored: ' || count(*) FROM orders;Example explained
Line 1PRAGMA foreign_keys = ON has to be set outside a transaction; SQLite leaves it off by default, so an unguarded restore can load orphans silently.
Line 2PRAGMA defer_foreign_keys = ON postpones every foreign key check to COMMIT and resets itself when the transaction ends.
Line 3The orders row referencing customer 1 is accepted while checks are deferred, and the check at COMMIT passes because the parent arrived before then.
Line 4If a parent were genuinely missing, COMMIT would be refused and you would roll the load back, which is the difference between deferring checks and switching them off.
Swapping in a restored table atomically
Loading into a side table and renaming inside one transaction keeps readers from ever seeing a half-restored table.
CREATE TABLE accounts(id INTEGER PRIMARY KEY, balance INTEGER NOT NULL);
INSERT INTO accounts VALUES (1, 70), (2, 80);
-- Restore into a side table; live queries keep using accounts.
CREATE TABLE accounts_restored(id INTEGER PRIMARY KEY, balance INTEGER NOT NULL);
INSERT INTO accounts_restored VALUES (1, 100), (2, 50);
-- Only swap once the side table has been verified.
BEGIN;
ALTER TABLE accounts RENAME TO accounts_broken;
ALTER TABLE accounts_restored RENAME TO accounts;
COMMIT;
SELECT 'accounts now holds ' || count(*) || ' rows, total ' || sum(balance) FROM accounts;Example explained
Line 1The load writes only to accounts_restored, so a restore that fails halfway leaves the live table untouched.
Line 2Both renames sit in one transaction, so a reader sees either the old accounts or the new one, never a moment with no accounts table.
Line 3The slow part, copying rows, needs no lock; only the rename needs a brief exclusive lock, so it can be done in a quiet second.
Line 4Keeping the replaced data as accounts_broken gives you an immediate way back if the restored numbers turn out to be wrong.
Important notes
A dump-side snapshot only covers transactional tables: mysqldump --single-transaction guarantees nothing for MyISAM, and copying a running SQLite file with cp can catch a torn page or leave the WAL behind.
Running your backup script against an idle database proves nothing, because the transaction's only job is to hide other sessions' commits; exercise it while writes are in flight.
Common mistakes
Backing up one table per transaction, for example one CREATE TABLE ... AS SELECT each: every copy is a different instant, so the restore yields orphan child rows and balances that no longer match their ledger, and no statement ever reports an error.
Treating CREATE TABLE bak AS SELECT * FROM t as a backup: it copies rows but not the primary key, indexes, defaults or foreign keys, and it lives in the same files as the original, so it dies with the database it was supposed to protect.
Restoring straight over the live table with DELETE then INSERT: if the load fails halfway you have destroyed the only current copy of the data, and until it finishes readers see a partially loaded table.
Try it yourself
Change, predict, then run
In a browser SQLite editor, create accounts and ledger, copy accounts with CREATE TABLE ... AS SELECT, commit a transfer, then copy ledger, and write a join with HAVING a.balance <> sum(l.amount) that exposes the disagreement. Then redo the whole export inside a single BEGIN/COMMIT and confirm that same query returns no rows.
Open the SQL workspaceCheck your understanding
A nightly job copies 40 tables, each with its own CREATE TABLE ... AS SELECT, while the application keeps writing. Every statement succeeds and every row count looks plausible. Why can the resulting copy still be wrong?
- Only the last copy is durable, because the whole script ran as one autocommit transaction.
- The copies read without locks, so writers skipped rows that were being updated.
- Each statement read its table at a different instant, so the copies can contradict each other even though each one is internally valid.
- The data is fine; the only loss is the indexes and constraints that CREATE TABLE ... AS SELECT does not copy.
Show answer
Each CREATE TABLE ... AS SELECT commits and takes a fresh snapshot, so the fortieth table was read long after the first; a transfer that committed in between appears in one copy and is absent from another, and restoring the set makes that impossible state permanent. Option 4 names a real weakness of CTAS, but rebuilding indexes cannot repair rows that reference each other inconsistently.