17 ms·

Amazon Aurora Backtrack

416 points by jeffbarr 8y ago

wpietri 8y ago
Very interesting. The describe it as a rewind. Does anybody know if it's really a rewind, where each log record is reversible? Or do they do the easier thing of saving snapshots and then replaying the log from snapshot to desired point?
- walru 8y ago
  Every thing is recorded. edit: And I'm being down voted because? Because HN does not enjoy the truth?
  - freehunter 8y ago
    The downvotes are probably because the answer wasn't helpful and doesn't mesh with Amazon's statements.
    - walru 8y ago
      How did that work for everyone deleting their FB account?
      - freehunter 8y ago
        How is this conversation helpful to this thread? The question is not "how much does Amazon keep" but "how long/much/granular can I restore".
        walru 8y ago
        That sounded like the original question to me. It's an estranged statement to believe otherwise. So, yes, they save snapshots and take you back to the time of your choosing based off that.
        icebraining 8y ago
        An estranged statement? What does that mean? How do you know they use the snapshots method, rather than reversing the changes?
        walru 8y ago
        Only just twenty years of software development (and what makes sense). Seeing as the rest of this thread is full of conjecture (and children), I thought I'd influence some experience.
        deleted 8y ago
        [deleted]
        icebraining 8y ago
        Well, as Bucephalus355 pointed out in another comment, Oracle has a similar feature and it's not implemented using snapshots.
        matthewmacleod 8y ago
        So, yes, they save snapshots and take you back to the time of your choosing based off that. Okay, but you're most likely wrong with that. If you read about how Aurora works (https://www.allthingsdistributed.com/files/p1041-verbitski.pdf https://www.allthingsdistributed.com/files/p1041-verbitski.p...) it seems likely that they will be using log entries to rollback database state, rather than retaining "snapshots".
- mmahemoff 8y ago
  Probably reversible transactions where possible, but with original-state metadata attached to the transaction if it's required to reverse it (https://en.wikipedia.org/wiki/Memento_pattern https://en.wikipedia.org/wiki/Memento_pattern).
  - awgupta 8y ago
    no, we don't perform destructive writes so we are able to simply mark a region of the log as to be skipped. this approach also allows us to move back and forth within the time domain even after a backtrack.
- misframer 8y ago
  Aurora uses a log-structured storage system, so they just point to a previous version that they haven't garbage collected.
  - awgupta 8y ago
    that's correct.
edge17 8y ago
I’m confused, how is this different than an Undo log?
- misframer 8y ago
  This takes advantage of log-structured storage so there isn't any "undoing".
  - edge17 8y ago
    In a database Undo, in spite of the name, is the act of applying a transaction log to return to a particular state. I used to work on databases earlier in my career, so selling this as a new invention seems somewhat bizarre to me.
    - delta-v 8y ago
      That's not what Aurora is doing. What you have described is more like "git revert". It's O(num of transcation in the undo log). Aurora is a log structured db, they can just reset their DB to a particular LSN. i.e. doing "git reset --hard", which's O(1).
      - awgupta 8y ago
        that's correct.
estsauver 8y ago
I'm slightly confused, is this the same as the existing point-in-time restore that's available for other RDS instances? Edit: Main difference seems to be new cluster vs. in place.
- coenhyde 8y ago
  Similar but from what I understand it allows granularity down to seconds instead of minutes and new cluster provisioning in minutes rather than potentially hours.
  - estsauver 8y ago
    Speed of new cluster provisioning is neat, amazon-rds console currently shows the option to do a point of time restore down to the second. Maybe that's actually not available already and they're truncating to the latest backup? Edit: I guess it's also nice that it's in place. Some people probably don't have the flexibility to easily point applications at a new cluster in a repeatable way, so that may help folks prevent down time.
- scrollaway 8y ago
  No; look at the screenshots. You can pick any point in time given the window you request.
  - estsauver 8y ago
    I see that option in well already for AWS-RDS postgres point in time restore.
  - manigandham 8y ago
    That usually what point in time restores allow, by using snapshots as a base and WAL since then.
- easel 8y ago
  I believe this is special because it pauses, backtracks and resumes your existing cluster. No new cluster provisioning time or configuration changes
ulkesh 8y ago
"Aurora will try to retain enough log information to support that window of time." It's good to know that Aurora will try. It's not like it needs to be reliable or anything.
- jeffbarr 8y ago
  As I noted in the blog post, the storage resides within the cluster. It is storing small values (log sequence numbers) so the space is used with great efficiency, but there is a finite limit. While I don't know the specifics, I would expect that the mapping (time to size) was chosen based on real-world historical data. Cause that's how we roll!
- ben509 8y ago
  Database activity is volatile, so they have to allocate disk space, and that takes time. So if you have light access, and then start uploading many large BLOBs, it may take time for them to allocate space, and during that time the backups might not go all the way back to X hours. And, really, all of this is just servers in a datacenter, so you can have however many 9's of reliability, and still be That Guy that wins the fail lottery.
