Also, a backup that you don't fully restore somewhere to check it actually fully works isn't really much of a backup at all.
One of my old employers dutifully did tape backups every day, and one of the bosses took them home.
During an upgrade it was revealed that the drive wasn't working, and hadn't been working for at least a year (I think that's how far back the backups went).
But I'd love to hear how people practice restoring all their backups on a regular basis when it requires having an enormous amount of space. Practically, I just restore a few backups every month, and if it works, I just assume the others work as well (which I know is a false assumption, but what are you going to do?)
These would happen outside heavy operation hours, and if successful, the restored servers would become the live servers in most instances.
These were done using scripted vmware instances, mostly restoring from StorageCraft Shadow Protect backups.
File server was the most resource intensive and the most prone to complaints about downtime, but usually the trade off was fine.
DB server was usually fine due to the out of hours restore, and the ability to master - slave - replicate - handover.
Mail server was usually transparent as you could set up smtp transfer rules for incoming, and outgoing didn't really care.
The trickier ones were things like IBM I Series servers that usually require periodic full system saves, restores and tests. These couldn't be done automatically without excessive licensing fees and much manual baby sitting.
If you get to the point where you have automatic scheduled DR, you can be pretty confident your backups are working.
It the automated DR fails, you roll back, learn whatever lesson the failure taught you, and change the system so it doesn't happen again.
Summary: schedule DR, not backups.
However, we have around 30 different databases, each of which take more than a day to restore. We backup databases every X days, upload them to S3, and delete the previous ones, keeping only the last 3 or so.
So if it takes us 30 days to restore all the databases, those backups might already have been gone in S3 as part of the cleanup operation anyway.
It just gets complicated, and non-trivial when you have multiple databases, each TB's large.
The solution is to have a tape or disk library and have the other machine take over for restore. If you have TBs of data you can afford a library and another PC to run the recovery test.
Nobody said it was. This subthread is about the importance of testing backups in general.
> If you have TBs of data you can afford a library and another PC to run the recovery test.
They already explained how that's not enough. Yes, they can add more, but you're missing some info there.
I've tried various options over the years but I haven't found a simple OTS non-tape offline backup solution. There's a lot of potential here for a solution.
Usually those conditions aren't met properly.
You're definitely right, though; there's gotta be a better way. SSDs look promising, though not necessarily for long-term storage (for that, I suspect it'd be hard to beat optical, aside perhaps from printing out a bunch of QR codes or something).
Learned that lesson early in my career - the fourth and final tape did restore.
NB The backups weren't my responsibility, but restoring the payroll database my shell scripting error had nuked most definitely was.
Edit: Why is this getting downvoted?
I'm not saying this is a "real" backup solution. Call it an archive if you want. It's still better than not having any copy of you data in offline storage off site. Defense in depth people, there's no reason you can't both have a real backup system and shove old disks in a storage tote.
Sure, that's better than nothing. People should start the process of making backups. But when the backups are important (as we're discussing here), you need to verify that a backup can be restored - otherwise you're ignoring a huge risk.
* If you backup the whole file system while the database is running, you may end up with an inconsistent (maybe unrecoverable?) backup. You need to use a file system with snapshots like ZFS or BTRFS, or use PostgreSQL’s own tools.
* Similarly, if the underlying file system experiments corruption, it will also seem to keep working for a while before, and after that you may be left with a bad database and backup. You have to be able to restore multiple time points or ensure your backup is good.
This is true for any sort of mounted database. Always dump the database separately from any FS backups.
Another good idea (but not a replacement for backups) is to replicate the DB to a read-only slave which you can either make the master or recreate your master from.
This gives you everything you need to recover to any point between the start and stop of the backup operation, and it's guaranteed to be consistent assuming the data itself was copied successfully. This is because any inconsistent files modified during the copy will be recovered from the archived WAL files to attain a consistent state.
If you haven't restored from a backup before, you don't know if it's possible to restore from that backup.
> As long as you know the 1s and 0s were properly written ...
How do you know? Did it actually write properly? If so, was the data you chose to write the data you actually need to fully restore?
You might be able to do certain tests or checks to determine this to a high degree of probability, but there's only one test that will tell you with certainty, and that's actually performing a restore successfully.
Practicing restores is mandatory to confirm integrity. Any other form of backup is ceremony.