Aurora Backtrack lets you rewind your database cluster to a previous point in time without needing to restore from a snapshot.
Let’s see it in action. Imagine you just made a destructive DROP TABLE statement on your production Aurora cluster, or perhaps a bad data import corrupted a critical table. You need to roll back fast.
Here’s a simulated scenario. We have an Aurora PostgreSQL cluster my-aurora-cluster. We’ll create a table and insert some data.
-- Connect to your Aurora PostgreSQL instance
CREATE TABLE test_backtrack (id INT, data VARCHAR(50));
INSERT INTO test_backtrack VALUES (1, 'initial data');
INSERT INTO test_backtrack VALUES (2, 'more data');
SELECT * FROM test_backtrack;
-- Output:
-- id | data
-- ----+----------------
-- 1 | initial data
-- 2 | more data
-- (2 rows)
Now, let’s say an accidental DELETE statement wiped out our data.
-- Oops! Accidental deletion
DELETE FROM test_backtrack WHERE id = 1;
SELECT * FROM test_backtrack;
-- Output:
-- id | data
-- ----+------------
-- 2 | more data
-- (1 row)
We want to go back to before that DELETE happened. We can use Backtrack. First, we need to enable it and set a target time. You can enable Backtrack on an existing cluster or during creation. For an existing cluster, you modify it:
aws rds modify-db-cluster \
--db-cluster-identifier my-aurora-cluster \
--backtrack-window 1440 # In minutes, 1440 minutes = 24 hours
This command enables Backtrack and sets the window to 24 hours. This means you can backtrack up to 24 hours into the past. The backtrack-window is the maximum amount of time you can rewind. The actual backtrack operation is what rewinds your cluster.
To perform the backtrack, you specify the target time. This can be a specific timestamp or a relative time. Let’s say we want to backtrack to 5 minutes ago.
aws rds start-db-cluster-backtrack \
--db-cluster-identifier my-aurora-cluster \
--backtrack-to-time "2023-10-27T10:30:00Z" \ # Replace with your desired timestamp
--use-latest-restorable-time \ # Or use this to backtrack to the most recent possible point
--backtrack-id my-backtrack-operation-001
The use-latest-restorable-time flag is useful if you want to go back to the absolute latest point, which is often what you need for a quick recovery. If you use a specific backtrack-to-time, ensure it’s within your backtrack-window. The backtrack-id is a unique identifier for this operation.
Aurora will then create a new cluster that is a copy of your original cluster at the specified backtrack time. Your original cluster remains untouched until you decide to switch over. This is a crucial safety feature.
After the backtrack operation completes, you’ll have a new cluster (e.g., my-aurora-cluster-backtrack-001). You can then connect to this new cluster, verify your data, and if it’s correct, promote it to be your primary cluster.
-- Connect to the new backtrack cluster
SELECT * FROM test_backtrack;
-- Output:
-- id | data
-- ----+--------------
-- 1 | initial data
-- 2 | more data
-- (2 rows)
See? Our data is back.
The internal mechanism here is quite clever. Aurora doesn’t store full snapshots for backtracking. Instead, it uses a log-based approach. When Backtrack is enabled, Aurora maintains a history of changes in a special log. When you initiate a backtrack, Aurora reconstructs the database state by replaying these logs in reverse until it reaches the target point in time. This log is what consumes storage and incurs a small cost. The backtrack-window setting determines how long this log is retained.
One key aspect is how Aurora handles the primary instance. When you initiate a backtrack, Aurora creates a new cluster. This new cluster will have its own primary instance. You then need to manually switch your application traffic to point to the new cluster’s endpoint. Aurora does not automatically switch your application for you. This manual step is critical for ensuring you’ve validated the data and are ready to make the switch.
The next concept to explore is how to automate this failover process using Route 53 or other DNS services to seamlessly switch your application endpoints after a successful backtrack.