- chimeracoder 8y ago
  > It's good to know that Aurora will try. It's not like it needs to be reliable or anything. "Reliable" isn't a binary concept. There's no service that is 100% reliable. Having a solution that works 99.99% of the time (which is "only" four nines) is probably good enough that people are willing to use it as a last-resort. (From what it seems, it's included for free, so it's not like this costs you anything anyway - at worst, you end up where you would be if they hadn't released this).
  - ddorian43 8y ago
    It's not free, look at "estimated 10$/month".
  - barkingcat 8y ago
    Not free - there's even a pricing directly on the screenshot!
NyrIsNotDead 8y ago
Why to use AWS when you can just get a cheap VPS from DO and do the same thing?
- freehunter 8y ago
  Because not everyone wants to manage their own infrastructure.
- mabbo 8y ago
  Why get a VPS when you could just keep your servers in your office server room and run everything yourself? Same answer: you're paying for someone else to do the boring parts so that you can work on the interesting bits.
- matthewmacleod 8y ago
  You fundamentally can't do this. Sure – you can set up a couple of VPSs running MySQL, then set up a durable, replicated configuration. Then you need some system to spin up new instances when an instance fails. Then you need some kind of UI to allow that to happen. And you need to implement some kind of point-in-time recovery system, as well as backups, user authentication, monitoring… and so on. The point is, AWS allows you to buy those services instead of spending time building them yourself. There's nothing wrong with building your own database cluster if it makes sense for your use case – but there's nothing wrong with buying one in if it makes sense, either!
craigkerstiens 8y ago
At this time looks like it only applies to MySQL, will be curious to hear if/when it becomes available for PostgreSQL.
duggan 8y ago
Aurora keeps coming along in leaps and bounds, congratulations to the team, this is a fantastic achievement! I only wish that every new feature didn't inevitably come with the caveat that it's only for the MySQL flavour of Aurora. I understand both the engineering and product development reasons for doing so (different stack and MySQL is undoubtedly a much larger customer base), but it always makes these announcements a little underwhelming as an Aurora Postgres user.
- ben509 8y ago
  I have no problem with the fact that the MySQL users get to be my beta-testers.
  - erik_seaberg 8y ago
    If my beta testers shrug off relational integrity, that makes me the real beta tester.
- wgjordan 8y ago
  > I only wish that every new feature didn't inevitably come with the caveat that it's only for the MySQL flavour of Aurora. Not only MySQL, but only MySQL version 5.6, which was released in 2013 and superceded by 5.7 in 2015. Aurora finally released support for 5.7 in February this year after years of development, but all new features so far have been stuck on the 5.6 branch which hasn't been very reassuring.
- paulddraper 8y ago
  One annoyance for we is that you can get MySQL for <$30/month, but posgresql is $200/month
qurashee 8y ago
Amazon rediscovering PITR, nice :P
- ben509 8y ago
  No, they've had that for ages: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PIT.html https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_...
  - manigandham 8y ago
    This post is about Aurora, their own custom db. They could've easily called it PITR to match the rest of the industry.
    - awgupta 8y ago
      This is a different feature than PiTR. We have both. They serve different needs.
thelastidiot 8y ago
Amazon is the new IBM. Knock yourself out and jump into the AWS ecosystem. In a few years down the line, you'll understand that you've lost the leverage you had to potentially take your public cloud business somewhere else when you have so many dependencies on Amazon tech. Basic principles from my view: don't adopt anything but standard EC2/S3 services and create diversity not only in your teams but in your infrastructure policies.
- potle 8y ago
  what are some alternative that one can use for rds postgresql and sqs?
  - deleted 8y ago
    [deleted]
  - josephruscio 8y ago
    For Aurora/RDS Postgres there's https://www.citusdata.com/ https://www.citusdata.com/ or https://www.heroku.com/postgres https://www.heroku.com/postgres
  - 013a 8y ago
    There probably isn't a lot of fear with using RDS. They just do management for you, it isn't any fundamentally different technology that would cause heartache during a migration. Just stay away from Aurora if you care about this. SQS: There's AWS MQ, which is based on ActiveMQ and supports AMQP. If you're going with a more CNCF-focused stack and want to use NATS, I'm not aware of any hosted options.
    - RhodesianHunter 8y ago
      You don't even need to stay away from Aurora ince it's MySql/Postgres SQL API compatible. You would just want to stay away from Aurora specific features like the one in OP.
      - 8y ago
setheron 8y ago
How is this different than point in time restore already available?
- codeonfire 8y ago
  This is amazon's model. A cobbled together team of 1-3 low level engineers takes a feature of some open source software package or the entire package itself, wraps it in an ill-conceived web layer or existing service and makes a big announcement about it. Almost no one uses it, but there will be lots of conference talks about how great it is.
- manigandham 8y ago
  It's not. Just a different name (which unfortunately adds more confusion).
- matthewmacleod 8y ago
  PITR on say RDS is going to be a fair bit slower than this, I'd imagine.
  - delta-v 8y ago
    PITR is a lot slower especially if there are large transactions in the binlog.
Bucephalus355 8y ago
Ok Oracle has had this feature for at least a decade, it’s called a “flashback query”. Obviously Aurora costs 10% of Oracle, but still, I thought this was going to be a huge feature-add considering the HN comment count. That being said, I love AWS, am Pro-Certified, and work with it everyday. I know Oracle is a giant mean bully company, but at least their arrogance was never of the “world-destabilizing” kind like Facebook. EDIT: changed rollback query to flashback query (flashback query can be used both to view or to actually change the DB)
- icebraining 8y ago
  Can you link to any docs about it? All I can find is the ROLLBACK statement in a transaction.
  - Bucephalus355 8y ago
    Sorry rollback was wrong, the correct term was “flashback”. https://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmflash.htm#BRADV89737 https://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmfl...
    - icebraining 8y ago
      Thanks!
- iknowstuff 8y ago
  The free MariaDB is about to introduce system versioning: https://mariadb.com/kb/en/library/system-versioned-tables/ https://mariadb.com/kb/en/library/system-versioned-tables/
aionic 8y ago
It's a relatively classic invention, take something that exists and repackage. A snapshot and a log replay accomplishes something pretty similar. AWS slapped a ui and some orchestration around it. The cloud lock stuff makes sense (although if having an easy "undo button" on your db layer is mission critical to your business you might have other interesteting challenges.
- awgupta 8y ago
  That's not correct. We made a change to mark portions of our log-structured storage as though they should not have occurred. It is a totally different approach than point-in-time restore.
polskibus 8y ago
How did Aurora begin its life? Was it written from scratch or forked from existing open source database?
- gtsteve 8y ago
  It's forked from MySQL 5.6 and 5.7. It's not open-source itself however so presumably they signed an agreement with Oracle to do this.
  - nimrody 8y ago
    MySQL has a GPL license (not AGPL) so I think they can modify and not distribute the source as long as they provide a service and not binaries.
manish_gill 8y ago
The seamlessness of this feature is quite amazing. Backups are usually a huge pain to deal with (I've recently been dealing with Postgres/Barman quite a bit). And disaster scenarios aside (for which AWS already does replications across regions), I think a frequent purpose of backups is really to do this "Undo", go back in time and pretend something didn't happen. All this makes me really really wanna use Aurora. :)
- takeda 8y ago
  What issues did you have with barman? My experience with it was quite pleasant.
  - manish_gill 8y ago
    Various issues with recovery during testing. Missing WALs in the data files that `list-files` command says should be present. Backup failure because of a different number of parallel workers and so on. Combined with the fact that I had to write my own code to push it to S3 (no out of the box support). I suppose it works fine on its own for small to medium databases, and it's a fantastic product. I just wish it was a bit better. :)
    - takeda 8y ago
      About WALs, did you use replication slots? That feature makes WAL logs only removed after all standbys process them. As for S3, WAL-E is probably what you wanted to use. Barman was more intended for on premises installation.
rustyworm 8y ago
Interesting - but what if your database has constant activity? "Oops, my SQL bad" becomes "Oops, my rewind lost 410 transactions"?
- gtowey 8y ago
  This is exactly my concern. There is no production system I have ever worked on which this feature would ever be used. What if you had an e-commerce site? Other customers were placing orders, right? Credit cards being charged? You didn't catch your mistake instantly. So you have a window of maybe, 5 minutes or a hour in which other things happened. You can't simply forget those other transactions and throw away the data.
  - Lightbody 8y ago
    Consider non-production use cases... run a test suite and then reset the data back to a pristine state after each run.
  - joemag 8y ago
    There have been many well publicized events where a production database was lost, or in some way corrupted. I’ve lived through one such event myself. When that happens, you usually go to a backup, and typically run into two problems: 1) Point in time back ups can be hours old. 2) More importantly, “backups” are useless, it’s the “restores” that are valuable. And very few organizations have a well practiced muscle memory for restoring from a backup. A turn key restore solution, with a per second granularity can both significantly decrease the loss window, and recovery time. Hope nobody gets to use it, but when you have, it can be a difference between a big and a small outage.
