Database Management SystemsBackup Implementation

Backup Implementation Strategies

LevelIntermediate

Duration60 mins

TopicBackup Implementation

2 / 5

Offline Backup (Cold Backup)

The Role of Offline Backup in Modern Data Protection

While online backups dominate production environments where uptime is paramount, offline backup (also called cold backup) remains a fundamental component of comprehensive data protection strategies. An offline backup is performed when the database is completely shut down—no active connections, no running transactions, no I/O activity. The database files are in a quiescent, consistent state, ready for direct, reliable copying.

Far from being an obsolete relic, offline backup offers unique advantages that make it indispensable for certain scenarios: guaranteed consistency without complex mechanisms, simplified implementation, complete isolation from production activity, and reliable baselines for disaster recovery. Understanding when and how to employ offline backup is essential knowledge for any database professional.

What You Will Learn

By the end of this page, you will understand when offline backup is the right choice, master the techniques for implementing cold backups across major database platforms, recognize the trade-offs compared to online backup, and be able to design hybrid backup strategies that leverage the strengths of both approaches.

Understanding Offline Backup Fundamentals

Defining Offline Backup

An offline (cold) backup occurs when the database management system is completely stopped before any backup activity begins. During a cold backup:

No database processes running: The DBMS service is stopped
No active connections: All client sessions terminated
No pending transactions: All work committed or rolled back
No buffer pool operations: All dirty pages flushed to disk
No log activity: Transaction logs in consistent, closed state

With the database in this quiescent state, all files can be copied directly using standard file system tools—cp, rsync, tar, or storage-level snapshot—with absolute certainty of consistency.

The Simplicity Advantage

Offline backup derives its reliability from simplicity. There's no need for:

Complex checkpoint coordination
WAL/redo log tracking during backup
Backup recovery phases (apply logs, undo incomplete transactions)
Concerns about page consistency during copy

The copied files are the backup—complete, consistent, immediately usable. This simplicity translates to fewer failure modes, easier verification, and simpler recovery procedures.

Comparing Online and Offline Backup Characteristics
Characteristic	Online (Hot) Backup	Offline (Cold) Backup
Database State	Running, accepting connections	Completely stopped
Application Impact	Minimal (performance only)	Total downtime during backup
Consistency Mechanism	WAL + checkpoint coordination	Natural quiescent state
Implementation Complexity	Higher (database-specific tools)	Lower (file system tools)
Recovery Complexity	Requires log replay	Direct file copy restore
Backup Speed	May be throttled for performance	Maximum disk throughput
Risk of Failure	More failure points	Fewer failure points
Validation	Requires database-level checks	Simple checksum verification

Cold Backup Is Not Outdated

Some engineers dismiss cold backup as obsolete in an age of online backup capabilities. This is a mistake. Cold backup provides the most reliable baseline for disaster recovery, the simplest recovery path when that reliability matters most, and often the fastest backup/restore speeds since no application coordination is needed.

When to Use Offline Backup

Offline backup is the right choice in numerous scenarios. Understanding these use cases ensures you select the appropriate strategy for each situation.

1. Scheduled Maintenance Windows

Many systems have defined maintenance windows—periods where downtime is planned and accepted:

Weekly maintenance windows (e.g., Sunday 2 AM - 6 AM)
Monthly patching windows
Quarterly system review periods

During these windows, cold backup provides maximum reliability without the complexity of online backup. Since downtime is already planned, there's no additional impact.

2. Major Upgrades and Migrations

Before significant system changes, a cold backup provides an unambiguous fallback:

Database version upgrades (PostgreSQL 14 → 15)
Operating system upgrades
Storage system migrations
Cloud migration projects

The ability to restore to the exact pre-change state, without any questions about consistency, is invaluable when rollback becomes necessary.

Ideal Scenarios for Offline Backup

•Development and Test Environments — Downtime doesn't impact users; simplicity is preferred
•Data Warehouse Refresh — Often refreshed during overnight batch windows anyway
•Disaster Recovery Baseline — Periodic verified-consistent backup for DR site
•Before Risky Operations — Schema changes, bulk data modifications, index rebuilds
•Archival and Compliance — Point-in-time snapshots for legal/regulatory requirements
•Limited DBA Expertise — Teams new to a database platform benefit from simpler procedures
•Legacy Systems — Older databases may lack robust online backup capabilities
•Embedded Databases — Small databases within applications (SQLite, embedded PostgreSQL)

3. Maximum Reliability Requirements

In scenarios where backup reliability is more critical than availability, cold backup wins:

Systems with very low Recovery Point Objective (RPO) tolerance for data loss
Regulatory requirements mandating provably consistent backups
Litigation holds requiring verifiable point-in-time snapshots
Critical financial close processes

4. Small Databases with Flexible SLAs

Not every database serves 24/7 global traffic. Many databases can tolerate brief downtime:

Internal tools and applications
Regional systems with natural off-hours
Batch processing systems
Analytics platforms with defined access windows

For these systems, a 15-minute cold backup at 2 AM is often the most practical approach.

Hybrid Strategy

Most enterprise environments benefit from a hybrid approach: frequent online backups (daily or more often) for normal operations, combined with periodic cold backups (weekly or monthly) to establish verified-consistent baselines. This provides both continuous protection and reliable restoration guarantees.

Implementation Procedures

Implementing cold backup requires a methodical approach to ensure all prerequisites are met and the backup is complete. The following procedures apply across database platforms.

Phase 1: Preparation

Before initiating shutdown:

Notify stakeholders: Ensure all teams aware of planned outage
Verify backup storage: Confirm sufficient space and accessibility
Document current state: Record configuration, version, and current data metrics
Prepare connection termination: Identify active connections to be terminated
Verify backup scripts: Test or review backup commands

Phase 2: Graceful Shutdown

Proper shutdown ensures all data is flushed and consistent:

shutdown_procedures.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
#!/bin/bash
# Graceful Database Shutdown for Cold Backup
 
# =====================================
# PostgreSQL Shutdown
# =====================================
# Standard graceful shutdown - wait for connections to close
sudo -u postgres pg_ctl stop -D /var/lib/postgresql/14/main -m smart
 
# Fast shutdown - disconnect clients, but complete running transactions
sudo -u postgres pg_ctl stop -D /var/lib/postgresql/14/main -m fast
 
# Immediate shutdown - abort transactions (not recommended for backup)
# sudo -u postgres pg_ctl stop -D /var/lib/postgresql/14/main -m immediate
 
# Using systemd (modern systems)
sudo systemctl stop postgresql
 
# Verify shutdown complete
pg_isready -h localhost
# Should return "no response" when properly stopped
 
