Postgres and MySQL DR on EC2: AWS Automates the Snapshot, Not the Restore

TL;DR Amazon Data Lifecycle Manager can run the whole snapshot half of DR for self-managed PostgreSQL and MySQL on EC2: multi-volume snapshot sets on a schedule, pre and post scripts, cross-region copy and retention, with no charge for the service itself. It stops at the snapshot: the restore, the hydration and the log replay are yours, and they set the RTO. DLM covers EBS only, so Elasticsearch, Kafka and Redis keep the DR mechanisms Part 1 describes.

Part 1 moved the data of self-managed databases onto EBS, split the log onto its own volume, and left the DR plan as a snapshot schedule plus a hydration rate. This part builds that plan for PostgreSQL and MySQL around Amazon Data Lifecycle Manager (DLM), which the EBS docs describe as a backup solution for EC2 instances and EBS volumes “at no additional cost” (snapshot storage and inter-region transfer are billed as usual). Since November 2023 it also runs pre and post scripts through Systems Manager around each snapshot.

What it produces is a set of snapshots sitting in a second region. Nothing in it creates a volume, starts a database, replays a log, or tells anyone how long those take.

“Use AWS Backup instead” is the usual first answer, and for part of the job it’s right. AWS Backup takes the same crash-consistent multi-volume backups of an instance and, per its feature table, adds vault lock, restore testing, organization-wide policies and the logically air-gapped vault that can be shared to a recovery account through AWS RAM. Its application-consistent path for EC2 is Windows VSS; DLM’s scripts are the Linux equivalent. So: DLM for the frequent, script-wrapped DR sets, AWS Backup for the one copy that has to survive a compromised account. Neither solves the restore. AWS Backup’s restore testing times a job that ends when the volume exists. Hydration and replay happen after that line.

Point one policy at the database host

The policy that matters targets instances, not volumes. ResourceTypes: INSTANCE creates multi-volume snapshot sets from every volume on each tagged instance at one instant, which is the crash consistency Part 1’s log split depends on; a volume-targeted policy is the split-instant problem all over again. The root volume can be excluded, since the restore boots a fresh AMI, and a volume attached later joins the set at the next run.

Schedules are the RPO knob. A policy holds up to four, each an interval of 1 to 24 hours or a cron expression. Every run starts “within one hour” of its scheduled time, so snapshots alone give an RPO of the interval, plus up to an hour of jitter, plus the copy. Retention is by count (1 to 1,000) or by age.

Cross-region copy is a per-schedule rule for up to three regions, each with its own retention. Encrypted snapshots stay encrypted, under the destination region’s default EBS key unless the rule names a KMS key the DLM role can use. The first copy is full and later ones incremental, per the time-based copy docs, which also sell a guaranteed completion time of 15 minutes to 48 hours. DLM’s copy rule has no field for it, so a DLM copy finishes when it finishes: [MEASURE: time for one incremental snapshot set (data plus log volume) to complete in the DR region, primary to DR, at the production change rate]. EBS also caps concurrent snapshot copies at 20 per destination region, raised only through AWS Support, so a fleet that snapshots on the hour copies in a queue.

 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

{
 "PolicyType": "EBS_SNAPSHOT_MANAGEMENT",
 "ResourceTypes": ["INSTANCE"],
 "TargetTags": [{ "Key": "dr-tier", "Value": "pg-main" }],
 "Parameters": { "ExcludeBootVolume": true },
 "Schedules": [{
 "Name": "every-4h",
 "CopyTags": true,
 "VariableTags": [{ "Key": "SnapSet", "Value": "$(timestamp)" }],
 "TagsToAdd": [{ "Key": "DrSet", "Value": "pg-main" }],
 "CreateRule": {
 "Interval": 4, "IntervalUnit": "HOURS", "Times": ["00:15"],
 "Scripts": [{
 "Stages": ["PRE", "POST"],
 "ExecutionHandlerService": "AWS_SYSTEMS_MANAGER",
 "ExecutionHandler": "pg-freeze-thaw",
 "ExecuteOperationOnScriptFailure": true,
 "ExecutionTimeout": 60,
 "MaximumRetryCount": 2
 }]
 },
 "RetainRule": { "Count": 18 },
 "CrossRegionCopyRules": [{
 "Target": "us-west-2",
 "Encrypted": true,
 "CmkArn": "arn:aws:kms:us-west-2:111122223333:key/EXAMPLE",
 "CopyTags": true,
 "RetainRule": { "Interval": 7, "IntervalUnit": "DAYS" }
 }]
 }]
}

The tags carry the restore. CopyTags copies the volumes’ own tags (a role of data or log, say) to each snapshot, the cross-region rule carries them to the copy, and VariableTags, valid only on instance policies, stamps each run’s snapshots with a shared value so a restore never pairs a data snapshot with a log snapshot from a different run. Sharing to another account works only under a customer-managed key, and copying inside that account takes a second, event-based policy there.

