Re: Copying databases with extensions - pg_dump question

On 21/01/2011 14:39, Bill Moran wrote:
> In response to Ivan Voras<ivoras@freebsd.org>:
>
>> A fairly frequent operation I do is copying a database between servers,
>> for which I use pg_dump. Since the database contains some extensions -
>> most notably hstore and tsearch2, which need superuser privileges to
>> install, I have a sort of a chicken-and-egg problem: the owner of the
>> database (and all its objects) should be a non-superuser account so I
>> can't simply use the output from pg_dump and expect everything to be
>> correct after restoring it.
>
> Why not?  If the ownership on the original database is non-superuser, then
> that will be faithfully preserved when the database is restored.  What
> are you doing to cause it to behave differently?

I have reviewed my operations and it looks like these are the important
differences:

* The database copy might be from a development machine to production so
I use pg_dump -O to remove any accidentally entered unwanted user
ownership data
* The database restore on the target machine is done as a nonprivileged
user (the target owner of the database)

Are there better ways to do this?