Works on Linux distros and all Windows versions.

Handles following objects:

• Tables - structure (indexes, constraints, keys) and optionally, data
• Views, Materialized Views - Structure and data
• Sequences, Serial, Identity
• Functions/Procedures
• Types (composite and enum)
• Collations, Domains, Rules, Policies
• Triggers, Trigger Functions
• Comments
• ACLs (Permissions/Grants)

Arguments:

• source schema
• target schema
• clone with data (boolean)
• only generate DDL (boolean)

You can call function like this to copy schema with data:

select clone_schema('sample', 'sample_clone', true, false);

Alternatively, if you want to copy only schema without data:

select clone_schema('sample', 'sample_clone', false, false);

If you just want to generate the DDL, call it like this:

select clone_schema('sample', 'sample_clone', false, true);

In this case, standard output with "INFO" lines are the generated DDL.


The schema_object_counts.sql file is useful for validating the cloning results. Just run it against source and target schemas to validate object counts after changing default schema name, sample.

Regression Testing is done in the following order:

• Execute the sampledb.sql script to create the clone_testing database and the sample schema within it as the basis for the source schema.
• Clone the sample schema in the 3 ways possible (save ddl only, create ddl only, or create ddl and copy rows).
• Run the schema_object_counts.sql queries to compare object counts and rows from the source and target schemas.
• Repeat all of the above for all supported versions of PG.

• Only works for PG Versions 10 and up.
• You should not clone the "public" schema. The resulting output may not be accurate even if it finishes without any errors.
• Foreign Tables are not handled at the present time. They must be done manually.
• Functions/procedures that reference schema-qualified objects will not clone successfully. The target schema will still reference the source schema in these cases. To use this utility at the present time, you may need to remove schema-qualified references within your functions.