4 ms·

If you're doing a update/delete/insert in prod because you really have to (I had this a couple times in the last few months): - reproduce the issue and fix loc

If you're doing a update/delete/insert in prod because you really have to (I had this a couple times in the last few months): - reproduce the issue and fix locally, with prod data copied over if needed (better than guessing) - save (back up) all data that is being changed somewhere, even if it's on your local machine temporarily (again, better than not having it) - if your datastore has transactions, run the change and a confirmation query in a transaction/rollback several times before committing - don't hide what you're doing, tell everyone who will listen that you're mucking about in prod - if someone will hop on a screen share with you, let them Main theme here, when making data changes, is test as much as you can and make sure folks know it's happening. If you're making data changes, the code has already failed, so don't feel bad doing what you need to do, but mitigate loss as much as you can. Shit happens, deal with it, but don't make it worse if you can.

tomxor 5y ago
Great advice. One issue with testing: even once you have a copy of the production DB, it's not actually a live production DB in that it's not being modified and used continuously in the same way. This can hide issues with your change. My point is, testing on a copy is necessary, but you must combine it with thinking as carefully and thoroughly as you can about the potential side-effects, which in turn depends on your understanding of the application architecture. Some of those side-effects you can test for, others are not so obvious unless you can better simulate the production environment with noisy users who will complain when things go wrong. One strategy is to find people internally who are good at being real life simulators of noisy users who bump into edge cases.
yakshaving_jgt 5y ago
You (not you specifically; anyone reading this) need to be careful with copying production data to another machine. This can be considered a security/privacy vulnerability in the eyes of people who care about things like ISO27001 (which is everyone if you’re selling at the enterprise level). With a sufficiently comprehensive data anonymisation tool you can work around this limitation, but that's an additional investment that a business needs to be aware of up front.
- dijit 5y ago
  FWIW this should be the standard practice anyway. Your dev environment should be running on a sanitised version of prod data. Some people prefer to operate on a subset (10%~) of prod data, I have always preferred to take the cost of taking everything. This has a nice consequence of allowing you to test automated restores at regular intervals, but you must be mindful of the "fail-open" nature of sanitising data. (IE: new data must be known to the sanitisation tool). Pipeline usually goes: Prod/live -> Replica -> Backup/Snapshot -> Copy to archive -> Copy from archive via streamed sanitiser -> sanitised backup -> Restore[n] Dev Envs from same backup.
  - satyrnein 5y ago
    You can also do zero copy clones of production in Aurora, Snowflake, etc, so you don't have to duplicate the whole thing.
  - efficax 5y ago
    Taking everything? I'll call you in 2 weeks when the indexes are done building
    - dijit 5y ago
      I am finding it difficult not to reply with snark, because I'm quite sure that 2weeks of downtime to restore your systems in a data corruption or complete failover scenario is not reasonable to your directors. But, even that said: you can copy the binary files over to a new machine (copy-on-write snapshots -> rsync) -> store a copy -> start up the database, sanitise -> ship around to dev envs.
      - sa46 5y ago
        What happens when prod is a few hundred terabytes or you use logical replication to stream changes to handle major version updates? The GP’s point was shipping 100% of a large database isn’t feasible. You’re conflating dev environments with restoring backups. Those can be the same thing but are often separate.

tomxor 5y ago

Great advice. One issue with testing: even once you have a copy of the production DB, it's not actually a live production DB in that it's not being modified and used continuously in the same way. This can hide issues with your change. My point is, testing on a copy is necessary, but you must combine it with thinking as carefully and thoroughly as you can about the potential side-effects, which in turn depends on your understanding of the application architecture. Some of those side-effects you can test for, others are not so obvious unless you can better simulate the production environment with noisy users who will complain when things go wrong. One strategy is to find people internally who are good at being real life simulators of noisy users who bump into edge cases.

yakshaving_jgt 5y ago

You (not you specifically; anyone reading this) need to be careful with copying production data to another machine. This can be considered a security/privacy vulnerability in the eyes of people who care about things like ISO27001 (which is everyone if you’re selling at the enterprise level). With a sufficiently comprehensive data anonymisation tool you can work around this limitation, but that's an additional investment that a business needs to be aware of up front.

dijit 5y ago
FWIW this should be the standard practice anyway. Your dev environment should be running on a sanitised version of prod data. Some people prefer to operate on a subset (10%~) of prod data, I have always preferred to take the cost of taking everything. This has a nice consequence of allowing you to test automated restores at regular intervals, but you must be mindful of the "fail-open" nature of sanitising data. (IE: new data must be known to the sanitisation tool). Pipeline usually goes: Prod/live -> Replica -> Backup/Snapshot -> Copy to archive -> Copy from archive via streamed sanitiser -> sanitised backup -> Restore[n] Dev Envs from same backup.
- satyrnein 5y ago
  You can also do zero copy clones of production in Aurora, Snowflake, etc, so you don't have to duplicate the whole thing.
- efficax 5y ago
  Taking everything? I'll call you in 2 weeks when the indexes are done building
  - dijit 5y ago
    I am finding it difficult not to reply with snark, because I'm quite sure that 2weeks of downtime to restore your systems in a data corruption or complete failover scenario is not reasonable to your directors. But, even that said: you can copy the binary files over to a new machine (copy-on-write snapshots -> rsync) -> store a copy -> start up the database, sanitise -> ship around to dev envs.
    - sa46 5y ago
      What happens when prod is a few hundred terabytes or you use logical replication to stream changes to handle major version updates? The GP’s point was shipping 100% of a large database isn’t feasible. You’re conflating dev environments with restoring backups. Those can be the same thing but are often separate.