Backup Implementation Strategies

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
# Backup Storage Configuration Examples
 
# =====================================
# DAS: RAID Configuration with mdadm
# =====================================
 
# Create RAID 6 array for backup storage
# 6 data disks + 2 parity = tolerates 2 disk failures
sudo mdadm --create /dev/md0 \
    --level=6 \
    --raid-devices=8 \
    /dev/sd[b-i]
 
# Create filesystem
sudo mkfs.xfs -L backup_storage /dev/md0
 
# Mount for backup use
sudo mkdir -p /backup
sudo mount /dev/md0 /backup
 
# Add to fstab for persistence
echo "LABEL=backup_storage /backup xfs defaults,noatime 0 2" | sudo tee -a /etc/fstab
 
# =====================================
# NAS: NFS Mount for Backup
# =====================================
 
# Install NFS client
sudo apt-get install nfs-common
 
# Mount NFS share for backup
sudo mkdir -p /backup/nfs
sudo mount -t nfs -o hard,intr,rsize=1048576,wsize=1048576 \
    nas.example.com:/vol/backups /backup/nfs
 
# Persistent mount in fstab
echo "nas.example.com:/vol/backups /backup/nfs nfs hard,intr,rsize=1048576,wsize=1048576 0 0" | sudo tee -a /etc/fstab
 
# =====================================
# SAN: iSCSI Target Mount
# =====================================
 
# Discover iSCSI targets
sudo iscsiadm -m discovery -t sendtargets -p san.example.com:3260
 
# Login to target
sudo iscsiadm -m node -T iqn.2024-01.com.example:backup001 -p san.example.com:3260 -l
 
# Create filesystem on iSCSI LUN
sudo mkfs.xfs /dev/sdc  # Newly discovered iSCSI disk
 
# Mount iSCSI volume
sudo mkdir -p /backup/san
sudo mount /dev/sdc /backup/san
 
# =====================================
# Object Storage: S3-Compatible Mount
# =====================================
 
# Install s3fs for S3 mounting (not recommended for primary backup)
sudo apt-get install s3fs
 
# Create credentials file
echo "ACCESS_KEY:SECRET_KEY" > ~/.passwd-s3fs
chmod 600 ~/.passwd-s3fs
 
# Mount S3 bucket
s3fs mybucket /backup/s3 -o passwd_file=~/.passwd-s3fs,url=https://s3.amazonaws.com
 
# Better approach: Use backup tool's native S3 support
# pgBackRest with S3
cat >> /etc/pgbackrest/pgbackrest.conf <<EOF
[global]
repo1-type=s3
repo1-s3-bucket=backup-bucket
repo1-s3-endpoint=s3.amazonaws.com
repo1-s3-region=us-east-1
repo1-path=/pgbackrest
repo1-s3-key=ACCESS_KEY
repo1-s3-key-secret=SECRET_KEY
EOF

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
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122

#!/bin/bash
# Cloud Storage Integration Examples
 
# =====================================
# AWS S3 Lifecycle Policy
# =====================================
 
# Create lifecycle policy for backup tiering
cat > lifecycle-policy.json <<EOF
{
    "Rules": [
        {
            "ID": "BackupLifecycle",
            "Status": "Enabled",
            "Filter": {
                "Prefix": "backups/"
            },
            "Transitions": [
                {
                    "Days": 30,
                    "StorageClass": "STANDARD_IA"
                },
                {
                    "Days": 90,
                    "StorageClass": "GLACIER_IR"
                },
                {
                    "Days": 365,
                    "StorageClass": "DEEP_ARCHIVE"
                }
            ],
            "Expiration": {
                "Days": 2555
            },
            "NoncurrentVersionExpiration": {
                "NoncurrentDays": 30
            }
        }
    ]
}
EOF
 
aws s3api put-bucket-lifecycle-configuration \
    --bucket my-backup-bucket \
    --lifecycle-configuration file://lifecycle-policy.json
 
# =====================================
# Backup to S3 with Intelligent-Tiering
# =====================================
 
# Upload backup with intelligent tiering
aws s3 cp /backup/daily_backup.tar.gz \
    s3://my-backup-bucket/daily/ \
    --storage-class INTELLIGENT_TIERING
 
