Re: Clone PostgreSQL schema

Melvin Davidson <melvin6925@xxxxxxxxx> · Mon, 17 Apr 2017 11:21:44 -0400

On Mon, Apr 17, 2017 at 11:02 AM, Melvin Davidson <melvin6925@xxxxxxxxx> wrote:

On Mon, Apr 17, 2017 at 9:42 AM, Melvin Davidson <melvin6925@xxxxxxxxx> wrote:

On Mon, Apr 17, 2017 at 2:20 AM, R. Reiterer <r.reiterer@xxxxxxxxx> wrote:
Hi Melvin,

thanks again for your help! I did some testing, but views in the new schema still refer to the old schema.

Regards, Reinhard

Am 17.04.2017 04:53 schrieb Melvin Davidson:

On Sun, Apr 16, 2017 at 4:42 PM, R. Reiterer <r.reiterer@xxxxxxxxx>

wrote:

Unfortunately, I do not have the skills to improve the function.

Maybe someone at dba.stackexchange.com [1] can help me. I'll open a

ticket. I hope this is okay for you.

Am 16.04.2017 22:31 schrieb Melvin Davidson:

I missed to note that this is a VIEW issue (?)

AH, IN THAT CASE, YOU ARE RIGHT. I AM RETIRED, BUT IF YOU WISH, GO

AHEAD AND APPLY THE FIX YOURSELF. JUST MAKE A COMMENT AT THE TOP TO

INCLUDE THE DATE, YOUR NAME AND THE FIX.

On Sun, Apr 16, 2017 at 4:24 PM, R. Reiterer <r.reiterer@xxxxxxxxx>

wrote:

Hi Melvin,

thanks for your reply. I missed to note that this is a VIEW issue

(?). After duplicating a schema, views in the cloned schema

(schema_new) refer still to the source schema (schema_old) in the

FROM clause:

View in cloned schema (schema_new) -->

CREATE VIEW schema_new.my_view AS

SELECT *

FROM schema_old.my_table;

To me 'FROM schema_new.my_table' would be more logical.

Regards, Reinhard

Am 16.04.2017 22:12 schrieb Melvin Davidson:

On Sun, Apr 16, 2017 at 3:20 PM, R. Reiterer <r.reiterer@xxxxxxxxx>

wrote:

Hi Melvin,

I use your PL/pgSQL function posted at

https://www.postgresql.org/message-id/CANu8FixK9P8UD43nv2s%2Bc-9jHkyy8wPUc_f5K3HcrmJYVPr-wQ%40mail.gmail.com

[2]

[1]

[1] to clone schemas in PostgreSQL databases. Many thanks for your

work!

I noticed that in cloned schemas the schema name isn't updated in

the FROM clause:

schema_old -->

CREATE VIEW schema_old.my_view AS

SELECT *

FROM schema_old.my_table;

schema_new -->

CREATE VIEW schema_new.my_view AS

SELECT *

FROM schema_old.my_table;

Are you interessted to fix this?

Regards,

Reinhard

FIRST, THANK YOU FOR THE COMPLEMENT.

However, AFAIC, there is nothing to "fix" with regards to cloning

schema name. In a database, you cannot have two schemas with the

same

name,

so what would be the point? If you want to "clone" to a different

database, then just use pg_dump and pg_restore.

--

MELVIN DAVIDSON

I reserve the right to fantasize. Whether or not you

wish to share my fantasy is entirely up to you.

Links:

------

[1]

https://www.postgresql.org/message-id/CANu8FixK9P8UD43nv2s%2Bc-9jHkyy8wPUc_f5K3HcrmJYVPr-wQ%40mail.gmail.com

[2]

[1]

--

MELVIN DAVIDSON

I reserve the right to fantasize. Whether or not you

wish to share my fantasy is entirely up to you.

Links:

------

[1]

https://www.postgresql.org/message-id/CANu8FixK9P8UD43nv2s%2Bc-9jHkyy8wPUc_f5K3HcrmJYVPr-wQ%40mail.gmail.com

[2]

Reinhard,

After reviewing things, I note it's possible that you downloaded an

earlier version that had some errors in it and was not as complete.

Therefore, I've attached the latest, more complete version of the

function. Please let me know if this solves the problem.

--

MELVIN DAVIDSON

I reserve the right to fantasize.  Whether or not you

 wish to share my fantasy is entirely up to you.

Links:

------

[1] http://dba.stackexchange.com

[2]

https://www.postgresql.org/message-id/CANu8FixK9P8UD43nv2s%2Bc-9jHkyy8wPUc_f5K3HcrmJYVPr-wQ%40mail.gmail.com

My apologies,

