[Postgresql] Postgres db dump, restore

데이터 베이스를 개발, 또는 운영 중에 백업은 중요한 작업중에 하나이다. 본 절에서는 postgres 데이터베이스를 통으로 바이너리 레벨로 dump하고 이를 그대로 restore하는 명령을 살펴본다.

 

pg_dump 명령어 옵션

PS C:\Program Files\PostgreSQL\13\bin> .\pg_dump.exe --help
pg_dump 프로그램은 데이터베이스를 텍스트 파일 또는 기타
다른 형태의 파일로 덤프합니다.

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

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

출력 내용을 다루는 옵션들:
  -a, --data-only              스키마 빼고 자료만 덤프
  -b, --blobs                  Large Object들도 함께 덤프함
  -B, --no-blobs               Large Object들을 제외하고 덤프함
  -c, --clean                  다시 만들기 전에 데이터베이스 개체 지우기(삭제)
  -C, --create                 데이터베이스 만드는 명령구문도 포함시킴
  -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-table-data=PATTERN 해당 테이블 자료는 덤프 안함
  --extra-float-digits=NUM     기본 extra_float_digits 값 바꿈
  --if-exists                  객체 삭제 시 IF EXISTS 구문 사용
  --include-foreign-data=패턴
                               지정한 패턴과 일치하는 외부 서버의 외부
                               테이블 자료를 포함
  --inserts                    COPY 대신 INSERT 명령으로 자료 덤프
  --load-via-partition-root    상위 테이블을 통해 하위 테이블을 로드함
  --no-comments                코멘트는 덤프 안함
  --no-publications            발행 정보는 덤프하지 않음
  --no-security-labels         보안 라벨 할당을 덤프 하지 않음
  --no-subscriptions           구독 정보는 덤프하지 않음
  --no-synchronized-snapshots  병렬 작업에서 스냅샷 일관성을 맞추지 않음
  --no-tablespaces             테이블스페이스 할당을 덤프하지 않음
  --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               테이블이나 스키마를 지정했을 때 그 패턴에 맞는
                               객체가 적어도 하나 이상 있어야 함
  --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 수행

데이터베이스 이름을 지정하지 않았다면, PGDATABASE 환경변수값을
사용합니다.

문제점 보고 주소 <pgsql-bugs@lists.postgresql.org>
PostgreSQL 홈페이지: <https://www.postgresql.org/>
PS C:\Program Files\PostgreSQL\13\bin>

 

pg_dump 수행 예제

덤프를 수행하기전 덤프 대상 데이터베이스 모습이다. 여러 프로시저와 테이블이 존재함을 볼 수 있다.

 

덤프를 수행한다. --format은 덤프 바이너리의 포맷을 지정하는 옵션으로 아래예제에서 t(tar)로 지정했다. 명령줄을 실행하면 DB 암호 입력을 요청한다. 암호 입력 후 엔터를 치면 스키마, 시퀀스, 프로시저 등 모두 통째로 덤프하는 모습을 볼 수 있다.

PS C:\Program Files\PostgreSQL\13\bin> .\pg_dump.exe --host "127.0.0.1" --username "sdp_user"
--dbname="sdp" --format=t --file="d:\backup.tar" --verbose
암호:
pg_dump: 마지막 내장 OID는 16383
pg_dump: 확장 기능 읽는 중
pg_dump: 확장 멤버를 식별 중
pg_dump: 스키마들을 읽는 중
pg_dump: 사용자 정의 테이블들을 읽는 중
pg_dump: 사용자 정의 함수들 읽는 중
pg_dump: 사용자 정의 자료형을 읽는 중
pg_dump: 프로시쥬얼 언어를 읽는 중
pg_dump: 사용자 정의 집계 함수를 읽는 중
pg_dump: 사용자 정의 연산자를 읽는 중
pg_dump: 사용자 정의 접근 방법을 읽는 중
pg_dump: 사용자 정의 연산자 클래스를 읽는 중
pg_dump: 사용자 정의 연산자 부류들 읽는 중
pg_dump: 사용자 정의 텍스트 검색 파서를 읽는 중
pg_dump: 사용자 정의 텍스트 검색 템플릿을 읽는 중
pg_dump: 사용자 정의 텍스트 검색 사전을 읽는 중
pg_dump: 사용자 정의 텍스트 검색 구성을 읽는 중
pg_dump: 사용자 정의 외부 데이터 래퍼를 읽는 중
pg_dump: 사용자 정의 외부 서버를 읽는 중
pg_dump: 기본 접근 권한 읽는 중
pg_dump: 사용자 정의 글자 정렬(collation) 읽는 중
pg_dump: 사용자 정의 인코딩 변환규칙을 읽는 중
pg_dump: 형변환자(type cast)들을 읽는 중
pg_dump: 변환자(transform) 읽는 중
pg_dump: 테이블 상속 정보를 읽는 중
pg_dump: 이벤트 트리거들을 읽는 중
pg_dump: 확장 테이블을 찾는 중
pg_dump: 상속 관계를 조사중
pg_dump: 재미난 테이블들(interesting tables)을 위해 열 정보를 읽는 중
pg_dump: "public.adhrncprdct" 테이블의 칼럼과 자료형을 찾는 중

... (중략)

