SQL 덤프

SQL 덤프 (SQL Dump) — pg_dump로 백업하기

백업 방법 중에서도 가장 익숙한 출발점이 바로 SQL 덤프예요. 덤프 시점의 데이터베이스 상태를 다시 만들 수 있는 SQL 명령들을 파일로 뽑아내는 방식이죠. 덤프 파일을 서버에 다시 넣으면 그 시점 그대로의 데이터베이스가 재현돼요. PostgreSQL은 이 작업을 위한 pg_dump 유틸리티를 제공해요. 이 방식의 장단점과, 큰 데이터베이스를 다룰 때의 요령을 정리해볼게요.

출처: 공식문서

pg_dump 기본 사용법

pg_dump의 가장 기본적인 사용법은 이래요.

pg_dump dbname > dumpfile

pg_dump는 결과를 표준 출력으로 써요. 그래서 리다이렉트나 파이프로 원하는 곳에 보내기 쉬워요. 위 명령은 텍스트 파일을 만들지만, pg_dump는 병렬 처리를 지원하거나 객체 복원을 더 세밀하게 제어할 수 있는 다른 형식의 파일도 만들 수 있어요.

pg_dump는 평범한 PostgreSQL 클라이언트 애플리케이션이에요(꽤 영리하긴 하지만요). 그래서 데이터베이스에 접근할 수 있는 원격 호스트 어디에서든 이 백업 절차를 수행할 수 있어요. 다만 pg_dump는 특별한 권한으로 동작하지 않는다는 점을 기억해야 해요. 백업하려는 모든 테이블에 대해 읽기 접근 권한이 있어야 하므로, 데이터베이스 전체를 백업하려면 거의 항상 데이터베이스 superuser로 실행해야 해요. (전체 백업에 충분한 권한이 없다면 -n schema나 -t table 같은 옵션으로 접근 가능한 부분만 백업할 수 있어요.)

pg_dump가 접속할 서버를 지정하려면 -h host와 -p port 옵션을 써요. 기본 호스트는 로컬 호스트, 또는 PGHOST 환경 변수가 지정한 값이에요. 기본 포트도 PGPORT 환경 변수나 컴파일된 기본값이 사용돼요. (편리하게도 서버도 보통 같은 컴파일 기본값을 갖죠.)

다른 PostgreSQL 클라이언트 앱처럼 pg_dump도 기본적으로 현재 OS 사용자명과 같은 데이터베이스 사용자명으로 접속해요. 이를 바꾸려면 -U 옵션을 지정하거나 PGUSER 환경 변수를 설정하면 돼요. pg_dump 접속도 일반 클라이언트 인증 메커니즘의 적용을 받는다는 것도 기억해두세요.

pg_dump의 중요한 장점은, 출력물을 보통 새 버전의 PostgreSQL에 다시 로드할 수 있다는 거예요. 반면 파일 레벨 백업과 연속 아카이빙은 모두 서버 버전에 극도로 특화되어 있어요. 또 pg_dump는 32비트에서 64비트 서버로 가는 것처럼 다른 머신 아키텍처로 데이터베이스를 옮길 때 동작하는 유일한 방법이에요.

pg_dump가 만든 덤프는 내부적으로 일관돼요. 즉 덤프는 pg_dump가 실행을 시작한 시점의 데이터베이스 스냅샷을 나타내요. 작업하는 동안 다른 데이터베이스 작업을 막지 않아요. (예외는 배타적 잠금(exclusive lock)이 필요한 작업들인데, 대부분의 ALTER TABLE 형태가 그렇죠.)

덤프 복원하기

pg_dump가 만든 텍스트 파일은 기본 설정의 psql 프로그램으로 읽어들이기 위한 것이에요. 텍스트 덤프를 복원하는 일반적인 명령은:

psql -X dbname < dumpfile

dumpfile은 pg_dump 명령이 만든 파일이에요. 이 명령은 dbname 데이터베이스를 만들지 않으므로, psql을 실행하기 전에 template0에서 직접 만들어야 해요(예: createdb -T template0 dbname). psql이 기본 설정으로 실행되게 하려면 -X(--no-psqlrc) 옵션을 사용해요. psql은 접속 서버와 사용자명을 지정하는 pg_dump와 비슷한 옵션을 지원해요.

텍스트가 아닌 파일 덤프는 pg_restore 유틸리티로 복원해야 해요.

SQL 덤프를 복원하기 전에, 덤프된 데이터베이스에서 객체를 소유했거나 권한을 부여받은 모든 사용자가 이미 존재해야 해요. 그렇지 않으면 복원이 원래 소유자나 권한으로 객체를 재생성하는 데 실패해요. (때로는 그게 원한 결과일 수 있지만 보통은 아니에요.)

기본적으로 psql 스크립트는 SQL 오류를 만나도 계속 실행돼요. ON_ERROR_STOP 변수를 설정하면 동작을 바꿔서, SQL 오류 발생 시 psql이 종료 상태 3으로 빠져나가요.

psql -X --set ON_ERROR_STOP=on dbname < dumpfile

어느 쪽이든 부분적으로만 복원된 데이터베이스를 갖게 돼요. 반면에 덤프 전체를 단일 트랜잭션으로 복원하게 지정할 수도 있는데, 그러면 복원이 완전히 완료되거나 완전히 롤백되거나 해요. 이 모드는 psql에 -1 또는 --single-transaction 옵션을 넘겨서 지정할 수 있어요. 이 모드에서는 아주 사소한 오류라도 몇 시간 동안 돌아간 복원을 롤백할 수 있다는 점을 유의하세요. 그래도 부분 복원 뒤 복잡한 데이터베이스를 수동으로 정리하는 것보다는 나을 수 있어요.

