From: | Andreas Haumer <andreas(at)xss(dot)co(dot)at> |
---|---|
To: | pgsql-admin(at)postgresql(dot)org |
Subject: | Restore dump into different databases/owners |
Date: | 2008-01-12 12:44:55 |
Message-ID: | 4788B647.1050600@xss.co.at |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
-----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(at)xss(dot)co(dot)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-----
From | Date | Subject | |
---|---|---|---|
Next Message | Andy Shellam | 2008-01-12 16:18:08 | Re: 8.3 RC1 - Logging and filenames |
Previous Message | Shane Ambler | 2008-01-12 10:30:44 | Re: SQL question: Highest column value of unique column pairs |