Re: schema-only -n option in pg_restore fails

<div dir="ltr"><div class="gmail_extra"><br />On Thu, Oct 9, 2014 at 6:19 PM, Josh Berkus <<a
href="mailto:josh@agliodbs.com">josh@agliodbs.com</a>>wrote:<br />><br />> On 10/09/2014 12:36 PM, Josh Berkus
wrote:<br/>> > Summary: pg_restore -n attempts to restore objects to pg_catalog schema<br />> > Versions
Tested:9.3.5, 9.3.0, 9.2.4<br />><br />> Explored this some with Andrew offlist.  Turns out this is going to be
a<br/>> PITA to fix, so it should go on the big pile of TODOs for when we<br />> overhaul search_path.<br
/>><br/>> Here's what's happening under the hood, pg_restore generates this SQL text:<br />><br />> SET
search_path= schem_a, pg_catalog;<br />> CREATE TABLE tab_a (<br />>      test text<br />> );<br />><br
/>>Since schem_a doesn't exist, it's skipped over and pg_restore attempts<br />> to create the objects in
pg_catalog. So this is Yet Another Issue<br />> caused by the ten meter tall tar baby which is search_path.<br
/>><br/>> So, my proposal for a resolution:<br />><br />> 1) In current versions, patch the docs to
explicitlysay that -n does<br />> not create the schema, and that if the user doesn't create the schema<br />>
pg_restorewill fail.<br />><br />> 2) Patch 9.5's pg_restore to do "CREATE SCHEMA IF NOT EXISTS" when -n is<br
/>>used.  This will be 100% backwards-compatible with current behavior.<br />><br /><br /></div><div
class="gmail_extra">Iagree with this solution. Always when I restore some schema from a dump I need to create schemas
beforeand it's sucks.<br /></div><div class="gmail_extra"><br /></div><div class="gmail_extra">I'm working on the 2th
item[1] together with other friend (Sebastian, in cc) to introduce him into the PostgreSQL development process.<br
/></div><divclass="gmail_extra"><br /></div><div class="gmail_extra">We'll register soon to the next commitfest.<br
/></div><divclass="gmail_extra"><br /></div><div class="gmail_extra">Regards,<br /></div><div class="gmail_extra"><br
/>[1]<a
href="https://github.com/fabriziomello/postgres/tree/create_schema_during_pg_restore_schema_only">https://github.com/fabriziomello/postgres/tree/create_schema_during_pg_restore_schema_only</a><br
/><br/>--<br />Fabrízio de Royes Mello<br />Consultoria/Coaching PostgreSQL<br />>> Timbira: <a
href="http://www.timbira.com.br">http://www.timbira.com.br</a><br/>>> Blog: <a
href="http://fabriziomello.github.io">http://fabriziomello.github.io</a><br/>>> Linkedin: <a
href="http://br.linkedin.com/in/fabriziomello">http://br.linkedin.com/in/fabriziomello</a><br/>>> Twitter: <a
href="http://twitter.com/fabriziomello">http://twitter.com/fabriziomello</a><br/>>> Github: <a
href="http://github.com/fabriziomello">http://github.com/fabriziomello</a></div></div>