Restore dump into different databases/owners

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

Hi!

I must be blind or something but it seems I just can't
figure out a working procedure to restore a PG dump in
custom format (created with "pg_dump -Fc") into several
different PG databases with different owners (i.e. for
testing purposes)

I want to do something like that:

On database server A:
pg_dump -Fc testdb_orig > testdb_orig.dump

On database server B:
createuser abc
createuser def
createuser xyz
createdb -O abc abc_db
createdb -O def def_db
createdb -O xyz xyz_db
pg_restore -d abc_db -O abc testdb_orig.dump
pg_restore -d def_db -O def testdb_orig.dump
pg_restore -d xyz_db -O xyz testdb_orig.dump

(this simple example above won't work as pg_restore option
"-O" doesn't accept the name of the new owner as parameter)

Depending on the combination of options (like "-U" or "-O")
I give to pg_restore, I either get errors or I end up with a
database owned by the original owner of the dumped database
or the database superuser (which I do not want)

Is there really no way to tell pg_restore to set the new
database object's owner to a DB user which already exists?

I really don't want to manually set ownership of hundreds of
database objects like tables, functions, domains, sequences, ...

Please tell me I just overlooked the obvious... ;-)

- - andreas

- --
Andreas Haumer | mailto:andreas(at)xss(dot)co(dot)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

iD8DBQFHiLZFxJmyeGcXPhERAjKOAKC5J+TXMTbdkKQtrIcaSUaq+lHrIwCeJQZ2
FR3yB7BwoNfeeiLZZN6Pix8=
=kA1j
-----END PGP SIGNATURE-----

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

Hi!

Just a short followup...

Andreas Haumer schrieb:
> Hi!
>
> I must be blind or something but it seems I just can't
> figure out a working procedure to restore a PG dump in
> custom format (created with "pg_dump -Fc") into several
> different PG databases with different owners (i.e. for
> testing purposes)
>
> I want to do something like that:
>
> On database server A:
> pg_dump -Fc testdb_orig > testdb_orig.dump
>
> On database server B:
> createuser abc
> createuser def
> createuser xyz
> createdb -O abc abc_db
> createdb -O def def_db
> createdb -O xyz xyz_db

I now solved it by temporarily adding the "superuser" privilege
to the roles in question.

That way I can do the following:

pg_restore -d abc_db -O -U abc testdb_orig.dump
pg_restore -d def_db -O -U def testdb_orig.dump
pg_restore -d xyz_db -O -U xyz testdb_orig.dump

It's not as nice as I would like it to be, but it seems to work...

> Is there really no way to tell pg_restore to set the new
> database object's owner to a DB user which already exists?
>

It indeed looks like there is no direct way with pg_dump/pg_restore

Comments?

- - andreas

- --
Andreas Haumer | mailto:andreas(at)xss(dot)co(dot)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

iD8DBQFHiOoDxJmyeGcXPhERAsIJAJ9yKVhytenhqqTHgQeN6WGlDDm3ogCfU9PZ
+wX0CPDrdH/ccLd4b6+plJ4=
=tZpa
-----END PGP SIGNATURE-----

Andreas Haumer <andreas(at)xss(dot)co(dot)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" ?

regards, tom lane

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

Hi Tom!

Thanks for your reply!

Tom Lane schrieb:
> Andreas Haumer <andreas(at)xss(dot)co(dot)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(at)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(at)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(at)goethe:/tmp {73} $ createdb -E UTF8 -O abc abc_db
CREATE DATABASE

postgres(at)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(at)xss(dot)co(dot)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-----

Andreas Haumer <andreas(at)xss(dot)co(dot)at> writes:
> Tom Lane schrieb:
>> Exactly what problems did you have using "-O -U user" ?

> So, the main problem is the call to
> CREATE PROCEDURAL LANGUAGE plpgsql;
> on the "mwdb" schema which contains all the tables, functions etc.

Right. As of 8.3, the default permissions arrangement will allow
database owners to create trusted procedural languages for themselves,
so this dump would work for a non-superuser (though you'd still have
to ignore the error from attempting to comment SCHEMA public --- I
suppose we ought to look into keeping pg_dump from emitting that
comment).

Otherwise, the recommended procedure is for the superuser to load
any required procedural languages when he makes the empty database.
The database owner can take it from there.

regards, tom lane