Protection is the gap. DLM has no snapshot-lock setting, and snapshot lock (launched November 15, 2023, no extra charge) is applied per snapshot. Governance mode can be lifted by anyone with the IAM permission; compliance mode, after an optional 1 to 72 hour cooling-off, holds until the lock expires or the account closes. Locking a DLM-created snapshot by hand takes it out of DLM’s management, so whatever locks it also owns deleting it. Recycle Bin is the cheaper backstop: a region-level rule keeps deleted snapshots for 1 to 365 days at normal rates and can be locked with a 7 to 30 day unlock delay. It also catches every snapshot DLM expires, so each retention cycle is paid for one extra bin period.

The archive tier sits outside any RTO Archived snapshots are full rather than incremental, carry a 90-day minimum, and take up to 72 hours to come back before any volume can be created from them. DLM archives only from volume-targeted policies on a schedule of 28 days or more, and never archives cross-region copies. It’s a retention tool for monthly sets, never a recovery path.

Freeze the filesystems, then find the log position

Scripts need the SSM Agent on the instance, the AWSDataLifecycleManagerSSMFullAccess policy on the DLM role, and a DLMScriptsAccess:true tag on the SSM document. For MySQL and PostgreSQL there’s no AWS-managed document; the app-consistent snapshot docs publish samples you create as your own. Both skip / and /boot, run fsfreeze -f on every other filesystem, and schedule a thaw after 60 seconds in case the post-script never arrives. The PostgreSQL sample runs CHECKPOINT first. The MySQL sample runs this, then sync:

1

sudo mysql -e 'FLUSH TABLES WITH READ LOCK;'

The sample's lock is gone before the freeze A global read lock belongs to the session that took it, and mysql -e closes its session as soon as the statement returns. On MySQL 8.4.11 in Docker, an INSERT issued from a second session right after that exact command completed in 58 ms; with the lock held in a session that stayed open, the same INSERT failed on a 5-second lock_wait_timeout. Whatever consistency the MySQL sample produces comes from fsfreeze and the multi-volume set. A pre-script that needs the lock through the freeze has to hold a session open in the background and release it from the post-script.

The snapshots restore anyway, because neither engine needs the lock. A multi-volume set is what a power cut would leave, and both engines recover from that by design. The PostgreSQL docs say a frozen snapshot starts up as a crash, and suggest a CHECKPOINT beforehand “to reduce recovery time”. For these two engines, then, the scripts buy a shorter crash recovery and filesystems with no half-written metadata. They don’t buy correctness. The freeze isn’t free, either: every commit blocks while the log volume is frozen, [MEASURE: commit stall on the production host between the PreScriptStarted and PostScriptCompleted metrics].

Tagging a replica instead of the primary moves that stall off the write path. The freeze then blocks replication apply rather than commits, and the lag it adds is paid at restore time as extra log to replay, not by the application. Two conditions. The replica’s position has to be expressible in the primary’s log stream, which GTIDs give for free (auto-skip works off the replica’s gtid_executed against the primary’s binlogs) and a non-GTID setup does not, since the replica’s own binlog coordinates mean nothing against the primary’s files; there the pre-script records SHOW REPLICA STATUS and maps through Exec_Source_Log_Pos. On PostgreSQL a streaming standby that was never promoted shares the primary’s timeline, so its data directory rolls forward from the same archive. And the replica has to be as durably configured as the primary, because a sync_binlog=0 replica snapshots an instant where the binlog trails the redo, which crash recovery resolves by rolling back, so the set is simply a little older than it looks.

That decides the failure setting. Each script gets a 10 to 120 second timeout and up to three retries, and per the script flow, when the pre-script still fails DLM either takes a crash-consistent set or skips the instance for that run. Enable the fallback: the set is recoverable, and a skipped run widens the RPO by a full interval. DLM records which happened in system tags (aws:dlm:pre-script and aws:dlm:post-script, SUCCESS or FAILED) and a PreScriptFailed CloudWatch metric worth an alarm.

Those two tags are all DLM writes. There’s no way to attach a script’s output, an LSN or a binlog coordinate, to the snapshot. Neither engine needs it, provided the set is atomic, because the position is already on the volumes.

PostgreSQL keeps it in pg_control. Restore the set, create recovery.signal, and set a restore_command pointing at the WAL archive (it’s ignored outside recovery, so it can live in the primary’s config and travel with every snapshot). The server runs crash recovery from the last checkpoint, then keeps fetching archived WAL. The test, on PostgreSQL 17.11 in Docker, used a stand-in for a crash-consistent EBS set: a copy of the data directory taken while docker pause froze the container mid-insert, with more rows committed and archived afterwards. The restore logged redo starts at 0/20000A0, consistent recovery state reached at 0/43224B8, two segments restored ... from archive, and archive recovery complete. Every row committed on the source after the copy was there.

MySQL with GTIDs works the same way through auto-skip: a server ignores any transaction whose GTID it has already committed. The same frozen-copy test on MySQL 8.4.11 restored to 1,533 rows of a running insert stream and a gtid_executed of 1-519. Piping every shipped binlog through mysqlbinlog into it, from the first file, skipped what it had and applied the rest, finishing at 18,000 rows and 1-6009, identical to the source. Without GTIDs the pre-script has to capture SHOW BINARY LOG STATUS under a lock it actually holds, and write it to a file on the data volume so the snapshot carries it.

