gem-hunter
Language: PLpgSQL
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/
clone_schema is a PostgreSQL tool for making a copy of a schema. It includes all objects associated with a schema.
Handles following objects:
Arguments:
Required: text - schema name
Required: text - table name
Required: One of 'DATA','NODATA','DDLONLY'
Optional: 'NOOWNER','NOACL','VERBOSE','FILECOPY'
Returns: INTEGER (0 for success, positive non-zero number for an error)
Examples
Clone the schema with no data:
select clone_schema('sample', 'sample_clone', 'NODATA');
Clone the schema with data:
select clone_schema('sample', 'sample_clone', 'DATA');
select clone_schema('sample', 'sample_clone', 'DATA','VERBOSE'); -- verbose mode shows row copy progress
Just generate DDL:
select clone_schema('sample', 'sample_clone', 'DDLONLY');
In the generated output for DDLONLY, modify the output as follows:
Ownership/Privileges
By default, ownership and privileges are also cloned from source to target schema. To override, specify NOOWNER and/or NOACL (similar to how pg_dump works). When NOOWNER is specified, the one running the script is the default owner unless overridden by a SET ROLE command before running this script.
Copying Data
You may get faster results copying data to/from disk instead of in-memory copy. FILECOPY is a workaround for tables with complex UDT-type columns that fail to copy. It only works for On-Prem PG Instances since it relies on using the COPY command to write to and read from disk on which the PostgreSQL server resides.
Although pg_clone_schema supports data copy, it is not very efficient for large datasets. It only copies tables one at a time. Using pg_dump/pg_restore in directory mode using parallel jobs would be a lot more efficient for large datasets.
Sequences, Serial, and Identity
Serial is treated the same way as sequences are with explicit sequence definitions. Although you can create a serial column with the serial keyword, when you export it through pg_dump, it loses its serial definition and looks like a plain sequence. This program also attempts to set the nextval (using setval) for all 3 types which have a valid last_value from the pg_sequences table.
Function Dependency Issues
If functions depend on other functions, creation may fail unless the referenced functions already exist.
Possible solutions:
SQL to PL/pgSQL.Set check_function_bodies = false.The second option is usually preferred because SQL functions can benefit from optimizer features that may be lost when converting to PL/pgSQL. Simply execute this SET command before running clone_schema. In general, it is recommended to leave it ON at the global level, and override it at the session level for dependency case issues like this.
Regression Testing is done in the following order:
This tool fully supports the bytea data type. However, Large Objects (LOs) referenced via OIDs are not currently supported for cloning. If your schema relies on pg_largeobject, the OID references will be copied, but the underlying binary data will not be duplicated.