clone_schema

Handles following objects:

• Tables - structure (indexes and keys) and optionally, data
• Views
• Materialized Views - Structure and data
• Sequences
• Functions/Procedures
• Types (composite and enum)
• Collations and Domains
• Triggers
• Permissions/GRANTs

Arguments:

• source schema
• target schema
• clone with data
• only generate DDL

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.

Limitations

• Foreign Tables are not handled at the present time. They must be done manually.
• DDL only option is not complete since it depends on objects that aren't created yet. See issue#29

Sponsor: http://elephas.io/

Compare cloning with EnterpriseDB's version that only works with their Advanced Server: https://www.enterprisedb.com/edb-docs/d/edb-postgres-advanced-server/user-guides/user-guide/11/EDB_Postgres_Advanced_Server_Guide.1.078.html