Hacker News Reader

Comment by dsfyu404ed | Hacker News Reader

As long as you know the 1s and 0s were properly written to a disk that's now sitting in a storage locker somewhere that's infinitely better than nothing. If shit really hits the fan you can figure out how to restore from it but the data actually needs to be there in the first place. Sure, practice restoring is ideal but anything is better than nothing. It's the difference between not being able to do anything for a week and probably having to close your business entirely.

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.

View on HN

You are getting downvoted because there is more to it than just "practice".

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.

My experience may shed some light on this: I use pgdump to make backups of an important PostgreSQL server and I thought I was done. Then the server died. I then used the standard import... but it failed. This was a while ago, but pgdump didn't preserve/escape some of the weird quoting in my strings or I (most likely) didn't understand hot to use pgdump properly. I hand to manually figure this out and fix the strings in the file before it would import. If I had tested the full restore, then I would have not have had to panic. Thankfully pgdump didn't throw up it's hands and not emit the strings - they would have been a decent and reasonably sane choice as well. I got lucky.

You'd have to test every backup though, because initially, your DB might not contain any strings that trigger the problem.

It’s good to test PostgreSQL backups (or basically everything that is constantly doing reads and writes)... some pitfalls in my small experience with a RPi and some cheap SD card:

* 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.

> * 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.

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.

You don't need a SQL dump to backup a PostgreSQL database. It has a mechanism (pg_start_backup) where it will only write WAL (journal) files and will not remove them while you use your filesystem backup (eg. rsync) as usual until you signal completion (pg_stop_backup), after which you also need to back up your WAL archive.

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.

How did you know if the pg_dump finished running? In my experience, there doesn't seem to be any way to find out if it finished, or exited prematurely. The only hack I came up with is to write the output to a log, and then grep the log for the last alphabetical table, and ensure it was in the log. That usually meant it backed up all the tables.

pg_dump provides return codes just like any other executable. Zero means no error, and treat non-zero value as an error.

IIRC mysqldump had a similar issue back in the day before the --quoted-names option became enabled by default.

I can't downvote so I can't say why. My first impression though is that your post doesn't offer much to the conversation.

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.

I guess you are getting downvoted because it would be really really hard to know if the all of the right 1s and 0s you need are there without restoring.

> Sure, practice restoring is ideal but anything is better than nothing.

Practicing restores is mandatory to confirm integrity. Any other form of backup is ceremony.

Reply on news.ycombinator.com