Duplicate postgresql schema including sequences

Question

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?

Answer 1

The root of the problem

The connection to the old sequence comes from a plain default value for the involved column. I quote the manual here:

Default expressions for the copied column definitions will only be copied if INCLUDING DEFAULTS is specified. The default behavior is to exclude default expressions, resulting in the copied columns in the new table having null default.

Since you create new tables with

INCLUDING ALL

And:

INCLUDING ALL is an abbreviated form of INCLUDING DEFAULTS INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING STORAGE INCLUDING COMMENTS.

.. you get the same default. You can either exclude defaults or explicitly change default values including nextval() for the new tables after you created them. I don't think there is any middle ground.

---

Simpler with dump / hack the dump / restore

or even another reliable way to duplicate entire schema

You could dump the schema of schema (same word, different meaning) with pg_dump:

pg_dump $DB -p $PORT -n $SCHEMA -sf /var/lib/postgresql/your_name.pgsql

Hack the dump (meaning: use a text editor on it, or script it): exchange the schema name at the top of the dump, and all other occurrences in SET search_path and as schema-qualification for sequences and possibly more. If you chose a unique name for the schema, a single run of global search & replace with your favorite tool (sed or vim or ...) should do the job.

Then run the SQL script with psql against the same or any other database:

psql $DB -p $PORT -f /var/lib/postgresql/your_name.pgsql > /dev/null

Contrary to what I first posted, serial columns are still split up in the dump (at least in PostgreSQL 9.1.5). The SQL script creates sequences separately, attaches them to the serial column with:

ALTER SEQUENCE seq OWNED BY tbl.col;

and sets the default value separately.

As an aside: Current versions of pgAdmin reverse engineer serial columns in DDL scripts when all the requirements are met.

Answer 2

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.