How to Set Up Multi-Region Database Replication with PostgreSQL 17 and Supabase 1.2 for Global SaaS

# Step 1: Provision Primary PostgreSQL 17 Instance in us-east-1
# Provider configuration for AWS
terraform {
  required_providers {
    aws = {
      source  = "hashicorp/aws"
      version = "~> 5.0"
    }
  }
}

# Configure AWS provider for primary region
provider "aws" {
  region = var.primary_region
}

# Variables for configuration
variable "primary_region" {
  type        = string
  default     = "us-east-1"
  description = "Primary AWS region for PostgreSQL 17 primary instance"
}

variable "db_instance_class" {
  type        = string
  default     = "db.m6g.large"
  description = "EC2 instance class for PostgreSQL 17"
}

variable "db_username" {
  type        = string
  description = "Master username for PostgreSQL 17"
  sensitive   = true
}

variable "db_password" {
  type        = string
  description = "Master password for PostgreSQL 17"
  sensitive   = true
  validation {
    condition     = length(var.db_password) >= 16
    error_message = "DB password must be at least 16 characters long."
  }
}

# VPC for primary database
resource "aws_vpc" "primary_db_vpc" {
  cidr_block           = "10.0.0.0/16"
  enable_dns_support   = true
  enable_dns_hostnames = true

  tags = {
    Name        = "postgres-17-primary-vpc"
    Environment = "production"
    ManagedBy   = "terraform"
  }
}

# Public subnet for PostgreSQL (for initial setup, restrict in prod)
resource "aws_subnet" "primary_public_subnet" {
  vpc_id                  = aws_vpc.primary_db_vpc.id
  cidr_block              = "10.0.1.0/24"
  map_public_ip_on_launch = true
  availability_zone       = "${var.primary_region}a"

  tags = {
    Name = "postgres-17-primary-public-subnet"
  }
}

# Security group allowing PostgreSQL traffic (restrict to internal CIDR in prod)
resource "aws_security_group" "primary_db_sg" {
  vpc_id = aws_vpc.primary_db_vpc.id

  ingress {
    from_port   = 5432
    to_port     = 5432
    protocol    = "tcp"
    cidr_blocks = ["10.0.0.0/16", "192.168.0.0/16"] # Internal only
  }

  egress {
    from_port   = 0
    to_port     = 0
    protocol    = "-1"
    cidr_blocks = ["0.0.0.0/0"]
  }

  tags = {
    Name = "postgres-17-primary-sg"
  }
}

# EC2 instance for PostgreSQL 17 primary
resource "aws_instance" "postgres_primary" {
  ami                    = data.aws_ami.postgres_17_ami.id
  instance_type          = var.db_instance_class
  subnet_id              = aws_subnet.primary_public_subnet.id
  vpc_security_group_ids = [aws_security_group.primary_db_sg.id]
  key_name               = aws_key_pair.postgres_ssh_key.key_name

  root_block_device {
    volume_size = 100
    volume_type = "gp3"
  }

  tags = {
    Name = "postgres-17-primary"
  }
}

# Data source to fetch latest PostgreSQL 17 AMI for Ubuntu 22.04
data "aws_ami" "postgres_17_ami" {
  most_recent = true
  owners      = ["099720109477"] # Canonical

  filter {
    name   = "name"
    values = ["ubuntu/images/hvm-ssd/ubuntu-jammy-22.04-amd64-server-*"]
  }

  filter {
    name   = "root-device-type"
    values = ["ebs"]
  }
}

# SSH key pair for accessing the instance
resource "aws_key_pair" "postgres_ssh_key" {
  key_name   = "postgres-17-primary-key"
  public_key = file("~/.ssh/id_rsa.pub")
}

# Output the primary instance public IP
output "primary_postgres_public_ip" {
  value = aws_instance.postgres_primary.public_ip
}

#!/bin/bash
# Step 2: Install and Configure PostgreSQL 17 on Primary Instance
# Exit on error, print commands
set -euxo pipefail

