[PostgreSQL] Backup - pg_dump

pg_dump

 

pg_dump

pg_dump를 수행하면 데이터베이스에 생성된 객체와 데이터들을 기본적으로 SQL 구문 텍스트 형식으로 백업을 받습니다. SQL형식으로 쿼리가 출력되기 때문에, 복원하려는 PostgreSQL 버전이 다르거나, 운영체제 비트가 달라도 적용이 가능합니다. 기본적으로 표준출력이기 때문에 리다이렉션 기능을 사용하여 파일을 저장할 수 있습니다.

 

pg_dump 기본 사용법

pg_dump [database_name] > [backupfile_name]

pg_dump -d [database_name] -f [backupfile_name] 

$ pg_dump testdb > testdb.sql
또는
$ pg_dump -d testdb -f testdb.sql

 

다음은 옵션을 사용한 백업 예시입니다.

---

백업 조건

1. 백업 대상 데이터베이스 : pgdb
2. 백업 파일 형식 : 사용자 지정 형식
3. 백업 로그 출력 여부 : O
4. 백업 파일 이름 : pgdb_backup_c.dump

$ pg_dump -p 5432 -U postgres -Fc -f pgdb_backup_c.dump -d pgdb -v

---

 

pg_dump 명령어를 이용하여 좀 더 유연한 백업관리를 위해 pg_dump 명령어 옵션 별 기능에 대해 숙지하고 필요한 옵션을 사용할 것을 권장드립니다.

 

pg_dump - Option 별 기능

 --help 옵션을 통해 명령어 각 옵션별 기능 설명 확인 가능

 

-d DBNAME

--dbname=DBNAME

백업받을 데이터베이스 이름을 지정합니다.

 

-h HOSTNAME

--host=HOSTNAME

서버가 실행중인 시스템의 호스트 이름을 지정합니다. 기본값은 PGHOST 환경변수에서 가져옵니다. 실제 IP를 입력하여도 됩니다.

 

-p PORT

--port=PORT

서버가 연결을 수신하는 TCP포트 또는 로컬 Unix 도메인 소켓 파일 확장자를 지정합니다. 기본값은 PGPORT환경변수(설정된 경우) 또는 컴파일된 기본값(5432)입니다.

 

-U USERNAME

--username=USERNAME

연결할 사용자 이름을 지정합니다.

 

-w

--no-password

pg_dump시 패스워드를 요구하지 않습니다. 만약 서버에서 암호를 요구하고 있는 경우 데이터베이스 연결 시도에 실패합니다. pg_hba.conf파일에서 local의 인증방식을 trust로 변경해야 동작합니다. 그렇지 않다면, 아래와 같은 에러를 나타냅니다.

pg_dump: error: ... failed: fe_sendauth: no password supplied

 

-W

--password

데이터베이스에 연결하기 전에 pg_dump가 암호를 묻도록 합니다. 서버가 암호 인증을 요구하면 pg_dump가 자동으로 암호를 묻기 때문에 이 옵션은 생략해도 무방합니다.

 

--role=ROLENAME

덤프를 만드는 데 사용할 역할 이름을 지정합니다. 이 옵션은 pg_dump가 데이터베이스에 연결한 후에 SET ROLE ROLENAME 명령을 실행하도록 합니다. 인증된 사용자가 pg_dump -U에 필요한 권한이 부족하지만 필요한 권한이 있는 역할로 전환할 수 있는 경우에 유용합니다.

 

-f FILENMAE

--file=FILENAME

사용자가 지정한 이름의 파일로 백업을 받습니다. 또는 사용자가 원하는 디렉토리를 지정하여 백업받을 수 있습니다.

 

-F [c|d|t|p]

--format=[c|d|t|p]

백업파일의 출력 형식을 선택합니다.

• p (plain) - [기본값]
  일반 텍스트 SQL 스크립트 파일로 출력합니다. -F 옵션을 따로 부여하지 않으면 기본값인 p 옵션으로 파일이 출력됩니다. 일반 텍스트 형식으로 파일이 출력되기 때문에 필요한 경우 파일내용을 사용자가 직접 편집할 수 있습니다.
• c (custom)
  사용자 지정 형식으로 파일을 출력합니다. 이 형식으로 파일을 백업받으면 복원 중에 보관된 항목을 수동으 로 선택하고 재정렬할 수 있다는 점에서 가장 유연하고 많이 사용되는 출력 형식입니다. 사용자 지정 형식은 기본적으로 바이너리 파일 형식으로 압축이 되며, 복원 시 pg_restore 명령어를 사용해야 합니다.
