Thread: pg_dump vs schemas

pg_dump by default puts at the top
SET search_path = public,pg_catalog;

This considering a plain vanilla setup where no schemas other than public
have been created.

I however noticed that pg_dump also does this:
ALTER TABLE public.mytable OWNER TO pgsql;

Shouldn't the "public." be left out?
I verified that even if multiple tables exist with the same name only the
table in the first referenced schema in the path will be deleted.

By the same token shouldn't all references to schemas be left out?
In the case there are reasons why the schema is referenced, perhaps create a
parameter in pg_dump to omit the schema.

The rationale is to be able to easily move schemas in the target restore.
Specially if one was doing an entire database.

Alternatively is there any easy way to take all data in one schema and load
it into a target DB and a different schema?
The default produced by pg_dump would be a problem because of the "schema."
references.


As for why I am doing this schema move..
From what i can tell it may be best to have tsearch into it's own schema so
I either move tsearch out of public, or my data out of public. I figure
since public is what tsearch and other utilities like it target may be
easier to move the data out of public.

Currently trying a small data set to see how this work and whether it is
better to move the data out of public or tsearch.

On Jul 13, 2007, at 19:10 , Francisco Reyes wrote:

> Alternatively is there any easy way to take all data in one schema
> and load it into a target DB and a different schema?

You might try using the -n flag with pg_dump, replace schema1 with
schema2 in the dump file, and loading the altered dump into the new
database. There may also be some tricks you can play with pg_restore
(on a dump file created with pg_dump -Fc), though I don't know
specifically what offhand.


Michael Glaesemann
grzm seespotcode net

Francisco Reyes <lists@stringsutils.com> writes:
> I however noticed that pg_dump also does this:
> ALTER TABLE public.mytable OWNER TO pgsql;

> Shouldn't the "public." be left out?

Probably in an ideal world, but that code is far from ideal --- it's
dealing with a bunch of considerations including compatibility with dump
files from old pg_dump versions with assorted bugs.  I'm hesitant to
remove the schema spec for fear we'd end up with underspecified output
in some corner case or other.

            regards, tom lane

Tom Lane writes:

>> Shouldn't the "public." be left out?
>
>  I'm hesitant to  remove the schema spec for fear we'd end up with underspecified output
> in some corner case or other.

Totally understand. How about making it an option?
Just like the --no-owner option. There are options that one may rarely use,
but that can be very usefull for certain type of specialized restores.

On Fri, 2007-07-13 at 20:06 -0500, Michael Glaesemann wrote:
> On Jul 13, 2007, at 19:10 , Francisco Reyes wrote:
>
> > Alternatively is there any easy way to take all data in one schema
> > and load it into a target DB and a different schema?
>
> You might try using the -n flag with pg_dump, replace schema1 with
> schema2 in the dump file, and loading the altered dump into the new
> database. There may also be some tricks you can play with pg_restore
> (on a dump file created with pg_dump -Fc), though I don't know
> specifically what offhand.
>

I would find it helpful if there were an easy way to rename objects
(specifically schemas) during the restore process.

Let's say I have a development database, and I want to copy the entire
schema myapp_dev1 to schema myapp_dev2 on the same database. Currently,
it's fairly awkward to do that.

How do other people do that? Is it worth trying to add a way for
pg_restore to rename object? Or what about an option so pg_restore will
not emit the the schema name at all, and the user who restores is can
just set their search_path to where they want all the objects to go?

Regards,
    Jeff Davis

Jeff Davis writes:

> Let's say I have a development database, and I want to copy the entire
> schema myapp_dev1 to schema myapp_dev2 on the same database. Currently,
> it's fairly awkward to do that.

One possible way may be:
Dump the source schema.
Remove references to the schema name in the pg_dump file.
Change search_path and restore schema.

Not sure how this would work with the special Fc format. It should work with
the ASCII format.


> How do other people do that? Is it worth trying to add a way for
> pg_restore to rename object?

In the particular case that you mentioned, if pg_dump could be made to not
include the schema name anywhere that should make the process easier.

On Mon, 2007-07-16 at 14:58 -0400, Francisco Reyes wrote:
> One possible way may be:
> Dump the source schema.
> Remove references to the schema name in the pg_dump file.
> Change search_path and restore schema.

That's what I currently do. It seems a little flimsy though: there are
too many objects to really remove the references by hand, so we do a
global search-and-replace. As long as the schema name is unique enough,
I suppose it's alright for a development (non-production) database.

> > How do other people do that? Is it worth trying to add a way for
> > pg_restore to rename object?
>
> In the particular case that you mentioned, if pg_dump could be made to not
> include the schema name anywhere that should make the process easier.

That's what I was thinking. It might be better placed in pg_restore
though, so that way you can decide after you've already made the backup.

Regards,
    Jeff Davis