postgresql数据库备份脚本

从源库直接恢复至目标库

#!/bin/bash  
  
export SOURCE_DB_HOST=172.16.15.12  
export SOURCE_DB_PASS=123456  
export SOURCE_DB_PORT=12000  
export SOURCE_DB_USER=postgres  
  
for db in $(cat dblist)  
do  
  echo "-----------------------------------------[restoring database $db]------------------------------------------"  
  # 备份
  PGPASSWORD=${SOURCE_DB_PASS} pg_dump -U ${SOURCE_DB_USER} -h ${SOURCE_DB_HOST} -p ${SOURCE_DB_PORT} -d $db > /tmp/$db.sql  
  createdb -U ${SOURCE_DB_USER} $db  
  PGPASSWORD=${SOURCE_DB_PASS} psql -U ${SOURCE_DB_USER} < /tmp/$db.sql  
  echo "-----------------------------------------[restoring database $db finish]------------------------------------------"  
done

定期备份

#!/bin/bash
export SOURCE_DB_HOST=localhost
export SOURCE_DB_PASS=xxx
export SOURCE_DB_PORT=5432
export SOURCE_DB_USER=admin

# backup folder
export BACKUP_FOLDER=/tmp/postgresql_backup/
export SUFFIX=$(date '+%Y%m%d-%H%M%S')  
export PATH=$PATH:/opt/PostgreSQL/9.6/bin

mkdir -p ${BACKUP_FOLDER}

for db in $(cat dblist)
do
  echo "[INFO] backup database $db"
  PGPASSWORD=${SOURCE_DB_PASS} pg_dump -U ${SOURCE_DB_USER} -h ${SOURCE_DB_HOST} -p ${SOURCE_DB_PORT} -d $db > ${BACKUP_FOLDER}/$db-${SUFFIX}.sql  
  echo "[INFO] backup database $db finished"
done

这个脚本会读取dblist中所有需要备份的数据库, 把备份文件存放至${BACKUP_FOLDER},文件名类似postgres-20230221-161308.sql