database, postgresql

Clone schema in Postgres

11 Comments

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!

11 thoughts on “Clone schema in Postgres”

  1. 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.

    1. 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?

  2. 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?

  5. Trigger are not cloned… I have many tables that have trigger defined in another schema and are not cloned.

Comments are closed.