- joemag 8y ago
  When faced with the alternative of loosing the entire database [1], loosing 410 transactions is usually preferable. Per second granularity gives you ability to restore as close to the fatal mistake (or poison pill transaction) as possible. After that, recovering transactions lost by the rewind can be really tricky (but not impossible). If the entire database got dropped, for example, it’s possible for the unwound writes to contain conflicting IDs. [1] https://about.gitlab.com/2017/02/10/postmortem-of-database-outage-of-january-31/ https://about.gitlab.com/2017/02/10/postmortem-of-database-o...
brettgo1982 8y ago
How is this better than their already existing PITR? Why would someone want to rollback their own production database instead of PITR to a new database and switching over to it? Surely you would end up losing data because you wouldn't be able to reconcile the new data written to it.
cody8295 8y ago
I don't know anything about Aurora and maybe I'm missing something. But why not just wrap everything in a TRANSACTION and then do a ROLLBACK if there's an issue?
- dx034 8y ago
  That's for the cases where you only realize the mistake after the commit. Often because other queries suddenly fail.
  - awgupta 8y ago
    Also, generally DDL statements like DROP TABLE typically autocommit.
- wiredone 8y ago
  Because nearly everyone I know has some memory where at a less experienced point in their engineering career they wrote: delete from ImportantTable where id = 1234 ... and forgot to select the second line in some query editor before running.
  - ben174 8y ago
    There is usually an option to disable delete statements which do not contain a where clause. And if you really want to run that statement you'd need to write: delete from ImportantTable where 1 = 1; But yea, I get your point.
    - mark-wagner 8y ago
      For mysql it is "--i-am-a-dummy". I wish SQL supported putting the where clause first: "where X = Y update..."
