0

Greetings, after a hdd failure, I have a PostgreSQL DB restored, but OS is not working. I try to copy it to another server with a newer version of PostgreSQL but all I managed to get is:

An old version of the database format was found. You need to upgrade the data format before using PostgreSQL. See /usr/share/doc/postgresql-8.1.22/README.rpm-dist for more information.

I don't know the old version number.

Please, advice me something.

2
  • Always back up data. I posted a response, but nothing good. Next time, set up a nightly crontab entry that runs a pgdump, and back it up to other disks.
    – Horus
    Apr 8, 2011 at 17:56
  • Dayan's backup plan would have worked, if only he'd taken steps to ensure that he'd have a compatible postgres version installed. Nightly pg_dumps aren't practical on large databases. Backing up the raw binary data is actually standard practice in that context; however, it's critical that you be ready to install a compatible version of postgres in a recovery situation. Apr 8, 2011 at 19:01

2 Answers 2

1

If you were able to get all of the files in the data directory, then there should be a PG_VERSION file. Using this, you can build a database with that version. From my analysis, I haven't been able to find a way to restore the database data without a database with the correct version.

Here are some steps for recovery if you know the version:

http://archives.postgresql.org/pgsql-general/2009-09/msg01152.php

If you know that your last version is 8.3 or greater, this should work:

http://www.postgresql.org/docs/9.0/static/pgupgrade.html

Not a pleasant answer.

2
  • 1
    Correct: if your source database version is older than 8.3, there's only one way to upgrade: dump and reload. I don't think initdb would be helpful here. That really just creates a new, empty database. Apr 8, 2011 at 17:38
  • I misunderstood something that I read and had done earlier. You are correct. I'll correct my answer.
    – Horus
    Apr 8, 2011 at 17:54
1

The advise on looking for the pg_version file is good, and also you should probably be able to run pg_controldata against the $PGDATA and get some information out. In the worst case, just keep installing major versions (7.4.x, 8.0.x, 8.1.x, etc...) until you find a compatible match.

One you have a running database, you can worry about upgrading if needed. pg_dump / restore is one option. pg_upgrade will work on 8.3+. You also have the option of using a replication system like slony to do cross version replication (how well it will work depends on what version you end up on).

For all of these methods though (including pg_upgrade) you'll need a running copy of your system, so start with trying to find the matching/compatible version to start with.

You must log in to answer this question.