Skip to content

Instantly share code, notes, and snippets.

@bolubee101

bolubee101/migration.sql

Last active February 26, 2025 08:12
Show Gist options
  • Save bolubee101/2a131cbaf691feca08d8722e3220cb7c to your computer and use it in GitHub Desktop.
Save bolubee101/2a131cbaf691feca08d8722e3220cb7c 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;
@Enzujp
Copy link

Enzujp commented May 27, 2024

What a man. 🥹

@Chinaza0413
Copy link

I no Sabi star for here oh.
Console.log "star"

@Chinaza0413
Copy link

Why e no work?

@Chinaza0413
Copy link

I find am!

@bolubee101
Copy link
Author

I no Sabi star for here oh. Console.log "star"

😭

@bolubee101
Copy link
Author

What a man. 🥹

Thank you.😂

@eleezs
Copy link

eleezs commented Jun 3, 2024

Na why them dey call am "GOAT"

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment