PostgreSQL pg_restore 할 때 외래키(foreign key) 제약(constraints) 조건 무시하기

Ignore foreign key constraints when pg_restore

개발하다보면 개발서버 혹은 운영중인 DB 서버의 데이터를 이용해야 하는 경우가 있습니다. 이런 경우 PostgreSQL 에서는 pg_dump, pg_restore를 이용해 백업 및 복원을 진행합니다.

전체 DB를 백업하고 복원하는것은 어렵지 않지만 특정 테이블 하나만 적용하려고 보면 왜래키(Foreign key) 제약 조건으로 인해 복원이 실패하는 경우가 발생합니다. MySQL 의 경우는 외래키 제약 조건을 임시로 중단할수 있는 SQL이 있지만 PostgreSQL 에서는 여러가지 방법을 찾아 봤지만 모두 실패 했고 하나의 성공 방법을 찾았습니다.

monsters 라는 테이블을 운영 DB에서 받아와서 개발 DB에 반영 한다면 아래와 같은 절차로 진행합니다.

pg_dump 명령어를 이용해 monsters 테이블을 파일로 저장합니다.

$ pg_dump -W \
    -h DB_HOST \
    -p DB_PORT \
    -U DB_USER \
    -d DB_DATABASE \
    -F c -b -v \
    -t monsters \
    -f monsters.dump

개발 DB에 접속후 복원하려는 DB와 같은 이름을 가진 테이블을 삭제하는데 이때 cascade 옵션이 중요합니다.

$ psql -U local_db_user -d local_db_name -c 'DROP TABLE IF EXISTS monsters cascade;'

cascade 옵션은 다른 테이블에서 외래키로 참조하고 있는 제약 조건을 같이 삭제합니다. 실행하면 아래와 같이 외래키 제약 조건이 같이 삭제되었다는 메시지가 출력됩니다.

NOTICE:  drop cascades to 4 other objects
DETAIL:  drop cascades to constraint xxx on table regions_111
drop cascades to constraint yyy on table regions_222
drop cascades to constraint zzz on table regions_333
drop cascades to constraint qqq on table regions_444
DROP TABLE

마지막으로 pg_restore 명령어를 이용해 DB를 복원합니다.

$ pg_restore --no-acl --no-owner -U local_db_user -d local_db_name monsters.dump

복원시에는 --no-acl, --no-owner을 주어 원본 DB의 사용자에 할당되어 있는 권한을 제거합니다.

pg_restore 할때 --clean 옵션을 주면 굳이 DROP TABLE 을 따로 안해도 되지만 제약조건이 걸려 있는 경우 복원이 실패합니다. 이런 이유로 DROP TABLE을 복원 전에 실행하는것이 문제 해결의 키입니다.

게시글의 아마존, iTunes 링크들을 통해 구매를 하시면 제휴(Affiliate) 프로그램에 의해 저에게 일정 금액이 적립될 수 있습니다. ^_____^