Backup Implementation - Learning Module

Loading content...

0/241

Consistent Backup

The Paramount Importance of Backup Consistency

A backup is only as valuable as its ability to restore a usable database. At the heart of this capability lies consistency—the guarantee that the restored database will be in a valid, coherent state with all data structures intact and all transactional guarantees honored. An inconsistent backup is not merely suboptimal; it may be entirely worthless, producing a corrupted database that cannot start, serves incorrect data, or loses critical transactions.

Consistency in backup is a deceptively complex topic. It operates at multiple levels—physical, logical, transactional, and application—each with different requirements and mechanisms. A backup that is consistent at one level may be inconsistent at another. Understanding these layers, and how to achieve consistency at each, is fundamental knowledge for any database professional responsible for data protection.

What You Will Learn

By the end of this page, you will understand the different levels of backup consistency, master the mechanisms and techniques for achieving each level, recognize common consistency pitfalls and how to avoid them, and be able to design backup strategies that guarantee the consistency level your applications require.

Levels of Backup Consistency

Backup consistency exists in a hierarchy, from the fundamental physical level up to complex application semantics. Each level builds upon the previous, and a backup must be consistent at all lower levels to achieve consistency at a higher level.

Level 1: File-Level Consistency

The most basic level ensures that each file in the backup is internally complete and not corrupted:

Each file is fully written (no partial files)
File system metadata is correct
No torn pages or blocks within files

File-level consistency is necessary but not sufficient—a database might have all files complete but still be in an inconsistent state.

Level 2: Block/Page-Level Consistency

Database files are composed of fixed-size blocks or pages. Page-level consistency ensures:

Each page is completely written (no torn pages)
Page checksums are valid
Page headers and footers are intact

Pages can be torn when a write is interrupted—for example, if power fails during an 8KB page write, half might be old data and half new, producing an unreadable page.

Converting Mermaid diagram...

Level 3: Transactional Consistency

This critical level ensures that the backup represents the effects of a set of complete transactions:

All committed transactions are fully present
No partial transactions exist
The rules of ACID are honored

A transactionally consistent backup can have its database started, and it will be indistinguishable from a database that was cleanly shut down at a specific moment.

Level 4: Referential Consistency

Beyond transactions, referential consistency ensures relationship integrity:

All foreign keys reference existing parent records
Cascading relationships are complete
Referential constraints are satisfied

Note: A transactionally consistent backup is automatically referentially consistent for intra-database relationships.

Level 5: Application Consistency

The highest level addresses application-specific invariants:

Business rules are satisfied (e.g., account balances sum correctly)
Cross-system references are valid (e.g., orders reference valid customers in external CRM)
Application state machines are in valid states

Application consistency often requires coordination beyond the database—quiescing application servers, coordinating multi-database backups, or involving external systems.

Consistency Levels Summary
Level	Scope	Achieved By	Failure Result
File-Level	Individual files complete	Proper file copy completion	Corrupted/unreadable files
Page-Level	Database pages intact	Double-write, checksums, atomic writes	Torn pages, checksum failures
Transactional	Complete transactions only	WAL, crash recovery, consistent snapshots	Partial transactions, ACID violation
Referential	FK relationships valid	Transactional consistency + proper ordering	Orphaned records, constraint violations
Application	Business logic satisfied	Application coordination, multi-system backup	Business rule violations, invalid state

Achieving Transactional Consistency

Transactional consistency is the most critical level for database backups. Multiple approaches can achieve it, each with different trade-offs.

Approach 1: Cold Backup (Quiescent State)

The simplest approach—stop the database entirely:

Gracefully shut down the database
All transactions complete or roll back
All dirty pages flush to disk
Database files are in consistent state
Copy files with any tool

Guarantees: Absolute transactional consistency Trade-off: Requires downtime

Approach 2: Online Backup with WAL Integration

Most production systems use this approach:

