This is a small update in our series about our backup setup and schedule. You can read the previous three here:
Most recently we did some database maintenance on Heroku. This is pretty straightforward as Heroku handles a lot of the heavy work for you. Periodically, we need to upgrade the database version or migrate to a larger database as needed.
As part of their database package, you can set up both manual and automatic backups. These are saved to an S3 bucket associated with your database instance. Every time we need to make a change, we make a manual backing-up. There is also an automatic, nightly back-up when the server is quiet.
During one maintenance phase, we were working to upgrade the database, but accidentally pressed the delete button. Luckily Heroku has a nice double confirmation so you can’t delete something that you’re not supposed to by accident. This got us worried, because had we actually deleted that database, all of the corresponding backups would have also disappeared. This is a very bad single point of failure. Having both the database and the backups so easily destroyable was something we needed to guard against.
This also doesn’t need to be malicious. Accidentally deleting a database is possible, so is forgetting to pay the credit card bill or update it when it expires. Heroku might quietly terminate all of our services and along with that we would lose all of our backups!
We set out to at least get a copy of those databases somewhere not inside Heroku’s control. Luckily the Heroku command line gives us a few tools to do this. We can get the most recent Postgres S3 backup URL and we can get the Postgres URL directly.
Our goal was to build a script which could save the database offsite in case something catastrophic happens. Rather than use the existing S3 backup URL we decided to fetch the Postgres database dump directly. (To make use of the Heroku CLI tools, you need to be logged in and that wasn’t going to be possible, so we saved the direct Postgres URL instead. This does periodically change, so we need to be aware of the fragility.)
For now, we setup a few different CSV files with the service names and the Postgres URLs. A bash script was created to run pg_dump looping through the corresponding csv file. Each row creates a database dump with the service name. We can direct that output to our DAS system. Once it is put onto a drive, that flows into our existing system of an additional nightly local copy and remote copy.
Using the same script, we can pass-in different target folders so we can separate the dumps by company, project or service. This also allows us to create a series of cron tasks that run at different intervals. For instance, some surveys only run for 1 month, so there is no need to be backing these up nightly the other 11 months of the year! The same is true of our reports server, we publish reports usually at the start of the month for the previous month’s results. Having that pull a remote backup once a month in the middle of the month is probably the most sensible. Especially since the DB size is pushing 3 GB.
Also, since these commands are all listed in the crontab, we can manually run the same command if we can’t wait for the next backup cycle to occur.
For some customers and internal projects we use our own 3-2-1 backups system. For others who are concerned about GDPR and sensitive data, we can save the database dumps to their network drives and let them handle the data.
It is a flexible system, which does require us to save the login strings, which periodically change. We’ll see how much of an issue this becomes as we implement the system.
We are constantly finding loop-holes, like these Heroku backups, but close them each time. We continue to iterate and improve on our backup setup and with each step, improve our piece of mind.