PostgreSQL 백업 방법
- PostgreSQL은 장애 시 복구를 위해 세 가지 백업 방법이 있습니다.
- 세 가지 방법은 SQL dump/ 파일 시스템 기반 백업 / 아카이브 모드 백업입니다.
pg_dump
- 이 백업 방식은 pg_dump 명령어로 진행합니다.
- pg_dump를 실행하면 DB_NAME 데이터베이스에 생성된 객체와 데이터들을 SQL 구문 텍스트 형식으로 백업합니다.
- 기본적으로 표준출력이기 때문에 리다이렉션 기능을 사용하여 파일로 저장합니다.
$ pg_dump -U 소유주 DB명 > 백업파일명
# sample
$ pg_dump -U agens postgres > DB_NAME_bak.sql- 테이블 단위로 백업을 진행하고 싶다면 아래의 방법을 참고합니다.
테이블 단위 pg_dump 백업
pg_dump -U [user_name][db_name] -t [table_name] > [new_file]
e.g. pg_dump -U agens postgres -t oscar > ./oscar.sql
테이블 단위 pg_dump 복구
psql -U [user_name] -f [dump_file_location][db_name]
e.g. psql -U agens -f ./oscar.sql postgres
pg_dump에서 제공하는 연결 옵션
- 옵션을 보면 일반 클라이언트와 동일하게 원격으로 접속할 수 있는 옵션을 제공하고 있습니다. 따라서 원격 데이터베이스에 접속 및 백업할 수 있는 권한이 있다면 원격 서버의 DB를 백업 받을 수 있습니다.
$ pg_dump --help
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 백업의 장점
- pg_dump를 이용하면 SQL 형식으로 쿼리가 출력되기 때문에, 복원하려는 PostgreSQL 버전이 다르거나, 운영체제 비트가 달라도 적용이 가능합니다.
- pg_dump는 실행되는 시점의 스냅샷 상태로 작업을 진행합니다.
- 이 때 배타적 잠금이 발생하지 않는 상태라면, 대부분 작업은 정상적으로 수행됩니다.
pg_dump 백업의 단점
- 서버가 실행되지 않는 상태에서 명령어 사용이 불가합니다.
- 증분 데이터를 반영할 수 없다는 문제가 있습니다.
pg_dump 주의점
- 만약 기존에 OID 값을 사용자 입장에서 사용하는 곳이 있다면, pg_dump 시 -o 옵션을 붙여 복원할 때 동일한 값으로 되도록 해야 합니다.
- 만약 -o 옵션이 부여되지 않고 백업받은 경우 복원 시 OID 값이 변경될 수 있습니다.
- 데이터를 포함한 모든 내용을 쿼리로 출력하기 떄문에 리다이렉션되는 파일의 크기가 매우 커질 수 있습니다.
- 최대 파일 크기가 제한된 운영체제도 있어, 이 크기를 초과하는 경우에 문제가 될 수 있으며 이런 경우 압축과 같은 기능을 이용하여 해결할 수 있습니다.
# 백업 시
$ pg_dump DB_NAME | gzip > DB_NAME_bak.gz
# 복원 시
$ gunzip -c DB_NAME_bak.gz | psql DB_NAMEpg_dumpall
- pg_dump는 데이터베이스 한 개에만 적용됩니다.
- PostgreSQL에 구성된 모든 데이터베이스를 한 번에 덤프받고 싶은 경우 pg_dumpall 명령어를 사용합니다.
- pg_dumpall은 모든 데이터베이스를 백업받기 때문에 DB_NAME을 지정하지 않습니다.
$ pg_dumpall > ALL_DB_NAME_bak.sqlpg dump와 pg_dumpall의 차이점
| pg_dump | pg_dumpall | |
|---|---|---|
| 적용범위 | 지정한 데이터베이스 1개 | PostgreSQL에 구성된 데이터베이스 전체 |
| 롤 생성 스크립트 (X) | 롤 생성 스크립트 (O) | |
| 데이터베이스 생성 스크립트 (X) | 데이터베이스 생성 스크립트 (O) | |
| SQL 지원 범위 | 테이블/SP 생성 스크립트 (O) | 테이블/SP 생성 스크립트 (O) |
| 권한 부여 스크립트 (O) | 권한 부여 스크립트 (O) | |
| 적재 스크립트 (O) | 적재 스크립트(O) |
SQL 덤프 복원 방법
- pg_dump 로 받은 백업파일을 SQL 형태로 되어 있기 때문에, 쿼리 Import 형식으로 바로 수행이 가능합니다.
- 위에서 백업 받은 덤프 백업 파일을 DB_NAME 에 수행해 줍니다.
- 참고로, SQL 덤프 백업에서는 데이터베이스와 사용자 생성 쿼리가 없기 떄문에, 복원한 데이터베이스와 사용자는 사전에 만들어 주어야 합니다.
- 복원 : psql -U {소유주} {DB 명} < {백업파일명}
$ psql -U 소유주 DB_NAME > DB_NAME_bak.sql- 기본적으로는 작업 도중 오류가 발생해도 파일의 끝까지 진행됩니다.
- 이런 기능을 원하지 않는 경우에는 ON_ERROR_STOP 옵션을 부여하여 실패한 이후 쿼리부터는 수행되지 않도록 합니다.
- 그러나 실패 전까지의 쿼리는 데이터베이스에 적용됩니다.
- 만약 이를 원치 않고 실패 시 모든 작업을 취소하고 싶은 경우 single-transaction 옵션을 사용하면 됩니다.
$ psql --set ON_ERROR_STOP=1 --single-transaction < DB_NAME_bak.sqlFilesystem backup (cold backup)
ag_ctl -D $PGDATA stop # 서버 정지
tar cf backup.tar $AGHOME # 백업 실행
mkdir -m 700 -p $AGHOME/backup/data
tar xf backup.tar -C $AGHOME/backup/data
ag_ctl start -D $AGHOME/backup/datacf ) $PGDATA 는 AG의 data file 경로
Archive Mode Backup(Hot Backup)
postgresql.conf 설정
# archive 설정
wal_level = replica
archive_mode = on
archive_command = 'cp %p $PGDATA/archive/%f.arc'
archive_timeout = 1mincf) $PGDATA 경로 밑에 archive 폴더 생성
# asql -U agens -d postgres
# archive 설정
show_archive_mode;
SELECT name, setting FROM pg_settings where name IN ('archive_command');
# 백업 시작 (Hot Backup)
SELECT pg_start_backup(now()::text);
# 백업 현황 (in terminal)
cat backup_label
cat tablespace_map
# 백업 종료
SELECT pg_stop_backup();
DB Engineer