# macOS
brew install postgresql@15

# Ubuntu / Debian (VPS)
sudo apt install -y postgresql-client-15

# Verify installation
pg_dump --version
# → pg_dump (PostgreSQL) 15.x

npm install -g supabase
supabase login
# Opens browser for OAuth — authorize with your account

# Link your Supabase project
supabase link --project-ref your_project_ref

# Export schema (no data) — Supabase CLI migration
supabase db dump --schema-only -f schema.sql

# Verify the output
wc -l schema.sql
head -50 schema.sql

SELECT extname, extversion
FROM pg_extension
ORDER BY extname;

-- Supabase-managed extensions to watch for:
-- pg_graphql, pg_net, pg_stat_monitor,
-- supabase_vault, pgsodium, http

# Set your Supabase connection string
export DB_URL="postgresql://postgres:[PASSWORD]@db.[PROJECT_REF].supabase.co:5432/postgres"

# pg_dump Supabase database (schema + data), compressed
pg_dump \
  --format=custom \
  --no-acl \
  --no-owner \
  --schema=public \
  --file=supabase_backup.dump \
  "$DB_URL"

# Check dump file size
ls -lh supabase_backup.dump

# Dump only the auth schema (users + sessions)
pg_dump \
  --format=custom \
  --no-acl \
  --no-owner \
  --schema=auth \
  --file=supabase_auth.dump \
  "$DB_URL"

# docker-compose.yml
version: '3.9'
services:
  postgres:
    image: postgres:15-alpine
    restart: unless-stopped
    environment:
      POSTGRES_DB: myapp
      POSTGRES_USER: postgres
      POSTGRES_PASSWORD: ${DB_PASSWORD}
    ports:
      - "5432:5432"
    volumes:
      - postgres_data:/var/lib/postgresql/data
      - ./init:/docker-entrypoint-initdb.d

  pgbouncer:
    image: bitnami/pgbouncer:latest
    environment:
      POSTGRESQL_HOST: postgres
      POSTGRESQL_PORT: 5432
      PGBOUNCER_DATABASE: myapp
      PGBOUNCER_POOL_MODE: transaction
      PGBOUNCER_MAX_CLIENT_CONN: 1000
    ports:
      - "6432:6432"
    depends_on:
      - postgres

volumes:
  postgres_data:

docker compose up -d

# Verify container is running
docker compose ps

# Connect and test
psql -h localhost -U postgres -d myapp -c "SELECT version();"

-- Connect to your new self-hosted database
CREATE EXTENSION IF NOT EXISTS uuid-ossp;
CREATE EXTENSION IF NOT EXISTS pgcrypto;
CREATE EXTENSION IF NOT EXISTS pg_trgm;  -- for text search
CREATE EXTENSION IF NOT EXISTS btree_gin;
CREATE EXTENSION IF NOT EXISTS vector;   -- if using pgvector

export LOCAL_URL="postgresql://postgres:password@localhost:5432/myapp"

# Restore Supabase backup with 4 parallel jobs
pg_restore \
  --format=custom \
  --no-acl \
  --no-owner \
  --jobs=4 \
  --dbname="$LOCAL_URL" \
  supabase_backup.dump

# Check for errors in output
echo "Restore exit code: $?"

-- Run on BOTH databases and compare for zero data loss
SELECT
  schemaname,
  tablename,
  n_live_tup AS row_count
FROM pg_stat_user_tables
WHERE schemaname = 'public'
ORDER BY n_live_tup DESC;

-- Run ANALYZE first for accurate counts
ANALYZE;
SELECT relname, reltuples::bigint AS estimated_rows
FROM pg_class
WHERE relkind = 'r' AND relnamespace = 'public'::regnamespace
ORDER BY reltuples DESC;

-- Run on Supabase production DB
COPY (
  SELECT
    id,
    email,
    raw_user_meta_data,
    created_at,
    last_sign_in_at
  FROM auth.users
  WHERE deleted_at IS NULL
) TO '/tmp/users_export.csv' CSV HEADER;

gotrue:
  image: supabase/gotrue:v2.156.0
  environment:
    GOTRUE_API_HOST: 0.0.0.0
    GOTRUE_API_PORT: 9999
    GOTRUE_DB_DRIVER: postgres
    DATABASE_URL: postgres://postgres:password@postgres:5432/myapp?search_path=auth
    GOTRUE_SITE_URL: https://yourdomain.com
    GOTRUE_JWT_SECRET: ${JWT_SECRET}
    GOTRUE_JWT_EXP: 3600
    GOTRUE_SMTP_HOST: your-smtp-host
    GOTRUE_SMTP_PORT: 587
    GOTRUE_SMTP_USER: ${SMTP_USER}
    GOTRUE_SMTP_PASS: ${SMTP_PASS}
  ports:
    - "9999:9999"

# ── OLD (Supabase cloud) ────────────────────────
NEXT_PUBLIC_SUPABASE_URL=https://xxxx.supabase.co
NEXT_PUBLIC_SUPABASE_ANON_KEY=eyJhbGciOiJIUzI1...
SUPABASE_SERVICE_ROLE_KEY=eyJhbGciOiJIUzI1...

# ── NEW (self-hosted / VPS) ────────────────────
DATABASE_URL=postgresql://postgres:pass@localhost:5432/myapp
GOTRUE_URL=http://localhost:9999
JWT_SECRET=your-local-jwt-secret
PGBOUNCER_URL=postgresql://postgres:pass@localhost:6432/myapp

// BEFORE — Supabase client
import { createClient } from '@supabase/supabase-js'
const supabase = createClient(SUPABASE_URL, ANON_KEY)

// AFTER — Direct PostgreSQL via Drizzle / Prisma
import { drizzle } from 'drizzle-orm/postgres-js'
import postgres from 'postgres'

const client = postgres(process.env.DATABASE_URL!)
export const db = drizzle(client)

// Example query (same logic, different client)
const users = await db
  .select()
  .from(usersTable)
  .where(eq(usersTable.active, true))

-- Create a drop-in replacement for auth.uid()
CREATE SCHEMA IF NOT EXISTS auth;

CREATE OR REPLACE FUNCTION auth.uid()
RETURNS uuid
LANGUAGE sql
STABLE
AS $$
  SELECT
    NULLIF(
      current_setting('request.jwt.claims', true)::json->>'sub',
      ''
    )::uuid
$$;

-- Also replace auth.role()
CREATE OR REPLACE FUNCTION auth.role()
RETURNS text
LANGUAGE sql
STABLE
AS $$
  SELECT NULLIF(
    current_setting('request.jwt.claims', true)::json->>'role',
    ''
  )
$$;

#!/bin/bash
# validate-supabase-migration.sh

echo "=== Running Supabase migration validation ==="

# 1. Check connection to self-hosted PostgreSQL
psql "$LOCAL_URL" -c "SELECT 1;" && echo "✓ Connection OK"

# 2. Verify extensions match Supabase original
psql "$LOCAL_URL" -c "SELECT extname FROM pg_extension ORDER BY extname;"

# 3. Check row counts — confirm Supabase migration without data loss
psql "$LOCAL_URL" -c "
  SELECT tablename, n_live_tup
  FROM pg_stat_user_tables
  WHERE schemaname='public'
  ORDER BY n_live_tup DESC LIMIT 20;
"

# 4. Verify RLS is enabled on tables (Supabase migration errors often here)
psql "$LOCAL_URL" -c "
  SELECT tablename, rowsecurity
  FROM pg_tables
  WHERE schemaname = 'public';
"

echo "=== Validation complete ==="