• d (directory)
  디렉토리 형식으로 백업을 받습니다. 이 형식을 사용하면 덤프되는 각 테이블 및 blob에 대해 하나의 파일이 있는 디렉토리와 디렉토리 안에 덤프된 개체를 pg_restore가 판독 가능한 목차 파일 형식으로 생성됩니다. 이 형식은 기본적으로 압축이 되며 병렬 덤프도 지원합니다.
• t (tar)
  tar 형식으로 파일을 출력합니다. tar 형식은 디렉토리 형식과 호환됩니다. tar 형식의 파일을 추출하면 유효한 디렉토리 형식의 파일들이 생성됩니다. 그러나 디렉토리 형식과 다르게 tar 형식은 압축을 지원 하지 않습니다. 또한 tar 형식을 사용하는 경우 복원 중에 테이블 데이터 항목의 상대적 순서를 변경할 수 없습니다.

-j NUM

--jobs=NUM

임의 설정한 개수의 테이블을 동시에 백업받아 백업 수행시간을 줄일 수 있습니다. 이 옵션은 여러 프로세스가 동시에 데이터를 쓸 수 있는 유일한 출력 형식이기 때문에 디렉토리 출력 형식에만 사용할 수 있습니다.

* 백업을 수행하는데 필요한 시간을 줄일 수 있지만 데이터베이스 서버의 부하도 증가시키니 참고해주세요.

 

-v

--verbose

이 옵션을 지정하면 pg_dump가 수행하는 자세한 개체 설명과 시작/중지 시간 및 오류 발생 시 메세지를 덤프파일에 출력합니다.

 

-V

--version

pg_dump 버전을 출력하고 종료합니다.

 

 

-Z

--compress=[0~9]

백업파일의 압축 수준을 지정합니다. 0은 압축이 없음을 의미합니다. 사용자 지정 및 디렉토리 백업 형식의 경우 개별 테이블 데이터 세그먼트의 압축을 지정합니다. 일반 텍스트 출력의 경우 압축하지 않는 0이 기본값인데, 0이 아닌 다른 압축 수준을 설정하면 gzip을 통해 공급된 것처럼 전체 출력 파일이 압축됩니다. 일반 텍스트 형식으로 백업받더라도 압축을 수행하면 파일내용을 사용자가 읽을 수 없습니다. tar 형식은 현재 압축을 전혀 지원하지 않습니다.

 

--lock-wait-timeout=TIMEOUT

백업을 시작할 때 공유 테이블 잠금을 획득할 때까지의 시간 제한을 설정할 수 있습니다.

 

--no-sync

기본적으로 pg_dump로 모든 파일이 디스크에 안전하게 기록될 때까지 기다립니다. 그러나, 이 옵션을 사용하면 pg_dump를 기다리지 않고 반환되므로 더 빠르지만 후속 운영체제 충돌로 인해 덤프가 손상된 상태로 남을 수 있습니다. 일반적으로 이 옵션은 테스트에 유용하지만 실제 운영에서 데이터를 백업받을 때는 사용하면 안됩니다.

 

-a

--data-only

스키마(데이터 정의)가 아닌, 즉 메타데이터를 제외한 데이터만 백업을 수행합니다.

 

-b

--blobs

덤프에 큰 개체를 포함합니다.

 

-c

--clean

데이터베이스 개체를 만들기 위한 명령을 출력하기 전에 데이터베이스 개체를 삭제하는 옵션입니다. 이 옵션은 --if_exists 와 같이 사용되어야 동작합니다.

 

-C

--create

데이터베이스 자체를 생성하고 생성된 데이터베이스에 다시 연결하는 명령으로 출력을 시작합니다. 이 옵션을 사용할 때 어떤 데이터베이스에 연결했는지는 중요하지 않습니다.

 

-E

--encoding=ENCODING

이 옵션에 지정된 CharacterSet으로 설정하여 백업파일을 생성합니다. 기본적으로 백업파일은 데이터베이스 ENCODING 기준으로 생성됩니다.

 

-n

--schema=PATTERN

특정 스키마만 백업을 수행하는 옵션입니다. 해당 스키마에 포함된 모든 개체에 대한 데이터를 백업받습니다.

 

-N

--exclude-schema=PATTERN

특정 스키마만 제외하여 백업받는 옵션입니다.

 

-O

--no-owner

원래 데이터베이스와 일치하도록 개체 소유권을 설정하는 명령을 출력하지 않게 합니다. 기본적으로 pg_dump는 생성된 데이터베이스 개체의 소유권을 설정하는 ALTER ... OWNER ... 문을 실행합니다. 수퍼유저 또는 객체를 소유한 사용자가 스크립트를 시작하지 않는 한 스크립트가 실행될 때 이러한 명령문은 실패합니다. 모든 사용자가 복원할 수 있지만 복원하려는 사용자에게 모든 개체의 소유권을 부여하는 스크립트를 만들려면 이 옵션을 지정하면 됩니다.

예) pg_dump -Fp -U postgres -d test_db -f Fp_test_full.dump -O

 

-s

--schema-only

데이터가 아닌 메타데이터만 백업을 수행합니다. 이 옵션의 반대는 --data-only입니다.

 

-S

--superuser=NAME

트리거를 비활성화할 때 사용할 수퍼유저 사용자 이름을 지정합니다.

 

-t

--table=PATTERN

특정 테이블만 백업을 수행하는 옵션입니다.

 

-T

--exclude-table=PATTERN

특정 테이블만 제외하여 백업을 수행하는 옵션입니다.

 

-x

--no-privileges

grant, revoke 등 권한에 대한 데이터를 제외하는 옵션입니다.

 

--binary-upgrade

이 옵션은 전체 업그레이드 유틸리티에서 사용하기 위한 것이니 사용하는 것을 권장하지 않습니다.

 

--column-inserts

(INSERT INTO table (column, ...) VALUES ...) 명령 형식으로 데이터 백업을 수행합니다. 다만, 이 옵션을 사용하여 백업받은 파일로 복원 시 매우 느려집니다.

 

--disable-dollar-quoting

이 옵션은 함수 본문에 대한 달러 인용 사용을 비활성화하고 SQL 표준 문자열 구문을 사용하여 인용하도록 강제합니다.

 

--disable-triggers

데이터를 복원할 때 트리거를 일시적으로 비활성화하는 명령을 포함하도록 pg_dump에 지시하는 옵션입니다. 데이터 복원중에 호출하지 않으려는 테이블에 대한 참조 무결성 검사 또는 기타 트리거가 있는 경우 이 옵션을 사용하세요.

 

--enable-row-security

이 옵션은 행 보안이 있는 테이블의 내용을 백업받을 때만 관련이 있습니다. 기본적으로 pg_dump는 row_security를 off로 설정하여 모든 데이터가 백업되도록 합니다.

 

--exclude-table-data=PATTERN

일치하는 테이블에 대한 데이터를 제외하고 백업을 수행합니다.

 

--extra-float-digits=NUM

부동 소수점 데이터를 덤프할 때 값을 지정해서 사용하는데 백업 목적인 일반 백업은 이 옵션을 사용하지 않습니다.

 

--if-exists

이 옵션을 사용하면 출력되는 파일에 IF EXISTS란 조건부 명령이 추가됩니다. --clean 옵션과 함께 써야 하며, --clean 옵션이 함께 지정되지 않으면 동작하지 않습니다.

 

--inserts

COPY명령이 아닌 INSERT 명령으로 데이터를 백업받습니다.

 

--no-comments

comment를 제외하고 백업받는 옵션입니다.

 

--no-publications

publications을 제외하고 백업받는 옵션입니다.

 

--no-security-labels

security label을 제외하고 백업받는 옵션입니다.

 

--no-subscriptions

subscription을 제외하고 백업받는 옵션입니다.

 

--no-tablespaces

테이블스페이스를 제외하고 백업받는 옵션입니다. 이 옵션을 사용하면 모든 객체가 복원 중에 기본값인 테이블스페이스에 생성됩니다.

 

--no-unlogged-table-data

기록되지 않은 테이블 및 시퀀스의 내용을 제외하고 백업받는 옵션입니다.

 

--on-conflict-do-nothing

INSERT 명령에 ON CONFLICT DO NOTHING 구문을 추가합니다.

 

--quote-all-identifiers

모든 식별자를 강제로 인용합니다.

 

--rows-per-insert=NROWS

COPY 명령이 아닌 INSERT 명령으로 명령 당 최대 행수를 제어하여 데이터를 백업 받습니다.

 

--section=SECTION

지정된 섹션만 백업받습니다.

 

--serializable-deferrable

serializable transaction을 사용하여 사용된 스냅샷이 이후 데이터베이스 상태와 일치하는지 확인합니다.

 

--snapshot=SNAPSHOT

데이터베이스 백업파일을 만들 때 동기화된 스냅샷을 사용합니다.

 

--strict-names

백업받을 데이터베이스에서 각 특정 스키마 및 테이블 지정 옵션에서 적어도 하나의 스키마 및 테이블과 일치 해야 합니다. 일치하는 항목을 찾지 못하면 pg_dump는 오류를 생성합니다

 

--use-set-session-authorization

개체 쇼유권을 결정하는 SET SESSION AUTHORIZATION 명령 대신 SQL 표준 명령을 출력합니다.

 

 

pg_dump 사용법 참조

https://www.postgresql.org/docs/current/app-pgdump.html

pg_dump

 

https://checktech.tistory.com/32

[PostgreSQL] Recovery - psql