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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
8 messages Options
Reply | Threaded
Open this post in threaded view
|

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.




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

Reply | Threaded
Open this post in threaded view
|

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

David G Johnston
On Fri, Mar 9, 2018 at 6:26 AM, Aldrin Martoq Ahumada <[hidden email]> wrote:
Thinking in the long term, how could be the best way to clone a schema into another?

Depends on why you are cloning schemas.

Generally not cloning is the best bet - instead place the reference schema into version control and simply install it whenever necessary.  Parameterize the installation script.

​Or, clone into a different database and leave the namespace the same.

pg_dump, as currently written, is tasked with recreating the original database exactly and safely.  If you need it to do something different you could teach it the desired behavior and add some relevant command line switches to enable said behavior.

David J.

Reply | Threaded
Open this post in threaded view
|

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

Andre Oliveira Freitas
In reply to this post by Aldrin Martoq Ahumada
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 <[hidden email]>:

> 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

[hidden email]
www.callix.com.br

Reply | Threaded
Open this post in threaded view
|

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

Aldrin Martoq Ahumada
Hi Andre,


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 <[hidden email]> 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 <[hidden email]>:
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

[hidden email]
www.callix.com.br

Reply | Threaded
Open this post in threaded view
|

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

Melvin Davidson-5


On Fri, Mar 9, 2018 at 10:00 AM, Aldrin Martoq Ahumada <[hidden email]> wrote:
Hi Andre,


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 <[hidden email]> 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 <[hidden email]>:
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

[hidden email]
www.callix.com.br


 >...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.


--
Melvin Davidson
Maj. Database & Exploration Specialist

Universe Exploration Command – UXC

Employment by invitation only!

clone_schema.sql (18K) Download Attachment
Reply | Threaded
Open this post in threaded view
|

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

Aldrin Martoq Ahumada


On Mar 9, 2018, at 12:15 PM, Melvin Davidson <[hidden email]> wrote:
On Fri, Mar 9, 2018 at 10:00 AM, Aldrin Martoq Ahumada <[hidden email]> wrote:
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.


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.


I think the solution for the long term is to add a flag to pg_dump, but in the short/mid term we’ll have to replicate some form of your script into the gem.


Cheers,
Aldrin.

PS: I’ve added initial support for PostgreSQL 10 to clone_schema here, but I have no time to test it well:


Reply | Threaded
Open this post in threaded view
|

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

Melvin Davidson-5



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!
Reply | Threaded
Open this post in threaded view
|

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

Melvin Davidson-5

On Tue, Mar 13, 2018 at 1:47 PM, Melvin Davidson <[hidden email]> wrote:



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!

Aldrin,

I've solved the problem with the sequences.

The attached clone_schema_10.sql has been tested on my system and now works.
Let me know if you find any bugs. As you also said it is not robust enough, Please also let me know what additional features you think it needs.


--
Melvin Davidson
Maj. Database & Exploration Specialist

Universe Exploration Command – UXC

Employment by invitation only!

clone_schema_10.sql (18K) Download Attachment
Previous Thread Next Thread