- ben509 8y ago
  This would be for deploying a database migration that hoses something. For instance, we had a migration that touched a bunch of tables and, due to a triggered procedure, it blew away all the modification dates. TBH, I'm not sure how useful it is compared to the normal PITR. My usual guidance on recovering after a database fuck up is "put the app into maintenance mode and the database in read-only, do your investigation, run PITR, port user activity to the backup, do spot checks to determine everything got through, roll the app back to before the change, take the old database down, point the app at the new database, bring the system back online." I'm sure a manager is thinking, "oh, we can be back online in 5 minutes, amazing!" But just because something broke doesn't mean users stopped hitting your database! You can't just hit Undo and throw all that out!
gtsteve 8y ago
This is nice but it appears that the entire database instance gets rolled back to that point. It'd be a lot nicer if it could be done at a per-db or per-table granularity. Realistically I'd never use this feature because of the risk of data loss. I'd restore a new instance from backups and copy the lost data back over manually.
- ben174 8y ago
  > per-table granularity Sounds like a nightmare as far as relationships are concerned.
- ryanianian 8y ago
  > It'd be a lot nicer if it could be done at a per-db or per-table granularity. As another commenter pointed out, per-table would be scary for referential-integrity. And per-db kinda makes sense, but if you've got totally different use-cases hosted by the same MySQL you may be using the service incorrectly. > I'd restore a new instance from backups and copy the lost data back over manually. That's always been possible, but even with the best instances and most iops it could take hours to do that. So it's really designed for a different scenario.
  - gtsteve 8y ago
    Regarding referential integrity I don't disagree. It would be a process to be performed by an expert in who understands the data structures and what the effect of rolling them back would be. I might be wrong but I believe Aurora backups can be restored quite a bit quicker than that, right? We are evaluating Aurora at work, hence my interest.
deleted 8y ago
[deleted]
qiuyesuifeng 8y ago
TiDB has already supported this (similar) feature about 2 years ago and it has been adopted by the gaming users: https://www.pingcap.com/blog/2016-11-15-Travelling-Back-in-Time-and-Reclaiming-the-Lost-Treasures/ https://www.pingcap.com/blog/2016-11-15-Travelling-Back-in-T...
ben509 8y ago
Reading the paper[1] linked from Jeff's post: > In Aurora, we have chosen a design point of tolerating (a) losing an entire AZ and one additional node (AZ+1) without losing data, and (b) losing an entire AZ without impacting the ability to write data. We achieve this by replicating each data item 6 ways across 3 AZs with 2 copies of each item in each AZ. We use a quorum model with 6 votes (V = 6), a write quorum of 4/6 (V w = 4), and a read quorum of 3/6 (V r = 3). With such a model, we can (a) lose a single AZ and one additional node (a failure of 3 nodes) without losing read availability, and (b) lose any two nodes, including a single AZ failure and maintain write availability. Ensuring read quorum enables us to rebuild write quorum by adding additional replica copies. There are many 2 AZ regions in AWS, of course. I don't think you can stripe 3 copies per AZ, an AZ failure drops you to potentially 2/6, and if you allow for 2/6 and 3/6 writing you could have a split brain. Any thoughts how they manage that? [1] https://www.allthingsdistributed.com/files/p1041-verbitski.pdf https://www.allthingsdistributed.com/files/p1041-verbitski.p...
- exawsthrowaway 8y ago
  I left AWS before Aurora was introduced, but I suspect it's similar to how S3 fulfills a similar promise. From the S3 FAQ[1]: > Amazon S3 Standard, S3 Standard-Infrequent Access, and Amazon Glacier storage classes replicate data across a minimum of three AZs to protect against the loss of one entire AZ. This remains true in Regions where fewer than three AZs are publicly available. Italic emphasis is mine. [1] https://aws.amazon.com/s3/faqs/ https://aws.amazon.com/s3/faqs/
- arosenbaum 8y ago
  AWS has consistently stated Aurora storage spans 3 AZs. I believe some 2 AZ regions have a third AZ for internal AWS use only (not a 3rd DC in small regions but otherwise a separate AZ). I would imagine they might run the 5th + 6th copies in storage cells there...seems more likely than the other options...
- discodave 8y ago
  AWS has long had AZs that were not exposed via EC2, as somebody else pointed out, the S3 FAQ gives the game away that some regions have a 'hidden' AZ. They are slowly adding a 3rd AZ to regions that didn't have one already. E.g. https://aws.amazon.com/blogs/aws/now-open-third-aws-availability-zone-in-london/ https://aws.amazon.com/blogs/aws/now-open-third-aws-availabi... https://aws.amazon.com/about-aws/whats-new/2018/03/aws-govcloud-us-region-adds-third-availability-zone/ https://aws.amazon.com/about-aws/whats-new/2018/03/aws-govcl... http://www.datacenterdynamics.com/content-tracks/colo-cloud/aws-opens-third-availability-zone-in-singapore/99634.fullarticle http://www.datacenterdynamics.com/content-tracks/colo-cloud/...
ethanpil 8y ago
Does Amazon release the source for these features? Would love to see these ported to other flavors of mySQL.
- tejasmanohar 8y ago
  Nope. And, FWIW, I'd bet Aurora has deviated far from core MySQL at this point, too.
sleepychu 8y ago
> We’ve all been there! You need to make a quick, seemingly simple fix to an important production database. Have we though? This could be one of those safety nets that makes me worse not better.
truth_seeker 8y ago
If you use Datomic with DynamoDB, this feature is available at Query level.
truth_seeker 8y ago
CockroachDB also support this. https://news.ycombinator.com/item?id=11958660 https://news.ycombinator.com/item?id=11958660
- awgupta 8y ago
  That is a different feature (although a cool one). They provide the ability to run a query as of a point in time. We are moving the database backward in time (which matters for running applications)

416 points by jeffbarr 8y ago

Very interesting. The describe it as a rewind. Does anybody know if it's really a rewind, where each log record is reversible? Or do they do the easier thing of saving snapshots and then replaying the log from snapshot to desired point?

walru 8y ago
Every thing is recorded. edit: And I'm being down voted because? Because HN does not enjoy the truth?
- freehunter 8y ago
  The downvotes are probably because the answer wasn't helpful and doesn't mesh with Amazon's statements.
  - walru 8y ago
    How did that work for everyone deleting their FB account?
    - freehunter 8y ago
      How is this conversation helpful to this thread? The question is not "how much does Amazon keep" but "how long/much/granular can I restore".
      - walru 8y ago
        That sounded like the original question to me. It's an estranged statement to believe otherwise. So, yes, they save snapshots and take you back to the time of your choosing based off that.
        icebraining 8y ago
        An estranged statement? What does that mean? How do you know they use the snapshots method, rather than reversing the changes?
        walru 8y ago
        Only just twenty years of software development (and what makes sense). Seeing as the rest of this thread is full of conjecture (and children), I thought I'd influence some experience.
        deleted 8y ago
        [deleted]
        icebraining 8y ago
        Well, as Bucephalus355 pointed out in another comment, Oracle has a similar feature and it's not implemented using snapshots.
        matthewmacleod 8y ago
        So, yes, they save snapshots and take you back to the time of your choosing based off that. Okay, but you're most likely wrong with that. If you read about how Aurora works (https://www.allthingsdistributed.com/files/p1041-verbitski.pdf https://www.allthingsdistributed.com/files/p1041-verbitski.p...) it seems likely that they will be using log entries to rollback database state, rather than retaining "snapshots".
mmahemoff 8y ago
Probably reversible transactions where possible, but with original-state metadata attached to the transaction if it's required to reverse it (https://en.wikipedia.org/wiki/Memento_pattern https://en.wikipedia.org/wiki/Memento_pattern).
- awgupta 8y ago
  no, we don't perform destructive writes so we are able to simply mark a region of the log as to be skipped. this approach also allows us to move back and forth within the time domain even after a backtrack.
misframer 8y ago
Aurora uses a log-structured storage system, so they just point to a previous version that they haven't garbage collected.
- awgupta 8y ago
  that's correct.

edge17 8y ago

I’m confused, how is this different than an Undo log?

misframer 8y ago
This takes advantage of log-structured storage so there isn't any "undoing".
- edge17 8y ago
  In a database Undo, in spite of the name, is the act of applying a transaction log to return to a particular state. I used to work on databases earlier in my career, so selling this as a new invention seems somewhat bizarre to me.
  - delta-v 8y ago
    That's not what Aurora is doing. What you have described is more like "git revert". It's O(num of transcation in the undo log). Aurora is a log structured db, they can just reset their DB to a particular LSN. i.e. doing "git reset --hard", which's O(1).
    - awgupta 8y ago
      that's correct.

estsauver 8y ago

I'm slightly confused, is this the same as the existing point-in-time restore that's available for other RDS instances? Edit: Main difference seems to be new cluster vs. in place.

coenhyde 8y ago
Similar but from what I understand it allows granularity down to seconds instead of minutes and new cluster provisioning in minutes rather than potentially hours.
- estsauver 8y ago
  Speed of new cluster provisioning is neat, amazon-rds console currently shows the option to do a point of time restore down to the second. Maybe that's actually not available already and they're truncating to the latest backup? Edit: I guess it's also nice that it's in place. Some people probably don't have the flexibility to easily point applications at a new cluster in a repeatable way, so that may help folks prevent down time.
scrollaway 8y ago
No; look at the screenshots. You can pick any point in time given the window you request.
- estsauver 8y ago
  I see that option in well already for AWS-RDS postgres point in time restore.
- manigandham 8y ago
  That usually what point in time restores allow, by using snapshots as a base and WAL since then.
easel 8y ago
I believe this is special because it pauses, backtracks and resumes your existing cluster. No new cluster provisioning time or configuration changes

ulkesh 8y ago

"Aurora will try to retain enough log information to support that window of time." It's good to know that Aurora will try. It's not like it needs to be reliable or anything.

