Skip to content
Tutorials Center
My learning

Topics

Lesson 102 of 125 ยท Database Systems

Backups, Migrations and Maintenance

Learn how teams back up and restore databases, version schemas with migration files, and keep databases healthy with VACUUM, ANALYZE and integrity checks.

Writing good queries is only half of working with databases. The other half is keeping the data safe and the database healthy over years: disks fail, someone runs a DELETE without a WHERE, a deploy needs a new column on a table with 50 million rows, and statistics drift until a fast query suddenly turns slow. None of this is glamorous, but it is what separates a hobby project from a system people can trust.

This lesson covers three jobs every team that runs a database must do. First, backups: the different kinds, how point-in-time recovery works and why an untested backup is barely a backup. Second, schema migrations: how teams change table structures safely and keep every environment on the same version. Third, routine maintenance: reclaiming space, refreshing planner statistics and checking for corruption. The server tools (pg_dump, mysqldump, Flyway) appear as read-only examples, and every idea that SQLite supports has a runnable demo.

Why backups matter (and what they protect against)

A backup is a copy of your data that you can restore after something goes wrong. It helps to be precise about what can go wrong, because different failures need different protections:

  • Hardware or disk failure. The server dies. Replicas (a second server that follows the first in real time) handle this well.
  • Human error. Someone drops the wrong table or runs an UPDATE that rewrites every row. A replica copies the mistake within milliseconds, so it doesn't help. Only a backup taken before the mistake does.
  • Software bugs and bad deploys. A code release quietly corrupts data for three days before anyone notices. You need backups that go back far enough.
  • Ransomware and account compromise. An attacker deletes the database and the backups stored next to it. Backups must live somewhere separate, ideally with deletion protection.

That's why "we have a replica" is not a backup strategy. Replication is for availability; backups are for recovery.

Two numbers describe what a business needs:

Term Question it answers Example target
RPO (Recovery Point Objective) How much data can we afford to lose? At most 5 minutes of orders
RTO (Recovery Time Objective) How long can we be down while restoring? Back online within 1 hour

A nightly backup gives an RPO of up to 24 hours. If losing a day of orders is unacceptable, you need point-in-time recovery (below). A common rule of thumb is 3-2-1: keep at least 3 copies of the data, on 2 different kinds of storage, with 1 copy off-site (for example, in another cloud region or provider).

Logical vs physical backups

There are two fundamentally different ways to copy a database.

A logical backup (a dump) exports the database as SQL statements or a portable archive: CREATE TABLE statements followed by the rows. You restore it by running it into an empty database.

  • Portable across versions and even operating systems; easy to inspect and to restore a single table.
  • Slow for big databases, because restoring means re-inserting every row and rebuilding every index.

A physical backup copies the database's actual data files, the bytes on disk.

  • Fast to take and to restore, even for terabytes.
  • Tied to the same database version and platform, and all-or-nothing: you restore the whole server, not one table.
  • The files must be copied consistently. Copying files of a running server with a plain cp usually produces a broken backup; use the database's own tool or a filesystem snapshot that the database supports.

Here is what both look like in practice (read-only):

Terminal
# PostgreSQL: logical dump in the compressed "custom" format
pg_dump --format=custom --file=shop_2026-10-17.dump shop

# Restore it into a new, empty database (4 parallel jobs)
createdb shop_restored
pg_restore --dbname=shop_restored --jobs=4 shop_2026-10-17.dump

# Restore only one table from the same dump
pg_restore --dbname=shop_restored --table=orders shop_2026-10-17.dump

# PostgreSQL: physical base backup of the whole server
pg_basebackup --pgdata=/backups/base --format=tar --wal-method=stream

# MySQL / MariaDB: consistent logical dump of InnoDB tables without locking them
mysqldump --single-transaction --routines --triggers shop > shop.sql
mysql shop_restored < shop.sql

The --single-transaction flag matters: it takes the whole dump inside one transaction, so the dump sees a consistent snapshot even while the application keeps writing. Without it, a dump of a busy database can contain an order whose order lines were written after the order_items table was already exported. Managed services (Amazon RDS, Google Cloud SQL, Azure, Neon, PlanetScale and others) take physical snapshots for you, but you still decide how long they are kept and you still need to test restoring them.

Backing up SQLite with VACUUM INTO

SQLite is a single file, but copying that file while a program writes to it can still produce a broken copy. SQLite offers two safe ways: the .backup command of the sqlite3 shell (which uses the online backup API), and the SQL statement VACUUM INTO, which writes a consistent, compacted copy of the database to a new file.

The runner on this site supports VACUUM INTO with a file in its temporary in-memory file system. To check the backup, you ATTACH it as a second database and query it with the schema name as a prefix:

