sqlpostgresqldatabase-schemamulti-tenant

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;

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?


Solution

  • 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;
    

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

    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.