# Variables (replace with your own values)
DB_USERNAME="postgres_admin"
DB_PASSWORD="SupaBase17!SecurePassword123" # Use terraform output in prod
PRIMARY_IP=$(curl -s http://169.254.169.254/latest/meta-data/public-ipv4)
REPLICA_USER="replica_user"
REPLICA_PASSWORD="ReplicaSecure!456"

# Update system packages
sudo apt-get update -y
sudo apt-get upgrade -y

# Add PostgreSQL 17 official repository
sudo sh -c 'echo "deb http://apt.postgresql.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
wget --quiet -O - https://www.postgresql.org/media/keys/ACCC4CF8.asc | sudo apt-key add -

# Install PostgreSQL 17
sudo apt-get update -y
sudo apt-get install -y postgresql-17 postgresql-client-17

# Stop PostgreSQL to modify config
sudo systemctl stop postgresql@17-main

# Configure postgresql.conf for replication
sudo tee -a /etc/postgresql/17/main/postgresql.conf > /dev/null << EOT
# Replication settings for PostgreSQL 17
listen_addresses = '*'
wal_level = logical
max_replication_slots = 10
max_wal_senders = 10
shared_preload_libraries = 'pg_stat_statements'
track_io_timing = on
# Parallel apply for logical replication (new in PG17)
max_parallel_apply_workers = 4
max_logical_replication_workers = 4
EOT

# Configure pg_hba.conf to allow replication connections
sudo tee -a /etc/postgresql/17/main/pg_hba.conf > /dev/null << EOT
# Allow replication from internal VPC CIDR
host replication ${REPLICA_USER} 10.0.0.0/16 scram-sha-256
host replication ${REPLICA_USER} 192.168.0.0/16 scram-sha-256
# Allow local connections
local replication ${REPLICA_USER} trust
EOT

# Start PostgreSQL
sudo systemctl start postgresql@17-main
sudo systemctl enable postgresql@17-main

# Create replication user
sudo -u postgres psql -c "CREATE USER ${REPLICA_USER} WITH REPLICATION LOGIN PASSWORD '${REPLICA_PASSWORD}';"

# Create a test database and table for replication
sudo -u postgres psql -c "CREATE DATABASE global_saas;"
sudo -u postgres psql -d global_saas -c "CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT now(),
  region VARCHAR(50) NOT NULL
);"

# Create a publication for logical replication (PG17 feature: all tables)
sudo -u postgres psql -d global_saas -c "CREATE PUBLICATION global_saas_pub FOR ALL TABLES;"

# Verify replication slot is available
sudo -u postgres psql -c "SELECT slot_name, plugin, slot_type, active FROM pg_replication_slots;"

echo "PostgreSQL 17 primary configured successfully. Primary IP: ${PRIMARY_IP}"

#!/bin/bash
# Step 3: Set Up Supabase 1.2 Read Replicas in Multi-Region
# Requires Supabase CLI 1.2+ installed: npm install -g supabase@1.2.0
set -euxo pipefail

# Variables
PRIMARY_DB_URL="postgresql://replica_user:ReplicaSecure!456@${PRIMARY_IP}:5432/global_saas"
SUPABASE_PROJECT_ID="global-saas-multi-region"
REPLICA_REGIONS=("eu-west-1" "ap-southeast-1")
SUPABASE_API_KEY="your-supabase-service-role-key"

# Login to Supabase
supabase login --token "${SUPABASE_ACCESS_TOKEN}"

# Initialize Supabase project
supabase init --project-ref "${SUPABASE_PROJECT_ID}"

# Configure Supabase 1.2 multi-region settings
supabase config set multi_region.enabled true
supabase config set multi_region.primary_region "us-east-1"
supabase config set db.replication.enabled true
supabase config set db.replication.type "logical"