SQL Back up, then compare with the copy ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The DELETE removes 3 rows, so the live table has 289 rows while the backup still has 292. The INSERT ... SELECT copies the three missing lines back, and the live table is complete again with 292 rows. This is a small version of a very common real-world restore: you rarely replace a whole production database because of one bad statement. Instead, you restore the backup to a separate place and copy back only what was lost.

Terminal
# The sqlite3 shell (read-only example)
sqlite3 app.db ".backup 'app-2026-10-17.db'"
sqlite3 app.db "VACUUM INTO 'app-2026-10-17.db'"

Point-in-time recovery

Nightly dumps leave a gap: if the database is lost at 17:45, everything since last night is gone. Point-in-time recovery (PITR) closes that gap. It combines two things:

  1. A periodic base backup (a physical copy), for example every night.
  2. A continuous archive of the database's change log: every change the database makes is first written to a log, and those log files are copied somewhere safe as they fill up.

To recover, you restore the base backup and then replay the log up to any moment you choose, such as 14:31:59, one second before someone ran the fatal DROP TABLE.

Database The change log PITR tooling
PostgreSQL Write-ahead log (WAL) archive_command, pgBackRest, Barman, WAL-G
MySQL / MariaDB Binary log (binlog) mysqlbinlog, Percona XtraBackup, MariaDB Backup
SQL Server Transaction log Full + differential + log backups (BACKUP LOG)
SQLite WAL file (local only) Litestream streams it to object storage
SQL
-- SQL Server (read-only): full backup plus log backups
BACKUP DATABASE shop TO DISK = 'D:\backup\shop_full.bak';
BACKUP LOG shop TO DISK = 'D:\backup\shop_log_1745.trn';

-- Restore to a moment just before the mistake
RESTORE DATABASE shop FROM DISK = 'D:\backup\shop_full.bak' WITH NORECOVERY;
RESTORE LOG shop FROM DISK = 'D:\backup\shop_log_1745.trn'
  WITH STOPAT = '2026-10-17T14:31:59', RECOVERY;

With PITR, your RPO shrinks from a day to minutes or seconds. The same write-ahead log idea is what makes transactions durable after a crash.

Test your restores

The most common backup failure isn't a missing backup. It's a backup that turns out to be empty, incomplete, encrypted with a lost key, or so slow to restore that the RTO is blown. The only way to know a backup works is to restore it regularly, ideally automatically:

  1. Restore the latest backup into a throwaway server or database.
  2. Run checks: do all tables exist, are row counts plausible, does the newest row have a recent timestamp?
  3. Time the whole restore, and compare it with your RTO.
  4. Alert someone if anything fails.

The checks in step 2 are plain SQL. Here's the idea, comparing a backup against the live database table by table:

SQL Verify a backup with row counts ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The backup has 120 orders and the live database 121, because one order arrived after the backup. That difference is expected; a backup that shows zero orders would be a red flag. The freshness query returns 2025-12-28 11:40, the last order in the sample data. In a real system you'd compare that timestamp with the current time and raise an alarm if the newest row in last night's backup is days old. Real restore tests do the same thing on a separate server, often with a scheduled job that posts the results to the team's chat.

Backups contain all your data, including personal data. Encrypt them, restrict who can read them, and delete old ones according to your retention policy. A leaked backup is a data breach. See Users, Roles and Permissions for least-privilege accounts, including a read-only account for the backup job.

Schema migrations

Your schema changes as your application grows: a new table here, a new column there, an index for a slow query. On a team, those changes must happen in the same order on every developer's laptop, in tests, in staging and in production. Running ad-hoc ALTER TABLE statements by hand doesn't scale: nobody knows which changes production already has.

The solution is migrations: each schema change is a small, numbered file in your code repository, and a tool records which ones each database has already run.

  • Each migration has a unique, ordered version, often a timestamp such as 2026_10_17_093000.
  • Migrations usually have an up step (apply the change) and sometimes a down step (undo it).
  • The tool keeps a table in the database itself, such as schema_migrations, listing the applied versions. On deploy, it runs only the missing ones, in order.
  • Migrations are reviewed and committed like any other code, so the repository is the history of your schema.

Here is that pattern in plain SQL. Each migration runs in a transaction together with the row that records it:

SQL A migrations table by hand

Press Run (or Ctrl + Enter) to see the result here.

The first list shows all three versions; after the "down" step, only 001_create_customers and 002_add_display_name remain. Because the PRIMARY KEY on version rejects duplicates, the same migration can never be recorded twice. SQLite's PRAGMA user_version is a simpler alternative that many mobile and desktop apps use: one integer stored in the database file header that the app compares with the version its code expects.

Wrapping each migration in a transaction works in SQLite, PostgreSQL and SQL Server, which support transactional DDL: if step two of a migration fails, step one is rolled back too. MySQL and Oracle commit implicitly after every DDL statement, so a half-failed migration there leaves a half-changed schema that you must fix by hand. Keep MySQL migrations small, ideally one change each.

