From PostgreSQL wiki
by Emanuel '3manuek'
Just remember to create language.
CREATE OR REPLACE FUNCTION clone_schema(source_schema text, dest_schema text) RETURNS void AS $BODY$ DECLARE objeto text; buffer text; BEGIN EXECUTE 'CREATE SCHEMA ' || dest_schema ; FOR objeto IN SELECT TABLE_NAME::text FROM information_schema.TABLES WHERE table_schema = source_schema LOOP buffer := dest_schema || '.' || objeto; EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || source_schema || '.' || objeto || ' INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING DEFAULTS)'; EXECUTE 'INSERT INTO ' || buffer || '(SELECT * FROM ' || source_schema || '.' || objeto || ')'; END LOOP; END; $BODY$ LANGUAGE plpgsql VOLATILE;
Execution is simple:
Worth noting is that this copies everything in the schema, including the default values. Which, in the case of serial fields, will mean the default value points to the old schema:
This actually has some nice side-effects: the original sequence is used for creating new ids, which means you have unique ids across tables.
However, very bad things will happen if you were to remove the source schema, as the sequence would also be removed.