Supabase DB 마이그레이션 방법 (백업 + 권한 포함)

들어가며

아래 게시물에서 진행하는대로 postgrSQL 설치 등 기본 작업 실행

https://sooncoding.tistory.com/395

Supabase 일시정지(Paused) 프로젝트 DB 복구 & 마이그레이션 전체 과정 정리

 

 

시작하기

1. 풀링 URI 복사

헤더 > Connect 클릭

Direct, Session pooler 선택

Connection string 복사

 

2. 백업 파일 다운로드

✔ 명령어

# 기존 DB 정보 입력 (여기만 수정)
$env:OLD_DB_URI="postgresql://USER:PASSWORD@HOST:PORT/DATABASE"
$env:OLD_DB_PASSWORD="기존_DB_비밀번호"

# 백업 실행
$env:PGPASSWORD=$env:OLD_DB_PASSWORD
pg_dump $env:OLD_DB_URI --no-owner --no-privileges --encoding=UTF8 -f backup.sql

✔ 예시

$env:OLD_DB_URI="postgresql://postgres.xxxxx:password@aws-1-ap-southeast-2.pooler.supabase.com:5432/postgres"
$env:OLD_DB_PASSWORD="old_password123"

$env:PGPASSWORD=$env:OLD_DB_PASSWORD
pg_dump $env:OLD_DB_URI --no-owner --no-privileges --encoding=UTF8 -f backup.sql

 

 

 

step 2. 마이그레이션 (신규 DB 적용)

✔ 명령어

# 신규 DB 정보 입력 (여기만 수정)
$env:NEW_DB_URI="postgresql://USER:PASSWORD@HOST:PORT/DATABASE"
$env:NEW_DB_PASSWORD="신규_DB_비밀번호"

# 복원 실행
$env:PGPASSWORD=$env:NEW_DB_PASSWORD
psql $env:NEW_DB_URI -f backup.sql

✔ 예시

$env:NEW_DB_URI="postgresql://postgres.yyyyy:password@aws-1-ap-northeast-2.pooler.supabase.com:5432/postgres"
$env:NEW_DB_PASSWORD="new_password456"

$env:PGPASSWORD=$env:NEW_DB_PASSWORD
psql $env:NEW_DB_URI -f backup.sql

 

---

후속 작업 (필수)

---

Step 1. 권한 설정 (GRANT)

✔ 명령어

-- =========================================================
-- 1. SCHEMA 권한
-- =========================================================
GRANT USAGE ON SCHEMA public TO anon, authenticated;
GRANT ALL ON SCHEMA public TO service_role;

-- =========================================================
-- 2. TABLE 권한 (현재 존재하는 모든 테이블)
-- =========================================================
GRANT SELECT ON ALL TABLES IN SCHEMA public TO anon;

GRANT SELECT, INSERT, UPDATE, DELETE
ON ALL TABLES IN SCHEMA public
TO authenticated;

GRANT ALL ON ALL TABLES IN SCHEMA public TO service_role;

-- =========================================================
-- 3. SEQUENCE 권한 (SERIAL / IDENTITY 포함)
-- =========================================================
GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO anon;

GRANT USAGE, SELECT, UPDATE
ON ALL SEQUENCES IN SCHEMA public
TO authenticated;

GRANT ALL ON ALL SEQUENCES IN SCHEMA public TO service_role;

-- =========================================================
-- 4. FUNCTION 권한
-- =========================================================
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO anon;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO authenticated;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO service_role;

-- =========================================================
-- 5. DEFAULT PRIVILEGES (미래 생성 객체 자동 권한)
-- ※ 중요: role 기준으로 설정됨
-- =========================================================

-- authenticated가 생성하는 테이블 기준
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO authenticated;

-- anon 읽기 전용
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT SELECT ON TABLES TO anon;

-- service_role full control
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON TABLES TO service_role;

-- sequences
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT, UPDATE ON SEQUENCES TO authenticated;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT USAGE, SELECT ON SEQUENCES TO anon;

ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT ALL ON SEQUENCES TO service_role;

-- functions
ALTER DEFAULT PRIVILEGES IN SCHEMA public
GRANT EXECUTE ON FUNCTIONS TO anon, authenticated, service_role;

✔ 설명

• anon, authenticated 사용자에게 테이블 접근 권한 부여
• 앞으로 생성되는 테이블에도 자동 적용