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 publickind 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/
Show quoted text
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:
Show quoted text
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