데이터베이스 백업

PostgreSQL에서 데이터를 백업하고 복구(Restore)하는 방법

PostgreSQL에서 데이터를 백업하고 복구(Restore)하는 방법은 크게 **논리적 백업(SQL 덤프)**과 물리적 백업으로 나뉩니다. 가장 일반적으로 사용되는 방법은 pg_dump와 pg_restore를 이용한 논리적 백업입니다.

상황별로 핵심적인 명령어 위주로 정리해 드릴게요.

1. 단일 데이터베이스 백업 및 복구 (pg_dump)

가장 많이 쓰이는 방식입니다. 특정 데이터베이스 하나를 대상으로 합니다.

백업하기

• SQL 형식 (텍스트): 사람이 읽을 수 있는 .sql 파일로 저장합니다.

  pg_dump -u [사용자명] [데이터베이스명] > [파일명].sql

• Custom 형식 (권장): 압축률이 좋고 pg_restore를 통해 유연하게 복구가 가능합니다.

  pg_dump -Fc -u [사용자명] [데이터베이스명] > [파일명].dump

복구하기

• SQL 형식 복구: psql 도구를 사용합니다.

  psql -u [사용자명] [데이터베이스명] < [파일명].sql

• Custom 형식 복구: pg_restore 도구를 사용합니다.

    pg_restore -u [사용자명] -d [데이터베이스명] [파일명].dump

2. 전체 서버 백업 및 복구 (pg_dumpall)

사용자(Role), 권한, 설정 및 모든 데이터베이스를 한 번에 백업할 때 사용합니다.

백업하기

pg_dumpall -u postgres > all_databases.sql

복구하기

psql -u postgres -f all_databases.sql postgres

3. 주요 옵션 설명

명령어를 실행할 때 유용하게 쓸 수 있는 옵션들입니다.

💡 유의사항 및 팁

1. 비밀번호 입력 자동화: 매번 비밀번호를 치기 번거롭다면, 환경 변수(PGPASSWORD)를 설정하거나 사용자 홈 디렉토리에 .pgpass 파일을 만들어 관리하면 편리합니다.
2. 버전 호환성: 백업은 하위 버전에서 상위 버전으로 가져오는 것이 원칙입니다. (예: PostgreSQL 13에서 백업 후 15에서 복구는 OK, 반대는 오류 발생 가능성 있음)
3. 권한 문제: 복구할 데이터베이스가 미리 생성되어 있어야 하는 경우가 많습니다. pg_restore 사용 시 -C 옵션을 넣으면 데이터베이스 생성까지 한 번에 처리해 줍니다.
4. 트리거 및 제약 조건 일시 중지: 복구 중에 외래 키 제약 조건 때문에 오류가 날 수 있습니다.

SET session_replication_role = 'replica'; -- 제약 조건 체크 일시 중지
-- 리스토어 실행 --
SET session_replication_role = 'origin';  -- 다시 활성화

Triggers, Functions, 계정 권한 포함 여부

이 부분들이 백업/복구 시 가장 까다로운 부분인데, 결론부터 말씀드리면 **"어떤 백업 도구를 쓰느냐"**에 따라 포함 여부가 완전히 달라집니다.

1. pg_dump를 쓸 경우 (단일 데이터베이스 백업)

일반적으로 가장 많이 사용하시는 pg_dump는 특정 데이터베이스 내부의 객체들을 백업합니다.

• 포함됨 (YES):
  • Triggers & Functions: 데이터베이스에 속한 객체이므로 완벽하게 백업됩니다.
  • 권한 (Privileges): 해당 DB 내의 테이블, 뷰 등에 설정된 GRANT 권한들이 포함됩니다.
  • Views, Sequences, Indices: 모두 포함됩니다.
• 포함 안 됨 (NO):
  • 계정 (Roles/Users): Postgres 서버 전체에 공유되는 정보이므로 포함되지 않습니다.
  • 문제 상황: 리스토어하려는 서버에 백업본에 설정된 '소유자(Owner) 계정'이 없으면, 리스토어 시 "Role 'owner_name' does not exist" 에러가 발생하며 권한 설정이 실패합니다.

2. pg_dumpall을 쓸 경우 (서버 전체 백업)

서버 전체를 백업하는 pg_dumpall은 모든 것을 다 담습니다.

• 포함됨 (YES):
  • 계정 (Roles/Users): 모든 사용자 계정과 비밀번호가 백업됩니다.
  • 권한: 서버 수준의 권한까지 포함됩니다.
  • Triggers, Functions: 당연히 모든 DB의 것들이 포함됩니다.
• 특이사항: pg_dumpall은 오직 텍스트(SQL) 형식으로만 백업됩니다. 파일 크기가 매우 커질 수 있습니다.

3. Drizzle 사용자를 위한 실전 팁

Drizzle 환경에서 Trigger나 Function을 직접 작성해서 사용 중이시라면, 리스토어 시 다음 사항을 꼭 체크해야 합니다.

A. 계정(Role) 먼저 생성하기

pg_dump로 만든 파일을 다른 서버에 옮겨 리스토어할 계획이라면, 리스토어 명령을 내리기 전에 백업본에서 사용했던 계정들을 미리 생성해 두어야 합니다.

CREATE ROLE my_app_user WITH LOGIN PASSWORD 'password';

B. 리스토어 시 --no-owner 옵션 활용

