重复的Postgresql模式，包括序列 [英] Duplicate postgresql schema including sequences

问题描述

我的数据库布局需要为每个新客户创建新的架构.目前，我使用在网上找到的内部函数并对其进行了一些修改.

My database layout needs to create new schema for each new customer. Currently I use internal function I found on the net and modified a little bit.

CREATE FUNCTION copy_schema(
    source_schema character varying, 
    target_schema character varying, 
    copy_data boolean)
RETURNS integer AS
$BODY$
DECLARE
    t_ex integer := 0;
    s_ex integer := 0;
    src_table character varying;
    trg_table character varying;
BEGIN
    if (select 1 from pg_namespace where nspname = source_schema) THEN
        -- we have defined target schema
        s_ex := 1;
    END IF;

    IF (s_ex = 0) THEN
        -- no source schema exist
        RETURN 0;
    END IF;

    if (select 1 from pg_namespace where nspname = target_schema) THEN
        -- we have defined target schema need to sync all table layout
        t_ex := 1;
    ELSE
        EXECUTE 'CREATE SCHEMA '||target_schema||' AUTHORIZATION user';
    END IF;

    FOR src_table IN 
       SELECT table_name 
       FROM information_schema.TABLES 
       WHERE table_schema = source_schema
    LOOP
        trg_table := target_schema||'.'||src_table;
        EXECUTE 
            'CREATE TABLE ' || trg_table || ' (LIKE ' || source_schema || '.' || src_table || ' INCLUDING ALL)';
        IF (copy_data = true) THEN
            EXECUTE 'INSERT INTO ' || trg_table || '(SELECT * FROM ' || source_schema || '.' || src_table || ')';
        END IF;
    END LOOP;

    return t_ex;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

此脚本的问题在于新架构中的表继续使用源架构的序列.有没有办法使用sql语句(或其他可靠的方法)为新创建的表获取序列的新副本(甚至是复制整个架构的另一种可靠的方法)?

The problem with this script is that tables in new schema continue to use source schema's sequences. Is there way using sql statements (or other reliable way) to get fresh copy of sequences (or even another reliable way to duplicate entire schema) for the newly created tables?

推荐答案

因此，经过一番思考，我开始更新第一篇文章中提到的sql函数，现在看起来像这样:

And so after some thinking I went along with updating sql function mentioned in my first post so now it looks like this:

CREATE FUNCTION copy_schema(
    source_schema character varying, 
    target_schema character varying, 
    copy_data boolean)
RETURNS integer AS
$BODY$
DECLARE
    t_ex integer := 0;
    s_ex integer := 0;
    src_table character varying;
    trg_table character varying;
BEGIN
    if (select 1 from pg_namespace where nspname = source_schema) THEN
        -- we have defined target schema
        s_ex := 1;
    END IF;

    IF (s_ex = 0) THEN
        -- no source schema exist
        RETURN 0;
    END IF;

    if (select 1 from pg_namespace where nspname = target_schema) THEN
        -- we have defined target schema need to sync all table layout
        t_ex := 1;
    ELSE
        EXECUTE 'CREATE SCHEMA '||target_schema||' AUTHORIZATION user';
    END IF;

    FOR src_table IN 
        SELECT table_name 
        FROM information_schema.TABLES 
        WHERE table_schema = source_schema
    LOOP
        trg_table := target_schema||'.'||src_table;
        EXECUTE 'CREATE TABLE ' || trg_table || ' (LIKE ' || source_schema || '.' || src_table || ' INCLUDING ALL)';
        EXECUTE 'CREATE SEQUENCE ' || trg_table || '_id_seq OWNED BY '||trg_table || '.id';
        EXECUTE 'ALTER TABLE ' || trg_table || ' ALTER COLUMN id SET DEFAULT nextval('''|| trg_table || '_id_seq''::regclass)';
        IF (copy_data = true) THEN
            EXECUTE 'INSERT INTO ' || trg_table || '(SELECT * FROM ' || source_schema || '.' || src_table || ')';
        END IF;
    END LOOP;
    return t_ex;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

对于所有人来说，这不是一个通用的解决方案，但是由于我架构中的所有表都具有名为id的串行字段，因此它很适合我.

This is not quite universal solution for everybody, but as all my tables in schema have serial field named id, it fits me.

@ erwin-brandstetter建议的版本，其中包含转储/hack转储文件/恢复还原转储文件，这通常是在论坛上看到的.

Version suggested by @erwin-brandstetter with dump / hack dump file / restore dump file back again is commonly seen on the forums as the way to go.

在专用服务器的情况下，它可以工作；在共享主机的情况下(或需要更少的外部脚本依赖性)，内部功能的方式似乎更好.

In case of dedicated server it could work, in case of shared hosting (or need of less dependencies on outside scripts) the way of internal function seems better.

这篇关于重复的Postgresql模式，包括序列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！