Re: Copying databases with extensions - pg_dump question

In response to Ivan Voras <ivoras@freebsd.org>:

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

In my experience, the answer is yes and no.

On the "yes" side, I would force developers to keep schema information in
sql files in subversion.  Then "copy" your database using a two-step
process (schema and data separately).  If your developers are not already
doing this, then there will likely be a lot of resistance, but it's
worth it in the long run.  You can use placeholders in the schema file
for things like ROLE_OWNER, etc ... then use sed or any number of
simple tools to replace those placeholders as needed.  At work, we
have some pretty awesome tools to make this easier that we're trying to
open-source for PGCon.

On the "no" side, doing this kind of thing is always complex.  We have a
slew of other, very specialized scripts that do things like convert a
production database to a development database by sanitizing sensitive
data, or automatically deploy new database or upgrades to either our
production, development, or lab environments.  Build tools like phing,
make, ant, etc make this kind of thing easier to create, but it's still
a lot of work because each situation is special.

So, my overall answer is that you're probably on the right track, you
probably don't realize how much work is involved to get this really
working well, and I would change just a few things about your approach
based on the information you've provided so far.  The requirement to
use an unprivileged user to restore is going to make the tools you
use to prepare the input files more important, but that's not insurmountable
if you keep control over them (i.e. use schema files and a tool for
normalizing them and do the data dump separately)

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/