PostgreSQL 定时备份

备份方式

使用 pg_dump 工具，创建一个由 SQL 语句组成的文件

编写备份脚本

#!/bin/bash

# 备份存放位置
BACKUP_DIR=/var/lib/backup/
# 如果不存在就创建
if [ ! -d "$BACKUP_DIR" ]; then
    mkdir -p "$BACKUP_DIR"
fi
cd $BACKUP_DIR

# 删除30天之前的备份
deleteDIR=$(date --date='30 day ago' +%Y-%m-%d)
rm -rf $deleteDIR

# 假设每天凌晨执行 备份数据文件夹名为昨日 yyyy-MM-dd 格式
dateDIR=$(date --date='1 day ago' +%Y-%m-%d)
mkdir $dateDIR

# 备份指定的数据库
backupDB=(db_name0 db_name1 db_name2)
backupDBArr=$(echo ${backupDB[@]})

# 使用压缩转储每一个数据库
for i in $backupDBArr; do
    pg_dump -U postgres $i | gzip > $BACKUP_DIR$dateDIR/${i}_${dateDIR}.gz
done

注意

如果遇到此类错误：psql: FATAL: Peer authentication failed for user “postgres” (or any user)

原因：By default psql connects over UNIX sockets using peer authentication, that requires the current UNIX user to have the same user name as psql.

解决方法：

• 在执行 pg_dump 之前先切换到 linux 操作系统用户 postgres (安装 postgres 时内建)

  sudo -u postgres pg_dump

• 参照：https://gist.github.com/AtulKsol/4470d377b448e56468baef85af7fd614

添加定时任务

添加一个新的任务到 crontab

# (edit user's crontab)
crontab -e 

# 追加以下内容到上面命令打开的文件末尾，代表每天 00:01 执行 pg_backup.sh
01 00 * * * sh /.../pg_backup.sh

有关 cron ，可参照 https://zh.wikipedia.org/wiki/Cron

恢复

# 运行
gunzip -c filename.gz | psql dbname
# 或者
cat filename.gz | gunzip | psql dbname