PostgreSQL: 14버전 이하 매뉴얼 온라인-백업, 복원

※ filesystem-level Manual Online-backup and restoration in PostgreSQL versions 14 and below.
 
안녕하세요. 듀스트림입니다.
 
오늘 포스팅은 파일시스템 레벨의 매뉴얼 백업과 복원입니다.
매뉴얼 백업도 함께 구성되어 있는 백업 솔루션에 따라 사용해야만 할 때가 있습니다.
 
※ 14버전 이하와 15버전 이상의 매뉴얼 백업 방법은 다릅니다.
• Release 15: Remove long-deprecated exclusive backup mode.
→ If the database server stops abruptly while in this mode, the server could fail to start. The non-exclusive backup mode is considered superior for all purposes. Functions pg_start_backup()/pg_stop_backup() have been renamed to pg_backup_start()/pg_backup_stop(), and the functions pg_backup_start_time() and pg_is_in_backup() have been removed.

---

1. 백업

1.1. 백업 프로세스

[그림 1] 백업 프로세스

---

1.2. 백업 방법

-- Step 1: 백업 시작
psql -c "SELECT pg_start_backup(to_char(current_timestamp, 'YYYYMMDD HH24MISS'), true);"


-- Step 2: WAL 파일 스위칭 (백업을 위해 최신 WAL을 보장)
psql -c "SELECT pg_switch_wal();"


-- Step 3: 데이터 및 아카이브 영역 복사
rsync -a /data /backup/<$BACKUP_DEST>/data
rsync -a /archive /backup/<$BACKUP_DEST>/archive


-- Step 4: 백업 종료
psql -c "SELECT pg_stop_backup();"

---

2. 복원

2.1. 복원 프로세스

[그림 2] 복원 프로세스

---

2.2. 복원 방법

# Step 1: PostgreSQL 정지
pg_ctl stop


# Step 2: PostgreSQL 기존 데이터, 아카이브 영역 제거
mv /data /data_old_$(date +%Y%m%d_%H%M%S)
mv /archive /archive_old_$(date +%Y%m%d_%H%M%S)