# Sync entire backup directory
aws s3 sync /backup/postgresql s3://my-backup-bucket/postgresql/ \
    --storage-class INTELLIGENT_TIERING \
    --only-show-errors
 
# =====================================
# Cross-Region Replication for DR
# =====================================
 
# Enable versioning (required for replication)
aws s3api put-bucket-versioning \
    --bucket my-backup-bucket \
    --versioning-configuration Status=Enabled
 
# Create replication configuration
cat > replication-config.json <<EOF
{
    "Role": "arn:aws:iam::123456789:role/S3ReplicationRole",
    "Rules": [
        {
            "ID": "DR-Replication",
            "Status": "Enabled",
            "Priority": 1,
            "DeleteMarkerReplication": { "Status": "Disabled" },
            "Filter": { "Prefix": "" },
            "Destination": {
                "Bucket": "arn:aws:s3:::my-backup-bucket-dr",
                "StorageClass": "STANDARD_IA"
            }
        }
    ]
}
EOF
 
aws s3api put-bucket-replication \
    --bucket my-backup-bucket \
    --replication-configuration file://replication-config.json
 
# =====================================
# Azure Blob Storage Integration
# =====================================
 
# Create storage account with geo-redundancy
az storage account create \
    --name mybackupstorage \
    --resource-group backups \
    --location eastus \
    --sku Standard_GRS \
    --kind StorageV2
 
# Create container with blob-level tiering
az storage container create \
    --name backups \
    --account-name mybackupstorage
 
# Upload with access tier
az storage blob upload \
    --account-name mybackupstorage \
    --container-name backups \
    --name daily/backup.tar.gz \
    --file /backup/daily_backup.tar.gz \
    --tier Cool
 
# Set lifecycle management policy
az storage account management-policy create \
    --account-name mybackupstorage \
    --policy @lifecycle-policy.json

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
90
91

#!/bin/bash
# Backup Immutability Configuration Examples
 
# =====================================
# AWS S3 Object Lock
# =====================================
 
# Create bucket with object lock enabled
aws s3api create-bucket \
    --bucket my-immutable-backup \
    --object-lock-enabled-for-bucket \
    --create-bucket-configuration LocationConstraint=us-east-1
 
# Set default retention policy (Governance mode)
aws s3api put-object-lock-configuration \
    --bucket my-immutable-backup \
    --object-lock-configuration '{
        "ObjectLockEnabled": "Enabled",
        "Rule": {
            "DefaultRetention": {
                "Mode": "GOVERNANCE",
                "Days": 30
            }
        }
    }'
 
# Upload with compliance retention (cannot be deleted even by root)
aws s3api put-object \
    --bucket my-immutable-backup \
    --key backups/critical-backup.tar.gz \
    --body /backup/critical-backup.tar.gz \
    --object-lock-mode COMPLIANCE \
    --object-lock-retain-until-date "2024-12-31T00:00:00Z"
 
# =====================================
# Azure Immutable Blob Storage
# =====================================
 
# Create container with legal hold
az storage container create \
    --name immutable-backups \
    --account-name mybackupstorage
 
# Set time-based retention policy (locked after confirmation)
az storage container immutability-policy create \
    --resource-group backups \
    --account-name mybackupstorage \
    --container-name immutable-backups \
    --period 90
 
# Lock the policy (irreversible!)
az storage container immutability-policy lock \
    --resource-group backups \
    --account-name mybackupstorage \
    --container-name immutable-backups
 
# =====================================
# Linux: Immutable File Attribute
# =====================================
 
# Make backup file immutable (cannot be modified/deleted even by root)
sudo chattr +i /backup/critical_backup.tar.gz
 
# Check immutable status
lsattr /backup/critical_backup.tar.gz
# Output: ----i--------e-- /backup/critical_backup.tar.gz
 
# Remove immutable (required for deletion/rotation)
sudo chattr -i /backup/critical_backup.tar.gz
 
# Automated immutable backup script
backup_file="/backup/$(date +%Y%m%d)_backup.tar.gz"
tar -czf "$backup_file" /data
sudo chattr +i "$backup_file"
 
