Обсуждение: pg_dump and using schema problem
Hi there, I got a problem playing with schema and dump/restore process (8.0.3). I created schema 'contrib' for my database and added it to search_path in postgresql.conf. Then I loaded a bunch of modules to 'contrib schema' and populated database with various tables. Everything is ok, I could dump separately schema 'contrib' and 'public' separately and I didn't expected any problem in restore. Unfortunately, pg_dump explicitly defines search path to 'public, pg_catalog' and any objects from 'contrib' schema doesn't seen, which cause many errors, for example, table uses type tsvector, which defined in tsearch2 module, so that table doesn't created. I have to use sed to add 'contrib' module to search_path to restore my database, but I don't like such solution. Is't possible to say pg_dump/pg_dumpall to use custom search_path or get it from postgresql.conf ? Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Oleg Bartunov <oleg@sai.msu.su> writes: > any problem in restore. Unfortunately, pg_dump explicitly defines > search path to 'public, pg_catalog' and any objects from 'contrib' schema > doesn't seen, which cause many errors, for example, table uses type tsvector, > which defined in tsearch2 module, so that table doesn't created. Can you give a more specific example? Anything not in the search path should be explicitly schema-qualified in pg_dump's output. If it isn't, that's a bug. regards, tom lane
On Wed, 18 May 2005, Tom Lane wrote: > Oleg Bartunov <oleg@sai.msu.su> writes: >> any problem in restore. Unfortunately, pg_dump explicitly defines >> search path to 'public, pg_catalog' and any objects from 'contrib' schema >> doesn't seen, which cause many errors, for example, table uses type tsvector, >> which defined in tsearch2 module, so that table doesn't created. > > Can you give a more specific example? Anything not in the search path > should be explicitly schema-qualified in pg_dump's output. If it isn't, > that's a bug. unfortunately, no, because I deleted dump file. But, I just tried to reproduce my problem with 8.0.3 and got no problem. I think, I tried dump from 7.4. > > regards, tom lane > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83
Oleg Bartunov <oleg@sai.msu.su> writes: > unfortunately, no, because I deleted dump file. But, I just tried to > reproduce my problem with 8.0.3 and got no problem. I think, I tried > dump from 7.4. Possibly it was just that 7.4's pg_dump isn't bright enough about dump order? I can't think of any other 7.4-to-8.0 change that would be relevant. regards, tom lane
On Wed, 18 May 2005, Tom Lane wrote: > Oleg Bartunov <oleg@sai.msu.su> writes: >> unfortunately, no, because I deleted dump file. But, I just tried to >> reproduce my problem with 8.0.3 and got no problem. I think, I tried >> dump from 7.4. > > Possibly it was just that 7.4's pg_dump isn't bright enough about dump > order? I can't think of any other 7.4-to-8.0 change that would be > relevant. I found the problem, I used old dump files from 7.4 and there were no schema, so it's my fault. Sorry for bothering. > > regards, tom lane > Regards, Oleg _____________________________________________________________ Oleg Bartunov, sci.researcher, hostmaster of AstroNet, Sternberg Astronomical Institute, Moscow University (Russia) Internet: oleg@sai.msu.su, http://www.sai.msu.su/~megera/ phone: +007(095)939-16-83, +007(095)939-23-83