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

151–160 of 456 posts

Re: We deleted the production database by accident

#151

Post by oppositelock »

You have to put a lot of thought into protecting and backing up production databases, and backups are not good enough without regular testing of recovery.

I have been running Postgres in production supporting $millions in business for years. Here's how it's set up. These days I use RDS in AWS, but the same is doable anywhere.

First, the primary server is configured to send write ahead logs (WAL) to a secondary server. What this means is that before a transaction completes on the master, she slave has written it too. This is a hot spare in case something happens to the master.

Secondly, WAL logs will happily contain a DROP DATABASE in them, they're just the transaction log, and don't prevent bad mistakes, so I also send the WAL logs to backup storage via WAL-E. In the tale of horror in the linked article, I'd be able to recover the DB by restoring from the last backup, and applying the WAL delta. If the WAL contains a "drop database", then some manual intervention is required to only play them back up to the statement before that drop.

Third is a question of access control for developers. Absolutely nobody should have write credentials for a prod DB except for the prod services. If a developer needs to work with data to develop something, I have all these wonderful DB backups lying around, so I bring up a new DB from the backups, giving the developer a sandbox to play in, and also testing my recovery procedure, double-win. Now, there are emergencies where this rule is broken, but it's an anomalous situation handled on a case by case basis, and I only let people who know what they're doing touch that live prod DB.

Re: We deleted the production database by accident

#152

Post by arthurcolle »

sushshshsh wrote: post #128
emodendroket wrote: post #122

Earlier quoted context omitted.

There is sense in not building more services than you need. But many folks end up finding it hard to break away from their monolith and then it becomes an albatross. Not sure how to account for that.

parallel rewrites before the current prod system ever hits performance problems :)

Sounds expensive

Re: We deleted the production database by accident

#153

Post by EdwardDiego »

sfkdjf9j3j wrote: post #45
smadge wrote: post #2

SSH tunnel from localhost to prod on database port?

That's my guess too. Naughty naughty. That means his production creds are the same as his development creds!

Yeah that's my guess, and likewise biggest concern - if the credentials are the same in dev and prod, it increases risk surface.

Re: We deleted the production database by accident

#154

Post by tzs »

Here is how we had our database deletion error, about 15 years ago. Our DBs had been on leased servers at a hosting company in New York City. They were getting out of the datacenter business so we had to move. We were moving to colocated servers at a Seattle datacenter.

This was the procedure:

1. Restore DB backups in Seattle.

2. Set up replication from NYC to Seattle.

3. Start changing things to read from Seattle, with writes still going to NYC.

4. After everything is reading from Seattle and has been doing so with no problems for a while, change the replication to be two-way between NYC and Seattle.

5. Start switching writes to Seattle.

6. After both reads and writes are all going to Seattle and it has been that way for a while with no problems, turn off replication.

7. Notify me that I can wipe the NYC servers, for which we had root access but not console access. I wasn't in the IT department and wasn't involved in the first 6 steps, but had the most Unix experience and was thought to be the best at doing a thorough server wipe.

My server wipe procedure was something like this.

8. "DELETE FROM table_name" for each DB table.

9. "DROP TABLE table_name" for each DB table.

10. Stop the DB server.

11. Overwrite all the DB data files with random data.

12. Delete all the DB data files.

13. Delete everything else of ours.

14. Uninstall all packages we installed after the base system install.

15. Delete every data file I could find that #14 left behind.

16. Write files of random data to fill up all the free space.

The problem was with step #6. They declared it done and turned it over to me for step #7 without actually having done the "turn off replication" part of step #6. Step #8 was replicated to Seattle.

It took them a while to figure out that data was being deleted and why that was happened.

We were split across three office buildings, and the one I was in had not yet had phones installed in all the offices, and mine was one of the ones with a phone. None of the people whose offices did have phones were in, so they lost a few more minutes before realizing that someone would have to run a couple blocks to my office to tell me to stop the wipe.

It took about 12 hours or so afterwards for them to restore Seattle from the latest backup, and then replay the logs from between the backup time and the start of the deletes.

After that they were overly cautious, taking a long time to let me resume the NYC wipe. They went right up to the point where I told them if we didn't start now we might not finish, and reminded them that those machines had sensitive customer personal information on them and were probably going to end up being auctioned off on eBay by the hosting company. They came to their senses and told me to go ahead.

Re: We deleted the production database by accident

#156

Post by danmur »

candiddevmike wrote: post #143
john_moscow wrote: post #139

Earlier quoted context omitted.

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 d…

Git GC won't do anything here unless you're deleting commits or resetting the repo constantly. Every commit will keep piling up, and you will never prune anything like you would a traditional backup tool. The day you do decide to start pruning things, expect your computer to burst into flames as it struggles to rewrite the commit history! Using a real database backup solution isn't a premature optimization, it's basi…

Agree. Nothing wrong with doing what works for most things, but with backups it's easier to use an appropriate process from the start. It's easy enough and saves you grief later.

Re: We deleted the production database by accident

#157

Post by duskwuff »

ethbr0 wrote: post #115
dnautics wrote: post #107

Earlier quoted context omitted.

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

> Please don't use csv Could you elaborate? I'm interested in the specific reasons.

High-quality software which interacts with real transactional SQL databases is readily available, free, and easy to use. Building an inferior solution yourself, like a CSV-based database, doesn't make your product any better. (If anything, it will probably make it worse.)

Re: We deleted the production database by accident

#158

Post by tedk-42 »

kerng wrote: post #24
sdepablos wrote: post #3

VPN to production and same hostname for dev and prod?

...and same credentials apparently also - there are lots of things that could have prevented something like this.

Yeah this stuck out to me as well. To be honest if they ARE using the same creds in prod as they are in dev, this is the first thing they should fix because it would have easily prevented the whole mess.

I'll give them the benefit of the doubt and suggest that it wasn't the case and that it's unlikely that a dev or OPs person was tunnelling through a bastion of some kind to run the script.

If they pulled their credentials from a store and populated the config object that way, it's very possible someone actually loaded the production credentials by mistake into the development secrets file. The CI/pipeline system has permissions/network access to deploy to any environment, hence how it ran the script to drop the table.

I'm purely speculating another alternative to the much worse case of the tunnelling scenario outline.

Re: We deleted the production database by accident

#159

Post by [deleted] »

TedDoesntTalk wrote: post #93
cle wrote: post #90

Earlier quoted context omitted.

No. Your systems and processes should protect you from doing something stupid, because we’ve all done stupid things. Most stupid things are done whilst sober. In this case there were like 10 relatively easy things that could have prevented this. Your ability to mentally compile and evaluate your code before you hit enter is not a reliable way to protect your production systems. Coding after drinking is probably not a…

> Coding after drinking is probably not a good idea I’ve done some of my most productive work this way. Not on production systems fortunately, and not in a long time.

[deleted]

Re: We deleted the production database by accident

#160

Post by tedk-42 »

anonunivgrad wrote: post #80

I wouldn’t want to be on the wrong side of a lawsuit, defending drunk employees working on production data. What outrageous recklessness. And how imprudent to admit this to the public. Some things are best kept to yourself. No one needs to know that.

If you built good systems that have reliable backups or rollback mechanisms, dev should be able to have a beer on a friday and do a deployment without worrying about the fire they just caused.

I'd rather a culture where people admit to their mistakes than one where they try hide or get whipped for owning up to them.

We're people after all and some of us like a glass of wine and unwind while still performing our duties as engineers. After all, it's not like we're in charge of life support or critical systems which absolutely cannot fail.

Post reply on HN