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表示可读权限
普通文件 文件主组用户 其他用户