Database continues operating
Backup process records starting checkpoint/LSN
Files are copied (may contain dirty pages, partial writes)
All WAL from start point is included
Recovery applies WAL to reach consistent state

Guarantees: Transactional consistency after recovery Trade-off: Backup requires recovery phase before use

consistent_backup_approaches.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- Approach 3: Snapshot Isolation for Logical Backup
-- Uses database's MVCC to get consistent read view
 
-- PostgreSQL: Consistent logical backup with pg_dump
-- pg_dump automatically uses snapshot isolation
-- All tables are read from the same consistent snapshot
pg_dump --format=custom --serializable-deferrable mydb > backup.dump
 
-- MySQL: Consistent backup with single transaction
-- All tables read within one consistent transaction
mysqldump --single-transaction --routines --triggers mydb > backup.sql
 
-- SQL Server: Snapshot-based consistent backup
-- Uses database snapshot for consistent reads
CREATE DATABASE MyDB_Snapshot ON
(NAME = MyDB_Data, FILENAME = 'D:\Snapshots\MyDB_snap.mdf')
AS SNAPSHOT OF MyDB;
 
-- Export from snapshot (reads are consistent)
-- ... perform export from MyDB_Snapshot ...
 
DROP DATABASE MyDB_Snapshot;
 
-- Oracle: Flashback for consistent point-in-time
-- Get consistent view as of specific SCN
SELECT * FROM orders AS OF SCN 123456789;
 
-- For backup, use RMAN with consistent point
RMAN> RUN {
    SET UNTIL SCN 123456789;
    BACKUP DATABASE;
}

Approach 3: Database Snapshot/MVCC

Leverage the database's Multi-Version Concurrency Control:

Database creates consistent read view at point in time
Export/backup reads from this consistent snapshot
Concurrent writes don't affect backup view
Result is transactionally consistent without stopping writes

Guarantees: Transactional consistency for logical backups Trade-off: Increased storage for version retention during backup

Approach 4: Storage-Level Snapshot

Use storage system's instant snapshot capability:

Database enters backup mode (pauses checkpointing, tracks WAL start)
Storage creates atomic snapshot (instant)
Database exits backup mode
Snapshot represents consistent state (with WAL for recovery)

Guarantees: Transactional consistency with minimal downtime Trade-off: Requires snapshot-capable storage

Consistency Is Not Optional

Never compromise on transactional consistency. A backup without it is not a backup—it's a liability. When the disaster occurs and you restore that backup, you'll discover missing records, partial transactions, violated constraints, and potentially an unrecoverable database. The 'savings' from skipping proper backup procedures become costs measured in lost data and business impact.

Multi-Table and Multi-Database Consistency

Real-world databases contain related tables and often span multiple database instances. Achieving consistency across these relationships requires careful approach.

Multi-Table Consistency Within One Database

When tables have foreign key relationships, their backup must be coordinated:

Problem Scenario:

Backup captures orders table at time T1
New order inserted referencing customer at time T1.5
Backup captures customers table at time T2
Result: Orders reference customers that don't exist in backup

Solution Approaches:

A. Global Snapshot (Preferred)

All tables read from the same consistent point in time:

multi_table_consistency.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
-- PostgreSQL: Serialize access for consistent multi-table export
-- All reads within transaction see same snapshot
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
COPY customers TO '/backup/customers.csv' CSV;
COPY orders TO '/backup/orders.csv' CSV;
COPY order_items TO '/backup/order_items.csv' CSV;
COPY products TO '/backup/products.csv' CSV;
COMMIT;
 
-- MySQL: Consistent multi-table dump
-- --single-transaction ensures all tables from same snapshot
mysqldump --single-transaction \
    --databases production \
    --tables customers orders order_items products \
    > /backup/related_tables.sql
 
-- Alternative: LOCK TABLES for MyISAM or explicit consistency
LOCK TABLES customers READ, orders READ, order_items READ;
-- Perform export while tables are locked
-- SELECT INTO OUTFILE or external copy
UNLOCK TABLES;
 