pg_dump: FK CONSTRAINT "public.device_info FK_relm_info_TO_device_info" 만드는 중
pg_dump: FK CONSTRAINT "public.device_schedule_version FK_schedule_version_TO_device_schedule_version" 만드는 중
pg_dump: FK CONSTRAINT "public.schedule_cntn FK_schedule_version_TO_schedule_cntn" 만드는 중
pg_dump: FK CONSTRAINT "public.bbscatemap FK_sdscatecode_TO_bbscatemap" 만드는 중
pg_dump: FK CONSTRAINT "public.call_info FK_store_info_TO_call_info" 만드는 중

--- (중략)

PS C:\Program Files\PostgreSQL\13\bin>

 

아래 복원테스트를 확인하기 위해 데이터 베이스 전체를 지운 모습이다.

 

pg_restore 명령어 옵션

PS C:\Program Files\PostgreSQL\13\bin> .\pg_restore.exe --help
pg_restore 프로그램은 pg_dump로 만들어진 자료파일로 PostgreSQL 데이터베이스에
그 자료를 일괄 입력합니다.

사용법:
  pg_restore [옵션]... [파일]

일반 옵션들:
  -d, --dbname=NAME        접속할 데이터베이스 이름
  -f, --file=FILENAME      출력 파일 이름 (표준 출력: -)
  -F, --format=c|d|t       백업 파일 형식 (지정하지 않으면 자동분석)
  -l, --list               자료의 요약된 목차를 보여줌
  -v, --verbose            자세한 정보 보여줌
  -V, --version            버전 정보를 보여주고 마침
  -?, --help               이 도움말을 보여주고 마침

리스토어 처리를 위한 옵션들:
  -a, --data-only              스키마는 빼고 자료만 입력함
  -c, --clean                  다시 만들기 전에 데이터베이스 개체 지우기(삭제)
  -C, --create                 작업 대상 데이터베이스를 만듦
  -e, --exit-on-error          오류가 생기면 끝냄, 기본은 계속 진행함
  -I, --index=NAME             지정한 인덱스 만듦
  -j, --jobs=NUM               여러 병렬 작업을 사용하여 복원
  -L, --use-list=FILENAME      출력을 선택하고 해당 순서를 지정하기 위해
                               이 파일의 목차 사용
  -n, --schema=NAME            해당 스키마의 개체들만 복원함
  -N, --exclude-schema=NAME    해당 스키마의 개체들은 복원 안함
  -O, --no-owner               개체 소유권 복원 건너뛰기
  -P, --function=NAME(args)    지정한 함수 만듦
  -s, --schema-only            자료구조(스키마)만 만듦
  -S, --superuser=NAME         트리거를 사용하지 않기 위해 사용할 슈퍼유저
                               사용자 이름
  -t, --table=NAME             복원할 객체 이름 (테이블, 뷰, 기타)
  -T, --trigger=NAME           지정한 트리거 만듦
  -x, --no-privileges          접근 권한(grant/revoke) 지정 안함
  -1, --single-transaction     하나의 트랜잭션 작업으로 복원함
  --disable-triggers           자료만 복원할 때 트리거 사용을 안함
  --enable-row-security        로우 보안 활성화
  --if-exists                  객체 삭제 시 IF EXISTS 구문 사용
  --no-comments                코멘트는 복원하지 않음
  --no-data-for-failed-tables  만들 수 없는 테이블에 대해서는 자료를 덤프하지 않음
  --no-publications            발행 정보는 복원 안함
  --no-security-labels         보안 라벨을 복원하지 않음
  --no-subscriptions           구독 정보는 복원 안함
  --no-tablespaces             테이블스페이스 할당을 복원하지 않음
  --section=SECTION            지정한 섹션만 복원함
                               섹션 종류: pre-data, data, post-data
  --strict-names               테이블이나 스키마를 지정했을 때 그 패턴에 맞는
                               객체가 적어도 하나 이상 있어야 함
  --use-set-session-authorization
                               SET SESSION AUTHORIZATION 명령을 ALTER OWNER 명령
                               대신 사용하여 소유권 설정

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

-I, -n, -N, -P, -t, -T, --section 옵션은 그 대상이 되는 객체를 복수로 지정하기
위해서 여러번 사용할 수 있습니다.

사용할 입력 파일을 지정하지 않았다면, 표준 입력(stdin)을 사용합니다.

문제점 보고 주소 <pgsql-bugs@lists.postgresql.org>
PostgreSQL 홈페이지: <https://www.postgresql.org/>
PS C:\Program Files\PostgreSQL\13\bin>

 

pg_restore 수행 예제

 

pg_restore명령을 수행한다. restore때는 --file 옵션을 줄 필요 없이 마지막에 파일명만 입력하면 된다. dump시와 동일하게 암호 입력을 하고 엔터를 수행하면 복원이 수행된다.

PS C:\Program Files\PostgreSQL\13\bin> .\pg_restore.exe --host "127.0.0.1" --username "sdp_user"
--dbname="sdp" --format=t "d:\backup.tar"

암호:
PS C:\Program Files\PostgreSQL\13\bin>

 

빈 데이터 베이스 상태에서 복원 후 확인해보면 고스란히 전체 덤프 내용이 로딩되어 있음을 볼 수 있다.

 

-- The End --