Re: Forgot to dump old data before re-installing machine - Mailing list pgsql-general

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

Ok, thanks for these steps. Coffee and chocolate helped... :-))

Ok, did what you said: stopping server, deleting "newly" created
"data" directory, re-running initdb, starting the server, stopping the
server.

Renamed "empty" data directory.

Restarting server: NOT COMPLAINING "you need to run initdb" or
something else.... Although it's saying that it starts, nothing
appears in the process list (ps -ef).

and renamed "old, backuped" data directory to "data_postgres"

Can't see any differences in permissions... Looks like this:

drwx------   14 schwarzer  schwarzer   476 Jan 14 13:33 data_postgres

Restarted the server.

/opt/local/lib/postgresql81/bin/psql postgres

\l

         List of databases
    Name    |   Owner   | Encoding
-----------+-----------+----------
  postgres  | schwarzer | UTF8
  template0 | schwarzer | UTF8
  template1 | schwarzer | UTF8


I should have a couple of databases appearing here...