Thursday, May 19, 2011

How to recover postgres database from postgres data folder

If Windows crashed by some reason you can't get into the system, then if you have to reinstall Windows or upgrade to a new hard drive. As long as the Postgres data folder still there then you are lucky to get your database back.

1) install the same version postgres you had on the old system before, say postgres8.3;

2) stop the postgres service from the service form or use the command below:
    net stop pgsql-8.3

3) open a CMD, then type in:
    runas   /user:postgres   cmd
    The above command will open a command window as postgres role, it will ask for the postgres service password to get the access. Please note here that is not postgres user password;

4) cd into postgres folder:
    c:\program files\postgres\

5) delete everything under the data folder;

6) then copy the old data folder(say from U drive) cross, type in the command:
    xcopy /H /E /I U:\program files\postgres\data C:\program files\postgres\data

7) restart postgres services from the service form or use the command below:
    net start pgsql-8.3

After that, you should be able to log in to use your postgres database as before