Re: Problem in restoring data

Rajesh Kumar Mallah <mallah@trade-india.com> writes:

I face the following problem in transferring data from
pgsql 7.3.4 to pgsql 7.4 RC1 . I am piping the output of
pg_dumpall from 7.3 to 7.4 running on different port.
The problem is there is a gist index on txtidx type on a
non-public schema and when search_path does not include
public the index cannot be created.

There is a post-7.3.4 bug fix in the 7.3 branch for this mistake:

2003-10-02 18:25 tgl

* src/backend/utils/adt/ruleutils.c (REL7_3_STABLE): When dumping
CREATE INDEX, must show opclass name if the opclass isn't in the
schema search path. Otherwise pg_dump doesn't correctly dump
scenarios where a custom opclass is created in 'public' and then
used by indexes in other schemas.

Since the bug is in the backend and not pg_dump, you can't escape it by
using the 7.4 version of pg_dump against the 7.3 server. You could
recompile the server using the 7.3-branch-head version of ruleutils.c,
though.

regards, tom lane

Rajesh Kumar Mallah <mallah@trade-india.com> writes:

Thanks for the explanation , Shall do that please tell me how to fetch
ruleutils.c
from 7.3-branch-head (do not know the cvs commands O:-) ) , thanks again.

The easiest way to get it is from the cvsweb interface:
http://developer.postgresql.org/cvsweb.cgi/pgsql-server/src/backend/utils/adt/ruleutils.c
Shift-click on the "download" link for the revision you want (1.124.2.2)
and you got it.

regards, tom lane

Yes i may drop the indexes even , but is it quite usual
to take these work arounds in doing major upgrades.

Regds
Mallah.

Alvaro Herrera wrote:

Tom Lane wrote:

Rajesh Kumar Mallah <mallah@trade-india.com> writes:

I face the following problem in transferring data from
pgsql 7.3.4 to pgsql 7.4 RC1 . I am piping the output of
pg_dumpall from 7.3 to 7.4 running on different port.
The problem is there is a gist index on txtidx type on a
non-public schema and when search_path does not include
public the index cannot be created.

There is a post-7.3.4 bug fix in the 7.3 branch for this mistake:

2003-10-02 18:25 tgl

* src/backend/utils/adt/ruleutils.c (REL7_3_STABLE): When dumping
CREATE INDEX, must show opclass name if the opclass isn't in the
schema search path. Otherwise pg_dump doesn't correctly dump
scenarios where a custom opclass is created in 'public' and then
used by indexes in other schemas.

Since the bug is in the backend and not pg_dump, you can't escape it by
using the 7.4 version of pg_dump against the 7.3 server.

Ok,I read somewhere its always better to use more recent pg_dump while
migrating.

You could
recompile the server using the 7.3-branch-head version of ruleutils.c,

Thanks for the explanation , Shall do that please tell me how to fetch
ruleutils.c
from 7.3-branch-head (do not know the cvs commands O:-) ) , thanks again.

regds
mallah.

It did solve my problem

now its dumped like:

SET search_path = archives, pg_catalog;
SET
CREATE INDEX site_search_gist ON site_search USING gist (keywordidx
public.gist_txtidx_ops);
CREATE INDEX

thanks
regds
mallah.

Tom Lane wrote: