Skip to content

Instantly share code, notes, and snippets.

@eleezs

eleezs/migration.sql

Forked from bolubee101/migration.sql
Created June 3, 2024 11:07
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save eleezs/c2856384201f883f6c88b59f5b95c14c to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save eleezs/c2856384201f883f6c88b59f5b95c14c to your computer and use it in GitHub Desktop.
Download ZIP
PostgreSQL Schema and Data Migration Script Using the dblink extension
Raw
migration.sql
DO $$
DECLARE
tbl RECORD;
col RECORD;
seq RECORD;
udt RECORD;
schema RECORD;
sql TEXT;
default_expr TEXT;
column_definitions TEXT;
column_definitions_with_types TEXT;
BEGIN
-- Disconnect if a connection with the same name already exists
BEGIN
PERFORM dblink_disconnect('source_conn');
EXCEPTION
WHEN others THEN
-- Do nothing if there was no existing connection
END;
-- Connect to the source database
PERFORM dblink_connect('source_conn', 'dbname=api_dev_db user=devdb_h2b5_user password=GvwXu8UvR4t9Sx1fnhrfMZIQTWfkcjU5 host=dpg-cnslbufjbltc73eu7vcg-a.oregon-postgres.render.com');
-- Loop through user-created schemas (excluding system schemas)
FOR schema IN
SELECT nspname
FROM dblink('source_conn',
'SELECT nspname FROM pg_namespace WHERE nspname NOT LIKE ''pg_%'' AND nspname NOT IN (''information_schema'')')
AS s(nspname TEXT)
LOOP
-- Create schema in the target database if it does not exist
sql := 'CREATE SCHEMA IF NOT EXISTS ' || quote_ident(schema.nspname) || ';';
EXECUTE sql;
RAISE NOTICE 'Schema created or already exists: %', quote_ident(schema.nspname);
-- Transfer all user-defined types (enums) for each schema
FOR udt IN
SELECT type_name, enum_labels
FROM dblink('source_conn',
'SELECT t.typname AS type_name, ARRAY_AGG(e.enumlabel ORDER BY e.enumsortorder) AS enum_labels
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 = ''' || schema.nspname || '''
GROUP BY t.typname')
AS s(type_name TEXT, enum_labels TEXT[])
LOOP
-- Create user-defined type in the target database
sql := 'CREATE TYPE ' || quote_ident(schema.nspname) || '.' || quote_ident(udt.type_name) || ' AS ENUM (';
FOR i IN array_lower(udt.enum_labels, 1) .. array_upper(udt.enum_labels, 1)
LOOP
sql := sql || quote_literal(udt.enum_labels[i]) || ', ';
END LOOP;
sql := left(sql, -2) || ');';
EXECUTE sql;
RAISE NOTICE 'Enum type created: %.%', quote_ident(schema.nspname), quote_ident(udt.type_name);
END LOOP;
-- Transfer all sequences for each schema
FOR seq IN
SELECT schemaname, sequencename
FROM dblink('source_conn',
'SELECT n.nspname AS schemaname, c.relname AS sequencename
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = ''S'' AND n.nspname = ''' || schema.nspname || '''')
AS s(schemaname TEXT, sequencename TEXT)
LOOP
-- Create sequence in the target database
sql := 'CREATE SEQUENCE ' || quote_ident(seq.schemaname) || '.' || quote_ident(seq.sequencename) || ';';
EXECUTE sql;
RAISE NOTICE 'Sequence created: %.%', quote_ident(seq.schemaname), quote_ident(seq.sequencename);
-- Set the current value of the sequence
sql := 'SELECT setval(''' || quote_ident(seq.schemaname) || '.' || quote_ident(seq.sequencename) || ''', (SELECT last_value FROM dblink(''source_conn'', ''SELECT last_value FROM ' || quote_ident(seq.schemaname) || '.' || quote_ident(seq.sequencename) || ''') AS t(last_value bigint)), true);';
EXECUTE sql;
RAISE NOTICE 'Sequence value set: %.%', quote_ident(seq.schemaname), quote_ident(seq.sequencename);
END LOOP;
-- Transfer all tables without default expressions involving sequences in each schema
FOR tbl IN
SELECT schemaname, tablename
FROM dblink('source_conn',
'SELECT schemaname, tablename FROM pg_tables WHERE schemaname = ''' || schema.nspname || '''')
AS t(schemaname TEXT, tablename TEXT)
LOOP
-- Create table schema in the target database
sql := 'CREATE TABLE ' || quote_ident(tbl.schemaname) || '.' || quote_ident(tbl.tablename) || ' (';
column_definitions := '';
column_definitions_with_types := '';
FOR col IN
SELECT column_name, data_type, is_nullable, column_default, udt_name, is_array
FROM dblink('source_conn',
'SELECT column_name, data_type, is_nullable, column_default, udt_name, (udt_name LIKE ''_%'' AND data_type = ''ARRAY'') AS is_array FROM information_schema.columns WHERE table_schema = ''' || tbl.schemaname || ''' AND table_name = ''' || tbl.tablename || '''')
AS c(column_name TEXT, data_type TEXT, is_nullable TEXT, column_default TEXT, udt_name TEXT, is_array BOOLEAN)
LOOP
IF col.is_array THEN
sql := sql || quote_ident(col.column_name) || ' ' || substr(col.udt_name, 2) || '[]';
column_definitions := column_definitions || quote_ident(col.column_name) || ', ';
column_definitions_with_types := column_definitions_with_types || quote_ident(col.column_name) || ' ' || substr(col.udt_name, 2) || '[], ';
ELSIF col.data_type = 'USER-DEFINED' THEN
sql := sql || quote_ident(col.column_name) || ' ' || quote_ident(tbl.schemaname) || '.' || quote_ident(col.udt_name);
column_definitions := column_definitions || quote_ident(col.column_name) || ', ';
column_definitions_with_types := column_definitions_with_types || quote_ident(col.column_name) || ' ' || quote_ident(tbl.schemaname) || '.' || quote_ident(col.udt_name) || ', ';
ELSE
sql := sql || quote_ident(col.column_name) || ' ' || col.data_type;
column_definitions := column_definitions || quote_ident(col.column_name) || ', ';
column_definitions_with_types := column_definitions_with_types || quote_ident(col.column_name) || ' ' || col.data_type || ', ';
END IF;
IF col.is_nullable = 'NO' THEN
sql := sql || ' NOT NULL';
END IF;
-- Exclude sequence defaults initially
IF col.column_default IS NOT NULL AND col.column_default NOT LIKE 'nextval%' THEN
sql := sql || ' DEFAULT ' || col.column_default;
END IF;
sql := sql || ', ';
END LOOP;
-- Add primary key constraint if the table has an 'id' column
IF EXISTS (
SELECT 1
FROM dblink('source_conn',
'SELECT column_name FROM information_schema.columns WHERE table_schema = ''' || tbl.schemaname || ''' AND table_name = ''' || tbl.tablename || ''' AND column_name = ''id''')
AS id_check(column_name TEXT)
) THEN
sql := sql || 'PRIMARY KEY (id), ';
END IF;
sql := LEFT(sql, -2) || ');';
EXECUTE sql;
RAISE NOTICE 'Table created: %.%', quote_ident(tbl.schemaname), quote_ident(tbl.tablename);
column_definitions := LEFT(column_definitions, -2);
column_definitions_with_types := LEFT(column_definitions_with_types, -2);
-- Insert data into the tables
sql := 'INSERT INTO ' || quote_ident(tbl.schemaname) || '.' || quote_ident(tbl.tablename) || ' (' || column_definitions || ') SELECT * FROM dblink(''source_conn'', ''SELECT * FROM ' || quote_ident(tbl.schemaname) || '.' || quote_ident(tbl.tablename) || ''') AS t(' || column_definitions_with_types || ');';
EXECUTE sql;
RAISE NOTICE 'Data inserted into table: %.%', quote_ident(tbl.schemaname), quote_ident(tbl.tablename);
END LOOP;
-- Apply default expressions for sequences
FOR tbl IN
SELECT schemaname, tablename
FROM dblink('source_conn',
'SELECT schemaname, tablename FROM pg_tables WHERE schemaname = ''' || schema.nspname || '''')
AS t(schemaname TEXT, tablename TEXT)
LOOP
FOR col IN
SELECT column_name, column_default
FROM dblink('source_conn',
'SELECT column_name, column_default FROM information_schema.columns WHERE table_schema = ''' || tbl.schemaname || ''' AND table_name = ''' || tbl.tablename || ''' AND column_default LIKE ''%nextval%''')
AS c(column_name TEXT, column_default TEXT)
LOOP
default_expr := 'ALTER TABLE ' || quote_ident(tbl.schemaname) || '.' || quote_ident(tbl.tablename) || ' ALTER COLUMN ' || quote_ident(col.column_name) || ' SET DEFAULT ' || col.column_default;
EXECUTE default_expr;
RAISE NOTICE 'Default expression set for table: %, column: %', quote_ident(tbl.schemaname), quote_ident(col.column_name);
END LOOP;
END LOOP;
END LOOP;
-- Disconnect from the source database
PERFORM dblink_disconnect('source_conn');
RAISE NOTICE 'Disconnected from source database';
END;
$$ LANGUAGE plpgsql;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment