从 Postgres 迁移到 Supabase

1
-- Check database size
2
select pg_size_pretty(pg_database_size(current_database())) as size;
3
4
-- Check Postgres version
5
select version();
6
7
-- List installed extensions
8
select * from pg_extension order by extname;
9
10
-- Check active connections
11
select count(*) from pg_stat_activity;

1
-- Connect to your Supabase database and check available extensions
2
SELECT name, comment FROM pg_available_extensions ORDER BY name;
3
4
-- Compare with source database extensions
5
SELECT extname FROM pg_extension ORDER BY extname;
6
7
-- Install needed extensions
8
CREATE EXTENSION IF NOT EXISTS extension_name;

1
# Install Postgres client and tools
2
sudo apt update
3
sudo apt install software-properties-common
4
sudo sh -c 'echo "deb http://apt.Postgres.org/pub/repos/apt $(lsb_release -cs)-pgdg main" > /etc/apt/sources.list.d/pgdg.list'
5
wget --quiet -O - https://www.Postgres.org/media/keys/ACCC4CF8.asc | sudo apt-key add -
6
sudo apt update
7
sudo apt install Postgres-client-17 tmux htop iotop moreutils
8
9
# Start or attach to tmux session
10
tmux a -t migration || tmux new -s migration

1
-- Connect to source database and run:
2
ALTER DATABASE your_database_name SET default_transaction_read_only = true;

1
# Determine number of parallel jobs based on:
2
# - Source database CPU cores (don't saturate production)
3
# - VM CPU cores
4
# - For testing without maintenance window: use lower values to be gentle
5
# - For production with maintenance window: can use higher values
6
7
DUMP_JOBS=4  # Adjust based on your setup
8
9
# Check available cores on VM
10
nproc
11
12
# Create dump with progress logging
13
pg_dump \
14
  --host=<source_host> \
15
  --port=<source_port> \
16
  --username=<source_username> \
17
  --dbname=<source_database> \
18
  --jobs=$DUMP_JOBS \
19
  --format=directory \
20
  --no-owner \
21
  --no-privileges \
22
  --no-subscriptions \
23
  --verbose \
24
  --file=./db_dump 2>&1 | ts | tee -a dump.log

1
# Set Supabase connection (Session Pooler on port 5432 or direct connection)
2
export SUPABASE_DB_URL="Postgres://postgres.[ref]:[password]@aws-0-[region].pooler.supabase.com:5432/postgres"
3
4
# Determine restore parallelization based on your Supabase compute size:
5
# Free tier: 2 cores → use -j 2
6
# Small compute: 2 cores → use -j 2
7
# Medium compute: 4 cores → use -j 4
8
# Large compute: 8 cores → use -j 8
9
# XL compute: 16 cores → use -j 16
10
11
RESTORE_JOBS=8  # Adjust based on your Supabase compute size
12
13
# Restore the dump (parallel mode)
14
# Note: -j cannot be used with --single-transaction
15
pg_restore \
16
  --dbname="$SUPABASE_DB_URL" \
17
  --jobs=$RESTORE_JOBS \
18
  --format=directory \
19
  --no-owner \
20
  --no-privileges \
21
  --verbose \
22
  ./db_dump 2>&1 | ts | tee -a restore.log

1
psql "$SUPABASE_DB_URL" -c "VACUUM VERBOSE ANALYZE;"

1
-- Check row counts
2
select schemaname, tablename, n_live_tup
3
from pg_stat_user_tables
4
order by n_live_tup desc
5
limit 20;
6
-- Verify data with application-specific queries

1
ALTER DATABASE your_database_name SET default_transaction_read_only = false;

1
ALTER TABLE schema.table_name REPLICA IDENTITY FULL;

1
# Set Supabase connection (Session Pooler on port 5432 or direct connection)
2
export SUPABASE_DB_URL="Postgres://postgres.[ref]:[password]@aws-0-[region].pooler.supabase.com:5432/postgres"
3
4
# Set WAL level to logical
5
wal_level = logical
6
7
# Ensure sufficient replication slots
8
max_replication_slots = 10
9
10
# Ensure sufficient WAL senders
11
max_wal_senders = 10
12
13
# Set appropriate max_connections (current connections + 1 for subscription)
14
max_connections = 200  # Adjust based on your needs
15
16
# Optional: Enable SSL for secure replication
17
ssl = on
18
19
# Allow connections from Supabase
20
listen_addresses = '*'  # Or specific IP addresses

