[PostgreSQL ADMIN] Backup & Recovery

Backup and Recovery

Postgresql 의 백업과 복원은 크게 두가지 방법을 지원합니다. Postgresql 에서 제공하는 명령어를 이용하는 방법과 파일시스템을 통째로 백업하는 방법입니다.

pg_dump

Postgresql 에서는 'pg_dump'라는 유틸리티를 제공합니다. 말그대로 데이터베이스, 스키마, 테이블들을 백업할 수 있도록 해주는 프로그램입니다. 기본적인 사용방법은 다음과 같습니다.

pg_dump -U username -Fc dbname > dump.sql
pg_dump -U username -Fc -f dump.sql dbname

-F 옵션은 덤프받을 파일의 타입을 설정하는 것으로 사용할 수 있는 타입은 다음과 같습니다.

p : plain 으로 텍스트 파일로 출력을 합니다.
c : custom 으로 적절한 커스터 아카이브로 출력을 하며 기본적으로 압축도 해줍니다.
t : tar 아카이브로 출력을 합니다.

기본값은 p 입니다.

만일 덤프 받는 파일에 데이터베이스를 생성하는 명령어를 넣고 싶다면 '-C' 옵션을 사용합니다.

pg_dump -U username -Fp -C -f dump.sql dbname

'-C' 옵션을 지정하면 '-F' 옵션이 p 여야 한다고 되어 있지만 실제로 c 를 주더라도 덤프는 실행이 됩니다.

위의 예제에서 덤프파일을 열어보면 데이터들이 SQL문이 아닙니다. INSERT 문으로 덤프를 받고 싶다면 다음과 같이 합니다.

특정 스키마만 덤프하고 싶다면 다음과 같이 합니다.

pg_dump -U username -Fp -C -f dump.sql dbname -n public

위의 예제에서 public 스키마만 덤프 하도록 했습니다.

특정 테이블만 덤프하는 것은 다음과 같습니다.

pg_dump -U username -Fp -C -f dump.sql dbname -t table1 -t table2

여러 테이블을 덤프 쓰고 싶다면 -t 옵션으로 여러개를 나열하거나 -t 옵션을 주고 테이블의 패턴(pattern)을 주시면 됩니다. -t 옵션은 table 형태만을 덤프해줍니다. 뷰(view), 시퀀스(sequence)등도 덤프를 할 수 있습니다. 함수(function)은 덤프를 할 수 없습니다. 그리고 -n 과 같은 옵션은 무시됩니다.

The -n and -N switches have no effect when -t is used, because tables selected by -t will be dumped regardless of those switches, and non-table objects will not be dumped.

pg_dump 는 단점도 있는데, 롤(roles)과 테이블스페이스(tablespace)에 존재하는 테이블과 데이터를 백업할 수 없습니다.

PITR(Point In Time Recovery) 를 이용한 백업/복원.

PITR은 Postgresql 의 WAL(Write Ahead Log)를 이용해서 증분 복원을 하는 방법입니다. WAL 은 pg_xlog 에 생성되는 트랜잭션 로그 파일이며, 모든 트랜잭션의 히스토리가 남겨져 있습니다. WAL이 존재하는 이유는 수많은 트랜잭션으로 인해서 발생되는 디스크 I/O로 인한 데이터베이스 시스템의 성능저하를 만회하고, 트랜잭션이 걸렸을때에 시스템 Crash가 발생하더라도 로그를 이용해서 복구를 할 수 있기 때문입니다.

Postgresql 의 WAL은 기본적으로 사용되는 것이 아닙니다. postgresql.conf 파일에 이와 관련된 설정을 해야 하는데 그 설정은 다음과 같습니다.

wal_level = archive
archive_mode = on
archive_command = 'cp %p /usr/local/pgsql9.0/standalone/archive/%f'

위의 설정을 하고 서버를 재시작하면 WAL을 이용할 수 있습니다. 좀 더 정확하게 말을하면 WAL의 운영을 할 수 있게 되는 것이죠.

WAL 파일을 'segment file'이라고 하는데 크기는 16MB 입니다. 이 크기는 컴파일 할때에 결정되는것이어서 바꾸고자 한다면 재컴파일 설치를 해줘야 합니다. 그리고 파일은 기본적으로 3개까지 생성되도록 되어 있고 4번재 파일을 만들지 않고 첫번째 파일을 재사용하거 지우거나해서 다시 생성을 합니다. 따라서 PITR을 위해서 많은 파일들을 보관해야 하는데 postgresql.conf 에서 'archvie_command' 설정을 통해서 이러한 파일들을 보관 하도록 하고 있습니다.

