Re: Forgot to dump old data before re-installing machine

Stefan Schwarzer wrote:
>>> I re-installed my machine and "forgot" to dump my database(s). I
>>> naturally still have the whole database folders. For the moment I
>>> installed the "old" postgres version (8.1) to be able to read my data.
>>> But how can I read them? It seems that it doesn't work that I just
>>> overwrite the new database folder with the old one... Would be too
>>> simple, I guess...
>>
>> Should work, if you've got the whole $PGDATA directory tree. Maybe
>> you forgot to stop the postmaster while copying the backup into place?
>
> Thanks a lot for this. Still trying. But although the postmaster did run
> at one time, now, after copying back and forth, it doesn't want to do
> anything anymore... Gush, getting really frustrated...

Stop. Deep breath. Cup of coffee / tea, optional biscuit.
Stop the server (if it's running)
Check the version-number of the installed postgresql packages.
Check you still have your backups somewhere safe.
Re-run initdb to re-create your "data" directory.
Make sure plenty of logging is turned on in postgresql.conf
Start the server.
Verify that everything is fine, particularly that select version()
displays what you expect.
Stop the server.
Do the logs contain everything you'd expect? If not, update
postgresql.conf and re-start the server until they do.
Rename the data directory to "data.old" (or similar).
Try starting the server - check that it fails complaining "you need to
run initdb" (or similar).
Restore your backup to "data".
Make sure plenty of logging is turned on in postgresql.conf
Compare ownership + permissions on "data" vs "data.old", correct if
necessary.
Start the server.

With this setup you can compare both versions of your data directory and
see what's different. If the version of your backup and the version of
the software are the same then it will work.

--
Richard Huxton
Archonet Ltd