[PostgreSQL] DB 백업 - pg_dump

업무를 하던 중, PostgreSQL DB를 직접 파일로 백업해야할 필요가 있었다. 직접 DB의 데이터 디렉토리를 압축할 수도 있고 AWS와 같은 플랫폼 서비스를 사용한다면 스냅샷을 찍는 방법으로 백업을 할 수 있을 것이다. 이번 경우에는 런타임중에 실행중인 온프레미스 DB 서버의 데이터를 백업이 필요했고 이와 관련하여 찾아보던 중 pg_dump 유틸을 발견했다. 이 글에서는 pg_dump를 사용하여 백업하는 방법을 정리해본다.

1. pg_dump

pg_dump는 PostgreSQL 백업을 위한 유틸리티이다. 명령어를 통해 PostgreSQL database 를 스크립트 파일이나 아카이브 파일 형식으로 덤프하여 백업할 수 있다.

 

- 스크립트 형식: DB를 백업 당시의 상태로 재구성하기 위한 SQL문이 포함된 일반 텍스트 파일. 스크립트 형식의 dump 파일을 이용해 DB를 복원하려면 psql을 이용해야 한다.

- 아카이브 형식: 사용자 정의 형식 (-Fc) 또는 디렉토리 형식 (-Fd) 등으로 생성되는 형식으로 pg_restore를 이용하여 아카이브 형식의 dump 파일을 복원할 수 있다.

2.  pg_dump 사용법

- pg_dump 명령어

 

pg_dump 명령어는 아래와 같은 형식으로 사용할 수 있다.

 

pg_dump [connection-option … ] [option … ] dbname

 

데이터베이스 연결 정보를 위한 옵션들은 다음과 같다.

 

-h host, --host=host: PostgreSQL 서버가 실행중인 머신의 호스트 명을 지정한다.
-p posrt, --port=port: PostgreSQL 서버의 포트번호
-U username, --username=username: 서버 접속 유저명
-w, --no-password: 서버 접근시 비밀번호 입력창을 생략하는 옵션. .pgpass 파일 등을 통한 인증이 실패하면 pg_dump 동작이 실패한다. 배치 작업이나 스크립트 실행 시에 패스워드 입력을 생략할 수 있다.
-W, --password: pg_dump 실행 시 데이터베이스 접근을 위한 패스워드 입력창을 출력하도록 하는 옵션
--role=rolename: 덤프 생성을 위해 사용할 role을 입력한다.

 

pg_dump에서 사용할 수 있는 옵션으로는 다음과 같은 것들이 있다.

 

dbname: 덤프 할 데이터베이스의 이름을 지정한다. 따로 지정하지 않는 경우 환경변수 PGDATABASE 값을 사용한다. 환경변수도 지정되어 있지 않은 경우 데이터베이스 접속 정보의 user name 을 사용한다.

-a, --data-only: 스키마가 아닌 데이터만 덤프한다. 테이블 데이터, blob 및 시퀀스 값이 덤프된다.

-b, --large-objects: blob 객체를 포함하여 덤프한다. --schema, --table, --schema-only 등의 옵션이 지정되어 있지 않은 경우 기본으로 옵션에 포함된다.

-B, --no-large-objects: blob 객체를 덤프에서 제외한다.

-c, --clean: 데이터베이스 개체 생성 명령어를 출력하기 전에 데이터베이스 개체를 DROP 하는 명령어를 출력한다. 이 옵션은 데이터베이스 복구 시, 기존 데이터베이스를 덮어쓸 때 유용하다.

-E encoding, --encoding=encoding: 덤프를 생성할 인코딩 타입을 지정한다. 기본적으로 데이터베이스의 인코딩 타입을 따라간다.

-f file, --file=file: 덤프 결과 출력을 저장할 파일을 지정한다. 파일 기반 형식을 사용하는 경우 이 옵션을 생략할 수 있으며 이때는 표준 출력이 사용되어 '>' 를 통해 특정 파일에 저장할 수 있다. 디렉토리 형식의 출력을 사용하는 경우에는 타겟 디렉토리를 무조건 지정해주어야 한다. 이 경우에는 pg_dump에 의해 해당 디렉토리가 생성되며, 그 전에는 디렉토리가 존재하지 않아야 한다.

-F format, --format=format: pg_dump 출력의 형식을 선택한다.
    p, plain: 기본 텍스트 형식의 SQL 스크립트 파일 출력 (기본값)
    c, custom: pg_restore에서 사용할 수 있는 사용자 지정 형식의 아카이브 파일로 출력. 기본적으로 압축된 형식이다.
    d, directory: pg_restore에서 사용할 수 있는 적합한 디렉토리 형식의 출력.
    t, tar: pg_restore에서 사용할 수 있는 tar 형식 파일로 출력.

-s, --schema-only: 데이터를 제외하고 스키마만 덤프한다.

-t pattern, --table=pattern: 패턴에 맞는 이름을 가진 테이블들만 덤프한다.

-v, --verbose: 상세모드로 자세한 덤프 동작과 관련된 진행 메시지들을 화면에 출력한다.

 

이외에도 다양한 옵션들이 있는데, 이는 공식문서에 자세하게 설명되어 있다.

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

- pg_dump 사용 예시

다음의 명령어들은 pg_dump를 사용하여 DB를 백업하는 예시이다.

 

# 데이터베이스 mydb를 SQL 스크립트 형식으로 덤프
$ pg_dump mydb > db.sql

# 데이터베이스 mydb를 사용자 지정 형식인 db.dump로 덤프한다.
$ pg_dump -Fc mydb > db.dump

# locahost:5432 에서 동작중인 DB서버의 mydb 데이터베이스를 backup.dump 파일 형식으로 덤프
# 이때 myuser 계정으로 접근하며 -w 옵션으로 패스워드 프롬프트를 생략했기 때문에 .pgpass 파일 등을 참조하여 접근 정보를 가져온다.
$ pg_dump -h localhost -p 5432 -d mydb -U myuser -w -Fc -f ./backup.dump

3.  .pgpass 파일

pg_dump의 접근옵션 중 -w 옵션을 보면 패스워드 입력 프롬프트를 생략한다. 그렇다면 -w 옵션으로 패스워드를 요구하는 DB에 접근할 때는 어떻게 접속 정보를 가져올 수 있을까? 이때 사용되는 것이 .pgpass 파일이다.

 

.pgpass 파일은 사용자 home 디렉토리에 위치하는 파일로 패스워드를 포함한 DB 접속 정보를 가지고 있어서 DB 접근시에 참조된다.

 

hostname:port:database:username:password

 

Unix 시스템에서는 패스워드 파일에 대한 접근 권한을 제한해야 한다. 이때문에 해당 파일에 chmod 0600 ~/.pgpass  명령어를 통해서 권한을 설정해주어야 한다. 만약 해당 권한보다 덜 엄격하게 권한을 설정하는 경우 pg_dump 실행 시에 .pgpass 파일은 참조되지 않고 자동으로 무시된다.

 

[Reference]

• https://www.postgresql.org/docs/current/app-pgdump.html
• https://www.postgresql.org/docs/current/libpq-pgpass.html
• https://tmaxtibero.blog/4437/
• https://m.blog.naver.com/anytimedebug/221222479261
• https://velog.io/@hyunj_523/%EC%9C%88%EB%8F%84%EC%9A%B0-postgresql-%EB%B0%B1%EC%97%85-%EB%B3%B5%EC%9B%90
• https://warpgate3.tistory.com/entry/PostgreSQL-%EB%B0%B1%EC%97%85-%EC%A0%84%EB%9E%B5