pg_dump pg_dumpall

[생짜초보]

pg_dump 방법

형식
pg_dump -d DB_NAME -U 사용자이름 > db backup 파일명

Connection options:

-d, --dbname=DBNAME      database to dump

-h, --host=HOSTNAME      database server host or socket directory

-p, --port=PORT                 database server port number

-U, --username=NAME      connect as specified database user

-w, --no-password        never prompt for password

-W, --password                  force password prompt (should happen automatically)

설명

예시
pg_dump -d testDB -U test > test_db_back.sql

pg_dump 백업의 장점

• pg_dump 를 이용하면 SQL 형식으로 쿼리가 출력되기 때문에 복원하려는 PostgreSQL 버전이 다르거나, 운영체제 비트가 달라도 적용이 가능합니다.
• pg_dump는 실행되는 시점의 스냅샷 상태로 작업을 진행합니다.
  이 때 배타적 잠금이 발생하지 않는 상태라면 대부분 작업은 정상적으로 수행됩니다.

pg_dump 주의점

• 만약 기존에 OID (Object Identifier Types) 값을 사용자 입장에서 사용하는 곳이 있다면 pg_dump 시 -o 옵션을 붙여서 복원할 때도 동일한 값으로 되도록 해야합니다. 만약 -o 옵션이 부여되지 않고 백업받은 경우 복원 시 OID 값이 변경될 수 있습니다.
• 데이터를 포함한 모든 내용을 쿼리로 출력하기 때문에 리다이렉션되는 파일의 크기가 매우 커질수 있습니다.
• 최대 파일 크기가 제한된 운영체제도 있어 이 크기를 초과하는 경우에 문제가 될 수 있으며 이런경우 압축과 같은 기능을 이용하여 해결할 수 있습니다.

# 백업시

pg_dump DB_NAME | gzip > postgres_DB_NAME_back.gz

# 복원시

gunzip -c postgres_DB_NAME_back.gz | psql DB_NAME

설명

pg_dumpall

• PostgreSQL에 구성된 모든 데이터베이스를 한번에 덤프받고 싶은 경우 pg_dumpall 명령어를 사용합니다.
• pg_dumpall은 모든 데이터베이스를 백업받기 때문에 DB_NAME을 지정하지 않습니다.

pg_dumpall > postgres_ALL_DB_NAME_back.sql

pg_dump와 pg_dumpall의 차이점

	pg_dump	pg_dumpall
적용 범위	지정한 데이터베이스 1개	PostgreSQL에 구성된 데이터베이스 전체
SQL 지원범위	롤 생성 스크립트 (X) 데이터베이스 생성 스크립트(X) 테이블 / SP 생성 스크립트(O) 권한부여 스크립트(O) 적재 스크립트(O)	롤 생성 스크립트(O) 데이터베이스 생성 스크립트(O) 테이블 / SP 생성 스크립트(O) 권한부여 스크립트(O) 적재 스크립트(O)

SQL 덤프 복원방법

• pg_dump로 받은 백업파일을 SQL 형태로 되어있기 때문에 쿼리 import 형식으로 바로 수행이 가능합니다. 위에서 백업받은 덤프 백업파일을 DB_NAME에 수행해 줍니다.
• 참고로 SQL 덤프백업에서는 데이터베이스와 사용자 생성 쿼리가 없기 때문에, 복원할 데이터베이스와 사용자는 사전에 만들어 주어야 합니다.

psql DB_NAME < postgres_DB_NAME_back.sql

• 기본적으로는 작업도중 오류가 발생해도 파일의 끝까지 진행됩니다.
• 이런 기능을 원하지 않는 경우에는 ON_ERROR_STOP 옵션을 부여하여 실패 한 이후 쿼리부터는 수행되지 않도록 합니다. 그러나 실패 전까지의 쿼리는 데이터베이스에 적용됩니다. 이를 원치 않고 실패시 모든 작업을 취소하고 싶은경우 single-transaction 옵션을 사용하면 됩니다.

psql --set ON_ERROR_STOP=1 --single-transaction < postgres_DB_NAME_back.sql