clone_schema
A PostgreSQL utility that clones an entire schema with all its objects (tables, views, functions, etc.) using a single SQL function.
What is clone_schema?
clone_schema is a PostgreSQL utility function that duplicates an entire database schema, including all its objects like tables, views, functions, and permissions, directly within the database. It solves the problem of needing to use external tools like pg_dump for schema copying, especially in restricted environments such as Amazon RDS. The tool handles complex scenarios including data transfer, DDL generation, and sequence management.
PostgreSQL database administrators and developers working in managed database environments like RDS, who need to quickly duplicate schemas for testing, staging, or migration purposes.
Developers choose clone_schema because it operates entirely within PostgreSQL, eliminating the need for external tools or leaving the database environment. It offers a simple, single-function interface for comprehensive schema cloning with flexible options for data handling, ownership, and permissions.
Overview
Postgres clone schema utility without need of going outside of database. Makes developers life easy by running single function to clone schema with all objects. It is very handy on Postgres RDS. Utility is sponsored by http://elephas.io/
Use Cases
Best For
- Creating staging or testing environments by duplicating production schemas
- Migrating schemas within managed PostgreSQL services like Amazon RDS
- Generating DDL scripts for schema objects without data
- Quickly copying schema structures for development or experimentation
- Handling schema duplication when external tools like pg_dump are unavailable or restricted
- Managing schema versions by creating snapshots of current schema state
Not Ideal For
- Projects requiring efficient cloning of very large datasets with parallel processing
- Schemas that heavily rely on Large Objects (LOs) or foreign tables
- Environments with complex, multiple interdependent user-defined schemas beyond the source and target
- Teams using PostgreSQL versions below 10
Pros & Cons
Pros
Clones a wide range of schema objects including tables, views, functions, types, and permissions, as detailed in the README's handled objects list.
Offers DATA, NODATA, and DDLONLY options for cloning with data, without data, or for DDL generation only, providing versatility for different use cases.
Includes NOOWNER and NOACL flags to customize ownership and privilege transfer, mimicking pg_dump behavior for finer control.
Properly clones serial, identity columns, and sequences by setting correct nextval values, ensuring data consistency as described in the README.
Cons
The README admits it copies tables one at a time, making it slower than pg_dump/pg_restore with parallel jobs for large datasets.
Does not support Large Objects (LOs) or foreign tables, limiting its usefulness for schemas that depend on these object types.
Struggles with multiple, interdependent user-defined schemas, as the README notes it only supports basic schema setups.
Open Source Alternative To
clone_schema is an open-source alternative to the following products:
Frequently Asked Questions
Related Projects
Database Lab EngineInstant thin cloning for PostgreSQL to scale the development process
SpawnCloud service for creating instant database copies for development and CI. No more local db installs, instant recovery to arbitrary save points, isolated copies for each feature branch or test. Instant provisioning regardless of database size
Found a gem we're missing?
Open-Awesome is built by the community, for the community. Submit a project, suggest an awesome list, or help improve the catalog on GitHub.