Postgresql upgrade to 9.5.12/10.3 changes pg_dump format for cloning schemas

[Aldrin Martoq Ahumada]

Hi,

For a multi tenant system, we are using the following command to blindly clone 
a schema into another:
pg_dump -s -x -O -n #{default_tenant} #{dbname}


This is done for us by a rails gem, which then feeds that script into the new 
created schema for the new tenant.
https://github.com/influitive/apartment/blob/80a21f2e1cdcbe5b0bd976f88c14332657804536/lib/apartment/adapters/postgresql_adapter.rb#L150
 
<https://github.com/influitive/apartment/blob/80a21f2e1cdcbe5b0bd976f88c14332657804536/lib/apartment/adapters/postgresql_adapter.rb#L150>




When we upgraded from 9.5.11 to 9.5.12, the format of the dump changed (it now 
always appends the schema name), so this is broken. We could patch the SQL 
generated, but that’s not a generic/robust solution.

# pg_dump postgresql 9.5.11
SET search_path = public, pg_catalog;
CREATE TABLE ahoy_events (
    id bigint NOT NULL,
    visit_id integer,
    user_id integer,
    name character varying,
    properties jsonb,
    "time" timestamp without time zone
);

# pg_dump postgresql 9.5.12
CREATE TABLE public.ahoy_events (
    id bigint NOT NULL,
    visit_id integer,
    user_id integer,
    name character varying,
    properties jsonb,
    "time" timestamp without time zone
);



Thinking in the long term, how could be the best way to clone a schema into 
another?




— 
Aldrin