PostgreSQL的自动备份和恢复

PostgreSQL自带一个客户端pgAdmin，里面有备份，恢复选项，可以对数据库进行手动的备份和恢复，但这个工具在备份大数据库时经常不能成功，而且不能做到自动备份。在搜索了网上的各种关于PostgreSQL的备份和恢复,也遇到了不少问题，特别是PostgreSQL的版本和脚本的兼容性问题，因此通过此文章把验证的方法记录下来。

PostgreSQL数据库中未提供数据库的定时备份功能，需要结合定时job功能来共同实现。当前有2种定时job方式，crontab是Linux中的定时job功能，故只能支持Linux系列操作系统；其中pgAgent是基于数据库的定时备份，可适用于各种操作系统，包括Windows和Linux，下面详细介绍如何使用pgAgent和pgAdmin Ⅲ来实现PostgreSQL自动备份和恢复。

适用版本：PostgreSQL 9.6

操作系统:Ubuntu 16.04

功能：定时备份到指定目录和手动恢复某一个版本的数据库

1. 准备工作

第一步，安装pgAgent

sudoapt-get update

sudoapt-get install pgagent

第二步，创建 pgAgent extension

创建方法可以使用psql执行命令也可以使用pgAdmin工具，连接postgres数据库，打开pgAdmin的命令执行窗口中执行以下命令，

CREATE EXTENSION pgagent;

第三步，创建plpgsql language

创建方法同创建pgAgent extension，执行以下命令

CREATE LANGUAGE plpgsql;

第四步，设置pgAgent开机自启动

将以下脚本pgagent.sh拷贝到/etc/init.d目录下

#!/bin/bash
export PGPASSWORD=yourpassword
pgagent hostaddr=127.0.0.1 dbname=postgres user=postgres
echo"pgagent started."

第五步，执行以下脚本，完成开机启动

sudo chmod 777 pgagent.sh
update-rc.d pgagent.sh start 2

 

2. 备份脚本

粘贴下面的脚本到postgresql_dbback.sh,此脚本完成的功能是在备份目录下创建一个以yyyymmddhh为格式的文件夹，并将此数据库server下的所有数据库分别进行备份（除template0和template1），备份格式为PostgreSQL自定义的格式，非sql格式，并自动删除上月的备份文件夹释放存储空间，备份脚本如下：

#!/bin/bash
#backup directory can be a file server share that the PgAgent daemon account has access to
BACKUPDIR="/home/backup/postgres"
PGHOST="localhost"
PGUSER="postgres"
PORT="5432"
PGBIN="/usr/bin"
thedate=`date --date="today" +%Y%m%d%H`
themonth=`date --date="today" +%Y%m`
export PGPASSWORD=!guomao1


dbs=$(psql -h localhost -U postgres -t -A -c 'SELECT datname FROM pg_database')

#create backup directory
mkdir -p $BACKUPDIR/$thedate
#iterate througth dbs in dbs array and backup each one as postgresql custom format
for db in ${dbs[@]}
do
    #exclude template1 , template1 database
    if [ "$db" != "template0" -a "$db" != "template1" ]; then
        #backup database
        $PGBIN/pg_dump -d $db -h $PGHOST -U $PGUSER -p $PORT -Fc > "$BACKUPDIR/$thedate/$db.dump"
    fi
done

#this section deletes the previous month of same day backup except for the full server backup
rm -rf $BACKUPDIR/`date --date="last month" +%Y%m%d`*

3. 恢复脚本

粘贴下面的脚本到postgresql_dbrestore.sh文件，此脚本接受两个参数，一个是恢复的数据库名和备份dump文件。

参数	实例
数据库名	test
脚本名	2018121111/test.dump

脚本内容如下：

#!/bin/bash
#parameter 1:database name, parameter 2: dump path
if [ -z $1 ]; then
    echo "database name is empty"
    exit 1
fi

if [ -z $2 ]; then
    echo "dump file is empty"
    exit 1
fi
if [ ! -f /home/backup/postgres/$2 ]; then
    echo "Dump File not found!"
    exit 1
fi
export PGPASSWORD=yourdatabasepassword
dbs=$(psql -h localhost -U postgres -t -A -c "SELECT datname FROM pg_database where datname='$1'")
if [ -z $dbs ]; then
    echo "new database:$1 will be created."
    createdb -h localhost -U postgres -p 5432 $1
    echo "database will be restored into new database $1 from $2"
    pg_restore -d "$1" -h localhost -U postgres -p 5432 "/home/backup/postgres/$2"
else
    while true; do
        read -p "The database exists, are you sure to clean and restore it?" yn
        case $yn in
            [Yy]* ) 
            echo "database:$1 will be dropped."
            dropdb -h localhost -U postgres -p 5432 $1
            echo "database:$1 will be recteated."
            createdb -h localhost -U postgres -p 5432 $1
            echo "database will be restored into new database $1 from $2"
            pg_restore  -d "$1" -h localhost -U postgres -p 5432 "/home/backup/postgres/$2"
            break;;
            [Nn]* ) exit;;
            * ) echo "Please answer yes or no.";;
        esac
    done
fi

设置脚本执行权限

sudochmod 777 postgresql-dbbackup.sh

sudochmod 777 postgresql-dbrestore.sh

4. 设置定期备份

利用pgAdmin工具来创建定时的job来完成数据库的自动备份，过程如下：

第一步，打开pgadmin3工具，导航到job标签，点击new job，如下图：

 

 

 

第二步，增加备份步骤，选中batch，在steps标签页中点击add按钮，如下图，

 

第三步，切换到Definition标签，并填充备份脚本路径，如下图 

 

第四步，创建Schedule，如下图

第五步，完成的Schedule配置如下图：

 

最后，检测是否能自动备份，可以点击Run now,并查看statics标签下的脚本执行情况，如下图

 

5. 手动恢复

数据库恢复的脚本postgres-dbrestore.sh已经拷贝到home/backup/postgres目录下,该脚本需要传入两个参数。

参数实例数据库名test脚本名2018121111/test.dump

脚本执行如下所示：

Home/backup/postgres/postgres_dbrestore.sh test 2018121111/test.dump

该脚本执行策略是，如果恢复的数据库存在，则会提示是否重新创建并恢复此数据库，如下图：

 

 

如果要恢复的数据库步存在，则直接恢复，如下图。

6. 脚本安全

为了安全考虑，密码文件不放在脚本中，可以在当前用户的HOME目录下，创建文件名为.pgpass的口令文件，这样就可以在我们连接PostgreSQL服务器时，客户端命令自动读取该文件已获得登录时所需要的口令信息。该文件的格式如下：

hostname:port:database:username:password，

以上数据是用冒号作为分隔符，总共分为五个字段，分别表示服务器主机名(IP)、服务器监听的端口号、登录访问的数据库名、登录用户名和密码，其中前四个字段都可以使用星号(*)来表示匹配任意值。

.pgpass文件的权限必须为0600，从而防止任何全局或者同组的用户访问，否则这个文件将被忽略。

1表示可执行权限，2表示可写权限，4表示可读权限

普通文件 文件主组用户 其他用户