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

[Melvin Davidson]

On Tue, Mar 13, 2018 at 1:47 PM, Melvin Davidson 
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!

-- Function: public.clone_schema(text, text, boolean)

-- DROP FUNCTION public.clone_schema(text, text, boolean);

CREATE OR REPLACE FUNCTION public.clone_schema(
source_schema text,
dest_schema text,
include_recs boolean)
  RETURNS void AS
$BODY$
--  Initial code by Emanuel '3manuek' 
--  Last revision 2017-04-17 by Melvin Davidson
--  Added SELECT REPLACE for schema views
--
--  This function will clone 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);

DECLARE
  src_oid  oid;
  tbl_oid  oid;
  seq_oid  oid;
  func_oid oid;
  owner_id oid;
  con_oid  oid;
  v_path   text;
  v_func   text;
  v_args   text;
  v_connametext;
  v_rule   text;
  v_trig   text;
  object   text;
  buffer   text;
  srctbl   text;
  default_ text;
  v_column text;
  qry  text;
  dest_qry text;
  v_deftext;
  v_stat   integer;
  seqval   bigint;
  seq_name name;
  sq_type  oid;
  sq_last_valuebigint;
  sq_max_value bigint;
  sq_start_value   bigint;
  sq_increment_by  bigint;
  sq_min_value bigint;
  sq_cache_value   bigint;
  sq_log_cnt   bigint;
--  sq_is_called boolean;
  sq_is_cycled boolean;
  sq_typname   name;
  seq_cycled   text;
  seq_ownername;  
--  sq_cycledchar(10);
  
BEGIN

-- Check that source_schema exists
  SELECT oid INTO src_oid
FROM pg_namespace
   WHERE nspname = quote_ident(source_schema);
   
  IF NOT FOUND
THEN 
  RAISE NOTICE 'source schema % does not exist!', source_schema;
  RETURN ;
  END IF;

  -- Check that dest_schema does not yet exist
  PERFORM nspname 
FROM pg_namespace
   WHERE nspname = quote_ident(dest_schema);
   
  IF FOUND
THEN 
  RAISE NOTICE 'dest schema % already exists!', dest_schema;
  RETURN ;
  END IF;

  EXECUTE 'CREATE SCHEMA ' || quote_ident(dest_schema) ;
   
  -- Add schema comment
  SELECT description INTO v_def
FROM pg_description
   WHERE objoid = src_oid
 AND objsubid = 0;

  IF FOUND 
THEN 
  EXECUTE 'COMMENT ON SCHEMA ' || quote_ident(dest_schema) || ' IS ' || 
quote_literal(v_def);
  END IF;
  
  -- Create sequences
  -- TODO: Find a way to make this sequence's owner is the correct table.
  FOR object IN
 SELECT sequence_name::text 
  FROM information_schema.sequences
 WHERE sequence_schema = quote_ident(source_schema)

  LOOP

SELECT oid, relowner into seq_oid, owner_id
  FROM pg_class 
 WHERE relname = quote_ident(object)
   AND relkind = 'S';

SELECT seqtypid,
   seqstart,
   seqincrement,
   seqmax,
   seqmin,
   seqcache,
   seqcycle
  INTO sq_type, 
   sq_start_value,
   sq_increment_by,
   sq_max_value,
   sq_min_value,
   sq_cache_value,
   sq_is_cycled
  FROM pg_sequence
 WHERE seqrelid = seq_oid;
 
SELECT typname INTO sq_typname
  FROM pg_type 
 WHERE oid = sq_type;

SELECT rolname INTO seq_owner
  FROM pg_authid WHERE oid = owner_id;

--EXECUTE 'CREATE SEQUENCE ' || quote_ident(dest_schema) || '.' || 
quote_ident(object);
--srctbl := quote_ident(source_schema) || '.' || quote_ident(object);

IF sq_is_cycled 
  THEN 
seq_cycled = ' CYCLE'; 
  ELSE 
seq_cycled = ' NO CYCLE';
END IF;

EXECUTE 'CREATE SEQUENCE ' || quote_ident(dest_schema) || '.' || 
quote_ident(object)
 

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

[Melvin Davidson]

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

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  wrote:
> On Fri, Mar 9, 2018 at 10:00 AM, Aldrin Martoq Ahumada 
> mailto:aldrin.mar...@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  > 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:
https://gist.github.com/aldrinmartoq/5df0aa03f86f3ad03982c793753c04a1 

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

[Melvin Davidson]

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

> 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 <
> afrei...@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  >:
>
> 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
>
> afrei...@callix.com.br
> 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  > 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 DavidsonMaj. Database & Exploration SpecialistUniverse Exploration
Command – UXC*
Employment by invitation only!
-- Function: public.clone_schema(text, text, boolean)

-- DROP FUNCTION public.clone_schema(text, text, boolean);

CREATE OR REPLACE FUNCTION public.clone_schema(
source_schema text,
dest_schema text,
include_recs boolean)
  RETURNS void AS
$BODY$
--  Initial code by Emanuel '3manuek' 
--  Last revision 2017-04-17 by Melvin Davidson
--  Added SELECT REPLACE for schema views
--
--  This function will clone 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);

DECLARE
  src_oid  oid;
  tbl_oid  oid;
  func_oid oid;
  con_oid  oid;
  v_path   text;
  v_func   text;
  v_args   text;
  v_connametext;
  v_rule   text;
  v_trig   text;
  object   text;
  buffer   text;
  srctbl   text;
  default_ text;
  v_column text;
  qry  text;
  dest_qry text;
  v_deftext;
  v_stat   integer;
  seqval   bigint;
  sq_last_valuebigint;
  sq_max_value bigint;
  sq_start_value   bigint;
  sq_increment_by  bigint;
  sq_min_value bigint;
  sq_cache_value   bigint;
  sq_log_cnt   bigint;
  sq_is_called boolean;
  sq_is_cycled boolean;
  sq_cycledchar(10);

BEGIN

-- Check that source_schema exists
  SELECT oid INTO src_oid
FROM pg_namespace
   WHERE nspname = quote_ident(source_schema);
  IF NOT FOUND
THEN 
RAISE NOTICE 'source schema % does not exist!', source_schema;
RETURN ;
  END I

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

[Aldrin Martoq Ahumada]

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 
>  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 :
>> 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
> 
> afrei...@callix.com.br
> www.callix.com.br

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

[Andre Oliveira Freitas]

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

afrei...@callix.com.br
www.callix.com.br

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 <
aldrin.mar...@gmail.com> 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.