[PostgreSQL] 데이터 백업 및 복원 하는 방법

 

 

1. 개요

pg_dump와 psql 명령어를 사용하여 PostgreSQL의 데이터를 백업하고 복원 하는 방법에 대해서 정리한다.

 

 

2. pg_dump로 데이터 백업 하는 방법

데이터를 백업하는 방법에 대해서 알아보자.

 

A. pg_dump 옵션

 

pg_dump 프로그램은 데이터베이스를 텍스트 파일 또는 기타
다른 형태의 파일로 덤프합니다.

사용법:
  pg_dump [옵션]... [DB이름]

일반 옵션들:
  -f, --file=파일이름          출력 파일 또는 디렉터리 이름
  -F, --format=c|d|t|p         출력 파일 형식(사용자 지정, 디렉터리, tar,
                               일반 텍스트(초기값))
  -j, --jobs=개수              덤프 작업을 병렬 처리 함
  -v, --verbose                작업 내역을 자세히 봄
  -V, --version                버전 정보를 보여주고 마침
  -Z, --compress=METHOD[:DETAIL]
                               압축 지정
  --lock-wait-timeout=초       테이블 잠금 시 지정한 초만큼 기다린 후 실패
  --no-sync                    fsync 작업 생략
  --sync-method=METHOD         파일을 디스크에 동기화할 방법 지정
  -?, --help                   이 도움말을 보여주고 마침

출력 내용을 다루는 옵션들:
  -a, --data-only              스키마 빼고 자료만 덤프
  -b, --large-objects          큰 객체를 덤프에 포함
  --blobs                      (--large-objects 와 같음, 옛날 옵션)
  -B, --no-large-objects       큰 객체 빼고 덤프
  --no-blobs                   (--no-large-objects와 같음, 옛날 옵션)
  -c, --clean                  다시 만들기 전에 데이터베이스 개체 지우기(삭제)
  -C, --create                 데이터베이스 만드는 명령구문도 포함시킴
  -e, --extension=PATTERN      지정한 확장 모듈들만 덤프 함
  -E, --encoding=인코딩        지정한 인코딩으로 자료를 덤프 함
  -n, --schema=PATTERN         지정한 SCHEMA들 자료만 덤프
  -N, --exclude-schema=PATTERN 지정한 SCHEMA들만 빼고 모두 덤프
  -O, --no-owner               일반 텍스트 형식에서
                               개체 소유권 복원 건너뛰기
  -s, --schema-only            자료구조(스키마)만 덤프
  -S, --superuser=NAME         일반 텍스트 형식에서 사용할 슈퍼유저 사용자 이름
  -t, --table=PATTERN          지정한 이름의 테이블들만 덤프
  -T, --exclude-table=PATTERN  지정한 테이블들만 빼고 덤프
  -x, --no-privileges          접근 권한 (grant/revoke) 정보는 덤프 안 함
  --binary-upgrade             업그레이드 유틸리티 전용
  --column-inserts             칼럼 이름과 함께 INSERT 명령으로 자료 덤프
  --disable-dollar-quoting     $ 인용 구문 사용안함, SQL 표준 따옴표 사용
  --disable-triggers           자료만 복원할 때 트리거 사용을 안함
  --enable-row-security        로우 보안 활성화 (현재 작업자가 접근할 수
                               있는 자료만 덤프 함)
  --exclude-extension=PATTERN  지정한 확장 모듈은 덤프하지 않음
  --exclude-table-and-children=PATTERN
                               상속 하위, 파티션 하위 테이블을 포함하는
                               패턴의 테이블들은 빼고 덤프
  --exclude-table-data=PATTERN 해당 테이블 자료는 덤프 안함
  --exclude-table-data-and-children=PATTERN
                               상속 하위, 파티션 하위 테이블을 포함하는
                               패턴의 테이블들의 자료는 빼고 덤프
  --extra-float-digits=NUM     기본 extra_float_digits 값 바꿈
  --filter=FILENAME            지정한 파일의 규칙대로 포함하거나 제외할
                               객체를 반영하여 덤프
  --if-exists                  객체 삭제 시 IF EXISTS 구문 사용
  --include-foreign-data=패턴
                               지정한 패턴과 일치하는 외부 서버의 외부
                               테이블 자료를 포함
  --inserts                    COPY 대신 INSERT 명령으로 자료 덤프
  --load-via-partition-root    상위 테이블을 통해 하위 테이블을 로드함
  --no-comments                코멘트는 덤프 안함
  --no-publications            발행 정보는 덤프하지 않음
  --no-security-labels         보안 라벨 할당을 덤프 하지 않음
  --no-subscriptions           구독 정보는 덤프하지 않음
  --no-table-access-method     테이블 접근 방법은 덤프하지 않음
  --no-tablespaces             테이블스페이스 할당을 덤프하지 않음
  --no-toast-compression       TOAST 압축 방법은 덤프하지 않음
  --no-unlogged-table-data     언로그드 테이블 자료는 덤프하지 않음
  --on-conflict-do-nothing     INSERT 구문에 ON CONFLICT DO NOTHING 옵션 추가
  --quote-all-identifiers      예약어가 아니여도 모든 식별자는 따옴표를 씀
  --rows-per-insert=NROWS      한 INSERT 명령으로 입력할 로우 수; --inserts
                               옵션을 사용한 것으로 가정 함
  --section=SECTION            해당 섹션(pre-data, data, post-data)만 덤프
  --serializable-deferrable    자료 정합성을 보장하기 위해 덤프 작업을
                               직렬화 가능한 트랜잭션으로 처리 함
  --snapshot=SNAPSHOT          지정한 스냅샷을 덤프 함
  --strict-names               테이블이나 스키마를 지정했을 때 그 패턴에 맞는
                               객체가 적어도 하나 이상 있어야 함
  --table-and-children=PATTERN 해당 패턴의 상속 하위, 파티션 하위 테이블만
                               덤프
  --use-set-session-authorization
                               SET SESSION AUTHORIZATION 명령을 ALTER OWNER 명령
                               대신 사용하여 소유권 설정