The atomic part is not optional. An earlier MySQL run copied the data directory file by file while the inserts ran, so the redo and the binlog were captured at different moments. After crash recovery and a full binlog replay it came back 84 rows short, while gtid_executed listed every transaction as applied. Auto-skip had skipped 28 transactions the restored server never had. No error, no warning. That is the split-instant failure Part 1 warned about, and the reason the policy above targets the instance and not the volumes.

Ship the log to the DR region

Snapshots every four hours mean an RPO of four hours and change. Shipping the log continuously cuts it to the shipping lag, and the restore becomes the snapshot set plus a replay.

On PostgreSQL the classic route is archive_command, which only runs for completed 16 MB segments, so a quiet server sits on unarchived commits unless archive_timeout forces a switch. pgBackRest’s archive-push and WAL-G’s wal-push both send segments to a bucket in the DR region. pg_receivewal, run on a DR-region host, streams WAL “in real time as it’s being generated” instead. It needs a replication slot to avoid gaps, and a slot whose receiver is down retains WAL on the primary until the disk fills, unless max_slot_wal_keep_size caps it.

On MySQL the equivalent is a host in the DR region running mysqlbinlog as a live backup:

1
2

mysqlbinlog --read-from-remote-server --host=primary.internal --raw \
 --stop-never --connection-server-id=9001 binlog.000130

It writes each binlog under its original name as events arrive. It “does not automatically reconnect” after a blip or a primary restart, so it runs under a supervisor that restarts it from the newest file it holds, and it stores encrypted binlogs unencrypted. The files go to S3 on whatever schedule the RPO tolerates.

Either way the archive has to reach back to the oldest snapshot set still retained in the DR region, or that set can’t be rolled forward. The lag is the RPO, and only a measurement gives it: [MEASURE: WAL archive or binlog stream lag at the primary’s peak write rate].

Pay for hydration in time or in money

A volume created from a snapshot is usable at once and slow until every block has come down from S3, at a default rate that per the initialization docs “fluctuates throughout the initialization process”. Two things make it predictable.

The provisioned initialization rate is chosen at create-volume, 100 to 300 MiB/s, delivered within 10 percent of the rate 99 percent of the time. Time is the snapshot’s data size over the rate: 1 TiB at 300 MiB/s is about 58 minutes. The charge is per GiB of snapshot data at the pricing page rate, once per restore. A region allows 5,000 MiB/s across concurrent requests by default, so 16 volumes at the top rate, and it isn’t available for volume copies.

Fast Snapshot Restore creates the volume fully initialized, at $0.75 per hour per snapshot per AZ in us-east-1 with a one-hour minimum ($540 a month for one snapshot in one AZ, per the docs). A region allows five FSR-enabled snapshots by default, and each snapshot-and-AZ pair has a credit bucket that starts empty, refills at MIN(10, 1024 ÷ size in GiB) credits an hour and holds MAX(1, MIN(10, 1024 ÷ size)). A 2,048 GiB snapshot earns half a credit an hour and holds one, so the first fast volume is two hours after enabling, on top of the 60 minutes per TiB DLM’s docs give for optimization.

DLM can enable FSR on the snapshots it creates, but its rule names AZs in the policy’s own region, and a copy of an FSR-enabled snapshot is not FSR-enabled. For DR that means automation in the DR region enabling it on each new copy and disabling it on the one before, with the credit clock restarting each time. A data-plus-log set in one AZ is $1.50 an hour, about $1,080 a month, around the clock.

If both are set, the provisioned rate wins and FSR is ignored. The engine can start before initialization finishes, so the real question is how much slower replay runs against blocks still in S3: [MEASURE: replay rate (GB of WAL or binlog per minute) on a volume still initializing versus one fully initialized, same instance type].

Pick the interval from the replay rate

Everything above except four numbers comes out of the AWS docs, and the four are the plan. The copy time is how far behind the DR region runs. The freeze stall, or the apply lag it adds on a replica, is how often the schedule can fire without the application noticing. The shipping lag is the RPO. The replay rate on a volume still pulling blocks from S3 is the one that decides the snapshot interval, and it is the one to measure first.

A longer interval is nearly free everywhere else. Snapshot sets are incremental, so a daily set stores about the same changed blocks as six four-hour sets; the copy queue and the freeze run once a day instead of six times; the shipped log covers the RPO either way. What a longer interval buys is replay: every hour between snapshots is more WAL or binlog that one recovery process applies, serially, before the first query runs. RDS lands on a daily snapshot with five-minute log uploads, and replays on a fully provisioned instance. On a volume created at the default rate the same day of log can take much longer, which is why the measurement has to be taken on the volume type and initialization rate the plan will pay for, with the log written just before a scheduled snapshot so it is the worst case. The interval that keeps that replay inside the RTO is the one the policy gets.