Обсуждение: normal user dump gives error because of plpgsql
Hi, i have a normal user with rights to create a db. template1 contains language plpgsql. the user wants to - dump his db - drop his db - create it again - and use the dump file to fill it. it gives errors because of CREATE LANGUAGE statements inside the dump. How can i prevent that the dump contains CREATE LANGUAGE statements. They are not needed if template1 contains the language, right? I didnt found anythng in the archives even though i am sure not to be the first one having this problem. kind regards, janning here is what i did with 7.4.6: +++ AS DB SUPERUSER # createlang plpgsql template1 # createuser -Ad testuser CREATE USER # su testuser +++ AS TESTUSER $ createdb $ pg_dump -O -x > /tmp/dump.sql $ dropdb $ createdb $ psql testuser < /tmp/dump.sql SET SET SET ERROR: permission denied for language c ERROR: must be superuser to create procedural language ERROR: must be owner of schema public kind regards, janning -- PLANWERK 6 websolutions Herzogstraße 85, 40215 Düsseldorf Tel.: 0211-6015919 Fax: 0211-6015917 http://www.planwerk6.de/
On Thu, Mar 10, 2005 at 01:31:26PM +0100, Janning Vygen wrote: > Hi, > > i have a normal user with rights to create a db. template1 contains language > plpgsql. the user wants to > - dump his db > - drop his db > - create it again > - and use the dump file to fill it. > > it gives errors because of CREATE LANGUAGE statements inside the dump. I don't beleive there is. But it's not really needed, you get and error and the restore continues. The only thing different is that you don't get a message, the result is the same. -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a > tool for doing 5% of the work and then sitting around waiting for someone > else to do the other 95% so you can sue them.
Вложения
I'm pretty sure I had the same problem when using pg_restore. If pl/pgsql is installed in template1, then the restore fails. And I couldn't find any solution to this on the list either. I definitely want pl/pgsql in template1 so that any databases I create have access to pl/pgsql without having to explicitly install it in each new db I create in the cluster. Recreating the database using psql dbname < dump.sql does work OK, but not pg_restore. Can pg_restore be made to ignore the error? John Sidney-Woollett Martijn van Oosterhout wrote: > On Thu, Mar 10, 2005 at 01:31:26PM +0100, Janning Vygen wrote: > >>Hi, >> >>i have a normal user with rights to create a db. template1 contains language >>plpgsql. the user wants to >>- dump his db >>- drop his db >>- create it again >>- and use the dump file to fill it. >> >>it gives errors because of CREATE LANGUAGE statements inside the dump. > > > I don't beleive there is. But it's not really needed, you get and error > and the restore continues. The only thing different is that you don't > get a message, the result is the same.
John Sidney-Woollett <johnsw@wardbrook.com> writes:
> I'm pretty sure I had the same problem when using pg_restore. If
> pl/pgsql is installed in template1, then the restore fails.
> And I couldn't find any solution to this on the list either.
You're supposed to restore into a database cloned from template0,
not template1.
> Can pg_restore be made to ignore the error?
It does, at least since 8.0.
regards, tom lane
Tom Lane wrote: > John Sidney-Woollett <johnsw@wardbrook.com> writes: > >>I'm pretty sure I had the same problem when using pg_restore. If >>pl/pgsql is installed in template1, then the restore fails. > > >>And I couldn't find any solution to this on the list either. > > > You're supposed to restore into a database cloned from template0, > not template1. > That's interesting because I normally create my databases using template1 so that I don't have to install pl/pgsql before I start adding pgsql functions. So what you're saying is that if you know you're just about to restore into a new DB (using pg_restore), then create the new DB from template0 instead of template1. I've just spotted the Notes section for pg_restore in the 7.4.x docs which confirms this. Strange that I didn't see it before... need to RTFM better! Thanks. John Sidney-Woollett
Am Donnerstag, 10. März 2005 18:17 schrieb Tom Lane: > John Sidney-Woollett <johnsw@wardbrook.com> writes: > > I'm pretty sure I had the same problem when using pg_restore. If > > pl/pgsql is installed in template1, then the restore fails. > > > > And I couldn't find any solution to this on the list either. > > You're supposed to restore into a database cloned from template0, > not template1. Thanks! I just didn't read the manual careful enough. Sorry for stealing your time as it is 10 times worth than mine, i guess. > > Can pg_restore be made to ignore the error? > > It does, at least since 8.0. I guess, it's time to upgrade! kind regards, Janning