132 lines
4.0 KiB
PL/PgSQL
132 lines
4.0 KiB
PL/PgSQL
-- Migration 005: Schema Separation for Dev/Testing/Production Environments
|
|
-- This migration creates separate schemas to avoid data collision between environments.
|
|
-- Each service should use the DB_SCHEMA environment variable to set search_path.
|
|
|
|
-- ============================================
|
|
-- PART 1: Create Environment Schemas
|
|
-- ============================================
|
|
|
|
-- Development schema (default)
|
|
CREATE SCHEMA IF NOT EXISTS dev;
|
|
|
|
-- Testing/QA schema
|
|
CREATE SCHEMA IF NOT EXISTS testing;
|
|
|
|
-- Production schema
|
|
CREATE SCHEMA IF NOT EXISTS prod;
|
|
|
|
-- ============================================
|
|
-- PART 2: Grant Permissions
|
|
-- ============================================
|
|
|
|
-- Grant usage on all schemas to the application user
|
|
-- Note: Adjust 'user' to match your DB_USER if different
|
|
DO $$
|
|
BEGIN
|
|
-- Grant permissions to the default user
|
|
EXECUTE format('GRANT ALL ON SCHEMA dev TO %I', current_user);
|
|
EXECUTE format('GRANT ALL ON SCHEMA testing TO %I', current_user);
|
|
EXECUTE format('GRANT ALL ON SCHEMA prod TO %I', current_user);
|
|
END $$;
|
|
|
|
-- ============================================
|
|
-- PART 3: Create Tables in Each Schema
|
|
-- ============================================
|
|
|
|
-- Note: The following creates the base tables in each schema.
|
|
-- This ensures each environment has isolated data.
|
|
|
|
-- Function to replicate tables from public schema to a target schema
|
|
CREATE OR REPLACE FUNCTION clone_tables_to_schema(target_schema TEXT)
|
|
RETURNS VOID AS $$
|
|
DECLARE
|
|
tbl RECORD;
|
|
sql_stmt TEXT;
|
|
BEGIN
|
|
-- Clone each table from public schema
|
|
FOR tbl IN
|
|
SELECT tablename
|
|
FROM pg_tables
|
|
WHERE schemaname = 'public'
|
|
AND tablename NOT LIKE 'pg_%'
|
|
LOOP
|
|
-- Create table if not exists (structure only)
|
|
sql_stmt := format(
|
|
'CREATE TABLE IF NOT EXISTS %I.%I (LIKE public.%I INCLUDING ALL)',
|
|
target_schema, tbl.tablename, tbl.tablename
|
|
);
|
|
EXECUTE sql_stmt;
|
|
END LOOP;
|
|
|
|
-- Clone sequences
|
|
FOR tbl IN
|
|
SELECT sequence_name
|
|
FROM information_schema.sequences
|
|
WHERE sequence_schema = 'public'
|
|
LOOP
|
|
sql_stmt := format(
|
|
'CREATE SEQUENCE IF NOT EXISTS %I.%I',
|
|
target_schema, tbl.sequence_name
|
|
);
|
|
BEGIN
|
|
EXECUTE sql_stmt;
|
|
EXCEPTION WHEN duplicate_table THEN
|
|
-- Sequence already exists, skip
|
|
END;
|
|
END LOOP;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
-- Clone tables to each schema
|
|
SELECT clone_tables_to_schema('dev');
|
|
SELECT clone_tables_to_schema('testing');
|
|
SELECT clone_tables_to_schema('prod');
|
|
|
|
-- Copy existing data from public to dev schema (for development continuity)
|
|
DO $$
|
|
DECLARE
|
|
tbl RECORD;
|
|
sql_stmt TEXT;
|
|
BEGIN
|
|
FOR tbl IN
|
|
SELECT tablename
|
|
FROM pg_tables
|
|
WHERE schemaname = 'public'
|
|
AND tablename NOT LIKE 'pg_%'
|
|
LOOP
|
|
-- Copy data only if target table is empty
|
|
sql_stmt := format(
|
|
'INSERT INTO dev.%I SELECT * FROM public.%I WHERE NOT EXISTS (SELECT 1 FROM dev.%I LIMIT 1)',
|
|
tbl.tablename, tbl.tablename, tbl.tablename
|
|
);
|
|
BEGIN
|
|
EXECUTE sql_stmt;
|
|
EXCEPTION WHEN OTHERS THEN
|
|
-- Log and continue if copy fails
|
|
RAISE NOTICE 'Could not copy data for table %: %', tbl.tablename, SQLERRM;
|
|
END;
|
|
END LOOP;
|
|
END $$;
|
|
|
|
-- ============================================
|
|
-- PART 4: Cleanup
|
|
-- ============================================
|
|
|
|
-- Drop the helper function
|
|
DROP FUNCTION IF EXISTS clone_tables_to_schema(TEXT);
|
|
|
|
-- ============================================
|
|
-- USAGE NOTES:
|
|
-- ============================================
|
|
-- To use a specific schema in your Go service, set search_path in the connection:
|
|
--
|
|
-- connStr := fmt.Sprintf(
|
|
-- "user=%s password=%s dbname=%s host=%s sslmode=%s search_path=%s",
|
|
-- user, password, name, host, sslMode, schema
|
|
-- )
|
|
--
|
|
-- Or execute after connection:
|
|
-- db.Exec(fmt.Sprintf("SET search_path TO %s", schema))
|
|
--
|
|
-- Environment Variable: DB_SCHEMA (dev, testing, prod)
|