Обсуждение: Loading 7.4 dump to 8.1 with user-custom search_path breaks
From a 7.4 dump: decibel@fritz.2[23:08]~:90>grep search_path fritz-20051106.sql ALTER USER decibel SET search_path TO 'decibel, rrs, rrd, page_log, public'; Trying that command in psql... decibel=# ALTER USER decibel SET search_path TO 'decibel, rrs, rrd, page_log, public'; NOTICE: schema "decibel, rrs, rrd, page_log, public" does not exist ALTER ROLE decibel=# \d No relations found. decibel=# show search_path; search_path ----------------------------------"decibel, rrs, page_log, public" (1 row) decibel=# There are tables in the database... Something else that struck me looking at some errors from this restore; would it be a good idea to come up with some kind of dedicated API/interface for dump to use so that it's not as vulnerable to these kind of issues? For example, this dumpall has: DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database WHERE datname = 'template0'); before all the CREATE USER statements. Problem with that is: psql:fritz-20051106.sql:11: ERROR: cannot delete from a view Granted, this could be handled by creating the needed rules on that view, but ISTM it would be better if instead we had a function like pg_delete_all_users that dump called instead. For most of the dump this isn't much of an issue, because it uses standard commands that we're really careful about not breaking backwards compatability on. -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
> decibel@fritz.2[23:08]~:90>grep search_path fritz-20051106.sql > ALTER USER decibel SET search_path TO 'decibel, rrs, rrd, page_log, public'; > > Trying that command in psql... > > decibel=# ALTER USER decibel SET search_path TO 'decibel, rrs, rrd, > page_log, public'; > NOTICE: schema "decibel, rrs, rrd, page_log, public" does not exist > ALTER ROLE > decibel=# \d > No relations found. > decibel=# show search_path; > search_path > ---------------------------------- > "decibel, rrs, page_log, public" > (1 row) > > decibel=# Yes, that's all fixed in the 8.0 and higher pg_dump. Use the 8.1 dump to dump your 7.4 database. > Something else that struck me looking at some errors from this restore; > would it be a good idea to come up with some kind of dedicated > API/interface for dump to use so that it's not as vulnerable to these > kind of issues? For example, this dumpall has: > > DELETE FROM pg_shadow WHERE usesysid <> (SELECT datdba FROM pg_database > WHERE datname = 'template0'); > > before all the CREATE USER statements. Problem with that is: > > psql:fritz-20051106.sql:11: ERROR: cannot delete from a view > > Granted, this could be handled by creating the needed rules on that > view, but ISTM it would be better if instead we had a function like > pg_delete_all_users that dump called instead. For most of the dump this > isn't much of an issue, because it uses standard commands that we're > really careful about not breaking backwards compatability on. I believe this is all done in 8.1 pg_dump. Chris