pg_dumpall의 개요

pg_dumpall이란?

pg_dumpall is a utility for writing out (“dumping”) all PostgreSQL databases of a cluster into one script file.
– PostgreSQL 공식 문서 –

pg_dumpall은 클러스터의 모든 PostgreSQL 데이터베이스를 하나의 스크립트 파일에 기록 (“덤프”) 하는 유틸리티입니다. 스크립트 파일에는 데이터베이스를 복원하기 위해 psql에 대한 입력으로 사용할 수 있는 SQL 커맨드가 포함되어 있습니다.

pg_dumpall은 클러스터의 각 데이터베이스에 대해 pg_dump를 호출하고, 또한 모든 데이터베이스의 공통 전역 개체(Global Object), 즉 데이터베이스의 role 및 테이블 스페이스, Config 파라미터에 대한 권한까지 덤프합니다. (pg_dump는 이러한 오브젝트들을 덤프하지 않습니다. )

pg_dumpall은 모든 데이터베이스에서 테이블을 읽기 때문에 완전한 덤프를 생성하려면 데이터베이스 수퍼유저로 연결해야 할 가능성이 큽니다. 또한 PostgreSQL 서버에 여러 번 연결하기 때문에 (데이터베이스당 한번 씩) 비밀번호 인증을 사용하게 되면 매번 비밀번호를 묻게 됩니다. 이럴 땐, ~/.pgpass파일을 생성하면 편리합니다!

pg_dumpall 문법

pg_dumpall[ connection-option…] [ option…]

pg_dumpall 옵션

옵션	설명
-a –data-only	스키마가 아닌 데이터만 덤프합니다.
-c –clean	데이터베이스 개체를 만들기 위한 명령을 출력하기 전에 데이터베이스 개체를 정리(삭제)하는 명령을 출력합니다.
-E encoding –encoding=encoding	지정된 character set으로 덤프를 작성합니다. 기본적으로 데이터베이스 인코딩으로 생성됩니다.
-f file –file=file	지정된 파일로 출력을 보냅니다. 파일 기반 출력 형식의 경우 이 매개변수를 생략할 수 있으며 이 경우 표준 출력이 사용됩니다.
-g –globals-only	전역 오브젝트(role 및 테이블스페이스)만 덤프하고 데이터베이스는 덤프하지 않습니다.
-O –no-owner	원래 데이터베이스와 일치하도록 오브젝트의 소유권을 설정하는 명령을 출력하지 않습니다.
-r –roles-only	role만 덤프하고 데이터베이스나 테이블 스페이스는 덤프하지 않습니다.
-s –schema-only	데이터가 아닌 오브젝트 정의(스키마)만 덤프합니다. 이 옵션은 –data-only 옵션과 반대입니다.
-S username –superuser=username	트리거를 비활성화할 때 사용할 수퍼유저 이름을 지정합니다. –disable-triggers가 사용되는 경우에만 사용됩니다.
-t –tablespaces-only	데이터베이스나 role이 아닌 테이블스페이스만 덤프합니다.
-v –verbose	상세 모드를 지정합니다. 이렇게 하면 pg_dump가 자세한 개체 설명과 시작/중지 시간을 덤프 파일에 출력하고 메시지를 표준 오류로 진행합니다.
-V –version	pg_dump 버전을 출력하고 종료합니다.
-x –no-privileges –no-acl	접근 권한 덤프를 하지 않습니다. (grant/revoke 커맨드)
–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 절 추가)을 사용합니다.
–inserts	데이터를 COPY가 아닌 INSERT 명령으로 덤프합니다. 이렇게 하면 복원 속도가 매우 느려집니다.
–load-via-partition-root	테이블 파티션에 대한 데이터를 덤프할 때는 COPY 또는 INSERT 문이 파티션 자체가 아닌 테이블 파티션을 포함하는 파티션 계층의 root를 대상으로 지정합니다. 따라서 데이터가 로드될 때 각 행에 대해 적절한 파티션이 다시 결정됩니다.
–lock-wait-timeout=timeout	덤프 시작 시, shared 테이블 lock을 얻기 위해 영원히 기다리지 않습니다.
–no-comments –no-publications –no-role-passwords –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-role-passwords : role에 대한 암호를 덤프하지 않습니다. –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명령 당 최대 행 수를 지정합니다.
–use-set-session-authorization	오브젝트의 소유권을 확인하려면 ALTER OWNER 명령 대신 SQL 표준 SET SESSION AUTHERITION 명령을 출력합니다. 이렇게 하면 덤프의 표준 호환성이 높아지지만 제대로 복원되지 않을 수 있습니다.
-? –help	pg_dump 커맨드에 대한 도움말을 표시 하고 종료합니다.
–d connstr –dbname=connstr	서버에 연결하는 데 사용되는 매개변수를 Connetion String 으로 지정합니다.
-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 사용 예시

모든 데이터베이스 덤프

pg_dumpall > db.out

• 덤프한 파일로 데이터베이스 복원하려면 다음과 같이 사용할 수 있습니다.

psql -f db.out postgres

주의할 점

1. 복원되면 옵티마이저가 최신 통계를 갖도록 각 데이터베이스에서 ANALYZE를 실행하는 것을 권장합니다. 모든 데이터베이스를 분석하려면 VACUUMDB -a -z 를 실행합니다.
2. pg_dumpall은 복원 전에 모든 테이블스페이스 디렉토리가 있어야합니다.
3. 덤프 스크립트는 모든 role에 대해 CREATE ROLE을 실행합니다.그렇기 때문에 대상 클러스터가 다른 부트스트랩 수퍼유저이름으로 초기화되지 않은 경우, 부트스트랩 수퍼유저에 대해 “role already exists” 에러가 발생할 수 있습니다. 이 오류는 무시해도 되며 --if-exists 이 옵션을 사용하면 에러를 최소화 할 수 있습니다.

pg_dumpall 환경변수

다음 환경변수들과 같이 pg_dumpall을 사용할 수 있습니다.

환경변수	설명
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_dumpall’에 관해 알아보았습니다

‘PostgreSQL pg_basebackup ’을 바로 이어서 확인해보세요!