# Schedule immutability removal for retention
echo "sudo chattr -i $backup_file && rm -f $backup_file" | \
    at "now + 30 days"
 
# =====================================
# ZFS: Snapshot Holds
# =====================================
 
# Create hold on snapshot (prevents destruction)
zfs hold compliance_hold zpool/backups@2024-01-15
 
# List holds
zfs holds zpool/backups@2024-01-15
 
# Release hold (when retention period expires)
zfs release compliance_hold zpool/backups@2024-01-15

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
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107

#!/bin/bash
# Backup Retention and Lifecycle Management
 
# =====================================
# GFS Retention Implementation
# =====================================
 
BACKUP_DIR="/backup/postgresql"
TODAY=$(date +%u)  # Day of week (1-7, Monday=1)
DAY_OF_MONTH=$(date +%d)
MONTH=$(date +%m)
 
# Daily backup (keep 7 days)
pg_dump mydb > "$BACKUP_DIR/daily/$(date +%Y%m%d).sql.gz"
find "$BACKUP_DIR/daily" -name "*.sql.gz" -mtime +7 -delete
 
# Weekly backup on Sunday (keep 4 weeks)
if [ "$TODAY" -eq 7 ]; then
    cp "$BACKUP_DIR/daily/$(date +%Y%m%d).sql.gz" "$BACKUP_DIR/weekly/"
    find "$BACKUP_DIR/weekly" -name "*.sql.gz" -mtime +28 -delete
fi
 
# Monthly backup on 1st (keep 12 months)
if [ "$DAY_OF_MONTH" -eq "01" ]; then
    cp "$BACKUP_DIR/daily/$(date +%Y%m%d).sql.gz" "$BACKUP_DIR/monthly/"
    find "$BACKUP_DIR/monthly" -name "*.sql.gz" -mtime +365 -delete
fi
 
# Yearly backup on Jan 1st (keep 7 years)
if [ "$DAY_OF_MONTH" -eq "01" ] && [ "$MONTH" -eq "01" ]; then
    cp "$BACKUP_DIR/daily/$(date +%Y%m%d).sql.gz" "$BACKUP_DIR/yearly/"
    find "$BACKUP_DIR/yearly" -name "*.sql.gz" -mtime +2555 -delete
fi
 
# =====================================
# Tiered Storage Migration
# =====================================
 
FAST_TIER="/backup/ssd"      # 0-7 days
CAPACITY_TIER="/backup/hdd"   # 7-30 days
ARCHIVE_TIER="/backup/s3"     # 30+ days
 
# Move from fast to capacity tier (7+ days old)
find "$FAST_TIER" -name "*.backup" -mtime +7 -exec mv {} "$CAPACITY_TIER/" \;
 
# Archive to cloud (30+ days old)
find "$CAPACITY_TIER" -name "*.backup" -mtime +30 | while read file; do
    # Compress before archive
    gzip "$file"
    
    # Upload to S3 with lifecycle
    aws s3 cp "${file}.gz" s3://backup-bucket/archive/ \
        --storage-class STANDARD_IA
    
    # Remove local copy after confirmed upload
    rm "${file}.gz"
done
 
# =====================================
# pgBackRest Retention Configuration
# =====================================
 
cat >> /etc/pgbackrest/pgbackrest.conf <<EOF
[global]
# Full backup retention (keep 4 full backups)
repo1-retention-full=4
repo1-retention-full-type=count
 
# Differential retention (keep 2 per full)
repo1-retention-diff=2
 
# Archive retention (match full backup retention)
repo1-retention-archive=4
repo1-retention-archive-type=full
EOF
 
# Expire old backups based on retention
pgbackrest --stanza=main expire
 
# =====================================
# Database-Level Retention Tracking
# =====================================
 
# Create backup catalog table
psql <<EOF
CREATE TABLE IF NOT EXISTS backup_catalog (
    backup_id SERIAL PRIMARY KEY,
    backup_date TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
    backup_type VARCHAR(20),  -- full, diff, incr, log
    backup_path TEXT,
    backup_size BIGINT,
    retention_class VARCHAR(20),  -- daily, weekly, monthly, yearly
    expire_date DATE,
    status VARCHAR(20) DEFAULT 'active'
);
 