-- SQL Server: Database snapshot for consistent multi-table
CREATE DATABASE Production_Snapshot ON
(NAME = Production_Data, FILENAME = 'D:\Snap\Prod_snap.mdf')
AS SNAPSHOT OF Production;
 
-- Query/export from snapshot - all tables consistent
USE Production_Snapshot;
SELECT * FROM customers;  -- Consistent with orders below
SELECT * FROM orders;
-- ...
 
-- Cleanup
USE master;
DROP DATABASE Production_Snapshot;

B. Ordered Export with Referential Awareness

When global snapshot isn't available, order the export:

Export parent tables first (customers, products)
Export child tables after (orders, order_items)
Accept that some references might be 'forward' (child references parent not yet exported when child was captured)

This approach works when:

Referential integrity is enforced on restore (deferred constraints)
Parent records are rarely deleted during backup window

Multi-Database Consistency

Distributed applications often span multiple databases. Achieving consistency across them requires coordination:

multi_database_consistency.sh
Bash
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
#!/bin/bash
# Multi-Database Consistent Backup Strategy
 
# Approach 1: Quiesce Point Coordination
# Stop applications to create natural quiesce point
 
echo "Stopping application servers..."
ssh app-server-1 'sudo systemctl stop myapp'
ssh app-server-2 'sudo systemctl stop myapp'
 
echo "Waiting for in-flight transactions to complete..."
sleep 30  # Allow transactions to finish
 
echo "Backing up all databases at quiesce point..."
# All databases are now at the same logical point
pg_dump -h db1 orders_db > /backup/orders_db.sql &
pg_dump -h db2 customers_db > /backup/customers_db.sql &
pg_dump -h db3 inventory_db > /backup/inventory_db.sql &
wait
 
echo "Restarting application servers..."
ssh app-server-1 'sudo systemctl start myapp'
ssh app-server-2 'sudo systemctl start myapp'
 
# Approach 2: Coordinated Timestamped Backup
# When quiesce isn't possible, use timestamp coordination
 
BACKUP_TIMESTAMP=$(date -u +"%Y-%m-%d %H:%M:%S")
echo "Coordinated backup timestamp: $BACKUP_TIMESTAMP"
 
# Extract transactions up to timestamp from each database
# Requires transaction log with timestamp
 
# Approach 3: Application-Level Markers
# Insert coordination records that link backups
 
BACKUP_ID=$(uuidgen)
echo "Backup correlation ID: $BACKUP_ID"
 
# Insert marker in each database before backup
psql -h db1 orders_db -c "INSERT INTO backup_markers(id, timestamp) VALUES ('$BACKUP_ID', NOW());"
psql -h db2 customers_db -c "INSERT INTO backup_markers(id, timestamp) VALUES ('$BACKUP_ID', NOW());"
psql -h db3 inventory_db -c "INSERT INTO backup_markers(id, timestamp) VALUES ('$BACKUP_ID', NOW());"
 
# Now backup - each contains the marker
pg_dump -h db1 orders_db > /backup/orders_db_$BACKUP_ID.sql
pg_dump -h db2 customers_db > /backup/customers_db_$BACKUP_ID.sql
pg_dump -h db3 inventory_db > /backup/inventory_db_$BACKUP_ID.sql
 
# On recovery, all databases with same BACKUP_ID are from the same logical point

Microservices Challenge

In microservices architectures with database-per-service patterns, cross-service consistency is particularly challenging. Strategies include: (1) event sourcing with coordinated snapshots, (2) saga-aware backup that captures compensation state, (3) accepting eventual consistency in backups and using application-level reconciliation. Design your services' data dependencies with backup consistency in mind from the start.

Verifying Backup Consistency

Creating a consistent backup is only half the challenge—you must verify that consistency was achieved. Verification catches issues before they become disaster recovery failures.

Physical Verification

Verify file and page-level integrity:

