Handles following objects:
- Tables - structure (indexes, constraints, keys) and optionally, data
- Views
- Materialized Views - Structure and data
- Sequences
- Functions/Procedures
- Types (composite and enum)
- Collations and Domains
- Triggers
- Comments (in progress: see Issue#62)
- 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.
- 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.
- DDL only option is not complete since it depends on objects that aren't created yet. See issue#29
- Functions 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 need to remove schema-qualified references within your functions.
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