# =====================================
# MySQL/MariaDB Shutdown
# =====================================
# Graceful shutdown
sudo systemctl stop mysql
# or
mysqladmin -u root -p shutdown
 
# Verify shutdown
mysqladmin ping 2>/dev/null || echo "MySQL is stopped"
 
# =====================================
# SQL Server (Linux) Shutdown
# =====================================
sudo systemctl stop mssql-server
# Verify
systemctl status mssql-server
 
# =====================================
# Oracle Shutdown
# =====================================
# Connect as SYSDBA and shutdown
sqlplus / as sysdba <<EOF
SHUTDOWN IMMEDIATE;
EXIT;
EOF
 
# SHUTDOWN options:
# NORMAL - Wait for all users to disconnect
# IMMEDIATE - Rollback active transactions, disconnect users
# TRANSACTIONAL - Complete ongoing transactions, prevent new ones
# ABORT - Immediate halt (requires recovery on startup)

Phase 3: File Backup

With the database stopped, copy all relevant files. The specific files depend on the database platform:

PostgreSQL: Data directory (PGDATA), tablespace directories, WAL archives
MySQL/InnoDB: Data directory (datadir), InnoDB files, log files, configuration
Oracle: Data files, control files, redo logs, parameter files (pfile/spfile)
SQL Server: .mdf, .ndf, .ldf files, plus system databases

file_backup_procedures.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
#!/bin/bash
# Cold Backup File Copy Procedures
 
BACKUP_ROOT="/backup/cold"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
 
# =====================================
# PostgreSQL Cold Backup
# =====================================
PG_DATA="/var/lib/postgresql/14/main"
PG_BACKUP="$BACKUP_ROOT/postgresql/$TIMESTAMP"
 
mkdir -p "$PG_BACKUP"
 
# Method 1: tar with compression
tar -czf "$PG_BACKUP/pgdata.tar.gz" -C "$PG_DATA" .
 
# Method 2: rsync (supports incremental, preserves attributes)
rsync -av --delete "$PG_DATA/" "$PG_BACKUP/data/"
 
# Method 3: cp with archive mode
cp -a "$PG_DATA" "$PG_BACKUP/data"
 
