Modifying production database

Problem

Although not often, I would find myself modifying production database. Either because there was a crisis which had to be handled immediately or because there was a business need. Each time going with the established process would take too long for time critical task.

When doing those modifications I had never made a mistake, but several times I got close. I found myself stopping at the last moment before issuing a disastrous command. Recovering data from the backup would be a really bad way of spending my time.

I was determined to make the chance of me making a mistake as low as possible.

Solution

I came up with a process wich (so far) prevented me from corrupting anything in the production database.

The process consists of 4 steps:

1. write a query which will confirm the desired changes and run it, this should show that the change is not yet there,
2. modify the database,
3. re-run query from the step 1 to confirm that the desired change had happened,
4. commit your changes

To help with the last step I have following bit in my ~/.psqlrc file:

\set AUTOCOMMIT off

This bit forces me to explicitly commit after any changes. Without it, quitting psql or any connection problems will rollback any modifications. It’s something which, in my mind should be the default.

Summary

Editing production database should be avoided as much as possible. In case that it cannot be, the best approach is to have a reliable and repeatable process minimizing the risk of breaking things. In the case of psql switching autocommit helps by forcing an extra step when applying any modifications. This extra step gives a chance to review any changes before applying them to the data.