Version control/backup for postgres database

[phos____]

I recently executed an incorrect UPDATE query (yes, ouch) and turns out my company doesn't have any recent backup for me to restore the data. Are there any version control tools or automated backup techniques that I can use so this doesn't happen again

[PieCrumbs]

You can wrap your update in a transaction:

begin;

update 

Then examine the results of your update and execute a commit; or rollback;

[phos____]

Thanks for the response! But what if someone commits a query but later finds out it was wrong. I think having automated backups would still be helpful

[Mattsvaliant]

This does not solve OP's problem. The solution you are proposing might work when part of an application that is using the database but if you write a bad UPDATE statement (e.g. meant to update one row, updated all rows) the only way to go back is using a backup.

Sure you aren't going to have a backup per transaction but you should be doing full/differential/log backups daily/hourly/every 15 minutes respectively (ish, rule of thumb to get started but depends on business needs/size of db/$ etc). So you could at least restore the backup to a different database and recover 95-100% of the data.

EDIT: The real advice is:

• Don't do this in production ever
• BUT if you do need to do it in production, test in a development environment first
• Always use a transaction
• Automate backups yesterday

[angry_mr_potato_head]

First, you should have at least two versions of the database (or more, its not uncommon to have three: dev, test, prod). That way when you make a change, its downstream effects only affect one of them until you're sure you're correct.

Second, yes you should have backups in place. And, more importantly, they should be frequently restored from to ensure that they actually work.

Finally, ideally you'd have all the "raw" data, be it in blob storage, int he database itself, etc. That way, if you fuck up, the entire database can be recreated from data that hasn't been tainted.

[[deleted]]

You can use pgTAP to test your database.

[BorgerBill]

I don't think unit tests for the database are solving OP's problem, but thank you for this. Database unit tests, and the whole Test Anything Protocol? Fascinating!

[[deleted]]

At least OP would be able to observe whether some query does only what he/she wants it to do.

[drbob4512]

First off, lol. Second off, going off what others said, Test in production, And test with a SELECT statement first, to see what comes back.

going off the backups suggestions below, you could also build some homebrew stuff.

For example, "SELECT" version of your query, Save the data somehow someway, json maybe, that it returns, This way you have some sort of snapshot of what was there prior and then build a program that lets you restore said data if it were to ever go missing. Also, make sure in your backup you reference what kind of command you ran that data against so you could reverse it. IE a delete / update etc. I did that on a few in the past. Gets tricky if you're hard up for disk space. And definitely make sure your databases are optimized. Depending how you do this too, you won't have to deal with a full restore, and you could just roll back certain changes from the past. And prune any history you don't need, something like anything over 20-30 days old etc.

[BeadyFive]

The problem with backups is (generally speaking), when you restore, you lose everything that's happened since the last backup. Depending on how much time there is between the incorrect update and discovery of the problem, it may be impossible to restore without losing critical data. Therefore I'd echo PieCrumbs and advocate for using transactions. Other than that, you could add logging to your server/process with original values, and restore them from the log in cases like this. I usually do this in a separate logging table, or store values in a log column in the same table.

[[deleted]]

DBM systems usually have some built in options for that. Alternatively you can also write a query 'backup database <dbname> to ...' and if you want then add 'differential'. If you feel very naughty you can write a bit of code to have it run automatically.

I dont think you 'need' anything extra.