Received: from localhost (unknown [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 7A3BB2E0084 for ; Sat, 12 Jan 2008 12:25:54 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 22008-09 for ; Sat, 12 Jan 2008 12:25:46 -0400 (AST) Received: from bernhard.xss.co.at (bernhard.xss.co.at [193.80.108.69]) by postgresql.org (Postfix) with ESMTP id A206A2E0082 for ; Sat, 12 Jan 2008 12:25:46 -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 m0CGPj6s017989 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=OK) for ; Sat, 12 Jan 2008 17:25:45 +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 m0CGPjLA005716 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO) for ; Sat, 12 Jan 2008 17:25:45 +0100 Message-ID: <4788EA09.4050808@xss.co.at> Date: Sat, 12 Jan 2008 17:25:45 +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: Re: Restore dump into different databases/owners References: <4788B647.1050600@xss.co.at> In-Reply-To: <4788B647.1050600@xss.co.at> 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/131 X-Sequence-Number: 27905 -----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@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 iD8DBQFHiOoDxJmyeGcXPhERAsIJAJ9yKVhytenhqqTHgQeN6WGlDDm3ogCfU9PZ +wX0CPDrdH/ccLd4b6+plJ4= =tZpa -----END PGP SIGNATURE-----