Re: Restore dump into different databases/owners

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi Tom!

Thanks for your reply!

Tom Lane schrieb:
> Andreas Haumer <andreas@xss.co.at> 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@xss.co.at
*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-----