pg clone schema

clone schema.sql

CREATE OR REPLACE FUNCTION clone_schema(source_schema text, dest_schema text)
RETURNS void AS $$
DECLARE
    object text;
    buffer text;
    default_ text;
    column_ text;
BEGIN
    EXECUTE 'CREATE SCHEMA ' || dest_schema;

    -- Clone sequences
    FOR object IN
        SELECT sequence_name FROM information_schema.sequences
        WHERE sequence_schema = source_schema
    LOOP
        EXECUTE 'CREATE SEQUENCE ' || quote_ident(dest_schema) || '.' || quote_ident(object);
    END LOOP;

    -- Clone tables, adjust sequence defaults, and copy data
    FOR object IN
        SELECT table_name FROM information_schema.tables
        WHERE table_schema = source_schema
    LOOP
        buffer := quote_ident(dest_schema) || '.' || quote_ident(object);
        EXECUTE 'CREATE TABLE ' || buffer ||
            ' (LIKE ' || quote_ident(source_schema) || '.' || quote_ident(object) ||
            ' INCLUDING CONSTRAINTS INCLUDING INDEXES INCLUDING DEFAULTS)';

        -- Copy the data
        EXECUTE 'INSERT INTO ' || buffer ||
            ' SELECT * FROM ' || quote_ident(source_schema) || '.' || quote_ident(object);

        -- Update sequence references
        FOR column_, default_ IN
            SELECT column_name, REPLACE(column_default, 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;
    END LOOP;
END;
$$ LANGUAGE plpgsql VOLATILE;

SELECT clone_schema('foo_schema', 'bar_schema');

CREATE OR REPLACE FUNCTION verify_schema_clone(source_schema text, dest_schema text)
RETURNS TABLE (
    table_name text,
    source_count bigint,
    dest_count bigint,
    row_count_match boolean,
    checksum_match boolean
) AS $$
DECLARE
    curr_table text;
    source_count bigint;
    dest_count bigint;
    source_checksum text;
    dest_checksum text;
BEGIN
    FOR curr_table IN
        SELECT t.table_name FROM information_schema.tables t
        WHERE t.table_schema = source_schema
        AND t.table_type = 'BASE TABLE'
    LOOP
        -- Get row counts
        EXECUTE 'SELECT COUNT(*) FROM ' || quote_ident(source_schema) || '.' || quote_ident(curr_table)
            INTO source_count;
        EXECUTE 'SELECT COUNT(*) FROM ' || quote_ident(dest_schema) || '.' || quote_ident(curr_table)
            INTO dest_count;

        -- Calculate checksums for all columns
        EXECUTE 'SELECT MD5(STRING_AGG(CAST(t.* AS TEXT), '''' ORDER BY (SELECT NULL)))
                FROM ' || quote_ident(source_schema) || '.' || quote_ident(curr_table) || ' t'
            INTO source_checksum;
        EXECUTE 'SELECT MD5(STRING_AGG(CAST(t.* AS TEXT), '''' ORDER BY (SELECT NULL)))
                FROM ' || quote_ident(dest_schema) || '.' || quote_ident(curr_table) || ' t'
            INTO dest_checksum;

        table_name := curr_table;
        row_count_match := (source_count = dest_count);
        checksum_match := (source_checksum = dest_checksum);

        RETURN NEXT;
    END LOOP;
END;
$$ LANGUAGE plpgsql;

-- Example usage:
SELECT * FROM verify_schema_clone('foo_schema', 'bar_schema');