Received: from localhost (unknown [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id C71962E3ED8 for ; Sat, 12 Jan 2008 08:45:07 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 02589-03 for ; Sat, 12 Jan 2008 08:44:53 -0400 (AST) Received: from bernhard.xss.co.at (bernhard.xss.co.at [193.80.108.69]) by postgresql.org (Postfix) with ESMTP id 06D6C2E3F34 for ; Sat, 12 Jan 2008 08:44:59 -0400 (AST) Received: from goethe.intern.xss.co.at (goethe.intern.xss.co.at [192.168.162.10]) by bernhard.xss.co.at (8.13.8/8.13.8) with ESMTP id m0CCitHc011569 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK) for ; Sat, 12 Jan 2008 13:44:57 +0100 Received: from [192.168.162.51] (ws1.intern.xss.co.at [192.168.162.51]) (authenticated bits=0) by goethe.intern.xss.co.at (8.13.8/8.13.8) with ESMTP id m0CCitVd017588 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO) for ; Sat, 12 Jan 2008 13:44:55 +0100 Message-ID: <4788B647.1050600@xss.co.at> Date: Sat, 12 Jan 2008 13:44:55 +0100 From: Andreas Haumer Organization: xS+S User-Agent: Thunderbird 2.0.0.9 (X11/20071031) MIME-Version: 1.0 To: pgsql-admin@postgresql.org Subject: Restore dump into different databases/owners X-Enigmail-Version: 0.95.3 Content-Type: text/plain; charset=ISO-8859-15 Content-Transfer-Encoding: 7bit X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200801/129 X-Sequence-Number: 27903 -----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@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-----