By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,548 Members | 1,735 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,548 IT Pros & Developers. It's quick & easy.

Problem in restoring data

P: n/a

Hi,

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.

I solution i have is to drop such indexes temporarily before
dumping. Is that the only way ?

PS: i do not want to edit the (splitted) dump file , my database
is ~ 7 GB
regds
mallah.

............
............

CREATE INDEX intranet_byline_ed ON intranet_byline USING btree (edition);
CREATE INDEX
CREATE INDEX iid_listing_userid ON iid_listing USING btree (userid);
CREATE INDEX
CREATE INDEX intranet_company_category_name ON intranet_company USING
btree (category_name);
CREATE INDEX
*SET search_path = archives, pg_catalog;*
SET
CREATE INDEX site_search_gist ON site_search USING gist (keywordidx);
*ERROR: data type public.txtidx has no default operator class for
access method "gist"*
HINT: You must specify an operator class for the index or define a
default operator class for the data type.
SET search_path = data_bank, pg_catalog;
SET
CREATE INDEX profiles_pincode ON profiles USING btree (pincode);
CREATE INDEX
CREATE INDEX profiles_city ON profiles USING btree (city);
CREATE INDEX
CREATE INDEX profiles_branch ON profiles USING btree (branch_id);
CREATE INDEX
CREATE INDEX profiles_area_code ON profiles USING btree (area_code);
CREATE INDEX
............
............
---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
On Mon, Nov 10, 2003 at 01:36:20AM +0530, Rajesh Kumar Mallah wrote:
PS: i do not want to edit the (splitted) dump file , my database
is ~ 7 GB


You can "edit" the dump file by means of
perl -pi -e 's/^CREATE INDEX.*gist.*//' dump-file

or something like that ...
(this isn't a solution to your problem, but may serve as workaround)

--
Alvaro Herrera (<alvherre[a]dcc.uchile.cl>)
"Lo esencial es invisible para los ojos" (A. de Saint Exúpery)

---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 12 '05 #2

P: n/a

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:
On Mon, Nov 10, 2003 at 01:36:20AM +0530, Rajesh Kumar Mallah wrote:
PS: i do not want to edit the (splitted) dump file , my database
is ~ 7 GB


You can "edit" the dump file by means of
perl -pi -e 's/^CREATE INDEX.*gist.*//' dump-file

or something like that ...
(this isn't a solution to your problem, but may serve as workaround)

Nov 12 '05 #3

P: n/a
Rajesh Kumar Mallah <ma****@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

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #4

P: n/a
Tom Lane wrote:
Rajesh Kumar Mallah <ma****@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.
though.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Nov 12 '05 #5

P: n/a
Rajesh Kumar Mallah <ma****@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/cvsw...dt/ruleutils.c
Shift-click on the "download" link for the revision you want (1.124.2.2)
and you got it.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 12 '05 #6

P: n/a
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:
Rajesh Kumar Mallah <ma****@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/cvsw...dt/ruleutils.c
Shift-click on the "download" link for the revision you want (1.124.2.2)
and you got it.

regards, tom lane

Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.