jeffbarr 8y ago
As I noted in the blog post, the storage resides within the cluster. It is storing small values (log sequence numbers) so the space is used with great efficiency, but there is a finite limit. While I don't know the specifics, I would expect that the mapping (time to size) was chosen based on real-world historical data. Cause that's how we roll!
ben509 8y ago
Database activity is volatile, so they have to allocate disk space, and that takes time. So if you have light access, and then start uploading many large BLOBs, it may take time for them to allocate space, and during that time the backups might not go all the way back to X hours. And, really, all of this is just servers in a datacenter, so you can have however many 9's of reliability, and still be That Guy that wins the fail lottery.
chimeracoder 8y ago
> It's good to know that Aurora will try. It's not like it needs to be reliable or anything. "Reliable" isn't a binary concept. There's no service that is 100% reliable. Having a solution that works 99.99% of the time (which is "only" four nines) is probably good enough that people are willing to use it as a last-resort. (From what it seems, it's included for free, so it's not like this costs you anything anyway - at worst, you end up where you would be if they hadn't released this).
- ddorian43 8y ago
  It's not free, look at "estimated 10$/month".
- barkingcat 8y ago
  Not free - there's even a pricing directly on the screenshot!

NyrIsNotDead 8y ago

Why to use AWS when you can just get a cheap VPS from DO and do the same thing?

freehunter 8y ago
Because not everyone wants to manage their own infrastructure.
mabbo 8y ago
Why get a VPS when you could just keep your servers in your office server room and run everything yourself? Same answer: you're paying for someone else to do the boring parts so that you can work on the interesting bits.
matthewmacleod 8y ago
You fundamentally can't do this. Sure – you can set up a couple of VPSs running MySQL, then set up a durable, replicated configuration. Then you need some system to spin up new instances when an instance fails. Then you need some kind of UI to allow that to happen. And you need to implement some kind of point-in-time recovery system, as well as backups, user authentication, monitoring… and so on. The point is, AWS allows you to buy those services instead of spending time building them yourself. There's nothing wrong with building your own database cluster if it makes sense for your use case – but there's nothing wrong with buying one in if it makes sense, either!

craigkerstiens 8y ago

At this time looks like it only applies to MySQL, will be curious to hear if/when it becomes available for PostgreSQL.

duggan 8y ago

Aurora keeps coming along in leaps and bounds, congratulations to the team, this is a fantastic achievement! I only wish that every new feature didn't inevitably come with the caveat that it's only for the MySQL flavour of Aurora. I understand both the engineering and product development reasons for doing so (different stack and MySQL is undoubtedly a much larger customer base), but it always makes these announcements a little underwhelming as an Aurora Postgres user.

ben509 8y ago
I have no problem with the fact that the MySQL users get to be my beta-testers.
- erik_seaberg 8y ago
  If my beta testers shrug off relational integrity, that makes me the real beta tester.
wgjordan 8y ago
> I only wish that every new feature didn't inevitably come with the caveat that it's only for the MySQL flavour of Aurora. Not only MySQL, but only MySQL version 5.6, which was released in 2013 and superceded by 5.7 in 2015. Aurora finally released support for 5.7 in February this year after years of development, but all new features so far have been stuck on the 5.6 branch which hasn't been very reassuring.
paulddraper 8y ago
One annoyance for we is that you can get MySQL for <$30/month, but posgresql is $200/month

qurashee 8y ago

Amazon rediscovering PITR, nice :P

ben509 8y ago
No, they've had that for ages: https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_PIT.html https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_...
- manigandham 8y ago
  This post is about Aurora, their own custom db. They could've easily called it PITR to match the rest of the industry.
  - awgupta 8y ago
    This is a different feature than PiTR. We have both. They serve different needs.

thelastidiot 8y ago

Amazon is the new IBM. Knock yourself out and jump into the AWS ecosystem. In a few years down the line, you'll understand that you've lost the leverage you had to potentially take your public cloud business somewhere else when you have so many dependencies on Amazon tech. Basic principles from my view: don't adopt anything but standard EC2/S3 services and create diversity not only in your teams but in your infrastructure policies.

potle 8y ago
what are some alternative that one can use for rds postgresql and sqs?
- deleted 8y ago
  [deleted]
- josephruscio 8y ago
  For Aurora/RDS Postgres there's https://www.citusdata.com/ https://www.citusdata.com/ or https://www.heroku.com/postgres https://www.heroku.com/postgres
- 013a 8y ago
  There probably isn't a lot of fear with using RDS. They just do management for you, it isn't any fundamentally different technology that would cause heartache during a migration. Just stay away from Aurora if you care about this. SQS: There's AWS MQ, which is based on ActiveMQ and supports AMQP. If you're going with a more CNCF-focused stack and want to use NATS, I'm not aware of any hosted options.
  - RhodesianHunter 8y ago
    You don't even need to stay away from Aurora ince it's MySql/Postgres SQL API compatible. You would just want to stay away from Aurora specific features like the one in OP.
    - 8y ago

setheron 8y ago

How is this different than point in time restore already available?

codeonfire 8y ago
This is amazon's model. A cobbled together team of 1-3 low level engineers takes a feature of some open source software package or the entire package itself, wraps it in an ill-conceived web layer or existing service and makes a big announcement about it. Almost no one uses it, but there will be lots of conference talks about how great it is.
manigandham 8y ago
It's not. Just a different name (which unfortunately adds more confusion).
matthewmacleod 8y ago
PITR on say RDS is going to be a fair bit slower than this, I'd imagine.
- delta-v 8y ago
  PITR is a lot slower especially if there are large transactions in the binlog.

Bucephalus355 8y ago

Ok Oracle has had this feature for at least a decade, it’s called a “flashback query”. Obviously Aurora costs 10% of Oracle, but still, I thought this was going to be a huge feature-add considering the HN comment count. That being said, I love AWS, am Pro-Certified, and work with it everyday. I know Oracle is a giant mean bully company, but at least their arrogance was never of the “world-destabilizing” kind like Facebook. EDIT: changed rollback query to flashback query (flashback query can be used both to view or to actually change the DB)

icebraining 8y ago
Can you link to any docs about it? All I can find is the ROLLBACK statement in a transaction.
- Bucephalus355 8y ago
  Sorry rollback was wrong, the correct term was “flashback”. https://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmflash.htm#BRADV89737 https://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmfl...
  - icebraining 8y ago
    Thanks!
iknowstuff 8y ago
The free MariaDB is about to introduce system versioning: https://mariadb.com/kb/en/library/system-versioned-tables/ https://mariadb.com/kb/en/library/system-versioned-tables/

aionic 8y ago

It's a relatively classic invention, take something that exists and repackage. A snapshot and a log replay accomplishes something pretty similar. AWS slapped a ui and some orchestration around it. The cloud lock stuff makes sense (although if having an easy "undo button" on your db layer is mission critical to your business you might have other interesteting challenges.

awgupta 8y ago
That's not correct. We made a change to mark portions of our log-structured storage as though they should not have occurred. It is a totally different approach than point-in-time restore.

polskibus 8y ago

How did Aurora begin its life? Was it written from scratch or forked from existing open source database?

gtsteve 8y ago
It's forked from MySQL 5.6 and 5.7. It's not open-source itself however so presumably they signed an agreement with Oracle to do this.
- nimrody 8y ago
  MySQL has a GPL license (not AGPL) so I think they can modify and not distribute the source as long as they provide a service and not binaries.

manish_gill 8y ago

The seamlessness of this feature is quite amazing. Backups are usually a huge pain to deal with (I've recently been dealing with Postgres/Barman quite a bit). And disaster scenarios aside (for which AWS already does replications across regions), I think a frequent purpose of backups is really to do this "Undo", go back in time and pretend something didn't happen. All this makes me really really wanna use Aurora. :)

takeda 8y ago
What issues did you have with barman? My experience with it was quite pleasant.
- manish_gill 8y ago
  Various issues with recovery during testing. Missing WALs in the data files that `list-files` command says should be present. Backup failure because of a different number of parallel workers and so on. Combined with the fact that I had to write my own code to push it to S3 (no out of the box support). I suppose it works fine on its own for small to medium databases, and it's a fantastic product. I just wish it was a bit better. :)
  - takeda 8y ago
    About WALs, did you use replication slots? That feature makes WAL logs only removed after all standbys process them. As for S3, WAL-E is probably what you wanted to use. Barman was more intended for on premises installation.

rustyworm 8y ago

Interesting - but what if your database has constant activity? "Oops, my SQL bad" becomes "Oops, my rewind lost 410 transactions"?

gtowey 8y ago
This is exactly my concern. There is no production system I have ever worked on which this feature would ever be used. What if you had an e-commerce site? Other customers were placing orders, right? Credit cards being charged? You didn't catch your mistake instantly. So you have a window of maybe, 5 minutes or a hour in which other things happened. You can't simply forget those other transactions and throw away the data.
- Lightbody 8y ago
  Consider non-production use cases... run a test suite and then reset the data back to a pristine state after each run.
- joemag 8y ago
  There have been many well publicized events where a production database was lost, or in some way corrupted. I’ve lived through one such event myself. When that happens, you usually go to a backup, and typically run into two problems: 1) Point in time back ups can be hours old. 2) More importantly, “backups” are useless, it’s the “restores” that are valuable. And very few organizations have a well practiced muscle memory for restoring from a backup. A turn key restore solution, with a per second granularity can both significantly decrease the loss window, and recovery time. Hope nobody gets to use it, but when you have, it can be a difference between a big and a small outage.
