June 03, 2017

Learning the Hard Way: Postgres Recovery from the File System

There are some lessons in life that are perhaps better learned the hard way. That you should have a proper backup system for your data is not one of them. And it's especially not a lesson to learn the hard way when you have been building and working with computers for nearly two decades. But so it goes.

Earlier this week, I had a hard disk fail in one of my servers that doubles as my main workstation. It wasn't the first time I have had a disk fail (or even the first time one has failed in this particular machine), but it was by far the most damaging instance. For reasons that now escape me, I never bought a second hard drive to setup a RAID 1 mirror when I upgraded the system a couple years back (which I normally always do for this system), nor did I ever get my regular backup system running after the upgrade. Even more inexplicably, during that same time period, I went from using that machine solely for development to using it host my definitive photo database (described here).

So you can imagine how I felt when the computer refused to boot after an update. After several consecutive nights of staying up into the wee hours, I can say that I've almost brought things back to normal. No part of the process was pleasant, but by far the worst of it was trying to recover the Postgres database cluster from the file system because my database backups generated by pg_dump were too old.