PostgreSQL 백업 전략

데이터 백업은 어떤 시스템에서든 중요한 작업 중 하나입니다. PostgreSQL 데이터베이스에 대한 백업 전략은 여러 가지 방법으로 수행할 수 있고, 각 방법은 특정 시나리오나 요구 사항에 더 적합할 수 있습니다.

전략

1. 빈도: 데이터의 중요도와 변동성에 따라 백업 빈도를 결정해야 합니다. 매일 백업을 할 것인지, 아니면 주간 혹은 월간 백업을 할 것인지를 결정해야 합니다.
2. 자동화: 가능하다면 백업 프로세스를 자동화해야 합니다. 이렇게 하면 사람의 실수를 줄이고, 일관된 백업을 유지할 수 있습니다.
3. 저장 장소: 로컬 머신뿐만 아니라 다른 물리적 위치에도 백업을 저장하는 것이 좋습니다. 이를 통해 재해 복구에 더 유연하게 대응할 수 있습니다.
4. 검증: 백업이 제대로 이루어졌는지 검증하는 단계도 포함되어야 합니다.

백업 방법

SQL 덤프 (pg_dump): 이 방법은 데이터베이스의 스냅샷을 SQL 파일로 저장합니다. 이 파일을 이용하여 데이터베이스를 복구할 수 있습니다.

pg_dump -U username -W -F c -b -v -f "backup.pgsql" dbname

파일 시스템 레벨 백업: PostgreSQL 데이터 디렉토리를 직접 복사하여 백업을 수행할 수도 있습니다. 이 방법은 데이터베이스가 작동 중이지 않거나,
모든 트랜잭션이 끝난 상태에서 수행되어야 합니다.

cp -R /var/lib/postgresql/9.3/main /path/to/backup/

증분 백업: pg_basebackup와 같은 도구를 사용하거나, WAL (Write-Ahead Logging) 파일을 저장하여 증분 백업을 수행할 수 있습니다.

`pg_basebackup -h localhost -D /path/to/backup/ -U username -v -P --wal-method=stream`

레플리케이션: 마스터-슬레이브 구조를 사용하여 레플리케이션을 설정할 수 있습니다. 이 방법은 실시간으로 데이터를 복사하므로, 가용성이 높아집니다.

복구 방법

SQL 덤프 복구: pg_restore나 psql을 이용하여 SQL 덤프 파일로부터 복구할 수 있습니다.

psql -U username -d dbname < backup.sql

pg_restore -U username -d dbname backup.pgsql

파일 시스템 레벨 복구: 백업 디렉토리를 PostgreSQL 데이터 디렉토리로 복사하고, 서비스를 재시작합니다.

cp -R /path/to/backup/* /var/lib/postgresql/9.3/main/`

데이터 백업과 복구는 매우 중요한 작업이므로, 먼저 테스트 환경에서 충분히 연습해보고 프로덕션 환경에 적용하는 것이 좋습니다.

복구 Shell

#!/bin/bash

# 설정 변수
USERNAME="your_db_username"
DBNAME="your_db_name"
BACKUP_DIR="/path/to/backup/directory"

# 현재 날짜로 백업 파일 이름을 생성합니다.
TODAY=$(date +"%Y%m%d")
BACKUP_FILE="$BACKUP_DIR/backup_$TODAY.pgsql"

# 데이터베이스 백업을 수행합니다.
pg_dump -U $USERNAME -F c -b -v -f "$BACKUP_FILE" $DBNAME

# 오래된 백업 파일을 찾아 삭제합니다. (일주일 이상된 파일)
find $BACKUP_DIR -name "backup_*.pgsql" -type f -mtime +7 -exec rm {} \;

# 로그 메시지를 추가합니다. (옵션)
echo "Backup and cleanup completed on: $(date)" >> "$BACKUP_DIR/backup.log"

Crontab 설정

0 0 * * * /path/to/your_script_name.sh

비밀번호 Skip

1..pgpass 파일 사용

PostgreSQL은 .pgpass 파일을 이용해 비밀번호를 안전하게 저장할 수 있습니다. 이 파일을 만들고 적절한 권한을 설정해주면 pg_dump 명령어가 자동으로 이 파일을 참조합니다.

1. 사용자의 홈 디렉토리에 .pgpass 파일을 생성합니다.
2. touch ~/.pgpass
3. 이 파일에 데이터베이스 접속 정보를 추가합니다.
4. hostname:port:dbname:username:password
5. 파일의 권한을 설정합니다.
6. chmod 0600 ~/.pgpass

2.환경 변수 PGPASSWORD

#!/bin/bash 
# 환경 변수 설정 export PGPASSWORD='your_password' 
# 나머지 코드...`

이제 스크립트에 이 환경 변수를 추가하면 pg_dump 명령어는 패스워드 프롬프트 없이 실행됩니다.

3.환경 변수

비밀번호를 스크립트에 직접 쓰거나 환경 변수로 노출시키는 것은 보안상 좋지 않습니다. 따라서, 가능하다면 .pgpass 방법을 추천합니다.이러한 접근 방법 중 하나를 선택하여 적용하면, 패스워드 프롬프트 없이 자동 백업이 수행될 것입니다.