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

Hi Andre,

Yes, here is the issue: https://github.com/influitive/apartment/issues/532 <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.

Cheers,

> On Mar 9, 2018, at 11:51 AM, Andre Oliveira Freitas <afreitas(at)callixbrasil(dot)com> wrote:
>
> Hello Aldrin,
>
> I'm also using apartment with postgresql 9.6.6, and I don't see any
> issue with it. Are you using Apartment::Tenant.create?
>
> 2018-03-09 10:26 GMT-03:00 Aldrin Martoq Ahumada <aldrin(dot)martoq(at)gmail(dot)com>:
>> 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
>>
>>
>>
>>
>> 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
>>
>
>
>
> --
>
> André Luis O. Freitas
> System Architect
>
> Rua do Rócio, 220 - Cj. 72
> São Paulo - SP - 04552-000
> 55 11 4063 4222
>
> afreitas(at)callix(dot)com(dot)br
> www.callix.com.br