만약 새 서버의 계정 시스템이 기존과 완전히 다르다면, 리스토어 시 소유권 복구를 무시하는 옵션을 쓸 수 있습니다.

# 바이너리 형태(Custom) 복구 시
pg_restore --no-owner --no-privileges -d [DB명] [파일명].dump

• 이렇게 하면 리스토어를 실행하는 계정이 모든 객체의 주인이 됩니다. (권한 꼬임을 방지하는 가장 편한 방법입니다.)

C. Trigger와 Function의 정합성

Trigger와 Function은 Drizzle schema.ts에 정의되지 않는 경우가 많습니다. (Drizzle은 아직 모든 복잡한 DB 객체를 완벽히 정의하진 못합니다.)

• 따라서 이들은 백업/복구 도구(pg_dump/pg_restore)를 통해서만 옮겨진다고 보시는 게 안전합니다.
• ORM으로 테이블만 미리 만들고 데이터만 밀어 넣으면(--data-only), Trigger나 Function은 수동으로 다시 생성해야 하는 번거로움이 생깁니다.

💡 요약: 가장 깔끔한 방법

1. 계정 정보가 필요하다면: pg_dumpall -g (Global objects only)로 계정 정보만 먼저 뽑아서 새 서버에 실행하고, 그 다음 pg_dump로 데이터를 복구하세요.
2. 단순 이전이라면:
   • pg_dump -Fc로 백업.
   • 새 서버에서 필요한 Role(계정) 수동 생성.
   • pg_restore로 복구 (Trigger, Function 모두 자동으로 따라옴).

정리하자면: pg_dump를 하면 계정(Role)만 빼고 그 안의 트리거, 함수, 권한 설정은 다 따라옵니다. 하지만 그 "권한"을 부여받을 "계정"이 새 서버에 미리 있어야 에러가 안 납니다!

개별 사용자(일반 유저)가 본인 DB만 백업하고 복구하기

개별 사용자(일반 유저)가 서버 전체가 아닌 본인 소유의 데이터베이스만 안전하게 백업하고 리스토어하려면, pg_dump의 **Custom 포맷(-Fc)**을 사용하는 것이 가장 현명합니다.

이 방식은 압축률이 높고, 리스토어 시 특정 객체만 선택하거나 소유권 문제를 유연하게 해결할 수 있기 때문입니다.

1. 내 DB만 백업하기 (pg_dump)

터미널(CLI)에서 다음 명령어를 실행합니다.

pg_dump -h [호스트주소] -U [내사용자명] -Fc [내DB이름] > my_backup.dump

• -Fc: Custom 포맷으로 백업합니다. (트리거, 함수, 스키마 구조, 데이터가 모두 포함된 바이너리 파일)
• -v: 백업 진행 상황을 자세히 보고 싶을 때 추가합니다.
• 비밀번호: 실행 후 비밀번호를 물어봅니다. (환경 변수나 .pgpass가 설정 안 된 경우)

2. 내 DB로 리스토어하기 (pg_restore)

새로운 DB 환경이나 기존 DB를 밀고 다시 복구할 때 사용합니다.

상황 A: 기존 DB 내용을 다 지우고 새로 복구할 때 (권장)

가장 깔끔한 방법입니다. 기존 스키마와 충돌을 방지합니다.

pg_restore -h [호스트주소] -U [내사용자명] -d [내DB이름] --clean --if-exists my_backup.dump

• --clean: 복구 전 기존 객체(테이블, 함수 등)를 먼저 삭제합니다.
• --if-exists: 삭제 시 객체가 없어도 에러를 내지 않습니다.

상황 B: 다른 이름의 DB 혹은 다른 계정으로 복구할 때

소유권 에러를 방지하기 위해 사용합니다.

pg_restore -h [호스트주소] -U [새사용자명] -d [새DB이름] --no-owner --privileges my_backup.dump

• --no-owner: 백업 파일에 기록된 원래 주인(Owner) 정보를 무시하고, 현재 리스토어를 실행하는 계정을 주인으로 만듭니다. (매우 유용!)

3. 사용자 입장에서 주의할 점

① 계정(Role)은 백업되지 않음

앞서 설명했듯이 pg_dump는 DB '내부'만 챙깁니다. 만약 새 서버로 옮기는 것이라면, 새 서버에 **내 사용자 계정(ID)**이 미리 생성되어 있어야 리스토어가 성공합니다.

② public 스키마 권한

간혹 리스토어 후 "Permission denied"가 뜬다면, public 스키마에 대한 권한이 꼬인 경우입니다. 이때는 아래 SQL을 DB에서 실행해 주세요.

GRANT ALL ON SCHEMA public TO [내사용자명];

③ Drizzle과의 조화

사용자 입장에서 리스토어를 마친 후, Drizzle 프로젝트 폴더에서 다음을 실행해 상태를 체크하세요.

npx drizzle-kit check  # DB와 schema.ts 간의 차이점 확인

💡 요약 세트 (이것만 복사해서 쓰세요)

백업: pg_dump -U 사용자명 -Fc DB이름 > backup.dump

복구 (가장 안전): pg_restore -U 사용자명 -d DB이름 --clean --no-owner backup.dump

이 조합이면 트리거, 함수, 인덱스까지 본인이 만든 모든 것을 가장 확실하게 옮길 수 있습니다. 리스토어할 때 대상 DB가 아예 비어있는 상태(스키마 삭제 후 생성 직후)라면 --clean 옵션 없이도 완벽하게 들어갑니다.