joemag 8y ago
When faced with the alternative of loosing the entire database [1], loosing 410 transactions is usually preferable. Per second granularity gives you ability to restore as close to the fatal mistake (or poison pill transaction) as possible. After that, recovering transactions lost by the rewind can be really tricky (but not impossible). If the entire database got dropped, for example, it’s possible for the unwound writes to contain conflicting IDs. [1] https://about.gitlab.com/2017/02/10/postmortem-of-database-outage-of-january-31/ https://about.gitlab.com/2017/02/10/postmortem-of-database-o...

brettgo1982 8y ago

How is this better than their already existing PITR? Why would someone want to rollback their own production database instead of PITR to a new database and switching over to it? Surely you would end up losing data because you wouldn't be able to reconcile the new data written to it.

cody8295 8y ago

I don't know anything about Aurora and maybe I'm missing something. But why not just wrap everything in a TRANSACTION and then do a ROLLBACK if there's an issue?

dx034 8y ago
That's for the cases where you only realize the mistake after the commit. Often because other queries suddenly fail.
- awgupta 8y ago
  Also, generally DDL statements like DROP TABLE typically autocommit.
wiredone 8y ago
Because nearly everyone I know has some memory where at a less experienced point in their engineering career they wrote: delete from ImportantTable where id = 1234 ... and forgot to select the second line in some query editor before running.
- ben174 8y ago
  There is usually an option to disable delete statements which do not contain a where clause. And if you really want to run that statement you'd need to write: delete from ImportantTable where 1 = 1; But yea, I get your point.
  - mark-wagner 8y ago
    For mysql it is "--i-am-a-dummy". I wish SQL supported putting the where clause first: "where X = Y update..."
ben509 8y ago
This would be for deploying a database migration that hoses something. For instance, we had a migration that touched a bunch of tables and, due to a triggered procedure, it blew away all the modification dates. TBH, I'm not sure how useful it is compared to the normal PITR. My usual guidance on recovering after a database fuck up is "put the app into maintenance mode and the database in read-only, do your investigation, run PITR, port user activity to the backup, do spot checks to determine everything got through, roll the app back to before the change, take the old database down, point the app at the new database, bring the system back online." I'm sure a manager is thinking, "oh, we can be back online in 5 minutes, amazing!" But just because something broke doesn't mean users stopped hitting your database! You can't just hit Undo and throw all that out!