연결 옵션들:
  -d, --dbname=DBNAME      덤프할 데이터베이스
  -h, --host=HOSTNAME      접속할 데이터베이스 서버 또는 소켓 디렉터리
  -p, --port=PORT          데이터베이스 서버의 포트 번호
  -U, --username=NAME      연결할 데이터베이스 사용자
  -w, --no-password        암호 프롬프트 표시 안 함
  -W, --password           암호 입력 프롬프트 보임(자동으로 처리함)
  --role=ROLENAME          덤프 전에 SET ROLE 수행

 

B. pg_dump 사용 예시

pg_dump -U <DB계정명> -h <백업서버호스트> -p <백업서버포트> -f <백업할파일명> <데이터베이스명>

pg_dump -U postgres -h 192.168.10.115 -p 5432 -f backup.sql board

 

 

3. psql로 데이터 복원하는 방법

데이터를 복원하는 방법에 대해서 알아보자.

 

A. psql 옵션

psql은 PostgreSQL 대화식 터미널입니다.

사용법:
  psql [OPTION]... [DBNAME [USERNAME]]

일반 옵션:
  -c, --command=COMMAND    하나의 명령(SQL 또는 내부 명령)만 실행하고 끝냄
  -d, --dbname=DBNAME      연결할 데이터베이스 이름
  -f, --file=FILENAME      파일 안에 지정한 명령을 실행하고 끝냄
  -l, --list               사용 가능한 데이터베이스 목록을 표시하고 끝냄
  -v, --set=, --variable=NAME=VALUE
                           psql 변수 NAME을 VALUE로 설정
                           (예, -v ON_ERROR_STOP=1)
  -V, --version            버전 정보를 보여주고 마침
  -X, --no-psqlrc          시작 파일(~/.psqlrc)을 읽지 않음
  -1 ("one"), --single-transaction
                           명령 파일을 하나의 트랜잭션으로 실행
  -?, --help[=options]     이 도움말을 표시하고 종료
      --help=commands      psql 내장명령어(\문자로 시작하는)를 표시하고 종료
      --help=variables     특별 변수들 보여주고, 종료

입출력 옵션:
  -a, --echo-all           스크립트의 모든 입력 표시
  -b, --echo-errors        실패한 명령들 출력
  -e, --echo-queries       서버로 보낸 명령 표시
  -E, --echo-hidden        내부 명령이 생성하는 쿼리 표시
  -L, --log-file=FILENAME  세션 로그를 파일로 보냄
  -n, --no-readline        확장된 명령행 편집 기능을 사용중지함(readline)
  -o, --output=FILENAME    쿼리 결과를 파일(또는 |파이프)로 보냄
  -q, --quiet              자동 실행(메시지 없이 쿼리 결과만 표시)
  -s, --single-step        단독 순차 모드(각 쿼리 확인)
  -S, --single-line        한 줄 모드(줄 끝에서 SQL 명령이 종료됨)

출력 형식 옵션:
  -A, --no-align           정렬되지 않은 표 형태의 출력 모드
      --csv                CSV (쉼표-분리 자료) 테이블 출력 모드
  -F, --field-separator=STRING
                           unaligned 출력용 필드 구분자 설정(기본 값: "|")
  -H, --html               HTML 표 형태 출력 모드
  -P, --pset=VAR[=ARG]     인쇄 옵션 VAR을 ARG로 설정(\pset 명령 참조)
  -R, --record-separator=STRING
                           unaligned 출력용 레코드 구분자 설정
                           (기본 값: 줄바꿈 문자)
  -t, --tuples-only        행만 인쇄
  -T, --table-attr=TEXT    HTML table 태그 속성 설정(예: width, border)
  -x, --expanded           확장된 표 형태로 출력
  -z, --field-separator-zero
                           unaligned 출력용 필드 구분자를 0 바이트로 지정
  -0, --record-separator-zero
                           unaligned 출력용 레코드 구분자를 0 바이트로 지정

연결 옵션들:
  -h, --host=HOSTNAME      데이터베이스 서버 호스트 또는 소켓 디렉터리
  -p, --port=PORT          데이터베이스 서버 포트
  -U, --username=USERNAME  데이터베이스 사용자 이름
  -w, --no-password        암호 프롬프트 표시 안 함

 

B. psql 사용 예시

psql -U <DB계정명> -h <복원서버호스트> -p <복원서버포트> -d <데이터베이스명> -f <복원에사용할SQL파일>

psql -U postgres -h 192.168.10.252 -p 5432 -d board -f backup.sql

 

 

4. 백그라운드 실행 방법(Option)

DB에 데이터 양이 많을 경우 백업과 복원 작업 시간이 오래걸린다.

백그라운드에서 실행하면 터미널을 계속 사용하며 다른 작업을 진행할 수 있다.

작업 시간이 오래 걸린다면 nohup을 사용하여 백그라운드 작업으로 실행하자.

 

백그라운드로 전환하는 방법을 알아보자.

1. 복원(psql) 또는 백업(pg_dump) 명령어 실행

2. Ctrl + Z 를 눌려 프로세스 일지 정지

3. bg 명령어로 백그라운드에서 계속 실행

4. jops로 백그라운드 작업 확인

5. disown으로 세션과 분리(터미널을 종료해도 계속 실행됨)