Windows – How to restore a file system level copy of a PostgreSQL database (not dump) to a different PC

backupdatabase-restorepostgresqlwindows

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?

Best Answer

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.

Related Topic