pg_dump의 개요
pg_dump란?
pg_dump is a utility for backing up a PostgreSQL database. It makes consistent backups even if the database is being used concurrently. pg_dump does not block other users accessing the database (readers or writers).
– PostgreSQL 공식 문서 –
pg_dump는 PostgreSQL 백업하기 위한 유틸리티입니다. pg_dump는 단일 데이터베이스만 덤프합니다. 전체 클러스터를 백업하거나 클러스터의 모든 데이터베이스에 있는 Global Object(ex: role, tablespace)를 백업하려면 **pg_dumpall **을 사용해야 합니다.
pg_dump는 다음 두 가지 형식으로 덤프 할 수 있습니다.
- 스크립트 형식
- 데이터베이스를 백업 당시의 상태로 재구성하는데 필요한 SQL문이 포함된 일반 텍스트 파일 입니다.
- 스크립트 형식의 덤프 파일을 이용해 복원하려면 psql 을 이용해야 합니다.
- 아카이브 형식
- 아카이브 형식의 덤프 파일을 이용해 복원하려면 pg_restore 을 이용해야 합니다.
- 가장 flexible한 포맷은 “사용자 정의” 포맷(-Fc)과 “디렉토리” 포맷(-Fd)입니다.
- 디렉토리 포맷은 유일하게 병렬 덤프를 지원하는 포맷입니다.
pg_dump 문법
pg_dump[ connection-option…] [ option…] [ dbname]
pg_ctl 옵션
| 옵션 | 설명 | |
|---|---|---|
| dbname | 덤프 할 데이터베이스의 이름을 지정합니다. | |
| -a –data-only | 스키마가 아닌 데이터만 덤프합니다. 테이블 데이터, blob 및 시퀀스 값이 덤프됩니다. | |
| -b –blobs | 덤프에서 blob 오브젝트를 포함합니다. | |
| -B –no-blobs | 덤프에서 blob 오브젝트를 제외합니다. | |
| -c –clean | 데이터베이스 개체를 만들기 위한 명령을 출력하기 전에 데이터베이스 개체를 정리(삭제)하는 명령을 출력합니다. | |
| -C –create | 데이터베이스 자체를 생성하고 생성된 데이터베이스에 다시 연결하는 명령으로 출력을 시작합니다. | |
| -e pattern –extension=pattern | 지정한 패턴과 일치하는 extension만 덤프합니다. | |
| -E encoding –encoding=encoding | 지정된 character set으로 덤프를 작성합니다. 기본적으로 데이터베이스 인코딩으로 생성됩니다. | |
| -f file –file=file | 지정된 파일로 출력을 보냅니다. 파일 기반 출력 형식의 경우 이 매개변수를 생략할 수 있으며 이 경우 표준 출력이 사용됩니다. | |
| -F format –format=format | 출력 형식을 선택합니다. 사용 가능한 포맷은 다음과 같습니다. – p/plain : 일반 텍스트 SQL 스크립트 파일 ( default) – c/custom : pg_restore 에 입력하기에 적합한 사용자 지정 형식 아카이브 – d/directory : pg_restore 에 입력하기에 적합한 디렉토리 형식 아카이브 – t/tar : pg_restore 에 입력하기에 적합한 tar 형식 아카이브 | |
| -j njobs –jobs=njobs | 테이블을 동시에 njobs만큼 덤프하여 덤프를 병렬로 실행합니다. | |
| -n pattern –schema=pattern | 지정한 패턴과 일치하는 스키마만 덤프합니다. | |
| -N pattern –exclude-schema=pattern | 지정한 패턴과 일치하는 스키마를 덤프하지 않습니다. | |
| -O –no-owner | 원래 데이터베이스와 일치하도록 오브젝트의 소유권을 설정하는 명령을 출력하지 않습니다. | |
| -R –no-reconnect | 이 옵션은 더 이상 사용되지 않지만 이전 버전과의 호환성을 위해 여전히 허용됩니다. | |
| -s –schema-only | 데이터가 아닌 오브젝트 정의(스키마)만 덤프합니다. 이 옵션은 –data-only 옵션과 반대입니다. | |
| -S username –superuser=username | 트리거를 비활성화할 때 사용할 수퍼유저 사용자 이름을 지정합니다. | |
| -t pattern –table=pattern | 지정한 이름과 이름이 일치하는 테이블만 덤프합니다. | |
| -T pattern –exclude-table=pattern | 지정한 패턴과 일치하는 테이블을 덤프하지 않습니다. | |
| -v –verbose | 상세 모드를 지정합니다. 이렇게 하면 pg_dump가 자세한 개체 설명과 시작/중지 시간을 덤프 파일에 출력하고 메시지를 표준 오류로 진행합니다. | |
| -V –version | pg_dump 버전을 출력하고 종료합니다. | |
| -x –no-privileges –no-acl | 접근 권한 덤프를 하지 않습니다. (grant/revoke 커맨드) | |
| -Z 0..9 –compress=0..9 | 사용할 압축 레벨을 지정합니다. 0 은 압축이 없음을 의미합니다. | |
| –binary-upgrade | 이 옵션은 upgrade유틸리티에서 사용합니다. 다른 용도로 사용하는 것은 권장되거나 지원되지 않습니다. | |
| –column-inserts –attribute-inserts | 데이터를 INSERT 명령으로 덤프합니다. 이렇게 하면 복원 속도가 매우 느려집니다. | |
| –disable-dollar-quoting | 함수 본문에 대해 $ 인용을 사용하지 않도록 설정하고, SQL 표준 문자열 구문을 사용하여 인용하도록 합니다. | |
| –disable-triggers | 이 옵션은 데이터 전용 덤프를 생성하는 경우에만 사용할 수 있습니다. 데이터가 복원되는 동안 대상 테이블에서 트리거를 일시적으로 비활성화하는 명령을 포함합니다. | |
| –enable-row-security | 이 옵션은 row security가 있는 테이블의 내용을 덤프할 때만 사용할 수 있습니다. 기본적으로 pg_dump는 테이블에서 모든 데이터가 덤프되도록 row_security를 off로 설정합니다. 사용자에게 row security을 무시할 수 있는 권한이 없는 경우 오류가 발생합니다 | |
| –exclude-table-data=pattern | 패턴과 일치하는 테이블의 데이터를 덤프하지 않습니다. | |
| –extra-float-digits=ndigits | 부동 소수점 데이터를 덤프할 때 사용 가능한 최대 정밀도 대신 지정된 extra_float_digits 값을 사용합니다. 백업 목적으로 만든 덤프에는 이 옵션을 사용하면 안 됩니다. | |
| –if-exists | 데이터베이스 객체를 cleaning할 때는 조건부 명령(즉, IF EXISTS 절 추가)을 사용합니다. | |
| –include-foreign-data=foreignserver | foreign server 패턴과 일치하는 foreign server가 있는 foreign table의 데이터를 덤프합니다. 여러 –include-foreign-data switch를 작성하여 여러 개의 외부 서버를 선택할 수 있습니다. | |
| –inserts | 데이터를 COPY가 아닌 INSERT 명령으로 덤프합니다. 이렇게 하면 복원 속도가 매우 느려집니다. | |
| –load-via-partition-root | 테이블 파티션에 대한 데이터를 덤프할 때는 COPY 또는 INSERT 문이 파티션 자체가 아닌 테이블 파티션을 포함하는 파티션 계층의 root를 대상으로 지정합니다. 따라서 데이터가 로드될 때 각 행에 대해 적절한 파티션이 다시 결정됩니다. | |
| –lock-wait-timeout=timeout | 덤프 시작 시, shared 테이블 lock을 얻기 위해 영원히 기다리지 않습니다. | |
| –no-comments –no-publications –no-security-labels –no-subscriptions –no-sync –no-table-access-method –no-tablespaces –no-toast-compression –no-unlogged-table-data | –no-comments : 덤프 코멘트를 덤프하지 않습니다. –no-publications : publication 을 덤프하지 않습니다. –no-security-labels : security label을 덤프하지 않습니다. –no-subscriptions : subscription을 덤프하지 않습니다. –no-sync : 기본적으로 pg_dump는 모든 파일이 디스크에 안전하게 기록될 때까지 기다립니다. 이 옵션을 사용하면 pg_dump가 대기하지 않고 반환되므로 더 빠르지만 이후 운영 체제 충돌로 인해 덤프가 손상될 수 있습니다. –no-table-access-method : 테이블 접근 방법을 선택하는 명령어를 출력하지 않습니다. –no-tablespaces : SELECT TABLESPACE 구문은 출력하지 않습니다. –no-toast-compression : TOAST 압축을 설정하는 구문은 출력하지 않습니다. –no-unlogged-table-data : unlogged 테이블과 시퀀스의 내용을 덤프하지 않습니다. | |
| –on-conflict-do-nothing | 충돌 시 아무런 추가 출력을 하지 않습니다. | |
| –quote-all-identifiers | 모든 식별자를 강제로 따옴표로 묶습니다. PostgreSQL major 버전이 pg_dump버전과 다른 서버에서 데이터베이스를 덤프하거나 출력을 major 버전이 다른 서버로 로드하려는 경우 이 옵션을 사용하는 것이 좋습니다. | |
| –rows-per-insert=nrows | 데이터를 COPY가 아닌 INSERT 명령으로 덤프합니다. 그리고 INSERT명령 당 최대 행 수를 지정합니다. | |
| –section=sectionname | 지정한 섹션만 덤프합니다. 사용 가능한 섹션 이름은 다음과 같습니다. – pre-data – data – post-data 여러 섹션을 지정할 수 있으며 기본값은 모든 섹션을 덤프하는 것입니다. | |
| –serializable-deferrable | 사용된 스냅샷이 이후 데이터베이스 상태와 일치하는지 확인하려면 덤프에 대해 직렬화 가능한 트랜잭션을 사용합니다. | |
| –snapshot=snapshotname | 데이터베이스 덤프를 만들 때 지정된 스냅샷을 사용합니다. logical replication slot 또는 동시 세션과 동기화해야 할 때 유용합니다. | |
| –strict-names | 각 지정한 extension(-e/–extension), schema(-n/–schema), table(-t/–table)이 덤프 할 데이터베이스에서 하나 이상의 확장/스키마/테이블과 일치해야 합니다. 일치하는 항목을 찾을 수 없는 경우 pg_dump에서 오류가 발생합니다. | |
| –use-set-session-authorization | 오브젝트의 소유권을 확인하려면 ALTER OWNER 명령 대신 SQL 표준 SET SESSION AUTHERITION 명령을 출력합니다. 이렇게 하면 덤프의 표준 호환성이 높아지지만 제대로 복원되지 않을 수 있습니다. | |
| -? –help | pg_dump 커맨드에 대한 도움말을 표시 하고 종료합니다. | |
| -d dbname –dbname=dbname | 연결할 데이터베이스의 이름을 지정합니다. | |
| -h host –host=host | 서버가 실행 중인 시스템의 호스트 이름을 지정합니다. | |
| -p port –port=port | 서버가 연결을 수신하는 TCP 포트 또는 로컬 Unix 도메인 소켓 파일 확장자를 지정합니다. | |
| -U username –username=username | 연결할 사용자 이름입니다. | |
| -w –no-password | 암호 프롬포트를 표시하지 않습니다. | |
| -W –password | 데이터베이스에 연결하기 전에 pg_dump가 암호를 묻도록 합니다. | |
| –role=rolename | 덤프를 만드는데 사용할 role을 지정합니다. |
pg_dump 사용 예시
형식 별 덤프
- 스크립트 형식으로 덤프
[opensql@localhost:dump]$ pg_dump test > db.sql
- 아카이브 형식으로 덤프
# 사용자 정의 형식
[opensql@localhost:dump]$ pg_dump -Fc test > db.dump
# 디렉토리 형식
[opensql@localhost:dump]$ pg_dump -Fd test -f dumpdir
# 디렉토리 형식을 5개의 job으로 병렬 덤프
[opensql@localhost:dump]$ pg_dump -Fd test -j 5 -f dumpdir
대상 별 덤프
- 테이블
# 단일 테이블 덤프
[opensql@localhost:dump]$ pg_dump -t mytab test> db.sql
# 지정한 테이블 제외하고 해당 이름으로 시작하는 모든 테이블 덤프
[opensql@localhost:dump]$ pg_dump -t ‘detroit.emp*’ -T detroit.employee_log test> db.sql
# 해당 이름으로 시작하는 테이블을 제외한 모든 데이터베이스의 오브젝트를 덤프
[opensql@localhost:dump]$ pg_dump -T ‘ts_*’ test> db.sql
- 스키마
# 지정한 스키마를 제외하고 해당 이름으로 끝나는 모든 스키마 덤프
[opensql@localhost:dump]$ pg_dump -n ‘east*gsm’ -n ‘west*gsm’ -N ‘*test*’ test> db.sql
복원
- 스크립트 형식의 덤프 파일 복원
[opensql@localhost:dump]$ psql -d test -f db.sql
- 아카이브 형식의 덤프 파일 복원
# 새로 생성된 데이터베이스에 로드
[opensql@localhost:dump]$ pg_restore -d newtest db.dump
# 동일한 데이터베이스로 로드하며, 해당 데이터베이스의 현재 내용을 삭제
[opensql@localhost:dump]$ pg_restore -d postgres –clean –create db.dump
서버 재시작
- fsync를 비활성화하고 5433포트를 사용하여 서버 재시작
[opensql@localhost:~]$ pg_ctl -o “-F -p 5433” -D /opensql/pg/14/data restart
pg_ctl: PID file “/opensql/pg/14/data/postmaster.pid” does not exist
Is server running?
trying to start server anyway
waiting for server to start….2023-01-13 23:05:18.568 KST [110396] LOG: redirecting log output to logging collector process
2023-01-13 23:05:18.568 KST [110396] HINT: Future log output will appear in directory “/opensql/pg/14/log/pg_log”.
done
server started
서버 상태 표시
- 서버의 상태 출력
[opensql@localhost:~]$ pg_ctl status
pg_ctl: server is running (PID: 110396)
/usr/pgsql-14/bin/postgres “-F” “-p” “5433”
주의할 점
- pg_dump에 의해 생성된 덤프 파일에는 쿼리 플랜을 정하는데 옵티마이저에서 사용하는 통계가 포함되어 있지 않습니다. 따라서 최적의 성능을 보장하려면 덤프파일에서 복원한 후, ANALYZE를 실행하는 것이 좋습니다.
- pg_dump는 PostgreSQL의 새로운 버전으로 데이터를 옮기는 데에 주로 사용하기 때문에 pg_dump는 또한 자신의 버전보다 오래된 PostgreSQL 서버로부터 덤프 할 수 있습니다. 그러나 pg_dump는 자신의 major 버전보다 더 높은 PostgreSQL 서버로부터 덤프할 수 없습니다. 만약 시도할 경우, 잘못된 dump를 만들까봐 시도조차 거부합니다.
- logical replication의 subscription을 덤프 할 때 pg_dump는 connect=false옵션을 사용하는 CREATE SUBSCRIPTION 명령을 생성하므로, subscription을 복원하면 remote connection이 생성되지 않습니다. 사용자가 subscription을 다시 직접 활성화해야 합니다.
pg_dump 환경변수
다음 환경변수들과 같이 pg_dump를 사용할 수 있습니다.
| 환경변수 | 설명 |
|---|---|
| PGDATABASE | |
| PGHOST | |
| PGOPTIONS | |
| PGPORT | |
| PGUSER | 기본 연결 매개변수 |
| PG_COLOR | 표시되는 메세지에서 색상을 사용할지 여부를 지정합니다. 사용 가능한 값은 다음과 같습니다. – always – auto – never |
대부분의 다른 PostgreSQL의 유틸리티와 마찬가지로 libpq에서 지원하는 환경변수를 사용합니다. libpq에서 지원하는 환경변수에 대한 내용은 여기 를 참고해주세요!!
사용 방법
export PGHOME=/opensql/pg #opensql engine directory
export PGDATA=/opensql/pg/$PGVERSION/data #opensql database clsuter directory
export PGHOST=/var/run/opensql #opensql socket directory
export PGUSER=postgres #opensql Basic DB User Name
export PGPASSFILE=’/opensql/.pgpass’ #opensql pgpass file
지금까지 ‘PostgreSQL pg_dump’에 관해 알아보았습니다
‘PostgreSQL pg_dumpall’을 바로 이어서 확인해보세요!
