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 . . . 

Update, 2019-03-31: Weirdly it turns out that the dump/restore process did not perfectly rebuild the database schemes. Not a huge issue, but some of the foreign keys have vanished in some tables, though the indexes built for them were still there. It takes but a few minutes to sort this out, far less than the arguably unnecessary but force-of-habit data tidying I spent time on . . . but why . . . ?? Checking this, the fkeys are in the old dump but right at the end . . . psql buffer overflow maybe? I remember something like that being an issue years ago, elsewhere.

Update, 2019-07-16: On a related note, a thing I’d noticed with the new installation is that using Quick Look to check the contents of text files produces mojibake rather than correctly interpreting UTF-8. For example:   for nbsp – for endash ä for auml This has been bugging me, but today I thought possibly using BOMs would sort it; and it did (i.e. I set BBEdit to add the BOM by default and tweaked a few files as I went), but it turns out that Apache (or at least the version I’m using and I don’t know if fixed since) doesn’t understand BOMs, so throws an error if an .htaccess file has one. So that was no use. Back to plain UTF-8. Then it occurred to me that Quick Look uses Apple applications to provide the service . . . so for plain text that would be TextEdit; wonder if there’s an option in TextEdit prefs? Yes, it turns out there is — change the Open default from Automatic to UTF-8. And it works; I can now quicklook at the text I wrote. Well, that took a while. The odd thing is, I don’t recall ever doing this before, so why has the new installation (from the same discs I used before) changed the prefs? Maybe more prefs corruption?

Comment or Question about this page? write

Article text ©2019 Electropict .
Click images for individual licences.