-- Insert today's backup
INSERT INTO backup_catalog (backup_type, backup_path, backup_size, retention_class, expire_date)
VALUES ('full', '/backup/daily/20240115.sql.gz', 1073741824, 'daily', CURRENT_DATE + 7);
 
-- Find expired backups
SELECT backup_path FROM backup_catalog 
WHERE expire_date < CURRENT_DATE AND status = 'active';
 
-- Mark as expired
UPDATE backup_catalog SET status = 'expired' WHERE expire_date < CURRENT_DATE;
EOF

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

#!/bin/bash
# Backup Storage Monitoring and Alerting
 
# =====================================
# Storage Capacity Monitoring
# =====================================
 
BACKUP_PATH="/backup"
THRESHOLD_WARNING=80
THRESHOLD_CRITICAL=90
 
# Check usage percentage
usage=$(df "$BACKUP_PATH" | awk 'NR==2 {print $5}' | tr -d '%')
 
if [ "$usage" -ge "$THRESHOLD_CRITICAL" ]; then
    echo "CRITICAL: Backup storage at ${usage}%" |         mail -s "CRITICAL: Backup Storage Space" dba@example.com
    exit 2
elif [ "$usage" -ge "$THRESHOLD_WARNING" ]; then
    echo "WARNING: Backup storage at ${usage}%" |         mail -s "WARNING: Backup Storage Space" dba@example.com
    exit 1
fi
 
# =====================================
# Backup Size Trending
# =====================================
 
# Log daily backup sizes
cat >> /var/log/backup_sizes.log <<EOF
$(date +%Y-%m-%d),$(du -sb /backup/daily/$(date +%Y%m%d)* 2>/dev/null | awk '{sum+=$1} END {print sum}')
EOF
 
# Detect abnormal backup size (>50% deviation from average)
recent_avg=$(tail -7 /var/log/backup_sizes.log | awk -F, '{sum+=$2; count++} END {print sum/count}')
today_size=$(tail -1 /var/log/backup_sizes.log | cut -d, -f2)
deviation=$(echo "scale=2; ($today_size - $recent_avg) / $recent_avg * 100" | bc)
 
if [ "$(echo "$deviation > 50 || $deviation < -50" | bc)" -eq 1 ]; then
    echo "Unusual backup size detected: ${deviation}% deviation" |         mail -s "Backup Size Anomaly" dba@example.com
fi
 
# =====================================
# Backup Success Monitoring
# =====================================
 
# Check for today's backup
expected_backup="/backup/daily/$(date +%Y%m%d)_backup.sql.gz"
if [ ! -f "$expected_backup" ]; then
    echo "CRITICAL: Missing backup for $(date +%Y-%m-%d)" |         mail -s "MISSING BACKUP" dba@example.com
    exit 2
fi
 
# Verify backup is not empty/corrupt
min_size=$((100 * 1024 * 1024))  # 100 MB minimum
actual_size=$(stat -c%s "$expected_backup")
if [ "$actual_size" -lt "$min_size" ]; then
    echo "CRITICAL: Backup too small (${actual_size} bytes)" |         mail -s "BACKUP SIZE CRITICAL" dba@example.com
    exit 2
fi
 
# =====================================
# Prometheus/Grafana Metrics
# =====================================
 
# Expose metrics for Prometheus scraping
cat > /var/lib/node_exporter/textfile_collector/backup.prom <<EOF
# HELP backup_storage_bytes Total backup storage used
# TYPE backup_storage_bytes gauge
backup_storage_bytes $(du -sb /backup | awk '{print $1}')
 
# HELP backup_storage_available_bytes Available backup storage
# TYPE backup_storage_available_bytes gauge
backup_storage_available_bytes $(df -B1 /backup | awk 'NR==2 {print $4}')
 
# HELP backup_last_success_timestamp Last successful backup timestamp
# TYPE backup_last_success_timestamp gauge
backup_last_success_timestamp $(stat -c%Y "$expected_backup" 2>/dev/null || echo 0)
 
# HELP backup_size_bytes Size of most recent backup
# TYPE backup_size_bytes gauge  
backup_size_bytes $actual_size
EOF