Currently, when the DDL only option is selected, last parm=true), some of the output DDL is missing, like for indexes.

There are references to nonexistent fields in the PRIVS section see my commit for change. One is in a commented out debug

dpmillerau@f32488b#diff-f2c8f6cbb117540fc01e34353d4ab0c70d7f0a9b98efdb77bfeaa7ebaafbabdb

If you have user-defined columns, then the following simple construct will not work anymore:
INSERT INTO newschema.newtable SELECT * from oldschema.oldtable;

This fails using the sampledb table, address, which has a user-defined datatype, udt_myint.

This looks like it requires a complicated fix to list all the columns and do a cast on the ones that are user-defined columns.

• Schema level permissions
• Object level permissions

Not sure how this could be addressed.
When copying a schema that is under load (users inserting new records), it's possible sequences become behind the actual table data, which results in duplicate key errors on target.
I'm using a separate SQL now to fix sequences in this case but it would be nice to avoid this issue if possible.

Hi!

Is there any roadmap or plan to include automated tests on this repo, so that the correctness of the procedure can be asserted programatically? I'll be more than happy to help with the initial scaffolding and tests if we can agree on a way to do this.

I'm facing this error when trying to use the clone_schema function:

ERROR:  Action: PRIVS: Functions/Procedures  Diagnostics: line=PL/pgSQL function clone_schema(text,text,boolean,boolean) line 732 at EXECUTE. 42601. syntax error at or near "DEFAULT"
CONTEXT:  PL/pgSQL function clone_schema(text,text,boolean,boolean) line 780 at RAISE

it comes from this generated query.

GRANT EXECUTE ON FUNCTION clone_schema_test_c.price_view_data (materialised_price_list_id integer, ofs integer, lim integer, filtering jsonb DEFAULT NULL::jsonb, sorting jsonb DEFAULT NULL::jsonb, flags jsonb DEFAULT NULL::jsonb) TO my_user;

which is generated by

SELECT 'GRANT ' || rp.privilege_type || ' ON ' || r.routine_type || ' ' || quote_ident(dest_schema) || '.' || rp.routine_name || ' (' || pg_get_function_arguments(p.oid) || ') TO ' || string_agg(distinct rp.grantee, ',') || ';' as func_dcl

for some reason, pg_get_function_arguments(p.oid) is generating an invalid sql?

Policies 1 and 2 actions were breaking for me doing clones due to the quoting around descriptions.

