Complete Supabase Database Migration Guide for 2025

Overview

Migrating a database to Supabase involves more than just copying data. You need to handle schema, relationships, RLS policies, auth users, and storage files. This guide walks through each step methodically.

Phase 1: Pre-Migration Audit

Before touching anything, audit your current database:

-- List all tables and row counts
SELECT schemaname, tablename, n_live_tup
FROM pg_stat_user_tables
ORDER BY n_live_tup DESC;

-- List all foreign keys
SELECT tc.table_name, kcu.column_name, ccu.table_name AS foreign_table
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name
JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name
WHERE tc.constraint_type = 'FOREIGN KEY';

Document all tables, relationships, indexes, functions, and triggers before migration.

Phase 2: Schema Migration

Export your schema without data first:

pg_dump -h your-host -U your-user -d your-db   --schema-only   --no-owner   --no-acl   -f schema.sql

Edit the schema file to remove any platform-specific extensions or sequences that conflict with Supabase. Then apply it to your new Supabase database:

psql -h db.yourproject.supabase.co -U postgres -d postgres -f schema.sql

Phase 3: Data Migration

For small databases (under 1GB), use pg_dump with data only:

pg_dump -h your-host -U your-user -d your-db   --data-only   --no-owner   --disable-triggers   -f data.sql

For large databases, use COPY format for speed:

pg_dump -h your-host -U your-user -d your-db   --format=custom   --data-only   -f data.dump

pg_restore -h db.yourproject.supabase.co -U postgres -d postgres   --data-only data.dump

Phase 4: Validate Data Integrity

After import, validate row counts match:

-- Run on both old and new DB, compare outputs
SELECT tablename, n_live_tup
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY tablename;

Also validate FK integrity:

-- Check for orphaned records
SELECT COUNT(*) FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL;

Phase 5: Configure RLS Policies

Enable RLS on all tables and create appropriate policies:

-- Enable RLS
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE orders ENABLE ROW LEVEL SECURITY;

-- Users read own data
CREATE POLICY "profiles_select_own" ON profiles
  FOR SELECT USING (auth.uid() = id);

-- Service role bypasses RLS (for admin operations)
-- This is automatic — no policy needed for service role

Phase 6: Test API Endpoints

Before switching traffic, test every API endpoint against the Supabase backend. Pay special attention to:

• Authentication flows (login, signup, password reset)
• Data reads and writes under authenticated and anon roles
• File uploads and downloads (if using Storage)
• Real-time subscriptions (if applicable)

Phase 7: Production Cutover

1. Run a final incremental data sync to capture changes since initial export
2. Enable maintenance mode on your app
3. Run final sync
4. Update environment variables to point to Supabase
5. Deploy updated application
6. Run smoke tests
7. Disable maintenance mode
8. Monitor for 48 hours

Need this done for you? Our migration service handles everything from audit to production deployment.