#!/usr/bin/env bash
# 文件名: pgsql_backup.sh
# 功  能: pgsql数据库备份脚本

export DB_USER="proxima"
export DB_PASS="proxima"
export DB_HOST="10.58.254.5"
export DB_PORT="29203"

export DUMP_OPTS=''

# 备份主目录
SAVE_DIR="/data/backup/pgsql"

LOG_FILE="$SAVE_DIR/backup.log"

# 备份数据保留周期（单位:天）
DAYS=5

# 时间格式
NOW="$(date +"%Y-%m-%d")"

# 创建备份子目录
MBD="$SAVE_DIR/$NOW"
install -d $MBD

# 忽略备份的数据库
SKIP="template0
template1
postgres"

# 获取所有数据库名
DBS="$(PGPASSWORD=$DB_PASS psql -h$DB_HOST -p$DB_PORT -U$DB_USER -c "SELECT datname FROM pg_database;" | sed -n '3,$p'|head -n -2)"

for db in $DBS
do
    skipdb=-1
    if [ "$SKIP" != "" ];
    then
        for i in $SKIP
        do
                [ "$db" == "$i" ] && skipdb=1 || :
        done
    fi
 
    if [ "$skipdb" == "-1" ]
    then
        FILE="$MBD/$db.sql"
        PGPASSWORD=$DB_PASS pg_dump -h$DB_HOST -p$DB_PORT -U$DB_USER $DUMP_OPTS $db > $FILE
        if [[ $? -eq "0" ]]
        then
            echo "$(basename $0) | $(date +%F" "%T) | $db | successfully completed." >> $LOG_FILE
        else
            echo "$(basename $0) | $(date +%F" "%T) | $db ] backup failed" >>$LOG_FILE
        fi
    fi
done

# 归档压缩
cd $SAVE_DIR && tar acvf $NOW.tar.bz2 $NOW --remove-files && echo "$(basename $0) | $(date +%F" "%T) | SQL backup is completed! Backup name is $NOW.tar.gz" >>$LOG_FILE

# 删除过期备份数据
find $SAVE_DIR -mtime +$DAYS -exec rm -f {} \;