PITR의 백업 순서는 다음과 같습니다.

1. 슈퍼유저로 psql 로 진입합니다. 그리고 다음의 명령어를 입력합니다.
   
   SELECT pg_start_backup("/usr/local/pgsql9.0/standalone/20110223.tar");
2. 그리고 쉘에서 다음과 같이 입력합니다.
   
   tar -cv --exclude=/usr/local/pgsql9.0/data/pg_xlog -f /usr/local/pgsql9.0/standalone/20110223.tar /usr/local/pgsql9.0/data/
3. tar 명령이 모두 완료가 되어서 20110223.tar 파일이 생성이 되었다면 pgsql 로 들어가서 다음을 입력합니다.
   SELECT pg_stop_backup();

위와 같은 과정을 거치면 백업이 완료 됩니다. 그리고 pg_xlog 디렉토리를 보면 "*.backup" 파일이 보일겁니다. 이 파일은 텍스트 파일인데 이 파일 내용을 열어보면 다음과 같이 되어 있습니다.

START WAL LOCATION: 0/4000020 (file 000000010000000000000004)

STOP WAL LOCATION: 0/40000A0 (file 000000010000000000000004)
CHECKPOINT LOCATION: 0/4000020
START TIME: 2011-02-23 17:50:50 KST
LABEL: /usr/local/pgsql9.0/standalone/20110223.tar
STOP TIME: 2011-02-23 17:51:31 KST

이 파일을 보면 많은 것을 알 수 있습니다. 가장 먼저 눈에 들어오는 것이 'LABEL' 입니다. 이 LABEL을 보면 백업할때의 파일임을 알수 있습니다. 그 다음 눈에 들어오는 것이 'CHECKPOINT' 입니다. CHECKPOINT는 굉장히 중요한 것인데, checkpoint 는 WAL 로그에만 적어놨던 작업들을 실제 디스크로 반영한 지점을 말합니다. 이게 필요한 이유는 어느시점에 모든 WAL 로그 내용이 실제 데이터베이스에 반영이 되었는지를 알아야지만 그 이후부터 WAL 로그를 이용해서 복구를 할것인지를 결정할 수 있게 됩니다. 'CHECKPOINT' 값이 '0/4000020' 이고 'START WAL' 값을 보면 그것이 파일이 '000000010000000000000004' 라는 것을 알수 있습니다.

CHECKPOINT 의 위치가 WAL 파일 '000000010000000000000004' 의 시작점에 있기 때문에 이 파일의 시작점 이후부터는 디스크에 반영이 안되어 있는 것이며 따라서 이 시작점을 기준으로 이후의 파일들이 있으면 데이터베이스를 복구 할 수 있게 됩니다. 이것이 PITR의 핵심 이론입니다.

현재 상황을 보면 pg_xlog 에 파일은 다음과 같이 존재합니다.

-rw------- 1 postgres postgres 16M Feb 23 17:51 000000010000000000000004

-rw------- 1 postgres postgres 276 Feb 23 17:51 000000010000000000000004.00000020.backup
-rw------- 1 postgres postgres 16M Feb 23 17:53 000000010000000000000005

압축한 20110223.tar 파일은 CHECKPOINT 이전의 모두 디스크에 반영된 파일들입니다. 따라서 이 파일을 압축을 해제하고 CHECKPOINT 이후의 로그 파일들만 있으면 언제든지 복구가 가능한 것이죠.

복구하는 방법은 다음과 같습니다.

1. Postgresql 을 중지하고 데이터 디렉토리의 이름을 임시로 바꿉니다.
2. /usr/local/pgsql9.0/standalone/20110223.tar 을 압축해제합니다.
   tar -xvf /usr/local/pgsql9.0/standalone/20110223.tar -C /
3. achive_command 로 보관했던 디렉토리를 복사합니다.
   cp -r /usr/local/pgsql9.0/standalone/archive /usr/local/pgsql9.0/
4. recovery.conf 파일을 복사하고 파일 내용을 고칩니다.
   cp /usr/local/pgsql9.0/share/recovery.conf.sample /usr/local/pgsql9.0/data/recovery.conf
   restore_command = 'cp /usr/local/pgsql9.0/archive/%f %p'
5. Postgresql 서버를 시작 시킵니다.

이렇게 하면 복구가 됩니다. 복구 다 되면 'recovery.conf' 파일은 'recovery.done' 파일로 바뀌게 됩니다.

 

출처 : http://histlinux.egloos.com

by. 백준 (2014.11.06)