Re: Schema-qualified statements in pg_dump output

On Mon, 2008-07-07 at 15:46 +0200, Bernd Helmle wrote:
> There's a behavior in pg_dump that annoyed me a little bit, the last few
> times i had to deal with it:
>
> Consider you have to dump a specific namespace only, you are going to use
>
> pg_dump -n <your_schema> [-t <tables>].
>
> I found it a common use case to restore this dump into a different schema
> by simply changing the search_path. With included ownerships this doesn't
> work, since pg_dump always outputs the necessary DDL as follows:
>
> ALTER TABLE bernd.foo OWNER TO bernd;
>
> Okay, it isn't too hard to use sed to replace the necessary statements to
> use the correct schema, but i think it would be much nicer if pg_dump would
> omit the schema-qualified table name here. I'd like to create a patch for
> this, if we agree on changing this behavior?

The use case you mention is something that would be of value to many
people, and I support your efforts to add a new option for this.

No useful workarounds exist without flaws: i) editing with sed might
well end up editing character data in the table(s) at the same time and
you may never even notice. ii) reloading to the same schema (renaming
etc) is not acceptable if the target has a production schema of that
name already. iii) manually editing a large file is problematic.

Tom's posted comments that you need to look at all of the places the
schemaname is used to see what we will need/not need to change. It's
more than just altering the owner, but that doesn't mean we don't want
it or its impossible.

Please pursue this further.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support