Postgresql DB 백업 및 복원

# Postgresql DB 백업 및 복원 스크립트 작성

- 일단 superuser 계정으로 접속을 해야함.

- 백업 스크립트 :  db_backup.sh

#!/bin/bash

#----- DELETE FILE_NAME SETTING -----
DEL_FILE=$(date -d '30 day ago' +'%Y-%m-%d_')"*.dump"

#----- FILE NAME SETTING -----
FILENAME=$(date +"%Y-%m-%d_%H%M").dump

#----- BACKUP_DIR SETTING -----
BACKUP_DIR=/var/db_backup

cd $BACKUP_DIR
echo "DB backup start time : " $(date +"%Y-%m-%d %H:%M:%S")

#----- RESTORE BACKUP_FILE -----
PGPASSWORD="admin123" pg_dump -h 10.10.10.10 -U user_name DB_name -Fc -v > "${BACKUP_DIR}/DBNAME_${FILENAME}"

echo "Successful db backup ( ${BACKUP_DIR}/DBNAME_${FILENAME} )"
echo "Delete old file DBNAME_${DEL_FILE}"

#----- DELETE AFTER 30 DAYS -----
rm "${BACKUP_DIR}/DBNAME_${DEL_FILE}"

echo "BACKUP - End time : " $(date +"%Y-%m-%d %H:%M:%S")

- 복구 명령어

PGPASSWORD="admin123" pg_restore -h 10.10.10.10 -U user_name  -d DB_name -v  ./DBNAME_2022-12-06_0747.dump

PGPASSWORD="admin123" pg_restore --verbose --clean --no-acl --no-owner -h 10.10.10.10 -U user_name -d DB_name ./DBNAME_2022-12-06_0747.dump