1
# Allow replication connections from Supabase
2
# Replace <supabase_ip_range> with actual Supabase IP range
3
host    replication     all     <supabase_ip_range>    md5
4
host    all            all     <supabase_ip_range>    md5
5
6
# With SSL:
7
hostssl replication     all     <supabase_ip_range>    md5
8
hostssl all            all     <supabase_ip_range>    md5

1
sudo systemctl restart Postgres
2
sudo systemctl status Postgres

1
-- Should return 'logical'
2
SHOW wal_level;
3
4
-- Check other parameters
5
SHOW max_replication_slots;
6
SHOW max_wal_senders;
7
8
-- Check current connections
9
SELECT count(*) FROM pg_stat_activity;

1
-- Find tables without primary keys
2
SELECT n.nspname, c.relname
3
FROM pg_class c
4
JOIN pg_namespace n ON n.oid = c.relnamespace
5
LEFT JOIN pg_constraint pk ON pk.conrelid = c.oid AND pk.contype = 'p'
6
WHERE c.relkind = 'r'
7
  AND pk.oid IS NULL
8
  AND n.nspname NOT IN ('pg_catalog','information_schema');
9
10
-- For tables without a primary key, set REPLICA IDENTITY FULL
11
ALTER TABLE my_schema.my_table REPLICA IDENTITY FULL;

1
# Export schema from source
2
pg_dump \
3
  -h <source_host> \
4
  -U <source_user> \
5
  -p <source_port> \
6
  -d <source_database> \
7
  --schema-only \
8
  --no-privileges \
9
  --no-subscriptions \
10
  --format=directory \
11
  -f ./schema_dump
12
13
# Restore schema to Supabase (use Session Pooler)
14
pg_restore \
15
  --dbname="$SUPABASE_DB_URL" \
16
  --format=directory \
17
  --schema-only \
18
  --no-privileges \
19
  --single-transaction \
20
  --verbose \
21
  ./schema_dump

1
-- Create publication for all tables
2
CREATE PUBLICATION supabase_migration FOR ALL TABLES;
3
4
-- Or for specific tables only (doesn't require superuser)
5
CREATE PUBLICATION supabase_migration FOR TABLE
6
  schema1.table1,
7
  schema1.table2,
8
  public.table3;
9
10
-- Verify publication was created
11
SELECT * FROM pg_publication;

1
-- Create subscription with SSL (recommended)
2
CREATE SUBSCRIPTION supabase_subscription
3
CONNECTION 'host=<source_host> port=<source_port> user=<source_user> password=<source_password> dbname=<source_database> sslmode=require'
4
PUBLICATION supabase_migration;
5
6
-- Or without SSL (if source doesn't support it)
7
CREATE SUBSCRIPTION supabase_subscription
8
CONNECTION 'host=<source_host> port=<source_port> user=<source_user> password=<source_password> dbname=<source_database> sslmode=disable'
9
PUBLICATION supabase_migration;

1
-- On Supabase (subscriber) - check subscription status
2
select * from pg_subscription_rel;
3
4
-- srsubstate = 'r' means ready (synchronized)
5
-- srsubstate = 'i' means initializing
6
-- srsubstate = 'd' means data is being copied
7
8
-- Overall subscription status
9
select * from pg_stat_subscription;
10
11
-- On source database - check replication status
12
select * from pg_stat_replication;
13
14
-- Check replication lag
15
select
16
  slot_name,
17
  pg_size_pretty(pg_wal_lsn_diff(pg_current_wal_lsn(), restart_lsn)) as lag_size
18
from pg_replication_slots;

1
# Set source to read-only
2
psql -h <source_host> -c "ALTER DATABASE <source_database> SET default_transaction_read_only = true;"
3
4
# Export sequences from source
5
pg_dump \
6
  -h <source_host> \
7
  -U <source_user> \
8
  -p <source_port> \
9
  -d <source_database> \
10
  --data-only \
11
  --table='*_seq' \
12
  --table='*_id_seq' > sequences.sql
13
14
# Import sequences to Supabase
15
psql "$SUPABASE_DB_URL" -f sequences.sql

1
-- On Supabase
2
select * from pg_stat_subscription;
3
-- Check that latest_end_lsn is current

1
DROP SUBSCRIPTION supabase_subscription;

1
-- Remove publication
2
DROP PUBLICATION supabase_migration;
3
4
-- Check and remove any remaining replication slots
5
SELECT * FROM pg_replication_slots;
6
DROP REPLICATION SLOT slot_name;  -- if any remain
7
8
-- The source database should remain read-only or be decommissioned
9
-- Do NOT re-enable writes to avoid a split-brain scenario!