DBAtlas · 데이터베이스 운영 아틀라스

PostgreSQL 논리 백업과 복원 (pg_dump / pg_restore)

PostgreSQL난이도: 중급소요 시간: 20–90분 (데이터 크기에 따라)

pg_dump, pg_dumpall, pg_restore로 테이블, 스키마, 데이터베이스 또는 전체 cluster 수준의 논리 백업과 복원을 수행합니다. 구조(DDL)만 또는 데이터만 처리, 다른 서버/버전으로 이전, dump에서 단일 테이블 복원, systemd/cron으로 예약 실행되고 검증되며 보존 기간이 있는 백업을 다룹니다. 모든 경로는 행 수 비교 검증으로 끝납니다. Streaming replication과 Patroni cluster에서는 dump를 replica(HAProxy 읽기 포트 또는 standby)에서 받고, 복원은 primary/leader에만 수행합니다.

논리 백업은 dump 시점으로만 되돌리며 특정 시점 복구(PITR)는 하지 않습니다. 운영 환경의 주 백업은 물리 백업이어야 합니다: pgBackRest(권장) 또는 추가 도구 없는 pg_basebackup + WAL 아카이브. 대용량 데이터베이스를 수 초의 중단으로 새 버전에 이전하려면 logical replication, 같은 서버에서 제자리 업그레이드하려면 pg_upgrade를 사용합니다.

구성: 서버 1대, streaming replication(primary + standby) 또는 Patroni cluster. 이전 시 소스 + 대상, 원격 예약 백업 시 + 백업 서버

시나리오

  • 단일 테이블 export / import — 선택한 테이블의 구조와 데이터(index, constraint, trigger 포함)를 받아 다른 데이터베이스에 적재합니다.
  • 스키마 export / import — 스키마의 모든 객체(테이블, view, 함수, sequence)를 받습니다.
  • 단일 데이터베이스 백업 / 복원 — 데이터베이스 전체(구조 + 데이터)를 하나의 dump로 받아 같은 데이터베이스 또는 다른 데이터베이스에 복원합니다. 롤(globals)은 포함되지 않습니다.
  • 전체 cluster (롤 + tablespace + 모든 데이터베이스) — pg_dumpall -g로 globals를, 데이터베이스마다 별도의 pg_dump를 받습니다. 복원은 임시 테스트 cluster에서 리허설합니다.
  • 스키마 구조만 (DDL) — 데이터 없이 테이블, index, view, 함수 정의만 받습니다. 검토, 버전 관리, 빈 환경 구축용입니다.
  • 데이터만 — 대상에 구조가 준비된 상태에서 행(COPY)만 전송합니다.
  • 다른 서버 / 버전으로 이전 — 대상 서버에서 새 버전의 pg_dump가 네트워크로 소스에 접속하고, pipe 또는 파일로 전송합니다.
  • dump에서 단일 테이블 / 객체 복원 — 기존 custom/directory dump에서 목록 파일(-L)로 테이블 하나와 그 index, constraint, 데이터만 복원합니다.
  • 예약 백업 (systemd timer / cron) — 매일 globals + 데이터베이스 백업, 읽기 가능 여부 검증, 보존 기간, 로그와 오류 알림.

시작하기 전에

  • …Patroni cluster가 실행 중이고, 이 서버에 PostgreSQL … 클라이언트 도구(…)가 설치되어 있으며 HAProxy/VIP(…)의 쓰기(…)… 및 읽기(…)… 포트에 접근할 수 있습니다. postgres 롤의 비밀번호(Patroni에서는 /etc/patroni/patroni.env의 PATRONI_SUPERUSER_PASSWORD)를 알고 있습니다.…Primary에서 PostgreSQL …가 실행 중이고 postgres 사용자가 로컬(peer)로 접속할 수 있습니다. Standby …는 streaming 상태이고 primary에서 … 포트로 접근할 수 있습니다. dump가 네트워크로 standby에 접속하므로 postgres 롤에 비밀번호가 있어야 합니다(없으면 primary에서 \password postgres. 비밀번호는 WAL로 standby에 전달됩니다).…PostgreSQL …가 실행 중이고 운영 체제의 postgres 사용자가 로컬(peer)로 접속할 수 있습니다. DBAtlas PostgreSQL 단일 서버 설치 가이드로 설치했다면 경로가 같습니다.…
  • …Cluster가 정상입니다: Leader 하나, 최소 하나의 replica가 streaming 상태이고 지연이 작습니다. Patroni node 중 하나에서 확인합니다.…Standby …가 primary에 연결되어 있고 지연이 작습니다(dump는 standby의 그 시점 이미지를 받습니다).…
  • 백업 디렉터리 …가 데이터 디스크와 분리된 디스크에 있고 여유 공간이 충분합니다. 압축된 custom/directory dump는 대개 데이터베이스 크기의 10–30% 정도입니다(index는 정의만 dump에 들어갑니다). plain 형식과 비압축 dump는 더 큽니다.
  • pg_dump 버전이 백업 대상 서버와 같거나 더 새로운 메이저 버전입니다(새 pg_dump는 이전 서버를 읽을 수 있지만 반대는 안 됩니다). pg_restore는 dump를 받은 pg_dump와 같거나 더 새로운 버전이어야 합니다.
  • 대상의 pg_dump/pg_restore(…)가 소스 서버(…)와 같거나 더 새로운 메이저 버전입니다. 이전은 대상의 새 도구로 수행합니다.
  • 소스에서 사용하는 extension(postgis, pgvector, pg_partman…)의 소프트웨어가 대상 서버에도 설치되어 있습니다. 논리 백업은 CREATE EXTENSION 문만 옮기고 extension 자체는 옮기지 않습니다.
  • 사용자 정의 tablespace가 있으면(아래 쿼리가 행을 반환하면) 대상에 같은 디렉터리를 준비하거나 --no-tablespaces로 기본 tablespace에 복원해야 합니다.
  • 대상 서버 …에 PostgreSQL …가 설치되어 실행 중이며 비어 있습니다(postgres, template0, template1만 존재). 대상은 소스의 … 포트에 접근할 수 있습니다.
  • 백업 서버 …가 …HAProxy/VIP …의 … 포트…standby … 서버의 … 포트…데이터베이스 서버의 … 포트…에 접근할 수 있고 PGDG repo(또는 사내 mirror)에 접속할 수 있습니다.
  • 서버에서 이메일을 보낼 수 있습니다: 로컬 MTA(postfix)가 사내 SMTP relay로 전달하도록 설정되어 있습니다.
  • 대규모 복원이나 이전을 위한 유지보수 시간이 계획되어 있습니다. 대상을 덮어쓸 경우 대상의 백업/snapshot을 받아 두었습니다.

