Обсуждение: Problem in restoring data
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 ........... ...........
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)
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 GBYou 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)
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
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. >though. > > regards, tom lane > >---------------------------(end of broadcast)--------------------------- >TIP 6: Have you searched our list archives? > > http://archives.postgresql.org > >
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
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:
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 <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