Migration tools

You'll rarely build this by hand. Popular tools all follow the same idea (read-only examples):

PHP
// Laravel (PHP): database/migrations/2026_10_17_093000_add_display_name_to_customers.php
return new class extends Migration
{
    public function up(): void
    {
        Schema::table('customers', function (Blueprint $table) {
            $table->string('display_name')->nullable();
        });
    }

    public function down(): void
    {
        Schema::table('customers', function (Blueprint $table) {
            $table->dropColumn('display_name');
        });
    }
};
// Run with: php artisan migrate   (records versions in the "migrations" table)
TEXT
Flyway (Java and any SQL database): plain SQL files with versioned names
  db/migration/V1__create_customers.sql
  db/migration/V2__add_display_name.sql
  db/migration/V3__index_display_name.sql
Run with: flyway migrate   (records versions in "flyway_schema_history")
YAML
# Liquibase: changesets in YAML, XML or SQL, tracked in "databasechangelog"
databaseChangeLog:
  - changeSet:
      id: 2
      author: dana
      changes:
        - addColumn:
            tableName: customers
            columns:
              - column: { name: display_name, type: varchar(100) }

Rails (Active Record migrations), Django, Prisma, Entity Framework, Alembic (Python/SQLAlchemy) and golang-migrate work the same way.

Rules for safe migrations

  • Never edit a migration that already ran somewhere shared. Write a new migration instead; otherwise databases that ran the old version silently differ from new ones.
  • Expand, then contract. To rename a column without downtime, don't rename it in one step while old code still reads it. Add the new column, write to both, backfill the old rows, switch the code to read the new column, and only then drop the old one in a later deploy.
  • Watch out for locks on big tables. Adding an index or changing a column type can lock or rewrite a large table for minutes. Use CREATE INDEX CONCURRENTLY in PostgreSQL, online DDL (ALGORITHM=INPLACE) or tools like gh-ost in MySQL, and test on a production-sized copy. See Isolation Levels and Locking.
  • Down migrations can't bring back data. Dropping a column is reversible on paper, but the "down" step re-creates an empty column. Take a backup before destructive migrations.
  • Keep data fixes separate from structure changes when they're large, so a long UPDATE doesn't hold a schema lock.

