[ADMIN] 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

(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)

Do you need help?

In addition to our free technical library, Pantek provides professional IT services to companies who utilize Open Source software: Learn more about Pantek's Technical Support offerings Place an order for 24/7/365 Technical Support here Learn more about Pantek, who we are, and what we do Chat with a Live Operator Related postgresql.org Links pgsql-admin pgsql-advocacy pgsql-announce pgsql-bugs pgsql-cygwin pgsql-docs pgsql-general pgsql-interfaces pgsql-jdbc pgsql-jobs pgsql-novice pgsql-odbc pgsql-performance pgsql-php pgsql-ports pgsql-sql Request more information about Pantek

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@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

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

---------------------------(end of broadcast)---------------------------
TIP 7: You can help support the PostgreSQL project by donating at

                
http://www.postgresql.org/about/donate