backup_verification.sh
Bash
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
#!/bin/bash
# Backup Consistency Verification Procedures
 
# =====================================
# File-Level Verification
# =====================================
 
# Verify archive integrity (tar)
echo "Verifying tar archive integrity..."
tar -tzf /backup/db_backup.tar.gz > /dev/null 2>&1
if [ $? -ne 0 ]; then
    echo "ERROR: Archive is corrupted!"
    exit 1
fi
 
# Verify with stored checksums
echo "Verifying checksums..."
cd /backup
sha256sum -c backup_checksums.sha256
if [ $? -ne 0 ]; then
    echo "ERROR: Checksum verification failed!"
    exit 1
fi
 
# =====================================
# PostgreSQL Verification
# =====================================
 
# Verify backup manifest (PostgreSQL 13+)
echo "Verifying PostgreSQL backup manifest..."
pg_verifybackup /backup/postgresql/latest
if [ $? -ne 0 ]; then
    echo "ERROR: PostgreSQL backup verification failed!"
    exit 1
fi
 
# Test restore to verify consistency
echo "Testing restore to verification database..."
pg_restore -d verify_db /backup/postgresql/latest/backup.dump
psql -d verify_db -c "SELECT count(*) FROM pg_stat_user_tables WHERE n_dead_tup > 0;"
 
# =====================================
# MySQL Verification
# =====================================
 
# Verify XtraBackup integrity
echo "Verifying XtraBackup..."
xtrabackup --validate-backup --target-dir=/backup/mysql/latest/
if [ $? -ne 0 ]; then
    echo "ERROR: XtraBackup validation failed!"
    exit 1
fi
 
# For logical backups, verify SQL syntax
echo "Checking SQL dump syntax..."
mysql --verbose --execute="" < /backup/mysql/backup.sql 2>&1 | head -20
 
# =====================================
# Oracle RMAN Verification
# =====================================
 
# Validate backup integrity
rman target / <<EOF
VALIDATE BACKUPSET TAG 'DAILY_FULL_BACKUP';
RESTORE DATABASE VALIDATE;
EOF
 
# =====================================
# Generic Database-Level Verification
# =====================================
 
# After restore to test instance, run integrity checks
echo "Running database integrity checks..."
 
# PostgreSQL
psql -d verify_db <<EOF
-- Check for corruption
SELECT schemaname, tablename FROM pg_tables WHERE schemaname = 'public';
-- Run ANALYZE to verify tables are readable
ANALYZE;
EOF
 
# MySQL
mysql verify_db <<EOF
-- Check all tables
CHECK TABLE customers, orders, products FOR UPGRADE;
-- Analyze to verify
ANALYZE TABLE customers, orders, products;
EOF

Transactional Verification

Beyond physical integrity, verify transactional consistency:

transactional_verification.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
-- Transactional Consistency Verification Queries
 
-- PostgreSQL: Verify transaction ID consistency
-- Compare current xid with backup's xid to ensure no gaps
SELECT pg_current_xact_id();
SELECT datname, xact_commit, xact_rollback 
FROM pg_stat_database 
WHERE datname = 'restored_db';
 
-- Referential Integrity Verification
-- Check that all FK relationships are satisfied
 
-- Find orders referencing non-existent customers
SELECT o.order_id, o.customer_id
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;
-- Result should be empty for consistent backup
 
-- Find order_items referencing non-existent orders
SELECT oi.order_item_id, oi.order_id
FROM order_items oi
LEFT JOIN orders o ON oi.order_id = o.order_id
WHERE o.order_id IS NULL;
 
-- Business Logic Verification
-- Application-specific consistency checks
 
-- Example: Order totals should match line items
SELECT o.order_id, 
       o.total_amount AS recorded_total,
       SUM(oi.quantity * oi.unit_price) AS calculated_total
FROM orders o
JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY o.order_id, o.total_amount
HAVING o.total_amount != SUM(oi.quantity * oi.unit_price);
-- Differences indicate inconsistency
 
