We deleted the production database by accident • Hacker News Board

Re: We deleted the production database by accident

#381

Post by x87678r » Sun, Oct 18, 2020, 1:25 PM UTC

oppositelock wrote: post #151

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…

Interesting, I immediately thought they would have a transaction log, I didn't think it would have the delete as well.

Its a real problem that we used to have trained DBAs to own the data where now devs and automatic tools are relied upon, there isn't a culture or toolset built up yet to handle it.

Re: We deleted the production database by accident

#382

Post by [deleted] » Sun, Oct 18, 2020, 1:30 PM UTC

pvorb wrote: post #348

junon wrote: post #230

Earlier quoted context omitted.

That whole ecosystem is a devexp nightmare. I try to stay away from it entirely having worked with it extensively. Docker + Kubernetes are the biggest socially-acceptable hacks in the industry at the moment.

Why do you think they are hacks? Could you please elaborate? In my opinion, they nicely abstract over server hardware and services running on them, so one is able to have simple infrastructure-as-code management of otherwise complicated setups.

[deleted]

Re: We deleted the production database by accident

#383

Post by Schiendelman » Sun, Oct 18, 2020, 1:40 PM UTC

KronisLV wrote: post #323

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.

Replace a breathalyzer with something that's less intruisive (like a camera with AI that would observe the person, AI with thermal imaging or air quality sensors, or another possibly-fictional-yet-believable piece of technology) and suddenly, in my eyes, the technology in this thought experiment becomes a no brainer. If there was more of a cultural pressure against drunk driving and actual mechanisms to prevent it th…

I think a camera is far more intrusive than a breathalyzer.

Re: We deleted the production database by accident

#384

Post by jrochkind1 » Sun, Oct 18, 2020, 1:48 PM UTC

The lack of the seriousness/professionalism of the postmortem seemed odd to me too. So, okay, what is this site?

> KeepTheScore is an online software for scorekeeping. Create your own scoreboard for up to 150 players and start tracking points. It's mostly free and requires no user account.

And also:

> Sat Sep 5, 2020, Running Keepthescore.co costs around 171 USD each month, whilst the revenue is close to zero (we do make a little money by building custom scoreboards now and then). This is an unsustainable situation which needs to be fixed – we hope this is understandable! To put it another way: Keepthescore.co needs to start making money to continue to exist.

https://keepthescore.co/blog/posts/monetizing-keepthescore/

So okay, it's basically a hobby site, for a service that most users probably won't really mind losing 7 hours of data, and that has few if any paying customers.

That context makes it make a little bit more sense.

Re: We deleted the production database by accident

#385

Post by smarx007 » Sun, Oct 18, 2020, 1:50 PM UTC

A question to the DBA experts from a developer: is there a way in MySQL and Postgres to configure a log specifically for destructive SQL queries so that it's easier to investigate a situation like this? I.e. to log most queries except for usual SELECT/INSERTs.

Also, @oppositelock pointed out that WAL would contain the destructive query too. How does one remove a single query from a WAL for replay or how does one correctly use WAL to recover after a 23-hour old backup was restored?

Finally, how does one work on the WAL level with managed DBs on AWS or DO if they block SSH access?

Re: We deleted the production database by accident

#386

Post by JackFr » Sun, Oct 18, 2020, 2:01 PM UTC

janoc wrote: post #366

globular-toast wrote: post #277

Earlier quoted context omitted.

There is sometimes a single cause, but as the parent comment pointed out, that should never be the case and is a flaw in the system. We are gradually working towards single errors being correctable, but we're not there yet. On the railways in Britain the failures were extensively documented. Years ago it was possible for a single failure to cause a loss. But over the years the systems have been patched and if you loo…

There is almost never a single cause. If a single cause can trigger a disaster, then there is another cause by definition - poor system design. E.g. in the article's case it is clear that there is some sort of procedural deficiency there that allows the configuration variables to be set wrong and thus cause a connection to the wrong database. Another one is that the function that has directly caused the data loss DOE…