가이드 단계

  • 준비 — 백업 디렉터리 생성 · 접속 비밀번호를 .pgpass에 저장 · Standby의 pg_hba.conf 허용 · 연결이 어느 역할로 가는지 확인 · 데이터베이스 크기와 장기 실행 작업 확인
  • 복원 (import) — 대상 데이터베이스 준비 · 대상 준비: extension, 스키마, trigger 함수 · 복원 · 대상을 덮어쓰며 복원 · 통계 수집 (ANALYZE) · 행 수 비교
  • 스키마 구조만 (DDL) — DDL dump 받기 · 대상 데이터베이스 생성 · 대상에 DDL 적용 · 구조 일치 확인
  • 데이터만 — 데이터 dump 받기 · 대상 데이터베이스 생성 · 대상에 테이블 구조 생성 · 대상 테이블 비우기 · 데이터 적재 · 통계 수집과 행 수 비교
  • 전체 cluster 백업 — Globals와 모든 데이터베이스 백업 · 백업 읽기 가능 여부 검증
  • 복원 리허설 (임시 테스트 cluster) — 테스트 디렉터리 준비 · 임시 cluster 생성과 시작 · Globals 적재 · 데이터베이스 적재 · ANALYZE와 행 수 비교 · 임시 cluster 중지와 삭제
  • 소스 서버 접근 허용 — 대상 서버에 pg_hba.conf 허용 · 대상 서버에 pg_hba 허용 (Patroni) · Firewall에서 대상에 포트 열기 · 애플리케이션 중지 (전환 시점)
  • 대상 서버에서 전송 — 소스 접속 비밀번호를 .pgpass에 저장 · 버전과 extension 호환성 확인 · 롤 이전 (globals) · 데이터베이스 전송 (파일, 병렬) · 데이터베이스 전송 (pipe) · 통계 수집 (ANALYZE) …
  • 전환 후 — 소스의 임시 접근 제거 · 소스의 임시 접근 제거 (Patroni) · 애플리케이션을 대상으로 전환
  • dump에서 단일 테이블 복원 — Dump 내용 검토 · 목록 파일 생성 (-L) · 대상 데이터베이스 준비 · 테이블 복원 · 원본 데이터베이스에 테이블 복원 · 행 수 비교 …
  • 백업 서버와 백업 롤 — 백업 롤 생성 · 대형 객체에 읽기 권한 부여 · 백업 롤에 비밀번호 지정 · 백업 서버에 pg_hba.conf 허용 · 백업 서버에 pg_hba 허용 (Patroni) · PostgreSQL 클라이언트 도구 설치 (백업 서버) …
  • 백업 스크립트 — 도구의 전체 경로 확인 · 알림 도구 설치 · 백업 스크립트 작성 · 스크립트에 실행 권한 부여 · 첫 백업을 수동으로 실행
  • 예약 실행 (systemd timer) — systemd 서비스 작성 · systemd timer 작성 · Timer 활성화와 서비스 수동 실행
  • 예약 실행 (cron) — cron 서비스 준비 · cron 작업 작성 · cron 파일 권한 설정과 cron 환경에서 시험 · cron 실행 확인
  • 검증과 모니터링 — 최신 백업을 테스트 데이터베이스에 복원 · 최신 백업의 경과 시간 모니터링
  • 다음 단계: 물리 백업 — 논리 백업의 한계 보완
내 환경에 맞춘 단계별 계획 열기 →