DEV Community

Cover image for Clone schema in PostgreSQL
Renato Suero
Renato Suero

Posted on

Clone schema in PostgreSQL

Hi everyone,

My goal is to keep the post short, so If you don't know what is schema access this link.

Schemas can be useful to clients, maybe a schema by the client or schema by "category". In my job(our business is based in soccer) we use schemas by championship and year.
A sample :
ucl_2018
ucl_2017
championship *that's the model

Ok, now we need to create a new schema ucl_2019, right?

I researched and after some time, I found a function to help me, but I needed to do changes. I didn't remember exactly what I needed to do, so I decided to share the function I've been using. Another thing this function will create everything indexes, keys, foreign keys, etc...

Ok, that is the function you need to run in your database.

After that, you only need to run

select clone_schema('source','destination')
Enter fullscreen mode Exit fullscreen mode

In my case,

select clone_schema('championship','ucl_2019');
Enter fullscreen mode Exit fullscreen mode

I hope it can be useful to you. I love PostgreSQL, so I want to write more about other features I like or I think that can be useful.
My new post will be a sequence for that, I'll show how to update the schemas, running migrations in the schemas. I wrote the post

Top comments (10)

Collapse
 
terry_steadman_dbc20113a1 profile image
Terry Steadman

Greetings.

I found that your function helped partially fill a gap in my processing needs. In my job, I am copying several yearly databases into Postgresql into their own schemas. But I need to also have functions that can read the data and not need to be changed every year. Your function helped in that respect as it allowed me a way to create the new year based schema and then also create a general (destination) schema to hold the current year. The only thing missing was copying the data into the destination schema. I found that by adding the extra code at the end of the other loops sufficed and did the job very well.

        -- reiterate tables and copy the data
        FOR object IN
            SELECT table_name::text FROM information_schema.TABLES WHERE table_schema = source_schema
        LOOP
            buffer := dest_schema || '.' || object;
            EXECUTE 'INSERT INTO ' || buffer || ' SELECT * FROM ' || source_schema || '.' || object;
        END LOOP;

Enter fullscreen mode Exit fullscreen mode

Thank you for your help.

Collapse
 
renatosuero profile image
Renato Suero

my goal was to clone the schema only, but is is a huge add to be here in my code for cases where it could be a needed

Collapse
 
juanefren profile image
Juan Castillo

Which Postgresl version are you using ? I tried it on 11.5 but got Syntax errror.

Collapse
 
renatosuero profile image
Renato Suero

I've been used 10.6 can you tell me the error, maybe I can help you

Collapse
 
kartoffelfee profile image
Jörg Thomsen

great function, thanks

I had to add quote_ident( befor the sequence and table names becaus of a bad names in my db.

25: SELECT QUOTE_IDENT(sequence_name::text) FROM ...
32: SELECT QUOTE_IDENT(table_name::text) FROM ...

Collapse
 
renatosuero profile image
Renato Suero

thanks for your info, it will help others :)

Collapse
 
miguelmendozamolina profile image
Miguel Mendoza Molina

this is amazing, saves me a lot of time, thanks for sharing this information with everyone

Collapse
 
renatosuero profile image
Renato Suero

Hi Miguel, thanks for the feedback I'm so happy to know my post as helpful to someone

Collapse
 
corbalanmagui_ profile image
Magui Corbalan

How can I do to clone also the data of the tables?

Collapse
 
renatosuero profile image
Renato Suero

I'm so sorry I didn't see you message :( I've just seen a person commented below a solution for that