Skip site navigation (1) Skip section navigation (2)

Re: Restore dump into different databases/owners

From: Andreas Haumer <andreas(at)xss(dot)co(dot)at>
To: pgsql-admin(at)postgresql(dot)org
Subject: Re: Restore dump into different databases/owners
Date: 2008-01-12 16:25:45
Message-ID: (view raw or whole thread)
Lists: pgsql-admin
Hash: SHA1


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


- - andreas

- --
Andreas Haumer                     | mailto:andreas(at)xss(dot)co(dot)at
*x Software + Systeme              |
Karmarschgasse 51/2/20             | Tel: +43-1-6060114-0
A-1100 Vienna, Austria             | Fax: +43-1-6060114-71
Version: GnuPG v1.4.7 (GNU/Linux)
Comment: Using GnuPG with Mozilla -


In response to


pgsql-admin by date

Next:From: Tom LaneDate: 2008-01-12 17:15:25
Subject: Re: Restore dump into different databases/owners
Previous:From: Andy ShellamDate: 2008-01-12 16:18:08
Subject: Re: 8.3 RC1 - Logging and filenames

Privacy Policy | About PostgreSQL
Copyright © 1996-2015 The PostgreSQL Global Development Group