Thread: Strange trouble with pg_dump import
Hi, Since two days we have a trouble with the importation of our database dump import ... In fact, it's not a real trouble of the import ... because there is not really any error message during the importation of the data ... But after we do some queries on the database ... and we, ONLY on all the table having a PRIMARY KEY, have a message like this : "index ix_mytable_index is not a btree" ... (not the exact message sorry). I insist we do not have this message with others tables which have index but without primary key ... and for the tables with primary key, we have only the trouble with the index of the primary key .. not the others index ... so the trouble seems to be on the constraint of the primary key ... We have the same message doing a vacuum full verbose analyse; of the database on each table having a primary key ... We import the dump doing : cat my_dump | psql -U postgres mydatabase We are using a 7.3.3 PostgreSQL version. Nothing change on the server, except one thing ... we only add 4 new tables in the database with about a total of 2 000 000 records ... the dump size that we are trying to import is about 1,8 Gb in text format. The server have no trouble of space disk, or memory during the importation of the data ... The really strange thing .. is that making this only from our classical cron of the night (that we do not changed since 2 years) ... BUT if we do the importation by "hands" doing a \i mydump or doing the same command line as inside the night script cat my_dump | psql -U postgres mydatabase ... then it's running perfectly ... !? We tried twice this morning to import the database on the same server without any troubles ... !???? Any ideas ? Regards, -- Hervé Piedvache Elma Ingénierie Informatique 6 rue du Faubourg Saint-Honoré F-75008 - Paris - France Pho. 33-144949901 Fax. 33-144949902
On 20 Jun 2003 at 12:28, Hervé Piedvache wrote: > The really strange thing .. is that making this only from our classical cron > of the night (that we do not changed since 2 years) ... BUT if we do the > importation by "hands" doing a \i mydump or doing the same command line as > inside the night script cat my_dump | psql -U postgres mydatabase ... then > it's running perfectly ... !? We tried twice this morning to import the > database on the same server without any troubles ... !???? Any errors recorded in logs when the cron import happened? Bye Shridhar -- job Placement, n.: Telling your boss what he can do with your job.
You may want to make sure the cron job is running with the same (relevant) shell environment settings as the manual import. Things like client encoding deduced from environment variables come to mind. Karsten -- GPG key ID E4071346 @ wwwkeys.pgp.net E167 67FD A291 2BEA 73BD 4537 78B9 A9F9 E407 1346
Nothing more than : NOTICE: ALTER TABLE / ADD PRIMARY KEY will create implicit index 'mytable_pkey' for table 'mytable' ALTER TABLE bye, Le Vendredi 20 Juin 2003 12:37, Shridhar Daithankar a écrit : > On 20 Jun 2003 at 12:28, Hervé Piedvache wrote: > > The really strange thing .. is that making this only from our classical > > cron of the night (that we do not changed since 2 years) ... BUT if we do > > the importation by "hands" doing a \i mydump or doing the same command > > line as inside the night script cat my_dump | psql -U postgres mydatabase > > ... then it's running perfectly ... !? We tried twice this morning to > > import the database on the same server without any troubles ... !???? > > Any errors recorded in logs when the cron import happened? > > Bye > Shridhar > > -- > job Placement, n.: Telling your boss what he can do with your job. > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) -- Hervé Piedvache Elma Ingénierie Informatique 6 rue du Faubourg Saint-Honoré F-75008 - Paris - France Pho. 33-144949901 Fax. 33-144949902