gtsteve 8y ago

This is nice but it appears that the entire database instance gets rolled back to that point. It'd be a lot nicer if it could be done at a per-db or per-table granularity. Realistically I'd never use this feature because of the risk of data loss. I'd restore a new instance from backups and copy the lost data back over manually.

ben174 8y ago
> per-table granularity Sounds like a nightmare as far as relationships are concerned.
ryanianian 8y ago
> It'd be a lot nicer if it could be done at a per-db or per-table granularity. As another commenter pointed out, per-table would be scary for referential-integrity. And per-db kinda makes sense, but if you've got totally different use-cases hosted by the same MySQL you may be using the service incorrectly. > I'd restore a new instance from backups and copy the lost data back over manually. That's always been possible, but even with the best instances and most iops it could take hours to do that. So it's really designed for a different scenario.
- gtsteve 8y ago
  Regarding referential integrity I don't disagree. It would be a process to be performed by an expert in who understands the data structures and what the effect of rolling them back would be. I might be wrong but I believe Aurora backups can be restored quite a bit quicker than that, right? We are evaluating Aurora at work, hence my interest.

deleted 8y ago

[deleted]

qiuyesuifeng 8y ago

TiDB has already supported this (similar) feature about 2 years ago and it has been adopted by the gaming users: https://www.pingcap.com/blog/2016-11-15-Travelling-Back-in-Time-and-Reclaiming-the-Lost-Treasures/ https://www.pingcap.com/blog/2016-11-15-Travelling-Back-in-T...

ben509 8y ago

Reading the paper[1] linked from Jeff's post: > In Aurora, we have chosen a design point of tolerating (a) losing an entire AZ and one additional node (AZ+1) without losing data, and (b) losing an entire AZ without impacting the ability to write data. We achieve this by replicating each data item 6 ways across 3 AZs with 2 copies of each item in each AZ. We use a quorum model with 6 votes (V = 6), a write quorum of 4/6 (V w = 4), and a read quorum of 3/6 (V r = 3). With such a model, we can (a) lose a single AZ and one additional node (a failure of 3 nodes) without losing read availability, and (b) lose any two nodes, including a single AZ failure and maintain write availability. Ensuring read quorum enables us to rebuild write quorum by adding additional replica copies. There are many 2 AZ regions in AWS, of course. I don't think you can stripe 3 copies per AZ, an AZ failure drops you to potentially 2/6, and if you allow for 2/6 and 3/6 writing you could have a split brain. Any thoughts how they manage that? [1] https://www.allthingsdistributed.com/files/p1041-verbitski.pdf https://www.allthingsdistributed.com/files/p1041-verbitski.p...

exawsthrowaway 8y ago
I left AWS before Aurora was introduced, but I suspect it's similar to how S3 fulfills a similar promise. From the S3 FAQ[1]: > Amazon S3 Standard, S3 Standard-Infrequent Access, and Amazon Glacier storage classes replicate data across a minimum of three AZs to protect against the loss of one entire AZ. This remains true in Regions where fewer than three AZs are publicly available. Italic emphasis is mine. [1] https://aws.amazon.com/s3/faqs/ https://aws.amazon.com/s3/faqs/
arosenbaum 8y ago
AWS has consistently stated Aurora storage spans 3 AZs. I believe some 2 AZ regions have a third AZ for internal AWS use only (not a 3rd DC in small regions but otherwise a separate AZ). I would imagine they might run the 5th + 6th copies in storage cells there...seems more likely than the other options...
discodave 8y ago
AWS has long had AZs that were not exposed via EC2, as somebody else pointed out, the S3 FAQ gives the game away that some regions have a 'hidden' AZ. They are slowly adding a 3rd AZ to regions that didn't have one already. E.g. https://aws.amazon.com/blogs/aws/now-open-third-aws-availability-zone-in-london/ https://aws.amazon.com/blogs/aws/now-open-third-aws-availabi... https://aws.amazon.com/about-aws/whats-new/2018/03/aws-govcloud-us-region-adds-third-availability-zone/ https://aws.amazon.com/about-aws/whats-new/2018/03/aws-govcl... http://www.datacenterdynamics.com/content-tracks/colo-cloud/aws-opens-third-availability-zone-in-singapore/99634.fullarticle http://www.datacenterdynamics.com/content-tracks/colo-cloud/...

ethanpil 8y ago

Does Amazon release the source for these features? Would love to see these ported to other flavors of mySQL.

tejasmanohar 8y ago
Nope. And, FWIW, I'd bet Aurora has deviated far from core MySQL at this point, too.

sleepychu 8y ago

> We’ve all been there! You need to make a quick, seemingly simple fix to an important production database. Have we though? This could be one of those safety nets that makes me worse not better.

truth_seeker 8y ago

If you use Datomic with DynamoDB, this feature is available at Query level.

truth_seeker 8y ago

CockroachDB also support this. https://news.ycombinator.com/item?id=11958660 https://news.ycombinator.com/item?id=11958660

awgupta 8y ago
That is a different feature (although a cool one). They provide the ability to run a query as of a point in time. We are moving the database backward in time (which matters for running applications)