3

I am new to PostgreSQL. I have to recover a database which was running in widows XP machine.

I have the zip folder of postgres.

I have extracted postgres installation in a different PC and started a using initDB and created a new database, I was able to login, but I am not able to see any old tables.

Would you please post the steps you have used to start server in another windows XP machine and how to recover tables and data in the old data folder?

2
  • > I have the zip folder of postgres < And what exactly is in this zipfile? Which directory did you save?
    – wildplasser
    Dec 1, 2011 at 17:11
  • Hi Thanks for you reply, postgresSQL 8.2 was running in one PC, our office people want to upgrade the PC. They zipped the folder containing postgresql, postgres and all associated folders like data are zipped in a single file]. Now I want to run the same postgresql is a different PC, as the old one was formatted with new OS. I extracted the zip in new PC, start postgres with new database , from this new datbase I am trying to access the old postgres database.But I am not able to see any table other then postgres related tables. would appreciate your reply.
    – user782224
    Dec 1, 2011 at 20:45

3 Answers 3

7

Ideally you would've done a dump with pg_dump, which would've made this much easier. Bad office people, no cookie.

Because you have the binaries from the same release, you can save some hassle.

  • Unzip your backup dir somewhere convenient where you have full read/write permission as your normal user account. I'll assume it's on your desktop: C:\Users\Myusername\Desktop\postgresql - for the purpose of this example.

Assuming that the c:\users\myusername\desktop\postgresql folder contains the folders named bin, data, lib, etc:

  • Open a cmd.exe shell

  • cd Desktop\postgresql

  • bin\pg_ctl.exe start -D C:\users\myusername\desktop\postgresql\data

  • bin\psql.exe -U mydbusername mydb

If psql connects OK disconnect with the \q command then promptly make a dump of the database:

  • bin\pg_dump.exe -Fc -U mydbusername -f mydb.dbbackup mydb

... and restore that to a modern PostgreSQL release using pg_restore from the current release.

If you have problems restoring to newer releases like 9.0 or 9.1 you might have to use pg_dump from the newer release to connect to the old database and make the dump, rather than using pg_dump from 8.2 to make the dump. You can copy pg_dump from a new machine to the old machine if you'd prefer not to set up to dump over the network; just copy the whole `bin' dir from a new PostgreSQL release to somewhere on the old machine and run pg_dump from there. Copying just pg_dump.exe alone will not work as it requires libpq and the other libraries.

Please do not try to keep on running 8.2, which is an obsolete and unsupported release. Get onto 9.0 or 9.1. This may require some changes to your code; see the release notes for each major .0 release since yours, eg 8.3.0, 8.4.0, 9.0.0 and 9.1.0 for upgrade info.

Now promptly fix your backup procedures to schedule a daily pg_dump so this never happens to you again

Here's what you'd do if you didn't have the PostgreSQL binaries. I'd already written it up when I saw you had the binaries in the zip file and I'll keep it here for others.

  • Install PostgreSQL 8.2. Yes, it MUST be 8.2.x. No other version will work for this purpose, as PostgreSQL raw data files are only compatible within the same major release. Because it's such an old version it may not co-exist with current versions already installed, so you might have to use a spare machine or a virtual machine.

  • Once you have 8.2 installed and you know it's working with the empty database created by the installer, shut it down from the services control panel (start -> run -> services.msc)

  • Rename the data directory to something like data-empty. I think that's C:\Program Files\PostgreSQL\8.2\data for 8.2, but I can't remember for sure.

  • Unzip your old datadir to the location you just moved the installer-created datadir from, so it has the same name and location. You might actually have to unzip it somewhere else then move it into place if you're running a non-admin or a UAC admin account.

  • Get properties on the unzipped data dir. In the security tab, give the "postgres" user full control and apply that to all files and folders within. This is important; the database won't work without correct permissions and the unzipped copy won't have the correct permissions.

  • Start the PostgreSQL service from services.msc

  • Check to make sure the service started correctly. If it didn't, look in the Event Viewer for info on why. Also check the logs in the pg_log directory of the datadir and make sure everything looks sensible, there are no errors there.

  • Try to connect with psql and see if you can access your data. If you can, follow the advice above to make a dump. Don't keep on using your obsolete database version.

6
  • Hi, Thanks a lot for your detailed reply, but When I try to point to old data folder , first of all it postgres not starting , I gave a different data fodler postgres gettign started but I am not able to any table in postgres database. would appreciate your suggestion.
    – Muthuvel P
    Dec 2, 2011 at 18:11
  • I can't understand your comment. Dec 4, 2011 at 4:39
  • Please supply the exact commands you run and exact error messages you get. Be detailed about every step you take. Update your original question with more info if it's too long for a comment, explaining what you've tried to far and where you get stuck. Dec 4, 2011 at 4:40
  • Hi Craig Ringer, thanks a lot for your prompt rely, your suggestion helped me a lot. postmaster -D C:\users\myusername\desktop\postgresql\data , started the databse in its original form , I was able to recover database and tables.
    – Muthuvel P
    Dec 13, 2011 at 5:51
  • 1
    I'm glad it helped. Remember to fix your backup procedures so you ate never stuck like this again. If you want more help from people in future please accept this answer (the tick) and up vote it, so people know you accept answers. Dec 13, 2011 at 14:32
0

Doing this using the Postgres.app tool was incredibly easy.

Just went to Preferences, changed the data directory, and restarted the server. That was it. I'm very surprised. I think the versions were identical in my case.

0
0

Craig Ringer's worked for me.

But for those of you having difficulty installing Postgresql 8.2 on windows 10 like me, I suggest creating a windows XP virtual box to do so.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .