
Show HN: Uphold – Tool for programmatically verifying database backups - lloydpick
https://github.com/forward3d/uphold
======
lloydpick
I created this tool a while ago, but since it's apparently world backup day I
thought it a good time to submit it here. It was created out of the fact that
we run automated backups of our various databases, yet we very rarely
attempted a restore. This allows me to know for sure that the backup was
created and that it's available, accessible & restorable. You can then if you
want to, programmatically test the contents of the database using RSpec, just
so that you make sure you aren't backing up the wrong one inside. So now we
have the backup rubygem
([https://github.com/backup/backup](https://github.com/backup/backup))
triggering Uphold to fully test the backup after it's finished being made
through the API.

~~~
cdubzzz
Very cool. Ever thought about including default test suites with the tool?
E.g. I have multiple nightly backups of Drupal databases and could think of
some obvious default tests against the core tables that would be useful for
all of them.

~~~
lloydpick
Sure, if you PR some example tests in I can make that happen.

~~~
cdubzzz
I'll look in to it. I have never used Docker or Ruby but RSpec looks pretty
straight forward from a quick scan.

~~~
lloydpick
Even if you just write the SQL I can translate it into RSpec if it'll save you
time.

~~~
code_research
do you have a tool that does this translation or is the tool your brain? :)

------
gramakri
Looks very interesting and I can possibly use this at my work.

I will take a closer look but what was not immediately apparent was: what kind
of backups are we talking about here? mysqldump or volume snapshots ? What
about incremental backups ?

Redis snapshots support would be awesome as well.

~~~
lloydpick
It supports MongoDB, MySQL and PostgreSQL at the moment. It's really easy to
add extra engines, you can even write your own custom ones and include them.
But it's mainly dealing with full dumps, rather than volume snapshots or
incrementals. That said, there's nothing to stop an "engine" being written
that could create an EC2 machine using Packer and attaching an EBS volume and
then testing that.

I have plans to support things like RDS as well in the longer term.

I've got some redis instances here that I can test restoring from, so could
look at adding that.

------
meesterdude
Nobody ever checks their backups! This is such a great way to take care of it
automatically.

I'm not at all against sanity checks, especially of something you trust will
work when you need it to like backups. But what would cause a DB backup to the
filesystem to fail? When they are/were stored on tape or other media, there is
certainly the concern of media corruption. But when we're talking about files
resting on disk, what would cause a backup to be corrupt or unusable?
Certainly there is some expectation that mysql/psql can reliably dump a DB. Or
is it purely for sleeping soundly at night?

~~~
lloydpick
We transfer our backups to S3, so there's always a chance that it could be
corrupted during transfer, or that the compression could have failed before
transfer.

It can also help catch operational changes, eg. someone changed the root
password and updated the apps that depend upon it, but didn't adjust the
backup scripts.

------
TheAndruu
> then running programmatic tests against it that you define to make sure they
> really have what you need

Sounds like the crux will still depend on how well one writes these tests.

Agreeing that no backup is verified until it is restored... one way this can
be achieved is use something like Ansible to script the backup of the
database, script the restore, and run them and verify everything is backed up
/ restored properly.

Then since everything is automated, a person can reasonably trust their
backups are verified, with the full confidence if they need to do a
backup/restore, they just have to run a script.

~~~
lloydpick
You don't have to run the tests if you don't want, the tool has a different
state for when you don't have them "ok_no_test". For some backups we run
through Uphold we have written some very basic ones, like looking for the
existence of some specific users, or that a table has X number of rows, which
are very good for quick sanity tests.

------
rubiquity
Writing something like this has been on my todo list for what feels like
forever. I'll definitely play around with this. Thank you!

