On Thu, Oct 9, 2014 at 6:19 PM, Josh Berkus <j...@agliodbs.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
