PostgreSQL 克隆schema - clone schema

作者

digoal

日期

2019-08-21

标签

PostgreSQL , clone , schema

背景

原文

http://www.pateldenish.com/2019/08/clone-schema-in-postgres.html

PG支持克隆database，但是没有内置克隆schema的功能。schema是一个数据库中的逻辑定义，对象必须挂载在schema里面，schema是一些对象的逻辑集合。

《PostgreSQL 逻辑结构 和 权限体系 介绍》

克隆database

```
Command: CREATE DATABASE
Description: create a new database
Syntax:
CREATE DATABASE name
[ [ WITH ] [ OWNER [=] user_name ]
[ TEMPLATE [=] template ]
[ ENCODING [=] encoding ]
[ LC_COLLATE [=] lc_collate ]
[ LC_CTYPE [=] lc_ctype ]
[ TABLESPACE [=] tablespace_name ]
[ ALLOW_CONNECTIONS [=] allowconn ]
[ CONNECTION LIMIT [=] connlimit ]
[ IS_TEMPLATE [=] istemplate ] ]

URL: https://www.postgresql.org/docs/12/sql-createdatabase.html
```

克隆时，被克隆的数据库不能被用户连接，指定为template即可。例如克隆db1

```
create database newdb with template db1;

postgres=# create database db3 with template db1;
psql: ERROR: source database "db1" is being accessed by other users
DETAIL: There is 1 other session using the database.
```

克隆schema

没有语法直接支持，开源社区有位热心人写了一个函数，可以用来克隆schema，当然克隆的对象类型有限，并不是所有对象的克隆。

包含的对象包括；

表、物化视图、视图、序列、约束、函数。

http://www.pateldenish.com/2019/08/clone-schema-in-postgres.html

https://github.com/denishpatel/pg-clone-schema/blob/master/clone_schema.sql

不包括的对象包括：

索引、自定义类型。

参考

https://github.com/denishpatel/pg-clone-schema

https://github.com/denishpatel/pg-clone-schema/blob/master/clone_schema.sql

``` -- Function: clone_schema(text, text)

-- DROP FUNCTION clone_schema(text, text);

CREATE OR REPLACE FUNCTION public.clone_schema( source_schema text, dest_schema text, include_recs boolean) RETURNS void AS $BODY$

-- This function will clone all sequences, tables, data, views & functions from any existing schema to a new one -- SAMPLE CALL: -- SELECT clone_schema('public', 'new_schema', TRUE);

DECLARE src_oid oid; tbl_oid oid; func_oid oid; object text; buffer text; srctbl text; default_ text; column_ text; qry text; dest_qry text; v_def text; 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) ;

-- 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, log_cnt, is_called
          FROM ' || quote_ident(source_schema) || '.' || quote_ident(object) || ';'
          INTO sq_last_value, sq_log_cnt,sq_is_called ;

EXECUTE 'SELECT max_value, start_value, increment_by, min_value, cache_size, cycle
          FROM pg_catalog.pg_sequences WHERE schemaname='''|| quote_ident(source_schema) || ''' AND sequencename=''' || quote_ident(object) || ''';'
          INTO sq_max_value, sq_start_value, sq_increment_by, sq_min_value, sq_cache_value, sq_is_cycled ;

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;
复制

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 := dest_schema || '.' || quote_ident(object); EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || quote_ident(source_schema) || '.' || quote_ident(object) || ' INCLUDING ALL)';

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 column_, default_ IN
  SELECT column_name::text,
         REPLACE(column_default::text, source_schema, 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 ' || column_ || ' SET DEFAULT ' || default_;
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) || ' ' || pg_get_constraintdef(ct.oid) || ';' 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;
复制

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

LOOP buffer := 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);

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

END LOOP;

-- Create Materialized views FOR object IN SELECT matviewname::text, definition FROM pg_catalog.pg_matviews WHERE schemaname = quote_ident(source_schema)

LOOP
  buffer := dest_schema || '.' || quote_ident(object);
  SELECT replace(definition,';','') INTO v_def
    FROM pg_catalog.pg_matviews
   WHERE schemaname = quote_ident(source_schema)
     AND matviewname = quote_ident(object);

     IF include_recs
       THEN
       EXECUTE 'CREATE MATERIALIZED VIEW ' || buffer || ' AS ' || v_def || ';' ;
       ELSE
       EXECUTE 'CREATE MATERIALIZED VIEW ' || buffer || ' AS ' || v_def || ' WITH NO DATA;' ;
     END IF;

END LOOP;
复制

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

LOOP SELECT pg_get_functiondef(func_oid) INTO qry; SELECT replace(qry, source_schema, dest_schema) INTO dest_qry; EXECUTE dest_qry;

END LOOP;

RETURN;

END;

$BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION public.clone_schema(text, text, boolean) OWNER TO postgres; ```

PostgreSQL 许愿链接

您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.

9.9元购买3个月阿里云RDS PostgreSQL实例

PostgreSQL 解决方案集合

德哥 / digoal's github - 公益是一辈子的事.