Hacker News Board Hacker News, browsed like a classic message board
Live data from Hacker News

We deleted the production database by accident

keepthescore.co

Post reply on HN
Print view View original

131–140 of 456 posts

Re: We deleted the production database by accident

#131

Post by hinkley »

bufferoverflow wrote: post #113
john_moscow wrote: post #81

Just my 2 cents. I run a small software business that involves a few moderately-sized databases. The day I moved from a fully managed hosting to a Linux VPS, I have crontabbed a script like this to run several times a day: for db in `mysql [...] | grep [...]` do mysqldump [...] > $db.sql done git commit -a -m "Automatic backup" git push [backup server #1] git push [backup server #2] git push [backup server #3] git gc…

You should really compress them instead of dumping them raw into Git. LZ4 or ZStandard are good.

Git repositories are compressed.

Re: We deleted the production database by accident

#132

Post by WarOnPrivacy »

emodendroket wrote: post #124
bigbubba wrote: post #95

Earlier quoted context omitted.

You know it's totally feasible to make a car that won't turn on for drunk people. Should those systems be installed on all cars, in pursuit of creating systems that don't permit stupid actions? Maybe such a breathalyzer interlock could be installed on your workstation too. After all, your systems and processes should prevent you from stupid things.

Honestly not a bad idea to install the interlocks on all cars.

Yes. I could finally start a failed interlock story blog.

Re: We deleted the production database by accident

#133

Post by theamk »

dnautics wrote: post #107
sushshshsh wrote: post #105

Earlier quoted context omitted.

Yup, 100% agree. It may be that you will eventually need an auto-scalable message queue and api gateway, but for most people a web server and csv will serve the first thousand customers

Please don't use csv. At the very least use SQLite. But hosted sqls are probably the smart thing to do.

Do use CSV (and other similar formats) for read-only data which fits entirely in the memory.

It is great for data safety -- chown/chmod the file, and you can be sure your scripts won't touch this. And if you are accessing live instance, you can be pretty sure that you won't accidentally break it by obtaining a database lock.

Now "csv" in particular is kinda bad because it never got standardized, so if you you have complex data (punctuation, newlines, etc..), it you might not be able to get the same data back using a different software.

So consider some other storage formats -- there are tons. Like TSV (tab-separated-values) if you want it simple; json if you want great tooling support; jsonlines if you want to use json tools with old-school Unix tools as well; protobufs if you like schemas and speed; numpy's npy if you have millions of fixed-width records; and so on...

There is no need to bother with SQL if the app will immediately load every row into memory and work with native objects.

Re: We deleted the production database by accident

#134

Post by bstar77 »

danellis wrote: post #77

> after a couple of glasses of red wine, we deleted the production database by accident > It’s tempting to blame the disaster on the couple of glasses of red wine. However, the function that wiped the database was written whilst sober. It was _written_ then, but you're still admitting to the world that your employees do work on production systems after they've been drinking. Since they were working so late, one might…

I am not a drinker myself (drink 1-3 times a year), but in the past I have coded while slightly buzzed on a few occasions. I could not believe the level of focus I had. I never investigated it further, but I'm pretty sure the effects of alcohol on our coding abilities is not nearly as bad as it affects our motor skills. Imo, fatigue is far worse.

Re: We deleted the production database by accident

#136

Post by emodendroket »

bstar77 wrote: post #134
danellis wrote: post #77

> after a couple of glasses of red wine, we deleted the production database by accident > It’s tempting to blame the disaster on the couple of glasses of red wine. However, the function that wiped the database was written whilst sober. It was _written_ then, but you're still admitting to the world that your employees do work on production systems after they've been drinking. Since they were working so late, one might…

I am not a drinker myself (drink 1-3 times a year), but in the past I have coded while slightly buzzed on a few occasions. I could not believe the level of focus I had. I never investigated it further, but I'm pretty sure the effects of alcohol on our coding abilities is not nearly as bad as it affects our motor skills. Imo, fatigue is far worse.

I've found just the opposite. Any booze at all and I basically can't work for hours.

Re: We deleted the production database by accident

#137

Post by theamk »

netheril96 wrote: post #127
amingilani wrote: post #47

Happens to all of us. Once I required logs from the server. The log file was a few gigs and still in use. so I carefully duplicated it, grepped just the lines I needed into another file and downloaded the smaller file. During this operation, the server ran out of memory—presumably because of all the files I'd created—and before I know it I'd managed to crash 3 services and corrupted the database—which was also on thi…

Why does the DB get corrupted? Does ACID mean anything these days?

Not original poster, but up to 2010, default MySQL table type was MyISAM, which does not support transactions.

Re: We deleted the production database by accident

#138

Post by jugg1es »

I totally sympathize with you and yours, I've made sphincter-clenching mistakes a handful of times during my 20 years of experience.

This is an abject lesson that understanding human psychology is actually a huge part of good architecture. Automating everything you do in production with a script that is QA tested prior to use is the best way to avoid catastrophic production outages.

It does take a bit longer to get from start to finish, and younger devs often try to ignore it, but it is worth putting a layer of oversight between your employees and your source of revenue.

Re: We deleted the production database by accident

#139

Post by john_moscow »

candiddevmike wrote: post #129
john_moscow wrote: post #81

Just my 2 cents. I run a small software business that involves a few moderately-sized databases. The day I moved from a fully managed hosting to a Linux VPS, I have crontabbed a script like this to run several times a day: for db in `mysql [...] | grep [...]` do mysqldump [...] > $db.sql done git commit -a -m "Automatic backup" git push [backup server #1] git push [backup server #2] git push [backup server #3] git gc…

Anyone reading the above: please don't do this. Git is not made for database backups, use a real backup solution like WAL archiving or dump it into restic/borg. Your git repo will balloon at an astronomical rate, and I can't imagine why anyone would diff database backups like this.

It really depends on your database size. This works just fine for ~300MB databases. Git gc takes pretty good care of the fluff and once every couple of years I reset the repository to prune the old snapshots. The big plus is that you can reuse your existing git infrastructure, so the marginal setup costs are minimal.

You can always switch to a more specialized solution if the repository size starts bugging you, but don't fall into the trap of premature optimization.

Re: We deleted the production database by accident

#140

Post by jugg1es »

candiddevmike wrote: post #129
john_moscow wrote: post #81

Just my 2 cents. I run a small software business that involves a few moderately-sized databases. The day I moved from a fully managed hosting to a Linux VPS, I have crontabbed a script like this to run several times a day: for db in `mysql [...] | grep [...]` do mysqldump [...] > $db.sql done git commit -a -m "Automatic backup" git push [backup server #1] git push [backup server #2] git push [backup server #3] git gc…

Anyone reading the above: please don't do this. Git is not made for database backups, use a real backup solution like WAL archiving or dump it into restic/borg. Your git repo will balloon at an astronomical rate, and I can't imagine why anyone would diff database backups like this.

Not every database is huge. It could be a good solution in certain circumstances.
Post reply on HN