The SQLite-specific details of ALTER TABLE (and the copy-and-swap trick for changes it can't do directly) are in ALTER TABLE and DROP TABLE.

Reclaiming space with VACUUM

When you delete rows, most databases don't shrink their files. The freed space is marked as reusable and filled by later inserts. That's usually fine, but after a large cleanup (say, deleting two years of old logs) you may want the disk space back.

SQLite keeps freed pages on a freelist. PRAGMA page_count reports the total pages in the file and PRAGMA freelist_count the unused ones. VACUUM rebuilds the whole database into a fresh, compact file:

SQL Deleting rows does not shrink the file

Press Run (or Ctrl + Enter) to see the result here.

With 2,000 rows the database has 169 pages (of 4,096 bytes each) and none are free. After deleting 1,800 rows, it still has 169 pages, but 150 of them are on the freelist. VACUUM rewrites the file, and it drops to 19 pages with an empty freelist.

VACUUM copies the entire database and needs exclusive access while it runs, so it's something you run occasionally, not after every delete. SQLite can also return free pages automatically with PRAGMA auto_vacuum = INCREMENTAL (set before creating tables) plus PRAGMA incremental_vacuum;.

Other databases have their own versions:

Database Routine cleanup Full rewrite that returns space to the OS
PostgreSQL VACUUM (run automatically by autovacuum) VACUUM FULL (locks the table) or the pg_repack extension
MySQL (InnoDB) Purge runs automatically OPTIMIZE TABLE orders; (rebuilds the table)
SQL Server Ghost cleanup is automatic ALTER INDEX ALL ON orders REBUILD; (shrinking files is rarely advised)
SQLite Freelist reuse VACUUM

In PostgreSQL, VACUUM matters more than anywhere else. Because of its MVCC design, every UPDATE and DELETE leaves an old row version behind ("dead tuples"). Autovacuum removes them so the space can be reused and protects against transaction ID wraparound. Don't turn autovacuum off; if a table is bloating, tune it to run more often on that table.

Refreshing statistics with ANALYZE

The query planner chooses between a full scan and an index by estimating how many rows each step returns. Those estimates come from statistics about your data: how many rows a table has and how selective each index is. ANALYZE gathers them. In SQLite, it stores them in the sqlite_stat1 table:

SQL What ANALYZE records ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Read the stat column as "rows in the table, then the average number of rows per distinct value". orders_customer_id_idx shows 120 4: 120 orders, about 4 per customer, a selective index worth using. orders_status_idx shows 120 30: with only four statuses, each value matches about 30 rows, so the planner knows that index narrows things down much less. The customers' email index shows 40 1, because every email is unique.

When statistics are stale, for example after a bulk import tripled a table, the planner can pick a bad plan and a query that used to take milliseconds takes seconds. Running ANALYZE after big data changes fixes that. PostgreSQL's autovacuum also runs ANALYZE automatically, MySQL's InnoDB updates statistics automatically (and you can force it with ANALYZE TABLE orders;), and SQL Server auto-updates statistics (UPDATE STATISTICS orders; forces it). In SQLite, PRAGMA optimize; is the recommended lightweight version to run before closing a long-lived connection. You'll see how these numbers change plans in Reading Query Plans.

Checking integrity

Corruption is rare, but disks, memory and power failures do occasionally damage database files, and a bug or a disabled constraint can leave data that breaks your own rules. Two SQLite checks cover both:

  • PRAGMA integrity_check walks the whole file: page structure, indexes matching their tables, NOT NULL and CHECK constraints. It returns a single row ok when everything is fine. (PRAGMA quick_check is a faster, less thorough version.)
  • PRAGMA foreign_key_check lists every row whose foreign key points at a missing parent row.

Foreign key checks matter in SQLite especially, because enforcement is off by default for each connection unless the application runs PRAGMA foreign_keys = ON. Rows inserted while it was off are never re-checked. This example shows exactly that:

SQL Finding an orphan row ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

integrity_check returns ok, and PRAGMA foreign_keys returns 0: enforcement is off. The bad row goes in without complaint, and foreign_key_check reports it as order_items, rowid 293, parent orders. The LEFT JOIN ... IS NULL query is the portable way to find orphans in any database, and the final DELETE removes one row. Read Foreign Keys for how enforcement and ON DELETE actions work.

Other databases detect physical corruption with checksums and their own commands: DBCC CHECKDB in SQL Server, CHECK TABLE in MySQL, and data checksums plus the amcheck extension in PostgreSQL.

A maintenance checklist

Most of this can be automated. A typical small team's routine looks like this:

How often Task
Continuously Archive the change log (PITR); monitor disk space, replication lag and slow queries
Daily Base backup or managed snapshot, copied off-site; check that the backup job succeeded
Weekly or monthly Automated test restore with row-count and freshness checks; review backup retention
After big data changes ANALYZE; consider VACUUM / OPTIMIZE TABLE after huge deletes
Every deploy Run pending migrations (reviewed in code, tested on a production-sized copy)
Regularly Integrity checks, unused-index review, version upgrades and security patches

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Your production database has a live replica. A developer accidentally runs DELETE FROM orders; without a WHERE clause. Why doesn't the replica save you?

    Replication protects against server failure, not human error. Only a backup (or point-in-time recovery to just before the DELETE) brings the rows back.
  2. 2. What does point-in-time recovery combine?

    You restore the base backup and replay the WAL, binlog or transaction log until just before the mistake. That keeps the RPO to minutes or seconds.
  3. 3. In SQLite, a table has 169 pages and 0 free pages. You delete 90% of its rows. What does PRAGMA page_count most likely show right after the DELETE?

    Deleted space is put on the freelist (freelist_count rises) and reused by later inserts. VACUUM rewrites the file and returns the space.
  4. 4. A teammate wants to fix a typo in a migration file that has already run in production. What should they do instead?

    Applied migrations are history. Editing them makes databases that ran the old version differ silently from new ones. A new migration brings every environment to the same state.
  5. 5. After ANALYZE, sqlite_stat1 shows 120 30 for an index on orders.status. What does that tell the planner?

    The first number is the row count, the next is the average rows per distinct value. A low-selectivity index like this narrows results less than one with 120 4.

Summary

  • Backups protect against human error, bugs and attacks; replicas protect only against server failure. Define your RPO and RTO, and follow the 3-2-1 rule.
  • Logical backups (pg_dump, mysqldump --single-transaction) are portable SQL exports; physical backups copy data files and restore fast.
  • Point-in-time recovery replays the archived WAL/binlog/transaction log on top of a base backup to any chosen moment.
  • In SQLite, VACUUM INTO 'file' (or the shell's .backup) makes a consistent copy; ATTACH lets you query it and restore single rows.
  • A backup is only proven by a tested restore: check tables, row counts, freshness and restore time.
  • Migrations are versioned schema-change files with up (and often down) steps, tracked in a table like schema_migrations. Never edit applied migrations; use expand-then-contract for zero-downtime changes.
  • VACUUM reclaims free pages, ANALYZE refreshes planner statistics, and PRAGMA integrity_check / foreign_key_check find corruption and orphan rows.

Next: SQL vs NoSQL, where you'll compare relational databases with document, key-value, wide-column and graph stores.