# Step 3: 백업한 파일 복사
rsync -a /backup/<$BACKUP_DEST>/data/* /data
rsync -a /backup/<$BACKUP_DEST>/archive/* /archive


# Step 4: restore_command 설정
# postgresql.auto.conf
restore_command = 'cp /archive/%f "%p"'

# Step 4.1: 복원 시점 설정(필요시)
recovery_target_time = '2025-03-15 20:00:00'


# Step 5: PostgreSQL 복구 모드 활성화
touch /data/recovery.signal


# Step 6: 이전 실행 정보 파일 삭제
rm -f /data/postmaster.pid /data/postmaster.opts /data/backup_label


# Step 7: 복구 완료 후 archive_status 정리(필요시)
#rm -f /data/pg_wal/archive_status/*.ready
#rm -f /data/pg_wal/archive_status/*.done


# Step 8: PostgreSQL 시작
pg_ctl start

---

3. 백업 자동화 쉘 스크립트

3.1. rsync로 copy 방식

#!/bin/bash

# PostgreSQL & Backup environment variables
PGUSER="postgres"
PGDATABASE="postgres"
PG_OS_USER="postgres"
PGDATA="/data"
ARCHIVE_DIR="/backup/archive"
BACKUP_DIR="/backup"
LOG_DIR="/data/log"
TODAY=$(date +%Y%m%d_%H%M%S)
LOG_FILE="${LOG_DIR}/backup_${TODAY}.log"
BACKUP_DEST="${BACKUP_DIR}/${TODAY}"

# Ensure log directory exists
mkdir -p $LOG_DIR
chown $PG_OS_USER:$PG_OS_USER $LOG_DIR

# Function to start backup
Backup_Start() {
    echo "Starting pg_start_backup()..." >> $LOG_FILE
    su - $PGUSER -c "psql -d $PGDATABASE <<EOF
\\o ${LOG_DIR}/backup_begin_${TODAY}.log
SELECT pg_start_backup(to_char(current_timestamp, 'YYYYMMDD HH24MISS'), true);
SELECT pg_switch_wal();
\\q
EOF"
}

# Function to stop backup
Backup_End() {
    echo "Stopping pg_stop_backup()..." >> $LOG_FILE
    su - $PGUSER -c "psql -d $PGDATABASE <<EOF
\\o ${LOG_DIR}/backup_end_${TODAY}.log
SELECT pg_stop_backup();
\\q
EOF"
}

echo "==== Starting PostgreSQL Backup ====" >> $LOG_FILE

# Step 1: Check if backup is already running
IS_BACKUP_ACTIVE=$(su - $PG_OS_USER -c "psql -U $PGUSER -d $PGDATABASE -tA -c 'SELECT pg_is_in_backup();'")

if [ "$IS_BACKUP_ACTIVE" == "t" ]; then
    echo "Error: A backup is already in progress." >> $LOG_FILE
    exit 1
fi

# Step 2: Start backup process
Backup_Start

# Step 3: Create file lists for DATA and ARCHIVE_DIR using find
echo "Generating file list for DATA..." >> $LOG_FILE
(cd $PGDATA && find . -path './pg_wal/archive_status' -prune -o -print) > /tmp/list_data_${TODAY}.txt

echo "Generating file list for ARCHIVE_DIR..." >> $LOG_FILE
(cd $ARCHIVE_DIR && find . -print) > /tmp/list_archive_${TODAY}.txt

# Step 4: Copy files to backup destination
mkdir -p $BACKUP_DEST

# Copy DATA files (including pg_wal/ and archive_status/)
echo "Copying DATA files to backup directory..." >> $LOG_FILE
rsync -a --files-from=/tmp/list_data_${TODAY}.txt $PGDATA $BACKUP_DEST/data >> $LOG_FILE 2>&1

if [ $? -ne 0 ]; then
    echo "Error: DATA copy failed." >> $LOG_FILE
    exit 1
fi

# Copy ARCHIVE_DIR files
echo "Copying ARCHIVE_DIR files to backup directory..." >> $LOG_FILE
rsync -a --files-from=/tmp/list_archive_${TODAY}.txt $ARCHIVE_DIR $BACKUP_DEST/archive >> $LOG_FILE 2>&1

if [ $? -ne 0 ]; then
    echo "Error: ARCHIVE_DIR copy failed." >> $LOG_FILE
    exit 1
fi

# Step 5: End backup process
Backup_End
if [ $? -eq 0 ]; then
    echo "Backup process completed successfully." >> $LOG_FILE
else
    echo "Error: Backup failed during pg_stop_backup()." >> $LOG_FILE
    exit 1
fi

echo "==== PostgreSQL Backup Completed ====" >> $LOG_FILE

---

3.2. tar로 압축 방식

#!/bin/bash

# PostgreSQL & Backup environment variables
PGUSER="postgres"
PGDATABASE="postgres"
PG_OS_USER="postgres"
PGDATA="/data"
ARCHIVE_DIR="/backup/archive"
BACKUP_DIR="/backup"
LOG_DIR="/data/log"
TODAY=$(date +%Y%m%d_%H%M%S)
LOG_FILE="${LOG_DIR}/backup_${TODAY}.log"
TAR_FILE="${BACKUP_DIR}/backup_${TODAY}.tar.gz"
TMP_LIST_DATA="/tmp/list_data_${TODAY}.txt"
TMP_LIST_ARCHIVE="/tmp/list_archive_${TODAY}.txt"

# Ensure log directory exists
mkdir -p $LOG_DIR
chown $PG_OS_USER:$PG_OS_USER $LOG_DIR

# Function to start backup
Backup_Start() {
    echo "Starting pg_start_backup()..." >> $LOG_FILE
    su - $PGUSER -c "psql -d $PGDATABASE <<EOF
\\o ${LOG_DIR}/backup_begin_${TODAY}.log
SELECT pg_start_backup(to_char(current_timestamp, 'YYYYMMDD HH24MISS'), true);
SELECT pg_switch_wal();
\\q
EOF"
}

# Function to stop backup
Backup_End() {
    echo "Stopping pg_stop_backup()..." >> $LOG_FILE
    su - $PGUSER -c "psql -d $PGDATABASE <<EOF
\\o ${LOG_DIR}/backup_end_${TODAY}.log
SELECT pg_stop_backup();
\\q
EOF"
}

echo "==== Starting PostgreSQL Backup ====" >> $LOG_FILE

# Step 1: Check if backup is already running
IS_BACKUP_ACTIVE=$(su - $PG_OS_USER -c "psql -U $PGUSER -d $PGDATABASE -tA -c 'SELECT pg_is_in_backup();'")

if [ "$IS_BACKUP_ACTIVE" == "t" ]; then
    echo "Error: A backup is already in progress." >> $LOG_FILE
    exit 1
fi

# Step 2: Start backup process
Backup_Start

# Step 3: Generate file lists
echo "Generating file list for PGDATA..." >> $LOG_FILE
(cd $PGDATA && find . -path './pg_wal/archive_status' -prune -o -print) > $TMP_LIST_DATA

echo "Generating file list for ARCHIVE_DIR..." >> $LOG_FILE
(cd $ARCHIVE_DIR && find . -print) > $TMP_LIST_ARCHIVE

# Step 4: Compress files directly into .tar.gz without copying
echo "Compressing backup files into tar.gz..." >> $LOG_FILE
tar -czf $TAR_FILE -C $PGDATA -T $TMP_LIST_DATA --transform "s|^./|data/|" \
             -C $ARCHIVE_DIR -T $TMP_LIST_ARCHIVE --transform "s|^./|archive/|" >> $LOG_FILE 2>&1

if [ $? -ne 0 ]; then
    echo "Error: Compression failed." >> $LOG_FILE
    exit 1
fi

# Step 5: End backup process
Backup_End
if [ $? -eq 0 ]; then
    echo "Backup process completed successfully." >> $LOG_FILE
else
    echo "Error: Backup failed during pg_stop_backup()." >> $LOG_FILE
    exit 1
fi

# Step 6: Clean up temporary file lists
rm -f $TMP_LIST_DATA $TMP_LIST_ARCHIVE

echo "==== PostgreSQL Backup Completed ====" >> $LOG_FILE

---

매뉴얼 백업도 함께 구성되어 있는 백업 솔루션에 따라 사용할 때가 있습니다.
 
오늘은 여기까지~