Обсуждение: Problem with restoring database from 7.3.1 to 8.0.1

Hi Folks,

I have a small problem.  We're currently using an old PostgreSQL 7.3.1
database. In near future we want to migrate to a new server and taking
the chance to upgrade postgres. Now I have a testsystem with postgres
8.0.1 where I tried to import a dump from our database. Everthing works
fine except 74 error messages which all look simliar to this one:

pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag 639;
0 33230571 CONSTRAINT $3 mh1004
pg_restore: [Archivierer (DB)] could not execute query: FEHLER:
Relation »public.$3« existiert nicht
    Command was: ALTER INDEX public."$3" OWNER TO mh1004;

Sorry it's the german version. I think the translation could be:

pg_restore: [Archivierer (DB)] Error in index directory 639; 0 33230571
CONSTRAINT $3 mh1004
pg_restore: [Archivierer (DB)] could not execute query: Error:  relation
»public.$3« does not exist
    Command was: ALTER INDEX public."$3" OWNER TO mh1004;

mh1004 is the postgres user for this database. Everthing seems to work
fine but I would really like to know what this error does mean.

Thanks and *greets*
Kai

Kai Hessing <kai.hessing@hobsons.de> writes:
> I have a small problem.  We're currently using an old PostgreSQL 7.3.1
> database. In near future we want to migrate to a new server and taking
> the chance to upgrade postgres. Now I have a testsystem with postgres
> 8.0.1 where I tried to import a dump from our database. Everthing works
> fine except 74 error messages which all look simliar to this one:

> pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag 639;
> 0 33230571 CONSTRAINT $3 mh1004
> pg_restore: [Archivierer (DB)] could not execute query: FEHLER:
> Relation �public.$3� existiert nicht
>     Command was: ALTER INDEX public."$3" OWNER TO mh1004;

Hmm.  This looks like a pg_dump bug, ie, issuing ALTER OWNER commands
for the wrong index name (or, perhaps, issuing them before having
created the index ... do you use any indexes named like that?).

Which pg_dump version did you use to make the dump?  The usual
recommendation is to use the newer version's pg_dump in this sort
of situation.

BTW, you should *not* be updating to 8.0.1.  8.0.6 is the latest release
in that series.  It is almost never a good idea to be running anything
but the latest minor release in a series.  (The fact that you're still
on 7.3.1 doesn't speak well for your attentiveness to updates either.
We don't make minor releases just to keep busy, you know.)

            regards, tom lane

Hi Tom,

thanks for your answer. I did some testing now but still have the same
problem.

Tom Lane wrote:

>> Kai Hessing <kai.hessing@hobsons.de> writes:
>
>>>>  [DB-Problem]
>
>>
>> Hmm.  This looks like a pg_dump bug, ie, issuing ALTER OWNER commands
>> for the wrong index name (or, perhaps, issuing them before having
>> created the index ... do you use any indexes named like that?).
>>
>> Which pg_dump version did you use to make the dump?  The usual
>> recommendation is to use the newer version's pg_dump in this sort
>> of situation.


The whole thing is a little bit strange because the indexes doesn't
really seem to exist. For example the error:

pg_restore: [Archivierer (DB)] Fehler in Inhaltsverzeichniseintrag 114;
0 5901799 CONSTRAINT usertypeid mh1004
pg_restore: [Archivierer (DB)] could not execute query: FEHLER:
Relation »public.usertypeid« existiert nicht
    Command was: ALTER INDEX public.usertypeid OWNER TO mh1004;

There is no index called 'public.usertypeid'. The index name is
'public.auth_usertype_pkey ' and references the row 'usertypeid' in
table 'auth_usertype'. So it might be a pg_dump bug. And I do not
understand the sense of this ALTER command because the whole db and
everything in it belongs already to user mh1004.

But the dump worked perfectly fine at our old testmachine with 7.4.x, so
I'm not sure if it's really pg_dump producing the error. The pg_dump
Version is the same like the database: 7.3.2.
I am not able to update postgres on this server (see below). So how may
I upgrade just the pg_dump?


>> BTW, you should *not* be updating to 8.0.1.  8.0.6 is the latest release
>> in that series.  It is almost never a good idea to be running anything
>> but the latest minor release in a series.  (The fact that you're still
>> on 7.3.1 doesn't speak well for your attentiveness to updates either.
>> We don't make minor releases just to keep busy, you know.)


You're perfectly right. Just to explain:

Test server: It was a fresh Suse 9.3 install and after my assistant told
me that postgres is running, I tried the import. Now it's patched to
8.0.3. It is still not the newest one, but Suse doesn't seem to provide
a newer precompiled version for their distribution yet.

Production system: It is a 7.3.2 (my mistake) and it is hosted
externally. Maybe it's something about debian and stable and so on. But
it doesn't matter anymore, because I think we're switching to a new
provider soon and actual patches are part of the contract.