Re: schema-only -n option in pg_restore fails

On Thu, Oct 9, 2014 at 6:19 PM, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>
> On 10/09/2014 12:36 PM, Josh Berkus wrote:
> > Summary: pg_restore -n attempts to restore objects to pg_catalog schema
> > Versions Tested: 9.3.5, 9.3.0, 9.2.4
>
> Explored this some with Andrew offlist. Turns out this is going to be a
> PITA to fix, so it should go on the big pile of TODOs for when we
> overhaul search_path.
>
> Here's what's happening under the hood, pg_restore generates this SQL
text:
>
> SET search_path = schem_a, pg_catalog;
> CREATE TABLE tab_a (
> test text
> );
>
> Since schem_a doesn't exist, it's skipped over and pg_restore attempts
> to create the objects in pg_catalog. So this is Yet Another Issue
> caused by the ten meter tall tar baby which is search_path.
>
> So, my proposal for a resolution:
>
> 1) In current versions, patch the docs to explicitly say that -n does
> not create the schema, and that if the user doesn't create the schema
> pg_restore will fail.
>
> 2) Patch 9.5's pg_restore to do "CREATE SCHEMA IF NOT EXISTS" when -n is
> used. This will be 100% backwards-compatible with current behavior.
>

I agree with this solution. Always when I restore some schema from a dump I
need to create schemas before and it's sucks.

I'm working on the 2th item [1] together with other friend (Sebastian, in
cc) to introduce him into the PostgreSQL development process.

We'll register soon to the next commitfest.

Regards,

[1]
https://github.com/fabriziomello/postgres/tree/create_schema_during_pg_restore_schema_only

--
Fabrízio de Royes Mello
Consultoria/Coaching PostgreSQL
>> Timbira: http://www.timbira.com.br
>> Blog: http://fabriziomello.github.io
>> Linkedin: http://br.linkedin.com/in/fabriziomello
>> Twitter: http://twitter.com/fabriziomello
>> Github: http://github.com/fabriziomello