Thread: Postgresql upgrade to 9.5.12/10.3 changes pg_dump format for cloningschemas

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

Hi Andre,

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.

Cheers,

On Mar 9, 2018, at 11:51 AM, Andre Oliveira Freitas <afreitas@callixbrasil.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.martoq@gmail.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@callix.com.br
www.callix.com.br

Thank you Melvin, I forgot to mention I've already found your script before I asked here, but I didn’t think it was robust enough (please don't offend :-). Particularly, it didn't work well on PostgreSQL 10.

Thank you Melvin, I forgot to mention I've already found your script before I asked here, but I didn’t think it was robust enough (please don't offend :-). Particularly, it didn't work well on PostgreSQL 10.

Aldrin,

I apologize. I just tested and found that the reason it is failing is because, once again, the catalogs have been changed. In this case the structure of sequences.

I am trying to find a workaround. I will get back to you when I do.


--

Melvin Davidson
Maj. Database & Exploration Specialist
Universe Exploration Command – UXC
Employment by invitation only!