-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
Hi Tom!
Thanks for your reply!
Tom Lane schrieb:
> Andreas Haumer <andreas@xxxxxxxxx> writes:
>> I now solved it by temporarily adding the "superuser" privilege
>> to the roles in question.
>
> That should not be necessary, unless the dump contained objects that
> require superuser permission to create (such as C-language functions)
> --- in which case giving ownership of them to a non-superuser account
> seems a bit unwise anyway.
>
> Exactly what problems did you have using "-O -U user" ?
>
Sorry, I should've mentioned it in the first mail...
postgres@goethe:/tmp {71} $ pg_restore -l testdb_orig.dump
;
; Archive created at Sat Jan 12 12:38:52 2008
; dbname: testdb_std
; TOC Entries: 705
; Compression: -1
; Dump Version: 1.10-0
; Format: CUSTOM
; Integer: 4 bytes
; Offset: 8 bytes
; Dumped from database version: 8.2.4
; Dumped by pg_dump version: 8.2.4
;
;
; Selected TOC Entries:
;
4; 2615 23923 SCHEMA - mwdb testdbo
2276; 0 0 COMMENT - SCHEMA mwdb testdbo
2277; 0 0 ACL - mwdb testdbo
6; 2615 2200 SCHEMA - public postgres
2278; 0 0 COMMENT - SCHEMA public postgres
2279; 0 0 ACL - public postgres
435; 2612 23926 PROCEDURAL LANGUAGE - plpgsql postgres
326; 1247 23927 DOMAIN mwdb d_code testdbo
2280; 0 0 COMMENT mwdb DOMAIN d_code testdbo
328; 1247 23928 DOMAIN mwdb d_comment testdbo
2281; 0 0 COMMENT mwdb DOMAIN d_comment testdbo
330; 1247 23929 DOMAIN mwdb d_mv testdbo
2282; 0 0 COMMENT mwdb DOMAIN d_mv testdbo
332; 1247 23930 DOMAIN mwdb d_mv_arr testdbo
2283; 0 0 COMMENT mwdb DOMAIN d_mv_arr testdbo
334; 1247 23931 DOMAIN mwdb d_name testdbo
2284; 0 0 COMMENT mwdb DOMAIN d_name testdbo
336; 1247 23932 DOMAIN mwdb d_pit testdbo
2285; 0 0 COMMENT mwdb DOMAIN d_pit testdbo
338; 1247 23933 DOMAIN mwdb d_rid testdbo
2286; 0 0 COMMENT mwdb DOMAIN d_rid testdbo
21; 1255 23934 FUNCTION mwdb func_d2n(d_pit) testdbo
2287; 0 0 COMMENT mwdb FUNCTION func_d2n(d_pit) testdbo
23; 1255 23935 FUNCTION mwdb func_eh2par_delete(d_rid) testdbo
2288; 0 0 COMMENT mwdb FUNCTION func_eh2par_delete(d_rid) testdbo
25; 1255 23936 FUNCTION mwdb func_eh2par_insert(d_rid, d_rid, d_rid, boolean) testdbo
2289; 0 0 COMMENT mwdb FUNCTION func_eh2par_insert(d_rid, d_rid, d_rid, boolean) testdbo
27; 1255 23937 FUNCTION mwdb func_eh2par_update(d_rid, d_rid, d_rid, boolean) testdbo
2290; 0 0 COMMENT mwdb FUNCTION func_eh2par_update(d_rid, d_rid, d_rid, boolean) testdbo
29; 1255 23938 FUNCTION mwdb func_eh_delete(d_rid) testdbo
2291; 0 0 COMMENT mwdb FUNCTION func_eh_delete(d_rid) testdbo
31; 1255 23939 FUNCTION mwdb func_eh_insert(d_rid, d_name, d_code, d_comment, character varying, real) testdbo
2292; 0 0 COMMENT mwdb FUNCTION func_eh_insert(d_rid, d_name, d_code, d_comment, character varying, real) testdbo
33; 1255 23940 FUNCTION mwdb func_eh_update(d_rid, d_name, d_code, d_comment, character varying, real) testdbo
2293; 0 0 COMMENT mwdb FUNCTION func_eh_update(d_rid, d_name, d_code, d_comment, character varying, real) testdbo
[...]
postgres@goethe:/tmp {72} $ createuser abc
Shall the new role be a superuser? (y/n) n
Shall the new role be allowed to create databases? (y/n) n
Shall the new role be allowed to create more new roles? (y/n) n
CREATE ROLE
postgres@goethe:/tmp {73} $ createdb -E UTF8 -O abc abc_db
CREATE DATABASE
postgres@goethe:/tmp {79} $ pg_restore -d abc_db -O -U abc testdb_orig.dump
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 2278; 0 0 COMMENT SCHEMA public postgres
pg_restore: [archiver (db)] could not execute query: ERROR: must be owner of schema public
Command was: COMMENT ON SCHEMA public IS 'Standard public schema';
pg_restore: [archiver (db)] Error from TOC entry 435; 2612 23926 PROCEDURAL LANGUAGE plpgsql postgres
pg_restore: [archiver (db)] could not execute query: ERROR: must be superuser to create procedural language
Command was: CREATE PROCEDURAL LANGUAGE plpgsql;
pg_restore: [archiver (db)] Error from TOC entry 23; 1255 23935 FUNCTION func_eh2par_delete(d_rid) testdbo
pg_restore: [archiver (db)] could not execute query: ERROR: language "plpgsql" does not exist
HINT: Use CREATE LANGUAGE to load the language into the database.
Command was: CREATE FUNCTION func_eh2par_delete(d_rid) RETURNS integer
AS $_$
DECLARE
old_id ALIAS FOR $1;
now_pit d_pit;
retva...
pg_restore: [archiver (db)] Error from TOC entry 2288; 0 0 COMMENT FUNCTION func_eh2par_delete(d_rid) testdbo
pg_restore: [archiver (db)] could not execute query: ERROR: function func_eh2par_delete(d_rid) does not exist
Command was: COMMENT ON FUNCTION func_eh2par_delete(d_rid) IS 'DELETE Funktion fuer View vc_eh2par';
pg_restore: [archiver (db)] Error from TOC entry 25; 1255 23936 FUNCTION func_eh2par_insert(d_rid, d_rid, d_rid, boolean) testdbo
pg_restore: [archiver (db)] could not execute query: ERROR: language "plpgsql" does not exist
[...]
pg_restore: [archiver (db)] Error from TOC entry 2234; 2620 25621 TRIGGER trigger_temporal_ug testdbo
pg_restore: [archiver (db)] could not execute query: ERROR: function func_pk_temporal_trigger() does not exist
Command was: CREATE TRIGGER trigger_temporal_ug
AFTER INSERT OR UPDATE ON t_ug
FOR EACH ROW
EXECUTE PROCEDURE func_pk_tempora...
pg_restore: [archiver (db)] Error from TOC entry 2236; 2620 25622 TRIGGER trigger_temporal_zr testdbo
pg_restore: [archiver (db)] could not execute query: ERROR: function func_pk_temporal_trigger() does not exist
Command was: CREATE TRIGGER trigger_temporal_zr
AFTER INSERT OR UPDATE ON t_zr
FOR EACH ROW
EXECUTE PROCEDURE func_pk_tempora...
pg_restore: WARNING: no privileges could be revoked for "public"
pg_restore: WARNING: no privileges could be revoked for "public"
pg_restore: WARNING: no privileges were granted for "public"
pg_restore: WARNING: no privileges were granted for "public"
WARNING: errors ignored on restore: 189
So, the main problem is the call to
CREATE PROCEDURAL LANGUAGE plpgsql;
on the "mwdb" schema which contains all the tables, functions etc.
I only have PL/pgSQL functions (and rules, triggers, etc.), but
these depend on the PL/pgSQL language installed for the schema, so...
- - andreas
- --
Andreas Haumer | mailto:andreas@xxxxxxxxx
*x Software + Systeme | http://www.xss.co.at/
Karmarschgasse 51/2/20 | Tel: +43-1-6060114-0
A-1100 Vienna, Austria | Fax: +43-1-6060114-71
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.7 (GNU/Linux)
Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org
iD8DBQFHiQaExJmyeGcXPhERAqSMAJ9f0nlRQpwYFWQemcfJp1kfAvNzTQCgntbi
23tgZjlMFgjtp9+zakEUc+Y=
=s2mj
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster
