Re: Recovering a broken database

Hello,

I'm having the almost exact problem described here:
http://archives.postgresql.org/pgsql-admin/2004-12/msg00110.php

That is, a 8.0 which started on a 7.4.2 repository. After noticing,
I switched back to 7.4.2, but now it gives this error and won't start:

FATAL: database files are incompatible with server
DETAIL: The database cluster was initialized with PG_CONTROL_VERSION 74, but
the server was compiled with PG_CONTROL_VERSION 72.
HINT: It looks like you need to initdb.

I read the pointer about using pg_resetxlog, but I obtain a seemingly
empty database (no tables). Here's the output from pg_resetxlog:

-------------------------
pg_resetxlog: pg_control exists but is broken or unknown version; ignoring it
Guessed pg_control values:

pg_control version number: 72
Catalog version number: 200310211
Current log file ID: 0
Next log file segment: 1
Latest checkpoint's StartUpID: 0
Latest checkpoint's NextXID: 514
Latest checkpoint's NextOID: 16384
Database block size: 8192
Blocks per segment of large relation: 131072
Maximum length of identifiers: 64
Maximum number of function arguments: 32
Date/time type storage: floating-point numbers
Maximum length of locale name: 128
LC_COLLATE: en_US.UTF-8
LC_CTYPE: en_US.UTF-8

If these values seem acceptable, use -f to force reset.
-------------------------

Then I use -f and it resets, but to no use.

I also tried recompiling 7.4.2 (I install it from source anyway) but
no use.

I did a backup of the repository before I attempted the reset.
Is there any other way I can try to recover the database?

--
Ciprian Popovici

Ciprian Popovici <ciprian(at)zuavra(dot)net> writes:
> That is, a 8.0 which started on a 7.4.2 repository. After noticing,
> I switched back to 7.4.2, but now it gives this error and won't start:

I think you are omitting some relevant information, like what you did
to force the 8.0 postmaster to start in a wrong-version database. It
absolutely will not do that by itself --- I can name at least two
separate checks that are made to prevent this error.

regards, tom lane

Quoting Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> Ciprian Popovici <ciprian(at)zuavra(dot)net> writes:
> > That is, a 8.0 which started on a 7.4.2 repository. After noticing,
> > I switched back to 7.4.2, but now it gives this error and won't start:
>
> I think you are omitting some relevant information, like what you did
> to force the 8.0 postmaster to start in a wrong-version database. It
> absolutely will not do that by itself --- I can name at least two
> separate checks that are made to prevent this error.

Come to think of it better, at some point I might've switched
repositories with 8.0 still running.

Let me see... I had 7.4.2 (code and repository) installed. The
machine is a rather ancient Red Hat 7.3 (remote, so I can't readily
upgrade it). FWIW, Postgres compilation mentions something about
an outdated Bison.

I compiled 8.0.0. I moved the old Postgres and repository out of the
way. I did make install with 8.0, then su'ed to postgres and did a
fresh initdb. Then loaded a fresh copy of the database from another
machine into the 8.0 repository.

However, people suddenly desired the old 7.4.2 installation alive
for a little more. At which point I tried to switch things back only
to discover the aforementioned error.

--
Ciprian Popovici

Oh, and FWIW, when I try a pg_dump on the database "recovered" with
pg_resetxlog, I get this:

pg_dump: missing pg_database entry for database "db"

--
Ciprian Popovici

Ciprian Popovici <ciprian(at)zuavra(dot)net> writes:
> Come to think of it better, at some point I might've switched
> repositories with 8.0 still running.

Hmm, you mean you renamed the 8.0 directory tree out of the way
and then moved the 7.4 tree where it had been, without stopping
the 8.0 postmaster? Oh dear.

You've more than likely got a database that has had parts of some
files overwritten with data from the 8.0 installation. I don't
think there's much chance of getting out of that mess, and certainly
not with any trust in the consistency of your data. Hope you had a
backup...

But congratulations on finding an entirely new way around the
safety interlocks that are intended to prevent this sort of
disaster. We shall have to think about whether we can fix that.

regards, tom lane

On Thu, 30 Jun 2005 09:11:13 -0400 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> But congratulations on finding an entirely new way around the
> safety interlocks that are intended to prevent this sort of
> disaster. We shall have to think about whether we can fix that.

Glad to be of help, whatever way that is. :) True, I haven't been able
to recover that repository. Fortunately, the setback was minor, since I did
have a recent enough backup. Once again, the absolute rule is "always make
a backup".

--
Ciprian Popovici