Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Apr 9 '11 at 6:55

This question came from our site for professional and enthusiast programmers.

    
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 '11 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. –  Frank Farmer Apr 8 '11 at 19:01
add comment

2 Answers 2

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.

share|improve this answer
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. –  Frank Farmer Apr 8 '11 at 17:38
    
I misunderstood something that I read and had done earlier. You are correct. I'll correct my answer. –  Horus Apr 8 '11 at 17:54
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.