# Create read replicas in each region
for region in "${REPLICA_REGIONS[@]}"; do
  echo "Creating read replica in ${region}..."

  # Provision PostgreSQL 17 replica instance in region (using Terraform module)
  terraform apply -auto-approve \
    -var "region=${region}" \
    -var "db_instance_class=db.m6g.large" \
    -var "primary_ip=${PRIMARY_IP}" \
    -var "replica_user=${REPLICA_USER}" \
    -var "replica_password=${REPLICA_PASSWORD}"

  # Get replica IP from Terraform output
  REPLICA_IP=$(terraform output -raw "${region}_postgres_ip")

  # Configure replica to connect to primary
  ssh -o StrictHostKeyChecking=no ubuntu@${REPLICA_IP} bash -c "'
    set -euxo pipefail
    # Install PostgreSQL 17 on replica
    sudo apt-get update -y
    sudo apt-get install -y postgresql-17 postgresql-client-17

    # Stop PostgreSQL
    sudo systemctl stop postgresql@17-main

    # Configure postgresql.conf for replica
    sudo tee -a /etc/postgresql/17/main/postgresql.conf > /dev/null << EOT
    listen_addresses = '*'
    wal_level = logical
    hot_standby = on
    max_replication_slots = 10
    max_wal_senders = 10
    # PG17 parallel apply
    max_parallel_apply_workers = 4
    EOT

    # Configure pg_hba.conf
    sudo tee -a /etc/postgresql/17/main/pg_hba.conf > /dev/null << EOT
    host replication ${REPLICA_USER} 10.0.0.0/16 scram-sha-256
    EOT

    # Start PostgreSQL
    sudo systemctl start postgresql@17-main

    # Create subscription to primary publication
    sudo -u postgres psql -d global_saas -c "CREATE SUBSCRIPTION global_saas_sub_${region}
      CONNECTION 'host=${PRIMARY_IP} port=5432 user=${REPLICA_USER} password=${REPLICA_PASSWORD} dbname=global_saas'
      PUBLICATION global_saas_pub
      WITH (copy_data = true, parallel_apply = true);"
  '"

  # Register replica with Supabase 1.2
  supabase db replica add \
    --region "${region}" \
    --host "${REPLICA_IP}" \
    --port 5432 \
    --database "global_saas" \
    --user "${REPLICA_USER}" \
    --password "${REPLICA_PASSWORD}"

  # Verify replica is connected
  supabase db replica list | grep "${region}"
done

# Configure Supabase region-aware routing
supabase config set api.region_routing.enabled true
supabase config set api.region_routing.header "x-user-region"
supabase config set api.region_routing.default_region "us-east-1"

# Deploy Supabase configuration
supabase deploy

echo "All read replicas configured and registered with Supabase 1.2"

# Step 4: Supabase 1.2 Configuration for Region-Aware Routing
# config.toml - Supabase project configuration
project_id = "global-saas-multi-region"
region = "us-east-1"

[api]
enabled = true
port = 54321
# Region-aware routing settings (Supabase 1.2+)
region_routing.enabled = true
region_routing.header = "x-user-region"
region_routing.default_region = "us-east-1"
region_routing.region_mapping = [
  { header_value = "eu", region = "eu-west-1" },
  { header_value = "ap", region = "ap-southeast-1" },
  { header_value = "us", region = "us-east-1" }
]
# Replica selection policy: round-robin for reads, primary for writes
read_policy = "nearest_replica"
write_policy = "primary"

[db]
host = "localhost"
port = 5432
database = "global_saas"
username = "postgres_admin"
password = "SupaBase17!SecurePassword123"
# Multi-region replica configuration
replicas = [
  { region = "eu-west-1", host = "10.0.2.10", port = 5432, database = "global_saas", user = "replica_user", password = "ReplicaSecure!456" },
  { region = "ap-southeast-1", host = "10.0.3.10", port = 5432, database = "global_saas", user = "replica_user", password = "ReplicaSecure!456" }
]

