Re: Postgresql upgrade to 9.5.12/10.3 changes pg_dump format forcloning schemas

On Mar 9, 2018, at 12:15 PM, Melvin Davidson <melvin6925@gmail.com> wrote:

On Fri, Mar 9, 2018 at 10:00 AM, Aldrin Martoq Ahumada <aldrin.martoq@gmail.com> wrote:

Yes, here is the issue: https://github.com/influitive/apartment/issues/532

It happens if you configured apartment with use_sql=true, which means it clones the schema from pg_dump. My first attempt was to “fix” the script generated by pg_dump, but I feel it will be a mess. We solved our issue going back to use_sql=false, which is the default (creates the schema from db/schema.rb). But there is people that have other requirements, like functions, so the easier way for them is to keep use_sql and replace strings in the script.

 >...how could be the best way to clone a schema into another?
 
 The safest way is to use pgdump -F p -n <the_schema_name> > schema.sql
 Then edit schema.sql and change all references to old_schema name to new_schema name.
 Finally, use psql < schema.sql to create the new_schema.
 
 That being said, a year ago I optimized a function originally written by Emanuel '3manuek'
 called clone_schema, which is added to the public schema. It clones all sequences, tables,
 indexes, rules, triggers, data(optional), views & functions from any existing schema to a
 new  schema
 SAMPLE CALL:
 SELECT clone_schema('public', 'new_schema', TRUE);
 
 I've attached it for your convenience.
 disclaimer: I do not accept any responsibility for any unknow bugs in the function.
 Test first and use at your own risk.