pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Andreas Haumer <andreas@xss.co.at>
To: pgsql-admin@postgresql.org
Subject: Restore dump into different databases/owners
Date: Sat, 12 Jan 2008 13:44:55 +0100
Message-ID: <4788B647.1050600@xss.co.at> (raw)

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



view thread (5+ messages)  latest in thread

Message-ID: <4788B647.1050600@xss.co.at>
Permalink:  ../4788B647.1050600@xss.co.at/
Also on:    postgresql.org/message-id/4788B647.1050600@xss.co.at

 · 

reply

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: <4788B647.1050600@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