SQL script to fully clone a schema and all of its contents in a Postgres DB

clone_schema.sql

-- Drop the schema if it currently exists
DROP SCHEMA IF EXISTS "platform-tmp" CASCADE;

-- Create the new schema
CREATE SCHEMA "platform-tmp";

-- Copy all objects from platform schema to platform-tmp schema
CREATE OR REPLACE FUNCTION clone_schema(source_schema text, dest_schema text) RETURNS void AS
$$
DECLARE
  obj record;
  enum_obj record;
  buffer text;
  command text;
  fkey record;
BEGIN
  -- Create the schema if it doesn't exist
  EXECUTE 'CREATE SCHEMA IF NOT EXISTS ' || quote_ident(dest_schema);
  
  -- First, handle ENUMs (custom types)
  FOR enum_obj IN
    SELECT n.nspname AS schema_name, t.typname AS type_name, 
           string_agg(e.enumlabel, ',' ORDER BY e.enumsortorder) AS enum_values
    FROM pg_type t 
    JOIN pg_enum e ON t.oid = e.enumtypid  
    JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
    WHERE n.nspname = source_schema
    GROUP BY schema_name, type_name
  LOOP
    -- Create the enum type in the destination schema
    command := 'CREATE TYPE ' || quote_ident(dest_schema) || '.' || 
               quote_ident(enum_obj.type_name) || ' AS ENUM (';
               
    -- Add the enum values
    buffer := '';
    FOR obj IN 
      SELECT e.enumlabel
      FROM pg_type t 
      JOIN pg_enum e ON t.oid = e.enumtypid  
      JOIN pg_catalog.pg_namespace n ON n.oid = t.typnamespace
      WHERE n.nspname = source_schema AND t.typname = enum_obj.type_name
      ORDER BY e.enumsortorder
    LOOP
      IF buffer <> '' THEN
        buffer := buffer || ', ';
      END IF;
      buffer := buffer || quote_literal(obj.enumlabel);
    END LOOP;
    
    command := command || buffer || ')';
    
    BEGIN
      EXECUTE command;
    EXCEPTION WHEN OTHERS THEN
      RAISE NOTICE 'Error creating enum type % in schema %: %', enum_obj.type_name, dest_schema, SQLERRM;
    END;
  END LOOP;
  
  -- Tables (including data, constraints, indexes, etc.)
  FOR obj IN
    SELECT table_name::text
    FROM information_schema.tables
    WHERE table_schema = source_schema
    AND table_type = 'BASE TABLE'
  LOOP
    command := 'CREATE TABLE ' || quote_ident(dest_schema) || '.' || quote_ident(obj.table_name) || ' (LIKE ' || quote_ident(source_schema) || '.' || quote_ident(obj.table_name) || ' INCLUDING ALL)';
    BEGIN
      EXECUTE command;
    EXCEPTION WHEN OTHERS THEN
      RAISE NOTICE 'Error creating table % in schema %: %', obj.table_name, dest_schema, SQLERRM;
    END;
    
    command := 'INSERT INTO ' || quote_ident(dest_schema) || '.' || quote_ident(obj.table_name) || ' SELECT * FROM ' || quote_ident(source_schema) || '.' || quote_ident(obj.table_name);
    BEGIN
      EXECUTE command;
    EXCEPTION WHEN OTHERS THEN
      RAISE NOTICE 'Error copying data for table % in schema %: %', obj.table_name, dest_schema, SQLERRM;
    END;
  END LOOP;

  -- Foreign Keys (have to be added separately)
  FOR fkey IN
    SELECT
      con.conname AS constraint_name,
      cl.relname AS table_name,
      att.attname AS column_name,
      cl2.relname AS referenced_table,
      att2.attname AS referenced_column,
      pg_get_constraintdef(con.oid) AS constraint_def
    FROM
      pg_constraint con
      JOIN pg_class cl ON con.conrelid = cl.oid
      JOIN pg_namespace nsp ON cl.relnamespace = nsp.oid
      JOIN pg_attribute att ON att.attrelid = cl.oid AND att.attnum = ANY(con.conkey)
      JOIN pg_class cl2 ON con.confrelid = cl2.oid
      JOIN pg_namespace nsp2 ON cl2.relnamespace = nsp2.oid
      JOIN pg_attribute att2 ON att2.attrelid = cl2.oid AND att2.attnum = ANY(con.confkey)
    WHERE
      con.contype = 'f'
      AND nsp.nspname = source_schema
  LOOP
    -- Extract the constraint definition to modify it
    buffer := fkey.constraint_def;
    
    -- Replace references to original schema with new schema
    buffer := regexp_replace(buffer, 'REFERENCES ' || quote_ident(source_schema) || '\.', 'REFERENCES ' || quote_ident(dest_schema) || '.', 'g');
    
    command := 'ALTER TABLE ' || quote_ident(dest_schema) || '.' || quote_ident(fkey.table_name) || 
              ' ADD CONSTRAINT ' || quote_ident(fkey.constraint_name) || ' ' || buffer;
              
    BEGIN
      EXECUTE command;
    EXCEPTION WHEN OTHERS THEN
      RAISE NOTICE 'Error creating foreign key constraint % in schema %: %', fkey.constraint_name, dest_schema, SQLERRM;
    END;
  END LOOP;

  -- Sequences
  FOR obj IN
    SELECT sequence_name::text
    FROM information_schema.sequences
    WHERE sequence_schema = source_schema
  LOOP
    command := 'CREATE SEQUENCE ' || quote_ident(dest_schema) || '.' || quote_ident(obj.sequence_name);
    BEGIN
      EXECUTE command;
    EXCEPTION WHEN OTHERS THEN
      RAISE NOTICE 'Error creating sequence % in schema %: %', obj.sequence_name, dest_schema, SQLERRM;
    END;
    
    command := 'SELECT setval(' || quote_literal(quote_ident(dest_schema) || '.' || quote_ident(obj.sequence_name)) || 
               ', nextval(' || quote_literal(quote_ident(source_schema) || '.' || quote_ident(obj.sequence_name)) || '))';
    BEGIN              
      EXECUTE command;
    EXCEPTION WHEN OTHERS THEN
      RAISE NOTICE 'Error setting sequence value for % in schema %: %', obj.sequence_name, dest_schema, SQLERRM;
    END;
  END LOOP;

  -- Views
  FOR obj IN
    SELECT table_name::text, view_definition
    FROM information_schema.views
    WHERE table_schema = source_schema
  LOOP
    -- Create view with modified schema references
    buffer := regexp_replace(obj.view_definition, quote_ident(source_schema) || '\.', quote_ident(dest_schema) || '.', 'g');
    
    command := 'CREATE OR REPLACE VIEW ' || quote_ident(dest_schema) || '.' || quote_ident(obj.table_name) || ' AS ' || buffer;
    BEGIN
      EXECUTE command;
    EXCEPTION WHEN OTHERS THEN
      RAISE NOTICE 'Error creating view % in schema %: %', obj.table_name, dest_schema, SQLERRM;
      -- Fallback method if regex replacement fails
      command := 'CREATE OR REPLACE VIEW ' || quote_ident(dest_schema) || '.' || quote_ident(obj.table_name) || 
                 ' AS SELECT * FROM ' || quote_ident(source_schema) || '.' || quote_ident(obj.table_name);
      BEGIN
        EXECUTE command;
      EXCEPTION WHEN OTHERS THEN
        RAISE NOTICE 'Fallback also failed for view % in schema %: %', obj.table_name, dest_schema, SQLERRM;
      END;
    END;
  END LOOP;

  -- Functions and Procedures
  FOR obj IN
    SELECT p.proname::text, pg_get_functiondef(p.oid) AS definition
    FROM pg_proc p
    JOIN pg_namespace n ON p.pronamespace = n.oid
    WHERE n.nspname = source_schema
  LOOP
    -- Create function with modified schema references
    buffer := regexp_replace(obj.definition, 'SCHEMA ' || quote_ident(source_schema), 'SCHEMA ' || quote_ident(dest_schema), 'g');
    buffer := regexp_replace(buffer, quote_ident(source_schema) || '\.', quote_ident(dest_schema) || '.', 'g');
    
    BEGIN
      EXECUTE buffer;
    EXCEPTION WHEN OTHERS THEN
      RAISE NOTICE 'Error creating function % in schema %: %', obj.proname, dest_schema, SQLERRM;
    END;
  END LOOP;

  -- Triggers
  FOR obj IN
    SELECT trigger_name::text, event_manipulation, event_object_table, action_statement
    FROM information_schema.triggers
    WHERE trigger_schema = source_schema
  LOOP
    buffer := regexp_replace(obj.action_statement, quote_ident(source_schema) || '\.', quote_ident(dest_schema) || '.', 'g');
    
    command := 'CREATE TRIGGER ' || quote_ident(obj.trigger_name) || 
               ' ' || obj.event_manipulation || ' ON ' || 
               quote_ident(dest_schema) || '.' || quote_ident(obj.event_object_table) || 
               ' ' || buffer;
    
    BEGIN
      EXECUTE command;
    EXCEPTION WHEN OTHERS THEN
      RAISE NOTICE 'Error creating trigger % in schema %: %', obj.trigger_name, dest_schema, SQLERRM;
    END;
  END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Execute the function to clone the schema
SELECT clone_schema('platform', 'platform-tmp');

-- Drop the function when done
DROP FUNCTION clone_schema(text, text);