-- Sequence Verification
-- Ensure sequences are ahead of existing data
SELECT schemaname, sequencename, last_value
FROM pg_sequences
WHERE last_value < (
    SELECT MAX(id) 
    FROM (SELECT id FROM customers UNION SELECT id FROM orders) subq
);
-- Should return no rows if sequences are consistent

Verification Checklist

•Archive Integrity — Tar/zip/backup files are complete and extractable
•Checksum Validation — File checksums match recorded values
•Restore Test — Backup successfully restores to test instance
•Database Startup — Restored database starts without errors
•Table Accessibility — All tables are readable without errors
•Foreign Key Satisfaction — No orphaned records from FK relationships
•Sequence Consistency — Auto-increment/sequences ahead of existing data
•Application Validation — Sample application queries return expected results

Common Consistency Pitfalls

Understanding common mistakes helps you avoid them. These pitfalls have caused real data loss in production environments.

Critical Consistency Pitfalls

•Pitfall 1: Copying Files from Running Database — Without proper backup mode or mechanisms, copied files are inconsistent. Never use simple file copy (cp, rsync) on running database files without database coordination.
•Pitfall 2: Forgetting Transaction Logs — Physical backup without corresponding WAL/redo logs is unrecoverable. Always include logs from the backup start point.
•Pitfall 3: Split-Second Backup Illusion — Thinking that fast copies prevent inconsistency. Even milliseconds can span transactions; proper mechanisms are required regardless of speed.
•Pitfall 4: Ignoring External Data — Backing up database but not external BLOBs, configuration files, or related metadata. The restored database may be internally consistent but unusable.
•Pitfall 5: Unsynchronized Multi-Database Backup — Backing up related databases at different times creates cross-database inconsistency.
•Pitfall 6: Forgetting About Replication — Backing up a replica that's behind the primary captures stale data. Always verify replication lag before backup.
•Pitfall 7: Assuming Tool Correctness — Assuming backup tools always work. Verify with checksums and test restores regularly.
•Pitfall 8: Compression Corruption — Corrupted compressed archives may not be detectable until extraction. Use checksums and verify archive integrity.

Case Study: The Split-Second Disaster

Consider this real-world scenario that illustrates the split-second illusion:

Scenario:

Database with accounts and transactions tables
Backup script copies files sequentially
accounts copied at T1, transactions copied at T2 (1 second later)

What went wrong:

At T1.5: Transfer of $10,000 from Account A to B
Transaction table update captured (money arrived in B)
Account A balance update captured (money left A)
Account B balance update NOT captured (still reflecting old balance)

Result:

After restore, $10,000 exists in neither account
The money 'disappeared'
Total account balances don't match transaction records
Forensic investigation required to identify and fix

This is why proper backup mechanisms—snapshot isolation, WAL integration, atomic snapshots—are non-negotiable. The 'cost' of using them is trivial; the cost of not using them is potentially catastrophic.

Consistency Failures Are Silent

The most dangerous aspect of consistency failures is that they're often invisible until recovery. The backup completes 'successfully', monitoring shows no errors, and everyone believes data is protected. Only when disaster strikes and recovery is attempted does the truth emerge—often too late to fix. This is why proactive verification and test restores are absolutely essential.

Platform-Specific Consistency Mechanisms

Each database platform provides specific mechanisms for achieving backup consistency. Understanding these enables you to use the right approach for your environment.

PostgreSQL Consistency Mechanisms

pg_start_backup() / pg_stop_backup(): Marks backup boundaries for manual backup
pg_basebackup with -X stream: Includes WAL required for consistency
SERIALIZABLE transactions: For logical backup consistency
Backup manifests (v13+): Machine-verifiable backup completeness

