From: Andreas Haumer <andreas@xss.co.at>
To: pgsql-admin@postgresql.org
Subject: Re: Restore dump into different databases/owners
Date: Sat, 12 Jan 2008 17:25:45 +0100
Message-ID: <4788EA09.4050808@xss.co.at> (raw)
In-Reply-To: <4788B647.1050600@xss.co.at>
References: <4788B647.1050600@xss.co.at>
-----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-----
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: andreas@xss.co.at
Subject: Re: Restore dump into different databases/owners
In-Reply-To: <4788EA09.4050808@xss.co.at>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox