I had some data in a postgres database on a laptop that died. Stupidly I didn't back up that data. However, using some hacker-jitsu (i.e. buying a hard drive-to-USB coverter) I had access to the data. However, there were further problems.

• where did/does postgresql store its data files?
• the data were postgresql 7.4 data, is that compatible with postgresql 8.2?
• can I force postgresql to access the data in a different data directory from the one it normally uses.

The documentation says that postgresql data files are not compatible from each major version to another. So 7.4 data are not compatible with 7.5 data let alone 8.2. Hence forcing 8.2 to read the 7.4 data files was bound to fail. In any case, the command line utiltites 'pg_ctl' and 'pg_ctlcluster' did not seem to respond to the -D switch which set the data directory.

To move data from one postgresql version to another the documentation advises to use postgres's pg_dump and pg_restore utilities. Alternatively there is the pg_dumpall utility.

There also seems is a 'pg_upgrade' utility, but I couldn't get my head around the documentation.

So, I mish-mashed all this information in my head and came up with a strategy.

The first thing I did was to install the old postgresql:

apt-get install postgresql-7.4

I then stopped the postgresqls from running.

/etc/init.d/postgresql-8.2 stop

/etc/init.d/postgresql-7.4 stop

I copied the data from the usb drive to the current drive. I guessed (correctly as it turned out) that, on a Debian system, the postgres data are in /var/lib/postgresql/7.4/. Your system might be different.

rsync -avz /media/disk-1/lib/postgresql/7.4/ /var/lib/postgresql/7.4/

Made sure that the owners are right:

chown -R postgres.postgres /var/lib/postgresql/7.4/

Now, time to get the database data. Note the use of the 'su' command as I can't be arsed to struggle with postgresql's authentication system, especially for one operation.

Start the pg server:

/etc/init.d/postgresql-7.4 start

Get a dump of the old data:

 su -c 'pg_dumpall > outfile.sql' postgres

Stop the old server and start the new one:

/etc/init.d/postgresql-7.4 stop
/etc/init.d/postgresql-8.2 start

Finally, load the data into the new server:

su -c 'psql -f outfile.sql' postgres

And there you have it.