Database-Specific Consistency Mechanisms
Database	Physical Backup	Logical Backup	Verification
PostgreSQL	pg_basebackup with streaming WAL	pg_dump --serializable-deferrable	pg_verifybackup, pg_checksums
MySQL (InnoDB)	XtraBackup with --apply-log	mysqldump --single-transaction	xtrabackup --validate-backup
Oracle	RMAN with BACKUP...PLUS ARCHIVELOG	Data Pump with FLASHBACK_SCN	RMAN VALIDATE, RESTORE...VALIDATE
SQL Server	BACKUP DATABASE (VDI integration)	BCP with snapshot isolation	RESTORE VERIFYONLY
MongoDB	mongodump --oplog	mongodump --oplog (same)	mongorestore --dryRun

platform_consistency_examples.sql
SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
-- Platform-Specific Consistency Examples
 
-- =====================================
-- PostgreSQL: Exclusive Backup Mode (legacy, pre-15)
-- =====================================
SELECT pg_start_backup('my_backup_label', true, false);
-- Copy data files here (externally)
SELECT pg_stop_backup(false, true);
-- Returns: (lsn, labelfile, spcmapfile)
-- These files must be included in backup
 
-- PostgreSQL: Non-exclusive backup (concurrent-safe)
SELECT pg_backup_start('my_backup_label', fast := true);
-- Copy data files here
SELECT * FROM pg_backup_stop(wait_for_archive := true);
 
-- =====================================
-- MySQL: Consistent Snapshot for InnoDB
-- =====================================
-- Lock and get consistent snapshot
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;  -- Record binlog position
-- Perform backup (e.g., LVM snapshot, file copy)
UNLOCK TABLES;
 
-- For XtraBackup (automatically handles consistency):
-- xtrabackup --backup --target-dir=/backup
-- xtrabackup --prepare --target-dir=/backup
 
-- =====================================
-- Oracle: SCN-based Consistency
-- =====================================
-- Get current SCN
SELECT current_scn FROM v$database;
 
-- Backup as of specific SCN (guaranteed consistent)
RMAN> RUN {
    SET UNTIL SCN 1234567890;
    BACKUP AS COMPRESSED BACKUPSET DATABASE;
}
 
-- Data Pump with flashback for consistency
expdp system/password \
    DIRECTORY=backup_dir \
    DUMPFILE=export.dmp \
    FLASHBACK_SCN=1234567890 \
    FULL=Y
 
-- =====================================
-- SQL Server: Snapshot Consistency
-- =====================================
-- BACKUP DATABASE is always consistent for that database
-- For multi-database consistency, use marked transactions:
 
-- In each database:
BEGIN TRANSACTION multi_db_backup WITH MARK 'Daily coordinated backup';
-- Do minimal work to register transaction
COMMIT TRANSACTION;
 
-- Backup with mark:
BACKUP DATABASE DB1 TO DISK='...' WITH STOPAT='<mark time>';
BACKUP DATABASE DB2 TO DISK='...' WITH STOPAT='<mark time>';

Summary and Key Takeaways

Backup consistency is the difference between usable backups and expensive failures. Let's consolidate the essential knowledge:

Key Takeaways on Backup Consistency

•Consistency exists at multiple levels — File, page, transactional, referential, and application. Each level builds on the previous.
•Transactional consistency is non-negotiable — A backup without it may be worthless or even dangerous (appearing valid but causing data loss on restore).
•Use proper mechanisms — WAL integration, snapshot isolation, database backup modes—never rely on 'fast file copy' for running databases.
•Multi-table backup needs coordination — Use global snapshots or properly ordered exports to preserve referential integrity.
•Multi-database requires explicit synchronization — Quiesce points, coordinated timestamps, or application markers link related backups.
•Verification is essential — Test restores, checksum validation, and integrity checks catch issues before they become disasters.
•Understand your platform — Each database has specific mechanisms; use the tools designed for your system.

Page Complete

You now understand backup consistency at a deep level—what it means, how to achieve it, and how to verify it. This knowledge is fundamental to designing backup systems that actually protect data, not just consume storage. Next, we'll explore the tools and technologies used to implement backup strategies across different platforms.