How do you backup your database?
Hey,
There are two types of people:
- Those who do backups.
- Those who will do backups.
Unfortunately, I was in the second group before the day when by accident I remove all my data 🤦♂️ After the day I tried multiple tools for backups without success. I wanted to backup DB at least every 30 minutes. I was looking for a SASS tool that supports 6 GB Postgresql DB, notify me about failures by slack, and send me daily reports to make sure that everything works.
- https://simplebackups.io/ - It is too slow. It takes hours to finish the backup.
- https://snapshooter.io - I had tested it before they added DB backups :) So, I do backups of the whole server (Snapshots on Digital Ocean). It is ok, but it takes about 50 minutes, and it is quite risky to based only on DO snapshots. (sometimes I cannot recover them :| )
- https://dbacked.com/ - it looks like no maintained anymore. Clearing old backups didn't work.
- https://www.backupbird.com/ - it seems not supported anymore.
I end with a few bash scripts, pg dump, and CRON. Do you have any better ideas? I'm thinking about creating a backup tool as a service. Would you like to use it?
Trending on Indie Hackers
Hey @devhulk
Simon here, the Founder and CEO of https://SnapShooter.io
Glade to be featured in your list but thought I would add some points of clarity.
DigitalOcean droplet snapshots can take from 30s per to 90s GB depending on the load of the server, the higher the write load the slower the backup will become. For example today we say a 145GB backup complete in 43m and a 112gb backup take 1h30. Really depends on the work load.
You're right we added Database (MySQL) support this year with PostgreSQL support added last month. I thought I would test it this morning how long at database that compresses to 6GB backup would take on a 1GB droplet. Took about 14m and was running a medium compression level, the backup went from a Droplet to AWS S3, and I got a slack notification :D.
Let me know if you do have more questions.
Simon
https://snapshooter.io
Some backup humor :-)
I like the Digital Ocean droplet backups (I've been managing my DBs myself without a managed DB solution). However, I've never actually tried to recover a database from a Droplet back-up, which is something I should probably try...
Unfortunately, I had some issues with droplet backups. When the droplet was corrupted, backups were also broken so I cannot restore the data.
Ah, I didn't know the droplet backups could be corrupted. Something I'll have to keep in mind.
We use Amazon RDS, which has database backups (and replication) all built in. Actually, now use Amazon Aurora, which allows constant 'sliding' backups, so at any point in time, I can say "I want to go back to what things were like 5 minutes ago", or 15 minutes etc.
Cron job with some python script. I didn't know there was such service till now.
Do you not afraid of some silent failures?
I use a docker container to backup db. Usually, it lets you set rotations with a cron job. It's fast and easy to set up. I just include it in the docker-compose.yml file.
Could you tell me more about it? How to setup it?
I'm using docker compose to host my dockerized apps. Just use the standard image and input the env variables. Input docker-compose up -d and it's good to go. The env variable already includes CRON schedule.
Here is a docker image for mysql: https://hub.docker.com/r/databack/mysql-backup
There are also images for other db like postgresql
Ok, I got it, I know docker a little. But how do you backup your DB with that? I know, that the whole container is saved as image.
Basically, the methodology is same as yours. A cron job will export the db as a sql file. But the image may enable remote backup. Local backup is good for me
DigitalOcean supposedly backs up managed databases automatically: https://www.digitalocean.com/community/questions/what-is-the-recommended-way-to-backup-my-managed-postgresql-database
That said, I haven't yet had to do a real test of restoring from one of their backups. Probably wouldn't be a bad idea to back it up myself manually as well.
Hi, where is the db hosted ? There are some options already included on the cloud providers side, for example your db is a perfect candidate for rds in AWS or gcp if u prefer, they handle all the management for you, automated snapshots every 15 minutes and you can use their SNS service to get notified. If this is really a critical DB I would suggest you investigate replication as this will give you the peace of mind as you will have a standby instance ready in case of issues. Of course this is coming with some cost but brings a lot of peace and less operational work. Happy to assist as we are offering cloud consultancy services :)
I backed up my Postgres database manually when I hosted in on Heroku multiple times a day. Now on GCP, I have daily backups automated.
Is it enough for you to have only daily backups?
It wasn't for heroku, that's why I did multiple backups. I have a HA deployment for Cloud SQL and am totally fine with daily backups there. I have a lot more trust in Google's infrastructure than the sporadic restarts on Heroku that may have cost me already some customers.
Works inside of Digital Ocean. They take care of everything. We use them. https://www.databaselabs.io/
automatic mysqldump thrown to S3
I would look into hot standby and daily snapshots.ive set one up for postgress but that was like 7 years ago.
Similarly to you I just use Bash and pg_dump + VPS snapshots. I think it's the best setup until you move to a managed database (where you will also use the service backup option).
Automated daily backups with Heroku
Wrote our own script in Google Cloud Functions to backup our firestore db in Google Cloud Storage running using a cron.
Actually planning to open source it (if there is any interest)
I have a bit of interest, but I wouldn't be backing things up to Cloud Storage.
I had this automated with a node.js script in the past, but it ended up hitting memory limits and randomly failing during the backup process on our server -- so I currently get a reminder every week on my calendar to run it locally so that I can be sure it actually ran!
You don't want to back to Cloud Storage since that's still Google's infra? (If that's the case, same can be changed to S3).
Wrt, timeout I haven't faced it. Maybe because my DBs are small.
Not actually requesting anything, just saying if you open sourced what you were working on I'd probably modify it & tinker with it a bit before using it.
There are a few NPM packages that do firestore backups -- haven't had any issues except large tables run into errors on my Linux VMs, never hit any errors on macOS -- could be a dependency, could be a quirk in the module, or it could be a memory limit. No idea, haven't dug into it enough.
Makes sense.
I use the builtin backup within AWS RDS. Or if using Digital Ocean using the builtin instance backup feature.
I use Digital Ocean and AWS for different projects. Both offer built-in backups on their managed databases.
You forgot the third. “ those who do backups and test their backups”
Yes, You're right. IMO it's the hardest part to test the backups. But without it, you can't be sure that everything is fine. How do you check your backups?
Easy. recreate the db with the backup periodically.
If you're using MongoDB in your stack and in the cloud - the big 3 major cloud providers are well supported options.
https://docs.atlas.mongodb.com/backup/cloud-backup/overview/
Otherwise, the good old fashioned rsync will work fine with a cron job. My current plans are to take advantage of cloud backups since it's mostly built into the managed database providers already.
If I do decide the manual approach, a simple database snapshot should be fine.
You should add a third option in there: Those who don't do backups themselves but get them anyways :)
I migrated all my projects to managed databases (both on DigitalOcean and GCP) and never looked back. It's such a relief not having to worry about backups, security updates, and connectivity. Would highly recommend trying it out.
This comment was deleted a year ago.
Same with me. I use lxd to run instances, so I have a simple script that backs up stuff in each container, encrypts with gpg, then uploads it to DO spaces via S3 api.
My biggest pain with this is occasionally having to go into DO's spaces UI and cleaning out a bunch of daily backups. My biggest worries are losing the gpg key/not being able to decrypt, and silent failures.
This comment was deleted a year ago.
I so badly want to build an open source project to make managing saas apps a little easier haha.
I'll bet it'd be a waste of time, but it would be fun to build :-P
Hi, just answering here, where is the db hosted, is it running inside the containers or is it externalised (Paas) ? There are some options already included on the cloud providers side, they handle all the management for you, automated snapshots and rotation that will free you up of operation tasks. If this is really a critical DB I would suggest you investigate replication as this will give you the peace of mind as you will have a standby instance ready in case of issues. Of course this is coming with some cost but brings a lot of peace and less operational work. Happy to assist as we are offering cloud consultancy services :)
I started my Internet life as sysadmin and then SRE, so I value this kind of stuff a lot. Definitely agree with replication (esp with replay logs in case of drastic bad changes), but none of my software is at the point of needing it yet :( haha