some data recovery

an experience of recovery from Postgresql database files

Ooh weirdness.

I had a significant drive failure a few weeks ago during backup, which managed to take out both the source and destination drives.  OK I should be rotating backups to avoid this problem, check that for the future.  Amongst the files was the most-recent-state postgres, and this is tricky.  I could have restored from the last dump, but thanks to a months-long bout of illness this was a bit behind.

I managed to recover basically everything else by dint of some older recovery software on an older machine, but as I suspected, just switching the recovered postgres data folder to the new installation didnʼt work.  Part of which might be that the recovery software writes all its recovered files under ownership of ‘privoxy’ rather than the original owners ... perhaps on the basis that you wonʼt know how to chown, not sure.  Or perhaps because the original owners might not be present on the new system, fair enough.  Maybe thatʼs enough to prevent postgres starting with the old folder (even with a few configuration tweaks).

So instead I cloned the whole drive ... slightly overkill for 100-odd MB of database gunk, but hopefully simpler than either figuring it out or trying to reconstruct manually – and I think the latter would be easier of those two.  This isnʼt really critical data, just a convenience thing.  But it took several hours.

The particular implementation of postgres here is Bitnamiʼs MAPPstack, and this makes one particular option attractive – to run the whole thing again from the cloned drive (which would include the data files), do a dump, and restore to the new installation.  Will it work?

Well, first problem is that the cloned volume is also regarded as unrepairable.  Second is that although the MAPPstack manager application there works it doesnʼt detect either postgres or apache, so no joy.  Curiously as well, the file sizes are very different on the clone than on the normal recovery version.

In particular, and this might be it, the postgresql.log is 314.5MB rather than 4KB as on the recovered version (at least, once postgres has been successfully started).  And it contains SQL transactions dating back to the original point of installation.  Ohh...K.  I had thought it was set up to turn over somehow, but obviously not.  So might this contain all the data I need?  Well, no, itʼs mostly a list of every error thatʼs occurred during development and data entry in that time.  Kind of the opposite, really.  Something else to check for the future: turn that log.

After much poking about I realised this wasnʼt going to work anyway and had a rest.  During which it occurred to me that the problem might just be that I hadnʼt reinstalled MAPPstack in the same filesystem position as it had been in previously.  I have better ideas now.  And possibly another installation in the original place would work with the recovered files.  Initially this also seemed to fail, but I wondered if a restart to hard-clear the old PID might do it, annnnd this time it did.  I now have a functioning set of databases.  Thereafter success is just a dump and restore to the new position away.  Which took several more hours including tidying, but Iʼm not going to complain about a zero data loss situation, after that failure.

Also?  Automate those database dumps, yes...