[db.replication]
enabled = true
type = "logical"
publication_name = "global_saas_pub"
# Automated failover settings
auto_failover.enabled = true
auto_failover.promotion_timeout = 30 # seconds
auto_failover.replica_priority = ["eu-west-1", "ap-southeast-1"]

[auth]
enabled = true
jwt_secret = "your-jwt-secret-here"
# Region-specific auth settings
region_auth.enabled = true
region_auth.allowed_regions = ["us-east-1", "eu-west-1", "ap-southeast-1"]

[storage]
enabled = true
# Replicate storage metadata to all regions
replication.enabled = true
replication.regions = ["eu-west-1", "ap-southeast-1"]

# Step 5: Prometheus Alert Rules for Multi-Region Replication
# alert-rules.yml
groups:
  - name: postgres-replication
    rules:
      - alert: HighReplicationLag
        expr: pg_logical_replication_lag_seconds > 0.2
        for: 1m
        labels:
          severity: critical
        annotations:
          summary: "High replication lag in {{ $labels.instance }} (region: {{ $labels.region }})"
          description: "Replication lag is {{ $value }}s, exceeding 200ms threshold for 1 minute."
      - alert: ReplicaDown
        expr: up{job="postgres-replication"} == 0
        for: 30s
        labels:
          severity: critical
        annotations:
          summary: "PostgreSQL replica down in {{ $labels.region }}"
          description: "Replica {{ $labels.instance }} is unreachable for 30 seconds."
      - alert: PrimaryDown
        expr: up{instance="us-east-1-postgres:9187"} == 0
        for: 30s
        labels:
          severity: critical
        annotations:
          summary: "Primary PostgreSQL instance down"
          description: "Primary us-east-1 instance is unreachable, failover may trigger."
      - alert: ParallelApplyWorkerDown
        expr: pg_parallel_apply_workers_active < pg_parallel_apply_workers_configured
        for: 2m
        labels:
          severity: warning
        annotations:
          summary: "Parallel apply worker down in {{ $labels.instance }}"
          description: "Configured {{ $value }} workers, but only {{ $value }} active."
      - alert: HighWALVolume
        expr: rate(pg_wal_files_total[5m]) > 10
        for: 5m
        labels:
          severity: warning
        annotations:
          summary: "High WAL volume on primary"
          description: "WAL generation rate is {{ $value }} files/sec, may cause lag."
      - alert: SupabaseRoutingError
        expr: supabase_api_routing_errors_total > 0
        for: 1m
        labels:
          severity: warning
        annotations:
          summary: "Supabase region routing errors detected"
          description: "{{ $value }} routing errors in the last minute."

# Prometheus scrape config for pg_exporter
scrape_configs:
  - job_name: 'postgres-replication'
    static_configs:
      - targets:
        - 'us-east-1-postgres:9187'
        - 'eu-west-1-postgres:9187'
        - 'ap-southeast-1-postgres:9187'
    metrics_path: /metrics
    params:
      collect[]:
        - pg_replication
        - pg_stat_statements
        - pg_logical_replication

-- Enable parallel apply for existing subscription (PG17 only)
ALTER SUBSCRIPTION global_saas_sub_eu_west_1
SET (parallel_apply = true, parallel_apply_workers = 4);

# Validate all multi-region replicas with Supabase 1.2 CLI
supabase db replica validate --all --output json

multi-region-postgres-supabase/
├── terraform/
│   ├── primary/
│   │   ├── main.tf
│   │   ├── variables.tf
│   │   └── outputs.tf
│   ├── eu-replica/
│   │   ├── main.tf
│   │   └── variables.tf
│   └── ap-replica/
│       ├── main.tf
│       └── variables.tf
├── scripts/
│   ├── install-postgres-primary.sh
│   ├── configure-replica.sh
│   └── setup-supabase.sh
├── supabase/
│   ├── config.toml
│   └── migrations/
│       └── 20240501000000_create_users.sql
├── monitoring/
│   ├── prometheus.yml
│   └── grafana-dashboard.json
└── README.md