Dump and Restore of Database by User
Hi,
When creating a dump of a database as a user "gemein" by the command
pg_dump mitglieder >...
I get
\connect - postgres
--
-- TOC Entry ID 33 (OID 16556)
--
-- Name: "plpgsql_call_handler" () Type: FUNCTION Owner: postgres
--
CREATE FUNCTION "plpgsql_call_handler" () RETURNS opaque AS '$libdir/plpgsql', 'plpgsql_call_handler' LANGUAGE 'C';
--
-- TOC Entry ID 34 (OID 16557)
--
-- Name: plpgsql Type: PROCEDURAL LANGUAGE Owner:
--
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler" LANCOMPILER '';
\connect - gemein
...
Because of the "\connect - postgres" the user cannot reload the
database himself. On the other hand, the language PL/pgSQL is available
by default in template1, so I think those lines are unnecessary:
template1=> select * from pg_language;
lanname | lanispl | lanpltrusted | lanplcallfoid | lancompiler
----------+---------+--------------+---------------+-------------
internal | f | f | 0 | n/a
C | f | f | 0 | /bin/cc
sql | f | f | 0 | postgres
plpgsql | t | t | 16556 |
(4 rows)
template1=>
So the user has to comment out those lines for the reload to work.
The same happens with the custom dump format, so this is unusable.
-- Is this a bug or a feature?
-- How can I avoid that behaviour?
I use version 7.2.1 on a Debian Linux system.
Thanks in advance,
--
Hubert Palme Am Walde 1
42119 Wuppertal
<hubert.palme@web.de>
hubert.palme@web.de (Hubert Palme) writes:
... On the other hand, the language PL/pgSQL is available
by default in template1,
Not in a default installation.
More to the point: pg_dump cannot assume that the contents of template1
are the same in the destination installation as they were in the source,
so it does not rely on template1 to supply anything.
regards, tom lane