pg_dump와 psql이 파이프로 읽고 쓰는 능력 덕분에, 데이터베이스를 한 서버에서 다른 서버로 직접 덤프할 수 있어요.

pg_dump -h host1 dbname | psql -X -h host2 dbname

중요: pg_dump가 만든 덤프는 template0을 기준으로 해요. 즉 template1을 통해 추가된 언어, 프로시저 등도 pg_dump가 덤프한다는 뜻이에요. 그래서 복원할 때 사용자 정의 template1을 쓰고 있다면, 위 예시처럼 template0에서 빈 데이터베이스를 만들어야 해요.

백업을 복원한 뒤에는 각 데이터베이스에서 ANALYZE를 실행하는 게 현명해요. 그래야 쿼리 최적화기가 유용한 통계를 가지거든요. 대량의 데이터를 PostgreSQL에 효율적으로 넣는 방법은 공식문서 Section 14.4를 참고하세요.

pg_dumpall: 클러스터 전체를 백업하기

pg_dump는 한 번에 데이터베이스 하나만 덤프하고, 롤(role)이나 테이블스페이스 정보는 덤프하지 않아요. (그것들은 데이터베이스별이 아니라 클러스터 전체에 걸친 것이거든요.) 데이터베이스 클러스터의 전체 내용을 편리하게 덤프하기 위해 pg_dumpall 프로그램이 제공돼요. pg_dumpall은 주어진 클러스터의 각 데이터베이스를 백업하고, 롤과 테이블스페이스 정의 같은 클러스터 전체 데이터도 보존해요. 기본 사용법:

pg_dumpall > dumpfile

결과 덤프는 psql로 복원할 수 있어요.

psql -X -f dumpfile postgres

(사실 어떤 기존 데이터베이스 이름이든 시작점으로 지정할 수 있지만, 빈 클러스터에 로드한다면 보통 postgres를 쓰면 돼요.) pg_dumpall 덤프를 복원하려면 항상 데이터베이스 superuser 접근 권한이 필요해요. 롤과 테이블스페이스 정보를 복원하는 데 그게 필요하거든요. 테이블스페이스를 쓴다면 덤프 속의 테이블스페이스 경로가 새 설치에 적합한지 확인하세요.

pg_dumpall은 롤, 테이블스페이스, 빈 데이터베이스를 재생성하는 명령을 출력한 다음 각 데이터베이스에 대해 pg_dump를 호출하는 방식으로 동작해요. 그래서 각 데이터베이스는 내부적으로 일관되지만, 서로 다른 데이터베이스의 스냅샷은 동기화되어 있지 않아요.

클러스터 전체 데이터만 따로 덤프하려면 pg_dumpall --globals-only 옵션을 써요. 개별 데이터베이스에 pg_dump를 돌리고 있다면 클러스터를 완전히 백업하기 위해 이것이 필요해요.

큰 데이터베이스 다루기

일부 OS는 최대 파일 크기 제한이 있어서 큰 pg_dump 출력 파일을 만들 때 문제가 생겨요. 다행히 pg_dump는 표준 출력으로 쓸 수 있으므로 표준 Unix 도구로 이 잠재적 문제를 우회할 수 있어요. 방법이 몇 가지 있어요.

압축 덤프 쓰기. 마음에 드는 압축 프로그램, 예를 들어 gzip을 써요.

pg_dump dbname | gzip > filename.gz

복원은:

gunzip -c filename.gz | psql dbname

또는:

cat filename.gz | gunzip | psql dbname

split 사용하기. split 명령으로 출력을 기반 파일 시스템이 수용할 만한 크기의 작은 파일들로 나눌 수 있어요. 예를 들어 2GB 덩어리로 만들려면:

pg_dump dbname | split -b 2G - filename

복원은:

cat filename* | psql dbname

GNU split을 쓴다면 gzip과 함께 쓸 수도 있어요.

pg_dump dbname | split -b 2G --filter='gzip > $FILE.gz'

이건 zcat으로 복원할 수 있어요.

pg_dump의 사용자 정의 덤프 형식 쓰기. PostgreSQL이 zlib 압축 라이브러리와 함께 빌드됐다면, 사용자 정의 덤프 형식은 출력 파일에 쓸 때 데이터를 압축해요. 이렇게 하면 gzip을 쓴 것과 비슷한 덤프 파일 크기가 되면서도, 테이블을 선택적으로 복원할 수 있다는 추가 장점이 있어요.

pg_dump -Fc dbname > filename

사용자 정의 형식 덤프는 psql용 스크립트가 아니라 pg_restore로 복원해야 해요.

pg_restore -d dbname filename

매우 큰 데이터베이스라면 split을 나머지 두 방법 중 하나와 결합해야 할 수도 있어요.

pg_dump의 병렬 덤프 기능 쓰기. 큰 데이터베이스의 덤프를 빠르게 하려면 pg_dump의 병렬 모드를 쓸 수 있어요. 여러 테이블을 동시에 덤프하는 거예요. 병렬 처리 수준은 -j 매개변수로 제어할 수 있어요. 병렬 덤프는 '디렉터리(directory)' 아카이브 형식에서만 지원돼요.

pg_dump -j num -F d -f out.dir dbname

pg_restore -j로 덤프를 병렬 복원할 수도 있어요. 이건 'custom'이나 'directory' 아카이브 모드 모두에서 동작하는데, pg_dump -j로 만들었는지와 무관해요.