Postgres doesn’t have built in function to clone schema. However, may of backend developers would like to perform a number of operations at schema level directly from command line tools such as psql or any database developer tools (DBeaver). One of the common operation is to copy or clone the entire schema. While researching any existing solutions, I stumbled upon postgres-general thread where one of the developer(s) provided clone_schema.sql function. I uploaded the existing function provided in thread to my git repo here.
However, when I tried to use it, it was throwing error related to sequence. I updated the function to solve the error reported . Additionally, I added support to copy Materialized Views as well. I have uploaded the final working function here
I have tested the public.clone_schema function for complex schema and it seems to be working with copying schema ONLY or schema WITH DATA as well .
You can call function like this to copy schema with data:
select clone_schema('development', 'development_clone', true);
Alternatively, if you want to copy only schema without data:
select clone_schema('development', 'development_clone', false);
Hope this will help someone. If you have better or other easier approach, please don’t hesitate to put your comments so I can improve processes on my end!
I’ve actually worked on this problem pretty extensively for my company, because we segregate our client’s data into their own schemas.
We have a “template” schema, and when creating a new client that schema needs to be cloned. I’ll have to compare what i’ve worked on to this. I know I did a ton of work to get it working with all the complex types we use (280+ tables, row and statement triggers, materialized and regular views, sequences, etc).
Will be interesting to see the differences and similarities.
Adam ,
Thanks for posting comment.
Yea, I’d be very interesting to see your solution. any chance you can share me work over email or github?
When I try to run the function on my PG 9.6.15 database, I get this error:
# select public.clone_schema(‘public’, ‘clone’, true);
ERROR: relation “pg_catalog.pg_sequences” does not exist
LINE 2: FROM pg_catalog.pg_sequences WHERE schemaname=…
^
QUERY: SELECT max_value, start_value, increment_by, min_value, cache_size, cycle
FROM pg_catalog.pg_sequences WHERE schemaname=’public’ AND sequencename=’check_number_store_id_2_seq';
CONTEXT: PL/pgSQL function clone_schema(text,text,boolean) line 69 at EXECUTE
The Postgres documentation shows that pg_sequences exists in version 10, but not in 9.6 or below. Is Postgres 10 the minimum required version for this function, or PG 11?
If you are using on Postgres 9.6, you can use older version https://github.com/denishpatel/pg-clone-schema/commit/62f4063cc014ea3cacb49d83ab7be3a981031706?diff=unified
How about adding the DDL for “TYPE”?
That’s good idea.. do you want to suggest patch?
https://github.com/denishpatel/pg-clone-schema/blob/master/clone_schema.sql
I’ve already created a pull request with TYPE support for composite and enum types. Ready for you to do the merge for my pull request.
Trigger are not cloned… I have many tables that have trigger defined in another schema and are not cloned.
Hi,
You are right. We have open issues to support additional objects. https://github.com/denishpatel/pg-clone-schema/issues contributions are welcome
As of today, I have added logic for TYPES, COLLATIONS, and DOMAINS.
Im goin crazy today, just added triggers