This should be a teachable moment with respect to their culture. Throwing up their hands without an understanding of what happened is unacceptable — if something that is believed impossible happens, it is important to know where your mental model failed. Otherwise you may make things worse by ‘remediating’ the wrong thing.

And while this sounds overly simplistic the simplest way this could have been avoided is enforcing production hygiene. No developers on production boxes. Ever.

Re: We deleted the production database by accident

#387

Post by rmrfrmrf » Sun, Oct 18, 2020, 2:09 PM UTC

macNchz wrote: post #60

smadge wrote: post #2

SSH tunnel from localhost to prod on database port?

A likely culprit. Having worked on a bunch of early-stage products where best practices are a distant future dream, I’ve developed a few “seatbelt” habits I use to avoid these kinds of things. One of them is to always use a random high-number local port if I’m tunneling to a production service. Another is to change my terminal theme to a red background before connecting to anything in production...never want to click…

what's a situation where you'd be tunneling to a production service?

Re: We deleted the production database by accident

#388

Post by a_imho » Sun, Oct 18, 2020, 2:11 PM UTC

tamrix wrote: post #225

Nextgrid wrote: post #89

Earlier quoted context omitted.

This is why I am against the current trend of over-complicating stacks for political or marketing reasons. Every startup nowadays wants microservices and/or serverless and a mashup of dozens of different SaaS (some that can't easily be simulated locally) from day 1 while a "boring" monolithic app will get them running just fine.

I think we're hitting peak tech. All this "technical" knowledge just dates itself in a year's time anyway. Eventually, you come to realise that the more tech you've got, the more problems you have. . Now developers spend more time googling errors and plugging in libraries and webservices together than writing any actual code. Sometimes I wish for a techless cloudless revolution when we just go back to the foundations…

My point today is that, if we wish to count lines of code, we should not regard them as “lines produced” but as “lines spent”: the current conventional wisdom is so foolish as to book that count on the wrong side of the ledger.

I'm yet to encounter a point in my career where KISS fails me. OTOH this is nothing new, I don't have my hopes up that the current trends of overcomplicating things are going to change in the near future.

Re: We deleted the production database by accident

#389

Post by ClumsyPilot » Sun, Oct 18, 2020, 2:14 PM UTC

mrmonkeyman wrote: post #352

qz2 wrote: post #341

Earlier quoted context omitted.

I think my point is probably being misunderstood and that is my fault for explaining it poorly. See I fucked up :) The fear I speak of is a personal barrier which is lacking in a lot of people. They can sleep quite happily at night knowing they did a shitty job and it's going to explode down the line. It's not their problem. They don't care. I can't do that. Even if there are no direct consequences for me. This is no…

HN likes to downplay this, apparantly, but not everything can be boiled down to bureaucracy. Yes, medical professionals use checklists. They also have a harsh and very unforgiving culture that fosters craftsmanship and values professionalism above all else. You see this in other high-stakes professions too. You cannot just take the checklist and ignore the relentless focus on quality, the feelings of personal failure…

'The system' includes the attitudes of developers and people that pay them.

Everyone takes job of a medical proffesional seriously, from the education to the hospitals that enmloys them to the lawmakers to the patients.

When you pick a surgeon, you avoid the ones that killed people. Do you avoid developers that introduce bugs? We don't even keep track of that!

You can have the license taken away as a surgeon, I've never heard of anyone becoming unemployable as a developer.

You are not gonna get an equivalent outcome even if tomorrow all developers show up to work with an attitude of a heart surgeon.

However if suddenly all data loss and data breaches would result in massive compensation, and if slow and buggy software resulted in real lawsuits, you would see the results very quickly.

Basically same issues as in trading securities: no accountability for either developers or the decision makers.

Re: We deleted the production database by accident

#390

Post by fallingfrog » Sun, Oct 18, 2020, 2:16 PM UTC

I once replaced a bunch of customer photos with a picture of Spock as part of a test on my first week on the job.. The dB admin had just overwritten a sales force dev dB from production and a previous developer had hardcoded the IP address of production in the code of a script somewhere..