DEV Community

Cover image for Clone schema in PostgreSQL
Renato Suero
Renato Suero

Posted on

10 2

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.

CREATE OR REPLACE FUNCTION public.clone_schema(
source_schema text,
dest_schema text)
RETURNS void AS
$BODY$
DECLARE
object text;
buffer text;
default_ text;
column_ text;
constraint_name_ text;
constraint_def_ text;
trigger_name_ text;
trigger_timing_ text;
trigger_events_ text;
trigger_orientation_ text;
trigger_action_ text;
BEGIN
-- replace existing schema
EXECUTE 'DROP SCHEMA IF EXISTS ' || dest_schema || ' CASCADE';
-- create schema
EXECUTE 'CREATE SCHEMA ' || dest_schema ;
-- create sequences
FOR object IN
SELECT sequence_name::text FROM information_schema.SEQUENCES WHERE sequence_schema = source_schema
LOOP
EXECUTE 'CREATE SEQUENCE ' || dest_schema || '.' || object;
END LOOP;
-- create tables
FOR object IN
SELECT table_name::text FROM information_schema.TABLES WHERE table_schema = source_schema
LOOP
buffer := dest_schema || '.' || object;
-- create table
EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || source_schema || '.' || object || ' INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING DEFAULTS)';
-- fix sequence defaults
FOR column_, default_ IN
SELECT column_name::text, REPLACE(column_default::text, source_schema||'.', dest_schema||'.') FROM information_schema.COLUMNS WHERE table_schema = dest_schema AND table_name = object AND column_default LIKE 'nextval(%' || source_schema || '.%::regclass)'
LOOP
EXECUTE 'ALTER TABLE ' || buffer || ' ALTER COLUMN ' || column_ || ' SET DEFAULT ' || default_;
END LOOP;
-- create triggers
FOR trigger_name_, trigger_timing_, trigger_events_, trigger_orientation_, trigger_action_ IN
SELECT trigger_name::text, action_timing::text, string_agg(event_manipulation::text, ' OR '), action_orientation::text, action_statement::text FROM information_schema.TRIGGERS WHERE event_object_schema=source_schema and event_object_table=object GROUP BY trigger_name, action_timing, action_orientation, action_statement
LOOP
EXECUTE 'CREATE TRIGGER ' || trigger_name_ || ' ' || trigger_timing_ || ' ' || trigger_events_ || ' ON ' || buffer || ' FOR EACH ' || trigger_orientation_ || ' ' || trigger_action_;
END LOOP;
END LOOP;
-- reiterate tables and create foreign keys
FOR object IN
SELECT table_name::text FROM information_schema.TABLES WHERE table_schema = source_schema
LOOP
buffer := dest_schema || '.' || object;
-- create foreign keys
FOR constraint_name_, constraint_def_ IN
SELECT conname::text, REPLACE(pg_get_constraintdef(pg_constraint.oid), source_schema||'.', dest_schema||'.') FROM pg_constraint INNER JOIN pg_class ON conrelid=pg_class.oid INNER JOIN pg_namespace ON pg_namespace.oid=pg_class.relnamespace WHERE contype='f' and relname=object and nspname=source_schema
LOOP
EXECUTE 'ALTER TABLE '|| buffer ||' ADD CONSTRAINT '|| constraint_name_ ||' '|| constraint_def_;
END LOOP;
END LOOP;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
view raw clone_schema.sql hosted with ❀ by GitHub

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

Do your career a big favor. Join DEV. (The website you're on right now)

It takes one minute, it's free, and is worth it for your career.

Get started

Community matters

Top comments (7)

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
 
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
 
corbalanmagui_ profile image
Magui Corbalan β€’

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

AWS Security LIVE!

Tune in for AWS Security LIVE!

Join AWS Security LIVE! for expert insights and actionable tips to protect your organization and keep security teams prepared.

Learn More

πŸ‘‹ Kindness is contagious

Engage with a sea of insights in this enlightening article, highly esteemed within the encouraging DEV Community. Programmers of every skill level are invited to participate and enrich our shared knowledge.

A simple "thank you" can uplift someone's spirits. Express your appreciation in the comments section!

On DEV, sharing knowledge smooths our journey and strengthens our community bonds. Found this useful? A brief thank you to the author can mean a lot.

Okay