I though I had had a fix. I even worked on it a couple of hours this morning, but it seems it's a bit trickier than I thought. I'll keep trying
until I get it right.

-- 
Melvin Davidson
I reserve the right to fantasize.  Whether or not you 
 wish to share my fantasy is entirely up to you. 

OK Reinhard, I think I have it, please try the revision I have attached.

-- 
Melvin Davidson
I reserve the right to fantasize.  Whether or not you 
 wish to share my fantasy is entirely up to you. 

Oops, I forgot to remove the premature RETURN, use this latest attached.

-- 
Melvin Davidson
I reserve the right to fantasize.  Whether or not you 
 wish to share my fantasy is entirely up to you. 

-- 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_conname        text;
  v_rule           text;
  v_trig           text;
  object           text;
  buffer           text;
  srctbl           text;
  default_         text;
  v_column         text;
  qry              text;
  dest_qry         text;
  v_def            text;
  v_stat           integer;
  seqval           bigint;
  sq_last_value    bigint;
  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_cycled        char(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
    EXECUTE 'CREATE SEQUENCE ' || quote_ident(dest_schema) || '.' || quote_ident(object);
    srctbl := quote_ident(source_schema) || '.' || quote_ident(object);

    EXECUTE 'SELECT last_value, max_value, start_value, increment_by, min_value, cache_value, log_cnt, is_cycled, is_called 
              FROM ' || quote_ident(source_schema) || '.' || quote_ident(object) || ';' 
              INTO sq_last_value, sq_max_value, sq_start_value, sq_increment_by, sq_min_value, sq_cache_value, sq_log_cnt, sq_is_cycled, sq_is_called ; 

    IF sq_is_cycled 
      THEN 
        sq_cycled := 'CYCLE';
    ELSE
        sq_cycled := 'NO CYCLE';
    END IF;

    EXECUTE 'ALTER SEQUENCE '   || quote_ident(dest_schema) || '.' || quote_ident(object) 
            || ' INCREMENT BY ' || sq_increment_by
            || ' MINVALUE '     || sq_min_value 
            || ' MAXVALUE '     || sq_max_value
            || ' START WITH '   || sq_start_value
            || ' RESTART '      || sq_min_value 
            || ' CACHE '        || sq_cache_value 
            || sq_cycled || ' ;' ;

    buffer := quote_ident(dest_schema) || '.' || quote_ident(object);
    IF include_recs 
        THEN
            EXECUTE 'SELECT setval( ''' || buffer || ''', ' || sq_last_value || ', ' || sq_is_called || ');' ; 
    ELSE
            EXECUTE 'SELECT setval( ''' || buffer || ''', ' || sq_start_value || ', ' || sq_is_called || ');' ;
    END IF;

  -- add sequence comments
    SELECT oid INTO tbl_oid
      FROM pg_class 
     WHERE relkind = 'S'
       AND relnamespace = src_oid
       AND relname = quote_ident(object);

    SELECT description INTO v_def
      FROM pg_description
     WHERE objoid = tbl_oid
       AND objsubid = 0;

    IF FOUND
      THEN      
        EXECUTE 'COMMENT ON SEQUENCE ' || quote_ident(dest_schema) || '.' || quote_ident(object)
                 || ' IS ''' || v_def || ''';';
    END IF;

  END LOOP;

-- Create tables 
  FOR object IN
    SELECT TABLE_NAME::text 
      FROM information_schema.tables 
     WHERE table_schema = quote_ident(source_schema)
       AND table_type = 'BASE TABLE'

  LOOP
    buffer := quote_ident(dest_schema) || '.' || quote_ident(object);
    EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || quote_ident(source_schema) || '.' || quote_ident(object) 
        || ' INCLUDING ALL)';

    -- Add table comment
    SELECT oid INTO tbl_oid
      FROM pg_class 
     WHERE relkind = 'r'
       AND relnamespace = src_oid
       AND relname = quote_ident(object);

    SELECT description INTO v_def
      FROM pg_description
     WHERE objoid = tbl_oid
       AND objsubid = 0;

    IF FOUND
      THEN      
        EXECUTE 'COMMENT ON TABLE ' || quote_ident(dest_schema) || '.' || quote_ident(object)
                 || ' IS ''' || v_def || ''';';
    END IF;

    IF include_recs 
      THEN 
      -- Insert records from source table
      EXECUTE 'INSERT INTO ' || buffer || ' SELECT * FROM ' || quote_ident(source_schema) || '.' || quote_ident(object) || ';';
    END IF;

    FOR v_column, default_ IN
      SELECT column_name::text, 
             REPLACE(column_default::text, quote_ident(source_schema) || '.', quote_ident(dest_schema) || '.' )
        FROM information_schema.COLUMNS 
       WHERE table_schema = dest_schema 
         AND TABLE_NAME = object 
         AND column_default LIKE 'nextval(%' || quote_ident(source_schema) || '%::regclass)'
    LOOP
      EXECUTE 'ALTER TABLE ' || buffer || ' ALTER COLUMN ' || v_column || ' SET DEFAULT ' || default_;

    END LOOP;

  END LOOP;

  -- set column statistics
  FOR tbl_oid, srctbl IN 
    SELECT oid, relname
      FROM pg_class
     WHERE relnamespace = src_oid
       AND relkind = 'r'

  LOOP

    FOR v_column, v_stat IN         
      SELECT attname, attstattarget
        FROM pg_attribute 
       WHERE attrelid = tbl_oid
         AND attnum > 0

    LOOP

      buffer := quote_ident(dest_schema) || '.' || quote_ident(srctbl);	 
--      RAISE EXCEPTION 'ALTER TABLE % ALTER COLUMN % SET STATISTICS %', buffer, v_column, v_stat::text;
      EXECUTE 'ALTER TABLE ' || buffer || ' ALTER COLUMN ' || quote_ident(v_column) || ' SET STATISTICS ' || v_stat || ';';

    END LOOP;
  END LOOP;

--  add FK constraint
  FOR qry IN
    SELECT 'ALTER TABLE ' || quote_ident(dest_schema) || '.' || quote_ident(rn.relname) 
                          || ' ADD CONSTRAINT ' || quote_ident(ct.conname) || ' ' || replace(pg_get_constraintdef(ct.oid), quote_ident(source_schema) || '.', quote_ident(dest_schema) || '.') || ';'
      FROM pg_constraint ct
      JOIN pg_class rn ON rn.oid = ct.conrelid
     WHERE connamespace = src_oid
       AND rn.relkind = 'r'
       AND ct.contype = 'f'

    LOOP
      EXECUTE qry;

    END LOOP;

    -- Add constraint comment
    FOR con_oid IN
      SELECT oid
        FROM pg_constraint
       WHERE conrelid = tbl_oid

    LOOP
      SELECT conname INTO v_conname
        FROM pg_constraint
       WHERE oid = con_oid;

      SELECT description INTO v_def
        FROM pg_description
       WHERE objoid = con_oid;

       IF FOUND
          THEN 
            EXECUTE 'COMMENT ON CONSTRAINT ' || v_conname || ' ON ' || quote_ident(dest_schema) || '.' || quote_ident(object) 
                    || ' IS ''' || v_def || ''';';
       END IF; 

    END LOOP;

-- Create views 
  FOR object IN
    SELECT table_name::text,
           view_definition 
      FROM information_schema.views
     WHERE table_schema = quote_ident(source_schema)

  LOOP
    buffer := quote_ident(dest_schema) || '.' || quote_ident(object);
    SELECT view_definition INTO v_def
      FROM information_schema.views
     WHERE table_schema = quote_ident(source_schema)
       AND table_name = quote_ident(object);

    SELECT REPLACE(v_def, source_schema, dest_schema) INTO v_def; 
--    RAISE NOTICE 'view def, % , source % , dest % ',  v_def, source_schema, dest_schema;

    EXECUTE 'CREATE OR REPLACE VIEW ' || buffer || ' AS ' || v_def || ';' ;

    -- Add comment
    SELECT oid INTO tbl_oid
      FROM pg_class 
     WHERE relkind = 'v'
       AND relnamespace = src_oid
       AND relname = quote_ident(object);

    SELECT description INTO v_def
      FROM pg_description
     WHERE objoid = tbl_oid
       AND objsubid = 0;

    IF FOUND
      THEN      
        EXECUTE 'COMMENT ON VIEW ' || quote_ident(dest_schema) || '.' || quote_ident(object)
                 || ' IS ' || quote_literal(v_def);
    END IF;

  END LOOP;

-- Create functions 
  FOR func_oid IN
    SELECT oid, proargnames
      FROM pg_proc 
     WHERE pronamespace = src_oid

  LOOP      
    SELECT pg_get_functiondef(func_oid) INTO qry;
    SELECT proname, oidvectortypes(proargtypes) INTO v_func, v_args
      FROM pg_proc 
     WHERE oid = func_oid;
    SELECT replace(qry, quote_ident(source_schema) || '.', quote_ident(dest_schema) || '.') INTO dest_qry;
    EXECUTE dest_qry;

    -- Add function comment
    SELECT description INTO v_def
      FROM pg_description
     WHERE objoid = func_oid
       AND objsubid = 0;

    IF FOUND
      THEN
--        RAISE NOTICE 'func_oid %, object %,  v_args %',  func_oid::text, quote_ident(object), v_args;               
        EXECUTE 'COMMENT ON FUNCTION ' || quote_ident(dest_schema) || '.' || quote_ident(v_func) || '(' || v_args || ')'
                 || ' IS ' || quote_literal(v_def) ||';' ;
    END IF;

  END LOOP;

  -- add Rules
    FOR v_def IN
      SELECT definition 
        FROM pg_rules
       WHERE schemaname = quote_ident(source_schema)

    LOOP

      IF v_def IS NOT NULL
        THEN 
          SELECT replace(v_def, 'TO ', 'TO ' || quote_ident(dest_schema) || '.') INTO v_def;
          EXECUTE ' ' || v_def;
      END IF;
    END LOOP;

  -- add triggers
  FOR v_def IN 
    SELECT pg_get_triggerdef(oid)
      FROM pg_trigger
     WHERE tgname NOT LIKE 'RI_%'
       AND tgrelid IN (SELECT oid
                         FROM pg_class
                        WHERE relkind = 'r'
                          AND relnamespace = src_oid)

  LOOP      

    SELECT replace(v_def, ' ON ', ' ON ' || quote_ident(dest_schema) || '.') INTO dest_qry;
    EXECUTE dest_qry;

  END LOOP;
  --  Disable inactive triggers
  --  D = disabled
  FOR tbl_oid IN 
    SELECT oid
      FROM pg_trigger
     WHERE tgenabled = 'D'
       AND tgname NOT LIKE 'RI_%'
       AND tgrelid IN (SELECT oid
                         FROM pg_class
                        WHERE relkind = 'r'
                          AND relnamespace = src_oid)
  LOOP
    SELECT t.tgname, c.relname INTO object, srctbl
      FROM pg_trigger t
      JOIN pg_class c ON c.oid = t.tgrelid
     WHERE t.oid = tbl_oid;

    IF FOUND 
      THEN 	
	EXECUTE 'ALTER TABLE ' || dest_schema || '.' || srctbl || ' DISABLE TRIGGER ' || object || ';';
    END IF;

  END LOOP;

      -- Add index comment

  FOR tbl_oid IN 
    SELECT oid 
      FROM pg_class 
     WHERE relkind = 'i'
       AND relnamespace = src_oid

  LOOP

    SELECT relname INTO object
      FROM pg_class
     WHERE oid = tbl_oid;
    SELECT description INTO v_def
      FROM pg_description
     WHERE objoid = tbl_oid
       AND objsubid = 0;

    IF FOUND
      THEN      
        EXECUTE 'COMMENT ON INDEX ' || quote_ident(dest_schema) || '.' || quote_ident(object)
                 || ' IS ''' || v_def || ''';';
    END IF;

    END LOOP;

  -- add rule comments
  FOR con_oid IN 
    SELECT oid, * 
      FROM pg_rewrite
     WHERE rulename <> '_RETURN'::name

  LOOP

    SELECT rulename, ev_class INTO v_rule, tbl_oid
      FROM pg_rewrite
     WHERE oid = con_oid;

    SELECT relname INTO object
      FROM pg_class
     WHERE oid = tbl_oid
       AND relkind = 'r';

    SELECT description INTO v_def
      FROM pg_description
     WHERE objoid = con_oid
       AND objsubid = 0;

    IF FOUND 
      THEN 
        EXECUTE 'COMMENT ON RULE ' || v_rule || ' ON ' || quote_ident(dest_schema) || '.' || object || ' IS ' || quote_literal(v_def);
    END IF;

  END LOOP;

  -- add trigger comments
  FOR con_oid IN 
    SELECT oid, * 
      FROM pg_trigger
     WHERE tgname NOT LIKE 'RI_%'

  LOOP

    SELECT tgname, tgrelid INTO v_trig, tbl_oid
      FROM pg_trigger
     WHERE oid = con_oid;

    SELECT relname INTO object
      FROM pg_class
     WHERE oid = tbl_oid
       AND relkind = 'r';

    SELECT description INTO v_def
      FROM pg_description
     WHERE objoid = con_oid
       AND objsubid = 0;

    IF FOUND 
      THEN 
        EXECUTE 'COMMENT ON TRIGGER ' || v_trig || ' ON ' || quote_ident(dest_schema) || '.' || object || ' IS ' || quote_literal(v_def);
    END IF;

  END LOOP;

  RETURN; 

END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION public.clone_schema(text, text, boolean)
  OWNER TO postgres;
COMMENT ON FUNCTION public.clone_schema(text, text, boolean) IS 'Duplicates sequences, tables, indexes, rules, triggers, data(optional),
     views & functions from the source schema to the destination schema';
-- 
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general