# Include tablespaces if external
for ts_dir in $(ls -d /var/lib/postgresql/tablespaces/* 2>/dev/null); do
    tar -czf "$PG_BACKUP/$(basename $ts_dir).tar.gz" -C "$ts_dir" .
done
 
# =====================================
# MySQL Cold Backup
# =====================================
MYSQL_DATA="/var/lib/mysql"
MYSQL_BACKUP="$BACKUP_ROOT/mysql/$TIMESTAMP"
 
mkdir -p "$MYSQL_BACKUP"
 
# Backup entire data directory
tar -czf "$MYSQL_BACKUP/mysql_data.tar.gz" \
    -C "$MYSQL_DATA" . \
    --exclude='*.sock' \
    --exclude='*.pid'
 
# Backup configuration
cp /etc/mysql/my.cnf "$MYSQL_BACKUP/"
cp -r /etc/mysql/conf.d "$MYSQL_BACKUP/" 2>/dev/null
 
# =====================================
# Oracle Cold Backup
# =====================================
ORACLE_BASE="/u01/app/oracle"
ORACLE_BACKUP="$BACKUP_ROOT/oracle/$TIMESTAMP"
 
mkdir -p "$ORACLE_BACKUP"
 
# Backup data files (example paths - verify for your environment)
tar -czf "$ORACLE_BACKUP/datafiles.tar.gz" \
    /u01/oradata/*/datafile/*.dbf
 
# Backup control files
tar -czf "$ORACLE_BACKUP/controlfiles.tar.gz" \
    /u01/oradata/*/controlfile/*.ctl
 
# Backup redo logs
tar -czf "$ORACLE_BACKUP/redologs.tar.gz" \
    /u01/oradata/*/onlinelog/*.log
 
# Backup parameter files
cp $ORACLE_HOME/dbs/init*.ora "$ORACLE_BACKUP/"
cp $ORACLE_HOME/dbs/spfile*.ora "$ORACLE_BACKUP/"
 
# =====================================
# Verification
# =====================================
echo "Backup completed at: $TIMESTAMP"
echo "Backup size:"
du -sh "$BACKUP_ROOT"/*/"$TIMESTAMP"
 
# Generate checksums for verification
find "$BACKUP_ROOT"/*/"$TIMESTAMP" -type f -exec sha256sum {} \; > \
    "$BACKUP_ROOT/checksums_$TIMESTAMP.txt"

Phase 4: Verification

Before restarting the database, verify the backup:

Checksum validation: Compare source and backup file checksums
Size verification: Confirm backup size matches source data
File completeness: Ensure all expected files are present
Integrity check: For tar archives, test extraction without writing

Phase 5: Database Restart

After successful verification, restart the database:

restart_and_verify.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
#!/bin/bash
# Database Restart After Cold Backup
 
# =====================================
# PostgreSQL
# =====================================
sudo systemctl start postgresql
 
# Wait for startup
for i in {1..30}; do
    pg_isready -h localhost && break
    sleep 1
done
 
# Verify database is operational
sudo -u postgres psql -c "SELECT datname, pg_database_size(datname) FROM pg_database;"
 
# Run quick consistency check
sudo -u postgres vacuumdb --analyze --all
 
# =====================================
# MySQL
# =====================================
sudo systemctl start mysql
 
# Wait for startup
for i in {1..30}; do
    mysqladmin ping 2>/dev/null && break
    sleep 1
done
 
# Run table checks
mysqlcheck -u root -p --all-databases --check
 
# =====================================
# Oracle
# =====================================
sqlplus / as sysdba <<EOF
STARTUP;
SELECT name, open_mode FROM v\$database;
SELECT tablespace_name, status FROM dba_tablespaces;
EXIT;
EOF
 
# =====================================
# Calculate Downtime
# =====================================
# Using environment variables set at shutdown/startup
echo "Database downtime: $(( $(date +%s) - $SHUTDOWN_TIMESTAMP )) seconds"

Storage-Level Cold Backup Techniques

Modern storage systems offer capabilities that significantly enhance cold backup efficiency. Leveraging these features can reduce backup windows from hours to seconds.

LVM Snapshots

Logical Volume Manager (LVM) on Linux enables instant snapshots of database volumes:

lvm_cold_backup.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
#!/bin/bash
# LVM-based Cold Backup 
 
# Configuration
VG_NAME="dbvg"                    # Volume group name
LV_NAME="pg_data"                 # Logical volume name
SNAPSHOT_NAME="pg_data_snap"      # Snapshot name
SNAPSHOT_SIZE="50G"               # Space for changed blocks during copy
MOUNT_POINT="/mnt/dbsnapshot"     # Where to mount snapshot
BACKUP_DEST="/backup/lvm"         # Backup destination
 
# Step 1: Stop database for consistency
echo "Stopping PostgreSQL..."
sudo systemctl stop postgresql
sleep 5
 
# Step 2: Create LVM snapshot (instant operation)
echo "Creating LVM snapshot..."
sudo lvcreate -L $SNAPSHOT_SIZE -s -n $SNAPSHOT_NAME /dev/$VG_NAME/$LV_NAME
# Snapshot created in milliseconds!
 
# Step 3: Restart database immediately - snapshot is independent
echo "Restarting PostgreSQL..."
sudo systemctl start postgresql
pg_isready -h localhost && echo "PostgreSQL is back online"
 
# Database downtime ends here - typically under 30 seconds!
 
# Step 4: Mount snapshot for backup (non-blocking)
echo "Mounting snapshot for backup..."
sudo mkdir -p $MOUNT_POINT
sudo mount -o ro /dev/$VG_NAME/$SNAPSHOT_NAME $MOUNT_POINT
 
# Step 5: Backup from snapshot at leisure
echo "Backing up from snapshot..."
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
tar -czf "$BACKUP_DEST/pg_cold_backup_$TIMESTAMP.tar.gz" \
    -C $MOUNT_POINT .
 
# Step 6: Cleanup snapshot
echo "Cleaning up snapshot..."
sudo umount $MOUNT_POINT
sudo lvremove -f /dev/$VG_NAME/$SNAPSHOT_NAME
 
echo "Backup complete. Database downtime was minimal."
echo "Backup file: $BACKUP_DEST/pg_cold_backup_$TIMESTAMP.tar.gz"

ZFS Snapshots

ZFS provides even more powerful snapshot capabilities with atomic, space-efficient snapshots and built-in send/receive for backup:

zfs_cold_backup.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
#!/bin/bash
# ZFS-based Cold Backup
 
# Configuration
ZFS_DATASET="zpool/database"      # ZFS dataset containing database
BACKUP_POOL="backup"               # Destination pool for backup
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
 
# Step 1: Stop database
echo "Stopping database for consistent snapshot..."
sudo systemctl stop postgresql
sleep 3
 
# Step 2: Create ZFS snapshot (atomic, instant)
echo "Creating ZFS snapshot..."
sudo zfs snapshot $ZFS_DATASET@cold-$TIMESTAMP
# Snapshot is atomic - guaranteed consistent
 
# Step 3: Restart database immediately
echo "Restarting database..."
sudo systemctl start postgresql
pg_isready && echo "Database online"
 
# Total downtime: typically 5-15 seconds
 
# Step 4: Send snapshot to backup storage
echo "Sending snapshot to backup storage..."
# Local backup
sudo zfs send $ZFS_DATASET@cold-$TIMESTAMP | \
    sudo zfs receive $BACKUP_POOL/db-backup-$TIMESTAMP
 
# Or remote backup via SSH
# sudo zfs send $ZFS_DATASET@cold-$TIMESTAMP | \
#     ssh backupserver "sudo zfs receive backup_pool/db-backup-$TIMESTAMP"
 
# Or save to file
# sudo zfs send $ZFS_DATASET@cold-$TIMESTAMP | \
#     gzip > /backup/zfs/db-$TIMESTAMP.zfs.gz
 
# Step 5: Verify backup
echo "Verifying backup..."
sudo zfs list -t snapshot | grep "cold-$TIMESTAMP"
 
# Step 6: Cleanup old snapshots (keep last N)
echo "Rotating old snapshots..."
KEEP_SNAPSHOTS=7
sudo zfs list -t snapshot -H -o name | \
    grep "$ZFS_DATASET@cold-" | \
    head -n -$KEEP_SNAPSHOTS | \
    xargs -I {} sudo zfs destroy {}
 
echo "ZFS cold backup complete"

Cloud Volume Snapshots

Cloud providers offer snapshot capabilities for their block storage:

AWS EBS Snapshots: Point-in-time copies to S3
Azure Managed Disk Snapshots: Incremental, consistent snapshots
GCP Persistent Disk Snapshots: Automatic and manual snapshot support

These integrate seamlessly with cold backup strategies:

cloud_snapshot_backup.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
#!/bin/bash
# AWS EBS Snapshot-based Cold Backup
 
# Configuration
INSTANCE_ID=$(curl -s http://169.254.169.254/latest/meta-data/instance-id)
REGION="us-east-1"
VOLUME_ID="vol-0abc123def456789"  # Database EBS volume
RETENTION_DAYS=30
 
# Step 1: Stop database service
echo "Stopping database for snapshot consistency..."
sudo systemctl stop postgresql
sleep 5
 
# Step 2: Sync filesystem (ensure all writes to disk)
sync
 
# Step 3: Create EBS snapshot
echo "Creating EBS snapshot..."
SNAPSHOT_ID=$(aws ec2 create-snapshot \
    --volume-id $VOLUME_ID \
    --description "Cold backup $(date +%Y-%m-%d)" \
    --tag-specifications "ResourceType=snapshot,Tags=[{Key=Name,Value=db-cold-backup},{Key=Date,Value=$(date +%Y-%m-%d)},{Key=Retention,Value=$RETENTION_DAYS}]" \
    --query 'SnapshotId' \
    --output text \
    --region $REGION)
 
echo "Snapshot initiated: $SNAPSHOT_ID"
 
# Step 4: Restart database immediately
echo "Restarting database..."
sudo systemctl start postgresql
pg_isready && echo "Database online"
 
# Step 5: Wait for snapshot completion (optional - for verification)
echo "Waiting for snapshot completion..."
aws ec2 wait snapshot-completed \
    --snapshot-ids $SNAPSHOT_ID \
    --region $REGION
 
echo "Snapshot completed: $SNAPSHOT_ID"
 
# Step 6: Copy to another region (disaster recovery)
DR_REGION="us-west-2"
echo "Copying snapshot to DR region..."
DR_SNAPSHOT_ID=$(aws ec2 copy-snapshot \
    --source-region $REGION \
    --source-snapshot-id $SNAPSHOT_ID \
    --destination-region $DR_REGION \
    --description "DR copy of $SNAPSHOT_ID" \
    --query 'SnapshotId' \
    --output text \
    --region $DR_REGION)
 
echo "DR snapshot: $DR_SNAPSHOT_ID in $DR_REGION"
 
# Step 7: Cleanup old snapshots
echo "Cleaning up snapshots older than $RETENTION_DAYS days..."
CUTOFF_DATE=$(date -d "-$RETENTION_DAYS days" +%Y-%m-%d)
aws ec2 describe-snapshots \
    --owner-ids self \
    --filters "Name=tag:Name,Values=db-cold-backup" \
    --query "Snapshots[?StartTime<='$CUTOFF_DATE'].SnapshotId" \
    --output text \
    --region $REGION | \
    xargs -r -n1 aws ec2 delete-snapshot --snapshot-id \
    --region $REGION
 
echo "Cold backup complete"

Minimize Downtime with Snapshots

Storage-level snapshots transform cold backup from a lengthy process to a brief pause. The database is only stopped for the instant required to create the snapshot—often just seconds. The time-consuming backup copy happens afterward, with the database fully operational. This hybrid approach gives you the consistency guarantees of cold backup with near-zero downtime impact.

Recovery from Offline Backup

Recovery from cold backup is straightforward compared to online backup—no log replay or complex recovery phases. The process is essentially the inverse of the backup process.

Recovery Workflow

Stop the database (if running)
Clear or preserve existing data (depending on recovery scenario)
Restore files from backup
Set correct permissions and ownership
Start the database and verify

Complete Database Recovery

For full disaster recovery, restoring to a new server or after complete data loss:

cold_backup_recovery.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
#!/bin/bash
# Cold Backup Recovery Procedure
 
# =====================================
# PostgreSQL Recovery
# =====================================
BACKUP_FILE="/backup/cold/postgresql/pgdata.tar.gz"
PG_DATA="/var/lib/postgresql/14/main"
 
# Step 1: Stop PostgreSQL if running
sudo systemctl stop postgresql 2>/dev/null || true
 
# Step 2: Clear existing data (CAUTION!)
echo "WARNING: This will destroy existing data. Press Ctrl+C to abort."
sleep 5
sudo rm -rf $PG_DATA/*
 
# Step 3: Restore from backup
echo "Restoring from backup..."
sudo tar -xzf $BACKUP_FILE -C $PG_DATA
 
# Step 4: Set correct ownership
sudo chown -R postgres:postgres $PG_DATA
 
# Step 5: Verify permissions on critical files
sudo chmod 700 $PG_DATA
sudo chmod 600 $PG_DATA/pg_hba.conf
sudo chmod 600 $PG_DATA/postgresql.conf
 
# Step 6: Start PostgreSQL
echo "Starting PostgreSQL..."
sudo systemctl start postgresql
 
# Step 7: Verify recovery
pg_isready -h localhost -p 5432
sudo -u postgres psql -c "SELECT current_timestamp, pg_database_size('postgres');"
 
# =====================================
# MySQL Recovery
# =====================================
MYSQL_BACKUP="/backup/cold/mysql/mysql_data.tar.gz"
MYSQL_DATA="/var/lib/mysql"
 
# Stop MySQL
sudo systemctl stop mysql
 
# Clear existing data
sudo rm -rf $MYSQL_DATA/*
 
# Restore
sudo tar -xzf $MYSQL_BACKUP -C $MYSQL_DATA
 
# Set ownership
sudo chown -R mysql:mysql $MYSQL_DATA
 
# Start MySQL
sudo systemctl start mysql
 
# Verify
mysql -u root -p -e "SHOW DATABASES;"
 
# =====================================
# ZFS Recovery (if using ZFS snapshots)
# =====================================
ZFS_SOURCE="backup/db-backup-20240115"
ZFS_TARGET="zpool/database"
 
# Stop database
sudo systemctl stop postgresql
 
# Rollback to snapshot (if snapshot exists on target)
sudo zfs rollback $ZFS_TARGET@recovery-point
 
# Or receive from backup pool/file
sudo zfs destroy $ZFS_TARGET  # CAUTION: destroys current data
sudo zfs send $ZFS_SOURCE@snapshot | sudo zfs receive $ZFS_TARGET
 
# Or from file
gunzip -c /backup/zfs/db-backup.zfs.gz | sudo zfs receive $ZFS_TARGET
 
# Start database
sudo systemctl start postgresql

Point-in-Time Considerations

Cold backup captures a single point in time—the moment the database was stopped. Unlike online backup with WAL archiving, you cannot recover to any arbitrary point between backups.

What this means:

Recovery Point Objective (RPO) limited to backup frequency
Daily cold backups = potential for up to 24 hours of data loss
Combine with transaction log shipping for better RPO

Hybrid Recovery Strategy

For environments requiring both reliability and minimal data loss:

Weekly cold backup — Verified consistent baseline
Daily incremental — Capture changes since cold backup
Continuous WAL/binlog archiving — Point-in-time recovery capability

Recovery then becomes:

Restore most recent cold backup
Apply incrementals to approach target time
Apply transaction logs to reach exact recovery point

Test Recovery Regularly

The simplicity of cold backup recovery can create false confidence. Regular restore tests remain essential. Verify that: (1) backup files are readable and complete, (2) restore procedures are documented and work, (3) recovery time meets business requirements, and (4) recovered data passes integrity checks.

Advantages and Trade-offs

Understanding the complete picture of cold backup strengths and limitations enables informed strategy decisions.

Advantages of Cold Backup

•Guaranteed Consistency — No complex mechanisms; natural quiescent state
•Simple Implementation — Standard file copy tools work perfectly
•Fastest Backup Speeds — No throttling; maximum disk throughput
•Simple Recovery — Direct file restore; no log replay needed
•No Database Overhead — Zero impact on production (database is stopped)
•Complete Capture — All files, all configurations, all metadata
•Portable — Backup is fully self-contained and version-independent
•Verifiable — Simple checksum validation proves integrity

Trade-offs of Cold Backup

•Requires Downtime — Database unavailable during backup window
•No Point-in-Time — Single recovery point per backup
•Scheduling Constraints — Must coordinate with maintenance windows
•RPO Limitations — Data loss potential equals backup interval
•Not for 24/7 Systems — Incompatible with zero-downtime requirements
•All-or-Nothing — Cannot backup/restore individual objects
•Storage Space — Full backup size every time (unless using incrementals)
•Coordination Required — Applications must handle database unavailability

Decision Matrix: Cold vs. Hot Backup
Requirement	Favors Cold Backup	Favors Hot Backup
24/7 Availability Required	No	Yes (strongly)
Maximum Recovery Reliability	Yes (strongly)	Yes
Point-in-Time Recovery	No	Yes (strongly)
Limited DBA Expertise	Yes	No
Compliance/Audit Requirements	Often yes	Depends on requirement
Development/Test Environment	Yes (usually)	Overkill
Before Major Changes	Yes (strongly)	Also recommended
Minimal RPO Tolerance	No	Yes (strongly)
Simple Recovery Needed	Yes	No (more complex)

Summary and Best Practices

Offline backup remains a valuable tool in the database professional's arsenal. Its simplicity, reliability, and guaranteed consistency make it indispensable for certain scenarios.

Cold Backup Best Practices

•Use for baseline backups — Even with online backup, periodic cold backups provide verified consistent baselines
•Leverage storage snapshots — LVM, ZFS, or cloud snapshots minimize downtime to seconds while maintaining cold backup reliability
•Document and test recovery — Cold backup is only valuable if recovery works; test regularly
•Schedule appropriately — Align with natural low-traffic periods or maintenance windows
•Include all components — Data files, configuration files, metadata—ensure backup is complete
•Verify with checksums — Generate and validate checksums for all backup files
•Combine with log archiving — For production systems, supplement cold backup with continuous log archiving for point-in-time capability
•Communicate downtime — Ensure all stakeholders are aware of and prepared for backup windows

Page Complete

You now understand offline (cold) backup—when to use it, how to implement it, and its role in comprehensive data protection. Cold backup's simplicity and reliability make it essential for baselines, pre-change snapshots, and scenarios where guaranteed consistency outweighs availability requirements. Next, we'll explore the critical concept of consistent backup and how to achieve it across different database architectures.

2 / 5

Loading learning content...

Database Management SystemsBackup Implementation

Backup Implementation Strategies

LevelIntermediate

Duration60 mins

TopicBackup Implementation

2 / 5

Offline Backup (Cold Backup)

The Role of Offline Backup in Modern Data Protection

What You Will Learn

Understanding Offline Backup Fundamentals

Defining Offline Backup

An offline (cold) backup occurs when the database management system is completely stopped before any backup activity begins. During a cold backup:

No database processes running: The DBMS service is stopped
No active connections: All client sessions terminated
No pending transactions: All work committed or rolled back
No buffer pool operations: All dirty pages flushed to disk
No log activity: Transaction logs in consistent, closed state

With the database in this quiescent state, all files can be copied directly using standard file system tools—cp, rsync, tar, or storage-level snapshot—with absolute certainty of consistency.

The Simplicity Advantage

Offline backup derives its reliability from simplicity. There's no need for:

Complex checkpoint coordination
WAL/redo log tracking during backup
Backup recovery phases (apply logs, undo incomplete transactions)
Concerns about page consistency during copy

The copied files are the backup—complete, consistent, immediately usable. This simplicity translates to fewer failure modes, easier verification, and simpler recovery procedures.

Comparing Online and Offline Backup Characteristics
Characteristic	Online (Hot) Backup	Offline (Cold) Backup
Database State	Running, accepting connections	Completely stopped
Application Impact	Minimal (performance only)	Total downtime during backup
Consistency Mechanism	WAL + checkpoint coordination	Natural quiescent state
Implementation Complexity	Higher (database-specific tools)	Lower (file system tools)
Recovery Complexity	Requires log replay	Direct file copy restore
Backup Speed	May be throttled for performance	Maximum disk throughput
Risk of Failure	More failure points	Fewer failure points
Validation	Requires database-level checks	Simple checksum verification

Cold Backup Is Not Outdated

When to Use Offline Backup

Offline backup is the right choice in numerous scenarios. Understanding these use cases ensures you select the appropriate strategy for each situation.

1. Scheduled Maintenance Windows

Many systems have defined maintenance windows—periods where downtime is planned and accepted:

Weekly maintenance windows (e.g., Sunday 2 AM - 6 AM)
Monthly patching windows
Quarterly system review periods

During these windows, cold backup provides maximum reliability without the complexity of online backup. Since downtime is already planned, there's no additional impact.

2. Major Upgrades and Migrations

Before significant system changes, a cold backup provides an unambiguous fallback:

Database version upgrades (PostgreSQL 14 → 15)
Operating system upgrades
Storage system migrations
Cloud migration projects

The ability to restore to the exact pre-change state, without any questions about consistency, is invaluable when rollback becomes necessary.

Ideal Scenarios for Offline Backup

•Development and Test Environments — Downtime doesn't impact users; simplicity is preferred
•Data Warehouse Refresh — Often refreshed during overnight batch windows anyway
•Disaster Recovery Baseline — Periodic verified-consistent backup for DR site
•Before Risky Operations — Schema changes, bulk data modifications, index rebuilds
•Archival and Compliance — Point-in-time snapshots for legal/regulatory requirements
•Limited DBA Expertise — Teams new to a database platform benefit from simpler procedures
•Legacy Systems — Older databases may lack robust online backup capabilities
•Embedded Databases — Small databases within applications (SQLite, embedded PostgreSQL)

3. Maximum Reliability Requirements

In scenarios where backup reliability is more critical than availability, cold backup wins:

Systems with very low Recovery Point Objective (RPO) tolerance for data loss
Regulatory requirements mandating provably consistent backups
Litigation holds requiring verifiable point-in-time snapshots
Critical financial close processes

4. Small Databases with Flexible SLAs

Not every database serves 24/7 global traffic. Many databases can tolerate brief downtime:

Internal tools and applications
Regional systems with natural off-hours
Batch processing systems
Analytics platforms with defined access windows

For these systems, a 15-minute cold backup at 2 AM is often the most practical approach.

Hybrid Strategy

Implementation Procedures

Implementing cold backup requires a methodical approach to ensure all prerequisites are met and the backup is complete. The following procedures apply across database platforms.

Phase 1: Preparation

Before initiating shutdown:

Notify stakeholders: Ensure all teams aware of planned outage
Verify backup storage: Confirm sufficient space and accessibility
Document current state: Record configuration, version, and current data metrics
Prepare connection termination: Identify active connections to be terminated
Verify backup scripts: Test or review backup commands

Phase 2: Graceful Shutdown

Proper shutdown ensures all data is flushed and consistent:

shutdown_procedures.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
#!/bin/bash
# Graceful Database Shutdown for Cold Backup
 
# =====================================
# PostgreSQL Shutdown
# =====================================
# Standard graceful shutdown - wait for connections to close
sudo -u postgres pg_ctl stop -D /var/lib/postgresql/14/main -m smart
 
# Fast shutdown - disconnect clients, but complete running transactions
sudo -u postgres pg_ctl stop -D /var/lib/postgresql/14/main -m fast
 
# Immediate shutdown - abort transactions (not recommended for backup)
# sudo -u postgres pg_ctl stop -D /var/lib/postgresql/14/main -m immediate
 
# Using systemd (modern systems)
sudo systemctl stop postgresql
 
# Verify shutdown complete
pg_isready -h localhost
# Should return "no response" when properly stopped
 
# =====================================
# MySQL/MariaDB Shutdown
# =====================================
# Graceful shutdown
sudo systemctl stop mysql
# or
mysqladmin -u root -p shutdown
 
# Verify shutdown
mysqladmin ping 2>/dev/null || echo "MySQL is stopped"
 
# =====================================
# SQL Server (Linux) Shutdown
# =====================================
sudo systemctl stop mssql-server
# Verify
systemctl status mssql-server
 
# =====================================
# Oracle Shutdown
# =====================================
# Connect as SYSDBA and shutdown
sqlplus / as sysdba <<EOF
SHUTDOWN IMMEDIATE;
EXIT;
EOF
 
# SHUTDOWN options:
# NORMAL - Wait for all users to disconnect
# IMMEDIATE - Rollback active transactions, disconnect users
# TRANSACTIONAL - Complete ongoing transactions, prevent new ones
# ABORT - Immediate halt (requires recovery on startup)

Phase 3: File Backup

With the database stopped, copy all relevant files. The specific files depend on the database platform:

PostgreSQL: Data directory (PGDATA), tablespace directories, WAL archives
MySQL/InnoDB: Data directory (datadir), InnoDB files, log files, configuration
Oracle: Data files, control files, redo logs, parameter files (pfile/spfile)
SQL Server: .mdf, .ndf, .ldf files, plus system databases

file_backup_procedures.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
#!/bin/bash
# Cold Backup File Copy Procedures
 
BACKUP_ROOT="/backup/cold"
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
 
# =====================================
# PostgreSQL Cold Backup
# =====================================
PG_DATA="/var/lib/postgresql/14/main"
PG_BACKUP="$BACKUP_ROOT/postgresql/$TIMESTAMP"
 
mkdir -p "$PG_BACKUP"
 
# Method 1: tar with compression
tar -czf "$PG_BACKUP/pgdata.tar.gz" -C "$PG_DATA" .
 
# Method 2: rsync (supports incremental, preserves attributes)
rsync -av --delete "$PG_DATA/" "$PG_BACKUP/data/"
 
# Method 3: cp with archive mode
cp -a "$PG_DATA" "$PG_BACKUP/data"
 
# Include tablespaces if external
for ts_dir in $(ls -d /var/lib/postgresql/tablespaces/* 2>/dev/null); do
    tar -czf "$PG_BACKUP/$(basename $ts_dir).tar.gz" -C "$ts_dir" .
done
 
# =====================================
# MySQL Cold Backup
# =====================================
MYSQL_DATA="/var/lib/mysql"
MYSQL_BACKUP="$BACKUP_ROOT/mysql/$TIMESTAMP"
 
mkdir -p "$MYSQL_BACKUP"
 
# Backup entire data directory
tar -czf "$MYSQL_BACKUP/mysql_data.tar.gz" \
    -C "$MYSQL_DATA" . \
    --exclude='*.sock' \
    --exclude='*.pid'
 
# Backup configuration
cp /etc/mysql/my.cnf "$MYSQL_BACKUP/"
cp -r /etc/mysql/conf.d "$MYSQL_BACKUP/" 2>/dev/null
 
# =====================================
# Oracle Cold Backup
# =====================================
ORACLE_BASE="/u01/app/oracle"
ORACLE_BACKUP="$BACKUP_ROOT/oracle/$TIMESTAMP"
 
mkdir -p "$ORACLE_BACKUP"
 
# Backup data files (example paths - verify for your environment)
tar -czf "$ORACLE_BACKUP/datafiles.tar.gz" \
    /u01/oradata/*/datafile/*.dbf
 
# Backup control files
tar -czf "$ORACLE_BACKUP/controlfiles.tar.gz" \
    /u01/oradata/*/controlfile/*.ctl
 
# Backup redo logs
tar -czf "$ORACLE_BACKUP/redologs.tar.gz" \
    /u01/oradata/*/onlinelog/*.log
 
# Backup parameter files
cp $ORACLE_HOME/dbs/init*.ora "$ORACLE_BACKUP/"
cp $ORACLE_HOME/dbs/spfile*.ora "$ORACLE_BACKUP/"
 
# =====================================
# Verification
# =====================================
echo "Backup completed at: $TIMESTAMP"
echo "Backup size:"
du -sh "$BACKUP_ROOT"/*/"$TIMESTAMP"
 
# Generate checksums for verification
find "$BACKUP_ROOT"/*/"$TIMESTAMP" -type f -exec sha256sum {} \; > \
    "$BACKUP_ROOT/checksums_$TIMESTAMP.txt"

Phase 4: Verification

Before restarting the database, verify the backup:

Checksum validation: Compare source and backup file checksums
Size verification: Confirm backup size matches source data
File completeness: Ensure all expected files are present
Integrity check: For tar archives, test extraction without writing

Phase 5: Database Restart

After successful verification, restart the database:

restart_and_verify.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
#!/bin/bash
# Database Restart After Cold Backup
 
# =====================================
# PostgreSQL
# =====================================
sudo systemctl start postgresql
 
# Wait for startup
for i in {1..30}; do
    pg_isready -h localhost && break
    sleep 1
done
 
# Verify database is operational
sudo -u postgres psql -c "SELECT datname, pg_database_size(datname) FROM pg_database;"
 
# Run quick consistency check
sudo -u postgres vacuumdb --analyze --all
 
# =====================================
# MySQL
# =====================================
sudo systemctl start mysql
 
# Wait for startup
for i in {1..30}; do
    mysqladmin ping 2>/dev/null && break
    sleep 1
done
 
# Run table checks
mysqlcheck -u root -p --all-databases --check
 
# =====================================
# Oracle
# =====================================
sqlplus / as sysdba <<EOF
STARTUP;
SELECT name, open_mode FROM v\$database;
SELECT tablespace_name, status FROM dba_tablespaces;
EXIT;
EOF
 
# =====================================
# Calculate Downtime
# =====================================
# Using environment variables set at shutdown/startup
echo "Database downtime: $(( $(date +%s) - $SHUTDOWN_TIMESTAMP )) seconds"

Storage-Level Cold Backup Techniques

Modern storage systems offer capabilities that significantly enhance cold backup efficiency. Leveraging these features can reduce backup windows from hours to seconds.

LVM Snapshots

Logical Volume Manager (LVM) on Linux enables instant snapshots of database volumes:

lvm_cold_backup.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
#!/bin/bash
# LVM-based Cold Backup 
 
# Configuration
VG_NAME="dbvg"                    # Volume group name
LV_NAME="pg_data"                 # Logical volume name
SNAPSHOT_NAME="pg_data_snap"      # Snapshot name
SNAPSHOT_SIZE="50G"               # Space for changed blocks during copy
MOUNT_POINT="/mnt/dbsnapshot"     # Where to mount snapshot
BACKUP_DEST="/backup/lvm"         # Backup destination
 
# Step 1: Stop database for consistency
echo "Stopping PostgreSQL..."
sudo systemctl stop postgresql
sleep 5
 
# Step 2: Create LVM snapshot (instant operation)
echo "Creating LVM snapshot..."
sudo lvcreate -L $SNAPSHOT_SIZE -s -n $SNAPSHOT_NAME /dev/$VG_NAME/$LV_NAME
# Snapshot created in milliseconds!
 
# Step 3: Restart database immediately - snapshot is independent
echo "Restarting PostgreSQL..."
sudo systemctl start postgresql
pg_isready -h localhost && echo "PostgreSQL is back online"
 
# Database downtime ends here - typically under 30 seconds!
 
# Step 4: Mount snapshot for backup (non-blocking)
echo "Mounting snapshot for backup..."
sudo mkdir -p $MOUNT_POINT
sudo mount -o ro /dev/$VG_NAME/$SNAPSHOT_NAME $MOUNT_POINT
 
# Step 5: Backup from snapshot at leisure
echo "Backing up from snapshot..."
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
tar -czf "$BACKUP_DEST/pg_cold_backup_$TIMESTAMP.tar.gz" \
    -C $MOUNT_POINT .
 
# Step 6: Cleanup snapshot
echo "Cleaning up snapshot..."
sudo umount $MOUNT_POINT
sudo lvremove -f /dev/$VG_NAME/$SNAPSHOT_NAME
 
echo "Backup complete. Database downtime was minimal."
echo "Backup file: $BACKUP_DEST/pg_cold_backup_$TIMESTAMP.tar.gz"

ZFS Snapshots

ZFS provides even more powerful snapshot capabilities with atomic, space-efficient snapshots and built-in send/receive for backup:

zfs_cold_backup.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
#!/bin/bash
# ZFS-based Cold Backup
 
# Configuration
ZFS_DATASET="zpool/database"      # ZFS dataset containing database
BACKUP_POOL="backup"               # Destination pool for backup
TIMESTAMP=$(date +%Y%m%d_%H%M%S)
 
# Step 1: Stop database
echo "Stopping database for consistent snapshot..."
sudo systemctl stop postgresql
sleep 3
 
# Step 2: Create ZFS snapshot (atomic, instant)
echo "Creating ZFS snapshot..."
sudo zfs snapshot $ZFS_DATASET@cold-$TIMESTAMP
# Snapshot is atomic - guaranteed consistent
 
# Step 3: Restart database immediately
echo "Restarting database..."
sudo systemctl start postgresql
pg_isready && echo "Database online"
 
# Total downtime: typically 5-15 seconds
 
# Step 4: Send snapshot to backup storage
echo "Sending snapshot to backup storage..."
# Local backup
sudo zfs send $ZFS_DATASET@cold-$TIMESTAMP | \
    sudo zfs receive $BACKUP_POOL/db-backup-$TIMESTAMP
 
# Or remote backup via SSH
# sudo zfs send $ZFS_DATASET@cold-$TIMESTAMP | \
#     ssh backupserver "sudo zfs receive backup_pool/db-backup-$TIMESTAMP"
 
# Or save to file
# sudo zfs send $ZFS_DATASET@cold-$TIMESTAMP | \
#     gzip > /backup/zfs/db-$TIMESTAMP.zfs.gz
 
# Step 5: Verify backup
echo "Verifying backup..."
sudo zfs list -t snapshot | grep "cold-$TIMESTAMP"
 
# Step 6: Cleanup old snapshots (keep last N)
echo "Rotating old snapshots..."
KEEP_SNAPSHOTS=7
sudo zfs list -t snapshot -H -o name | \
    grep "$ZFS_DATASET@cold-" | \
    head -n -$KEEP_SNAPSHOTS | \
    xargs -I {} sudo zfs destroy {}
 
echo "ZFS cold backup complete"

Cloud Volume Snapshots

Cloud providers offer snapshot capabilities for their block storage:

AWS EBS Snapshots: Point-in-time copies to S3
Azure Managed Disk Snapshots: Incremental, consistent snapshots
GCP Persistent Disk Snapshots: Automatic and manual snapshot support

These integrate seamlessly with cold backup strategies:

cloud_snapshot_backup.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
#!/bin/bash
# AWS EBS Snapshot-based Cold Backup
 
# Configuration
INSTANCE_ID=$(curl -s http://169.254.169.254/latest/meta-data/instance-id)
REGION="us-east-1"
VOLUME_ID="vol-0abc123def456789"  # Database EBS volume
RETENTION_DAYS=30
 
# Step 1: Stop database service
echo "Stopping database for snapshot consistency..."
sudo systemctl stop postgresql
sleep 5
 
# Step 2: Sync filesystem (ensure all writes to disk)
sync
 
# Step 3: Create EBS snapshot
echo "Creating EBS snapshot..."
SNAPSHOT_ID=$(aws ec2 create-snapshot \
    --volume-id $VOLUME_ID \
    --description "Cold backup $(date +%Y-%m-%d)" \
    --tag-specifications "ResourceType=snapshot,Tags=[{Key=Name,Value=db-cold-backup},{Key=Date,Value=$(date +%Y-%m-%d)},{Key=Retention,Value=$RETENTION_DAYS}]" \
    --query 'SnapshotId' \
    --output text \
    --region $REGION)
 
echo "Snapshot initiated: $SNAPSHOT_ID"
 
# Step 4: Restart database immediately
echo "Restarting database..."
sudo systemctl start postgresql
pg_isready && echo "Database online"
 
# Step 5: Wait for snapshot completion (optional - for verification)
echo "Waiting for snapshot completion..."
aws ec2 wait snapshot-completed \
    --snapshot-ids $SNAPSHOT_ID \
    --region $REGION
 
echo "Snapshot completed: $SNAPSHOT_ID"
 
# Step 6: Copy to another region (disaster recovery)
DR_REGION="us-west-2"
echo "Copying snapshot to DR region..."
DR_SNAPSHOT_ID=$(aws ec2 copy-snapshot \
    --source-region $REGION \
    --source-snapshot-id $SNAPSHOT_ID \
    --destination-region $DR_REGION \
    --description "DR copy of $SNAPSHOT_ID" \
    --query 'SnapshotId' \
    --output text \
    --region $DR_REGION)
 
echo "DR snapshot: $DR_SNAPSHOT_ID in $DR_REGION"
 
# Step 7: Cleanup old snapshots
echo "Cleaning up snapshots older than $RETENTION_DAYS days..."
CUTOFF_DATE=$(date -d "-$RETENTION_DAYS days" +%Y-%m-%d)
aws ec2 describe-snapshots \
    --owner-ids self \
    --filters "Name=tag:Name,Values=db-cold-backup" \
    --query "Snapshots[?StartTime<='$CUTOFF_DATE'].SnapshotId" \
    --output text \
    --region $REGION | \
    xargs -r -n1 aws ec2 delete-snapshot --snapshot-id \
    --region $REGION
 
echo "Cold backup complete"

Minimize Downtime with Snapshots

Recovery from Offline Backup

Recovery from cold backup is straightforward compared to online backup—no log replay or complex recovery phases. The process is essentially the inverse of the backup process.

Recovery Workflow

Stop the database (if running)
Clear or preserve existing data (depending on recovery scenario)
Restore files from backup
Set correct permissions and ownership
Start the database and verify

Complete Database Recovery

For full disaster recovery, restoring to a new server or after complete data loss:

cold_backup_recovery.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
#!/bin/bash
# Cold Backup Recovery Procedure
 
# =====================================
# PostgreSQL Recovery
# =====================================
BACKUP_FILE="/backup/cold/postgresql/pgdata.tar.gz"
PG_DATA="/var/lib/postgresql/14/main"
 
# Step 1: Stop PostgreSQL if running
sudo systemctl stop postgresql 2>/dev/null || true
 
# Step 2: Clear existing data (CAUTION!)
echo "WARNING: This will destroy existing data. Press Ctrl+C to abort."
sleep 5
sudo rm -rf $PG_DATA/*
 
# Step 3: Restore from backup
echo "Restoring from backup..."
sudo tar -xzf $BACKUP_FILE -C $PG_DATA
 
# Step 4: Set correct ownership
sudo chown -R postgres:postgres $PG_DATA
 
# Step 5: Verify permissions on critical files
sudo chmod 700 $PG_DATA
sudo chmod 600 $PG_DATA/pg_hba.conf
sudo chmod 600 $PG_DATA/postgresql.conf
 
# Step 6: Start PostgreSQL
echo "Starting PostgreSQL..."
sudo systemctl start postgresql
 
# Step 7: Verify recovery
pg_isready -h localhost -p 5432
sudo -u postgres psql -c "SELECT current_timestamp, pg_database_size('postgres');"
 
# =====================================
# MySQL Recovery
# =====================================
MYSQL_BACKUP="/backup/cold/mysql/mysql_data.tar.gz"
MYSQL_DATA="/var/lib/mysql"
 
# Stop MySQL
sudo systemctl stop mysql
 
# Clear existing data
sudo rm -rf $MYSQL_DATA/*
 
# Restore
sudo tar -xzf $MYSQL_BACKUP -C $MYSQL_DATA
 
# Set ownership
sudo chown -R mysql:mysql $MYSQL_DATA
 
# Start MySQL
sudo systemctl start mysql
 
# Verify
mysql -u root -p -e "SHOW DATABASES;"
 
# =====================================
# ZFS Recovery (if using ZFS snapshots)
# =====================================
ZFS_SOURCE="backup/db-backup-20240115"
ZFS_TARGET="zpool/database"
 
# Stop database
sudo systemctl stop postgresql
 
# Rollback to snapshot (if snapshot exists on target)
sudo zfs rollback $ZFS_TARGET@recovery-point
 
# Or receive from backup pool/file
sudo zfs destroy $ZFS_TARGET  # CAUTION: destroys current data
sudo zfs send $ZFS_SOURCE@snapshot | sudo zfs receive $ZFS_TARGET
 
# Or from file
gunzip -c /backup/zfs/db-backup.zfs.gz | sudo zfs receive $ZFS_TARGET
 
# Start database
sudo systemctl start postgresql

Point-in-Time Considerations

Cold backup captures a single point in time—the moment the database was stopped. Unlike online backup with WAL archiving, you cannot recover to any arbitrary point between backups.

What this means:

Recovery Point Objective (RPO) limited to backup frequency
Daily cold backups = potential for up to 24 hours of data loss
Combine with transaction log shipping for better RPO

Hybrid Recovery Strategy

For environments requiring both reliability and minimal data loss:

Weekly cold backup — Verified consistent baseline
Daily incremental — Capture changes since cold backup
Continuous WAL/binlog archiving — Point-in-time recovery capability

Recovery then becomes:

Restore most recent cold backup
Apply incrementals to approach target time
Apply transaction logs to reach exact recovery point

Test Recovery Regularly

Advantages and Trade-offs

Understanding the complete picture of cold backup strengths and limitations enables informed strategy decisions.

Advantages of Cold Backup

•Guaranteed Consistency — No complex mechanisms; natural quiescent state
•Simple Implementation — Standard file copy tools work perfectly
•Fastest Backup Speeds — No throttling; maximum disk throughput
•Simple Recovery — Direct file restore; no log replay needed
•No Database Overhead — Zero impact on production (database is stopped)
•Complete Capture — All files, all configurations, all metadata
•Portable — Backup is fully self-contained and version-independent
•Verifiable — Simple checksum validation proves integrity

Trade-offs of Cold Backup

•Requires Downtime — Database unavailable during backup window
•No Point-in-Time — Single recovery point per backup
•Scheduling Constraints — Must coordinate with maintenance windows
•RPO Limitations — Data loss potential equals backup interval
•Not for 24/7 Systems — Incompatible with zero-downtime requirements
•All-or-Nothing — Cannot backup/restore individual objects
•Storage Space — Full backup size every time (unless using incrementals)
•Coordination Required — Applications must handle database unavailability

Decision Matrix: Cold vs. Hot Backup
Requirement	Favors Cold Backup	Favors Hot Backup
24/7 Availability Required	No	Yes (strongly)
Maximum Recovery Reliability	Yes (strongly)	Yes
Point-in-Time Recovery	No	Yes (strongly)
Limited DBA Expertise	Yes	No
Compliance/Audit Requirements	Often yes	Depends on requirement
Development/Test Environment	Yes (usually)	Overkill
Before Major Changes	Yes (strongly)	Also recommended
Minimal RPO Tolerance	No	Yes (strongly)
Simple Recovery Needed	Yes	No (more complex)

Summary and Best Practices

Offline backup remains a valuable tool in the database professional's arsenal. Its simplicity, reliability, and guaranteed consistency make it indispensable for certain scenarios.

Cold Backup Best Practices

•Use for baseline backups — Even with online backup, periodic cold backups provide verified consistent baselines
•Leverage storage snapshots — LVM, ZFS, or cloud snapshots minimize downtime to seconds while maintaining cold backup reliability
•Document and test recovery — Cold backup is only valuable if recovery works; test regularly
•Schedule appropriately — Align with natural low-traffic periods or maintenance windows
•Include all components — Data files, configuration files, metadata—ensure backup is complete
•Verify with checksums — Generate and validate checksums for all backup files
•Combine with log archiving — For production systems, supplement cold backup with continuous log archiving for point-in-time capability
•Communicate downtime — Ensure all stakeholders are aware of and prepared for backup windows

Page Complete

2 / 5