I believe it should do something like this ' IS ' || quote_literal(d.description) || ';' instead of ' IS ''' || d.description || ''';'.

I was going to put up a PR but my IDE got rid of all the extra spaces at the end of the lines and it was a bit of a PITA fix.

$user in the search path causes an exception right at the end of the function!

I think the information_schema does not list foreign tables. I may be wrong, but need to investigate...

Missing indexes on materialized views and comments

i used this script to copy a schema on heroko. It immediately generated thousands of randomly named function with the copied schema. It also attempted to make OS calls on my local machine. DO NOT USE THIS SCRIPT.

Just found that you've been managing this after we just upgraded from 9.6 to 11, but when running the latest master version we're getting the following error

ERROR:  Action: PRIVS: Schema  Diagnostics: 42704. type "perm_type" does not exist . .  PL/pgSQL function clone_schema(text,text,boolean) line 627 at GET STACKED DIAGNOSTICS
CONTEXT:  PL/pgSQL function clone_schema(text,text,boolean) line 629 at RAISE
STATEMENT:  select clone_schema('test_tenant', 'test_demo_3', false);

(I've removed the 4th parameter from our version of the function and just set it to True for now).

I think this is because there aren't any permissions getting returned, although I'm not exactly sure how to test this.

Please add some license.

While working on Issue#35, I discovered that privileges were given to other users that should not have gotten them for other objects as well. Fix permissions on all other objects like the way we did it for Issue#35.

I think we need to add collations.

Thank you very much for this script.

My views all contain hard coded schema references to the schema that contains them. E.g. schema1 contains a view that references schema1.table1 and schema2 contains a view that references schema2.table1.

These references are not updated in the cloned schema. Views in the cloned schema still point to the tables in the original schema.

Is there any way to update these references? Or is my issue due to non colloquial use of postgres?

I have attempted to change the script to run REPLACE on the view definition as it is cloned but my changes seem to have no effect (probably due to my lack of experience with plpgsql).

Hi,
It would be great to support transition_relation_name reference when cloning triggers.

For instance, this one could work :

SELECT quote_ident(dest_schema) AS TRIGGER_SCHEMA, TRIGGER_NAME, event_object_table, action_order, action_condition, action_statement, action_orientation, action_timing, action_reference_new_table, action_reference_old_table, array_to_string(array_agg(event_manipulation::text), ' OR '), 'CREATE TRIGGER ' || TRIGGER_NAME || ' ' || action_timing || ' ' || array_to_string(array_agg(event_manipulation::text), ' OR ') || ' ON ' || quote_ident(dest_schema) || '.' || event_object_table || CASE WHEN action_reference_new_table IS NOT NULL THEN ' REFERENCING ' || 'NEW TABLE AS ' || action_reference_new_table ELSE '' END || CASE WHEN action_reference_old_table IS NOT NULL THEN ' REFERENCING' || ' OLD TABLE AS ' || action_reference_old_table ELSE '' END || ' FOR EACH ' || action_orientation || ' ' || action_statement || ';' AS TRIG_DDL FROM information_schema.triggers WHERE TRIGGER_SCHEMA = quote_ident(source_schema) GROUP BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10

Hi there!

Thanks for maintaining this very useful repo!

I'm having a problem when cloning a schema that contains one table whose name includes the schema name, for instance some_schema.table_with_some_schema_in_the_name.

One of the referenced code chunks might be the culprit:

Happy to submit a PR. Will enclosing the arguments in quote_ident solve the problem?

If you provide following two permission on sampledb, the clone schema will fail

ALTER DEFAULT PRIVILEGES FOR ROLE mydb_owner IN SCHEMA sample GRANT ALL ON SEQUENCES TO mydb_owner;
ALTER DEFAULT PRIVILEGES FOR ROLE postgres IN SCHEMA sample GRANT ALL ON SEQUENCES TO mydb_owner;

I have a NUMERIC(25, 0) column in the database. It scripts as just numeric, which has a non-zero scale.

I'm getting this error while running the clone_schema during the clone functions flow.

PL/pgSQL function clone_schema(text,text,boolean,boolean) line 484 at EXECUTE. 42704. type "some_type" does not exist

when the source function uses the type in a declare statement. like:

DECLARE
    my_var some_type;
BEGIN

I realized that we clear the source_path before cloning the functions in:

-- Create functions
  action := 'Functions';
  cnt := 0;
  SET search_path = '';

I could solve the issue, changing the SET search_path = ''; line to EXECUTE 'SET search_path = ' || src_path_old;

However, I'm thinking about why clone_schema clears the initial search_path that is the source_schema to be an empty one. Meaning that we should have the types on the public schema, which I think isn't the purpose of the cloning function.

What are your thoughts about this solution?

I can submit a PR for any of both solutions if you agree.

While working on the clone_schema.sql script (cf. my recent pull requests! ;) ), I noticed not all the code follows the same coding style (and sometimes no style at all! :) ).
It's not a big deal but it would be nice to follow a same style for the whole file.
One can find many different coding styles for SQL on the net. I am not attached to a specific one but I just it would be nice to select one and use it for clarity (reading) and code sharing.
If I had to choose, I would go for (as a suggestion):

• capitalize reserved words
• lowercase for object names (schema, tables, columns,...) and functions
• no space between functions and parenthesis
• no more than 1 space between words
• main keywords on new line and vertically aligned by level
• two spaces indentation
• indent next line when it is a sub-part of the line above
• lines should not exceed 80 characters as much as possible
• comments above the related code line
  ...

It would look like this for instance:

SELECT
  con.conname AS constraint_name,
  con.contype AS constraint_type,
  CASE
    -- primary key constraint
    WHEN con.contype = 'p' THEN 1
    -- unique constraint
    WHEN con.contype = 'u' THEN 2
    -- foreign key constraint
    WHEN con.contype = 'f' THEN 3
    WHEN con.contype = 'c' THEN 4
    ELSE 5
  END AS type_rank,
  pg_get_constraintdef(con.oid) AS constraint_definition
FROM pg_catalog.pg_constraint con
  JOIN pg_catalog.pg_class rel ON rel.oid = con.conrelid
  JOIN pg_catalog.pg_namespace nsp ON nsp.oid = connamespace
WHERE nsp.nspname = in_schema
  AND rel.relname = in_table
ORDER BY type_rank

I could work on that once all my PR are accepted. ;)

When creating a table with a field that has an enum type, the enum type isn't properly quoted and the clone can fail when the enum type has a capital letter. For example, the clone_schema function tries to run:

CREATE TABLE newschema.SomeTable (
  field_with_enum newschema.SomeEnumField NOT NULL DEFAULT 'NONE'::"SomeEnumField",
)

Which results in the error:

PL/pgSQL function clone_schema(text,text,boolean,boolean) line 329 at EXECUTE. 42804. column "status" is of type newschema.someenumfield but default expression is of type "SomeEnumField" (SQLSTATE P0001)

I am getting the following when running it on PostgreSQL 11.

NOTICE:  COLLATIONS cloned:     0
NOTICE:     DOMAINS cloned:     0
NOTICE:       TYPES cloned:     0
NOTICE:   SEQUENCES cloned:     7
NOTICE:  Populating cloned table, pristine.django_migrations
NOTICE:  Populating cloned table, pristine.fifthtry_team
NOTICE:  Populating cloned table, pristine.fifthtry_team_member
NOTICE:  Populating cloned table, pristine.fifthtry_user
NOTICE:  Populating cloned table, pristine.fifthtry_session
NOTICE:  Populating cloned table, pristine.fifthtry_namespace
NOTICE:  Populating cloned table, pristine.fifthtry_content
NOTICE:      TABLES cloned:     7
NOTICE:       FKEYS cloned:     6
NOTICE:       VIEWS cloned:     0
NOTICE:   MAT VIEWS cloned:     0
ERROR:  "min" is an aggregate function
CONTEXT:  SQL statement "SELECT pg_get_functiondef(func_oid)"
PL/pgSQL function clone_schema(text,text,boolean) line 273 at SQL statement

Currently, partitioned tables are cloned as regular tables. This includes parent and child tables. Also, unlogged tables are being cloned as regular tables too.

Currently, there is no dependency checking. For example, if a view is dependent on another view, they may not be created in the correct order during the clone operation resulting in errors that abort the cloning operation at that point.

Perhaps the pg_depend catalog table can help in this regard.

Fix get_table_ddl() for partitioning and inheritance so we are not dependent on outside function, pg_get_tabledef().

For varchar columns I'm getting "character varying" and for any array I just get "ARRAY". I think the solution is to always use the udt_name and never use the data_type.

I had three consecutive errors related to the quote problem. Here they are:

SQL Error [P0001]: ERROR: Action: Tables  Diagnostics: line=SQL statement "ALTER TABLE teste_clone."CUBE_6c1b118be1df454e8b1770822d5cbe45_FACT" ALTER COLUMN FACT_ORDER SET DEFAULT nextval('teste_clone."CUBE_6c1b118be1df454e8b1770822d5cbe45_FACT_FACT_ORDER_seq"'::regclass)"
PL/pgSQL function clone_schema(text,text,boolean,boolean) line 688 at EXECUTE. 42703. column "fact_order" of relation "CUBE_6c1b118be1df454e8b1770822d5cbe45_FACT" does not exist
 Where: PL/pgSQL function clone_schema(text,text,boolean,boolean) line 1584 at RAISE

SQL Error [P0001]: ERROR: Action: PRIVS: Sequences  Diagnostics: line=SQL statement "GRANT SELECT ON teste_clone.CUBE_72a2ff79600040f488b43fe7887a6da7_FACT_FACT_ORDER_seq TO "user";"
PL/pgSQL function clone_schema(text,text,boolean,boolean) line 1488 at EXECUTE. 42P01. relation "teste_clone.cube_72a2ff79600040f488b43fe7887a6da7_fact_fact_order_seq" does not exist
  Where: PL/pgSQL function clone_schema(text,text,boolean,boolean) line 1584 at RAISE

SQL Error [P0001]: ERROR: Action: PRIVS: Tables  Diagnostics: line=SQL statement "GRANT DELETE ON TABLE teste_clone.CUBE_6c1b118be1df454e8b1770822d5cbe45_FACT TO user;"
PL/pgSQL function clone_schema(text,text,boolean,boolean) line 1563 at EXECUTE. 42P01. relation "teste_clone.cube_6c1b118be1df454e8b1770822d5cbe45_fact" does not exist
  Where: PL/pgSQL function clone_schema(text,text,boolean,boolean) line 1584 at RAISE

Can you add RLS policies, which are located in pg_policies?

Hello,

it seems that for some simple databases the function fails when copying rows.

Error: Error in query (7): ERROR: Action: Copy Rows Diagnostics: line=PL/pgSQL function clone_schema(text,text,boolean,boolean,boolean) line 1642 at FOREACH over array. 22004. FOREACH expression must not be null CONTEXT: PL/pgSQL function clone_schema(text,text,boolean,boolean,boolean) line 1706 at RAISE

To reproduce:

create schema sample1;
create table sample1.tbl (v int);
set search_path to sample1;
insert into sample1.tbl (v) values (1), (2);
select clone_schema('sample1', 'sample2', true, false);

version: PostgreSQL 14.4 (Debian 14.4-1.pgdg110+1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 10.2.1-6) 10.2.1 20210110, 64-bit

I am not at all a db expert, I may be doing something wrong. Thank you very much for all the work you put in this function!

I have this script for a Materialized view:

-- View: sample_clone.mv_groups

-- DROP MATERIALIZED VIEW IF EXISTS sample_clone.mv_groups;

CREATE MATERIALIZED VIEW IF NOT EXISTS sample_clone.mv_groups
TABLESPACE pg_default
AS
 SELECT customer_products.customer_id,
    customer_products.product_id,
    customer_products.current_month
   FROM sample.customer_products
  WHERE customer_products.action::text = 'E'::text
  ORDER BY customer_products.customer_id, customer_products.product_id
WITH NO DATA;

ALTER TABLE IF EXISTS sample_clone.mv_groups
    OWNER TO postgres;


CREATE UNIQUE INDEX mv_groups_customer_id_product_id_unique
    ON sample_clone.mv_groups USING btree
    (customer_id, product_id)
    TABLESPACE pg_default;

The query of the materialized view still refers to the original schema: sample, and not the cloned one: sample_clone.

Im facing the following issue when cloning the schema.

The trigger function shouldn't be the one from the dest schema?

This is coming from this SQL

CREATE TRIGGER modify_after_delete_dimension_modalities AFTER DELETE ON dest_schema.dimension_modalities FOR EACH STATEMENT EXECUTE FUNCTION modify_dimension_modalities();

I see we use pg_get_functiondef for procs and funcs, but what about trigger functions?
Don't we need to call pg_get_triggerdef?

I'm facing this error when trying to clone my schema, probably related with #34 solution

ERROR:  Action: PRIVS: Functions  Diagnostics: line=SQL statement "GRANT EXECUTE ON FUNCTION clone_schema_test_b.__crud_attributes(clone_schema_test_b.crud_ops,jsonb,jsonb,jsonb,jsonb) TO "pg_monitor";"
PL/pgSQL function clone_schema(text,text,boolean,boolean) line 729 at EXECUTE. 42883. function clone_schema_test_b.__crud_attributes(clone_schema_test_b.crud_ops, jsonb, jsonb, jsonb, jsonb) does not exist
CONTEXT:  PL/pgSQL function clone_schema(text,text,boolean,boolean) line 773 at RAISE

cause the sql that created this function is actually:

CREATE OR REPLACE FUNCTION clone_schema_test_b.__crud_attributes(crud_op crud_ops, data jsonb, filtering jsonb DEFAULT NULL::jsonb, sorting jsonb DEFAULT NULL::jsonb, flags jsonb DEFAULT NULL::jsonb)
...

to help to debug it, creating the functions using the src_path_old as the search_path doesn't cause this error.

Left some unintentional debug statements in changes to previous issues.

Hello,
When cloning a table from source_schema using a type defined in source_schema, I should have my destination table being defined with the type of the destination schema (which is correctly cloned by the way).
It is not the case as the CREATE TABLE LIKE statement will use the source schema type instead.
Help would be much appreciated!

When cloning schemas where primary keys in tables are defined as identity columns, postgres automatically creates new sequences for the keys, because of the 'INCLUDING ALL' parameter. But the sequences are not set to the same value as in the original schema, but is reset to the starting value. Any attempts to insert new data into the tables fails because the sequence starts at 1.
The script should update the corresponding sequences with the correct value from the sequence in the source schema.

Regression testing revealed some errors.

1. an error with the pg_proc table. v10 does not have the prokind column, but uses the proisagg column instead.
2. errors with creating some aggregate functions.
3. Inheritance-based tables are copied to target as stand-alone tables losing their inheritance to a parent.

When I try to execute the subjected comment getting the below error.

SQL Error [P0001]: ERROR: Action: Tables Diagnostics: line=SQL statement "INSERT INTO sample_clone.address SELECT * FROM sample.address;"
PL/pgSQL function clone_schema(text,text,boolean,boolean) line 346 at EXECUTE. 428C9. cannot insert into column "id"
Where: PL/pgSQL function clone_schema(text,text,boolean,boolean) line 753 at RAISE

Currently, the FKeys created for the new schema point to the old schema tables! Note to myself, fix it!

This issue is related to #34 and #35 fix.

For the "create functions" part, there is now:
EXECUTE 'SET search_path = ' || quote_ident(source_schema); to fix those issues.
However, I have a schema to clone with a function referencing a schema table without its schema prefix. The cloned function references the source schema table instead of the cloned one.
The SELECT replace(qry, quote_ident(source_schema) || '.', quote_ident(dest_schema) || '.') INTO dest_qry; would not help as the table is not prefixed in the function definition.

So, for instance, in the source schema "srcschema":

CREATE FUNCTION somefunc(bigint) RETURNS SETOF sometable
    LANGUAGE sql
    AS $_$SELECT sometable.* FROM sometable WHERE sometable.somefield > $1 AND  sometable.somefield < 2*$1 $_$;

But in the cloned schema I got:

CREATE FUNCTION somefunc(bigint) RETURNS SETOF srcschema.sometable
    LANGUAGE sql
    AS $_$SELECT sometable.* FROM srcschema.sometable WHERE sometable.somefield > $1 AND  sometable.somefield < 2*$1 $_$;

I guessed it is because the search path is set to the source schema and the object "sometable" is found there when the table is created. I think we should use the destination schema instead:
EXECUTE 'SET search_path = ' || quote_ident(dest_schema);
BUT the problem would be that some functions not cloned yet may be requested by others being created. I would see 2 ways of solving that issue: the first one would be to have a complex algorithm that would determine function dependencies and order them. The second one would be to have a stack of functions to create, start from the top of the stack, catch "missing function" exception while create each function and put back not created functions at the bottom of the stack. There should be some counter that prevents a function to be put at the bottom of the stack more than a certain number of times (20?) to avoid an infinite loop on the stack.
...or another approach I don't think of. :)

I think we need to add domain defs.

Rules are also something that can be cloned as part of a schema copy. Implement this.

Currently, clone_schema does not copy rows from tables that have user-defined column data types. That is because you cannot cast from a schema.user-defined column type to another. It's just a limitation of PG. So the normal INSERT INTO ... SELECT FROM .... syntax won't work even with casting methods.

An inefficient method will be utilized to use the COPY command to export and import from file for these cases. Some limitations will still exist:

1. It will not work on PG in DBaaS environments, i.e., AWS, Azure, GCP.

It does require that the user be a superuser to write to the PG server underlying filesystem. It also assumes the existing of the /tmp directory on Linux distros and c:\windows\temp on Windows versions. The same file will be used in all cases, so as not to clutter the file system.

Feel free to interject other ideas about this.

Currently, comments on most objects are not captured and cloned. Thanks to @guignonv for providing the code to do it for indexes only, which is implemented. Please add comments for other objects as well. And note them here as they are done and integrated into the master.

Rows are not being copied correctly for parent/child tables for both inheritance and declarative partitioned tables.

Currently, policies are cloned correctly, but not enabled for tables affected by those RLS policies.