一、生产备份方式：pg_basebackup
PostgreSQL脚本监控由于没有安装RMAN插件，我们使用PG_BASEBACKUP进行备份。
任何数据库搭建完成，准备投入使用之后，首先要确定的就是数据库的备份策略，合理有规划的备份是数据安全的关键。
以下就是针对于PostgreSQL数据库，设计的一整套备份策略
WAL归档策略
wal日志
所谓wal，即 write ahead log。存储了数据库系统中所有更改和操作的历史，以确保数据库不会因为故障(例如掉电或其他导致服务器崩溃的故障)而丢失数据。它是在写的操作完成之前写好，但当写真正完成之后，它的意义似乎就没那么大了，但是绝不是以所用。虽然在默认的设置中wal归档并不是开启的，而是会自动删除。
有时我们使用全备进行恢复数据时，时间点总是没那么精确，因为全备一般都是一段时间之前的数据，这之间的数据可以通过应用wal找回。这也是我们推荐开启wal归档的原因
参数配置
在开启wal日志归档前，首先需要设置几个参数
wal_level = replica
该参数的可选的值有minimal，replica和logical，wal的级别依次增高，在wal的信息也越多。由于minimal这一级别的wal不包含从基础的备份和wal日志重建数据的足够信息，在该模式下，无法开启wal日志归档
archive_mode = on
上述参数为on，表示打开归档备份,可选的参数为on,off,always 默认值为off，所以要手动打开
archive_command = 'cp %p /pgdata/10/archive_wals%f'
该参数的默认值是一个空字符串，他的值可以是一条shell命令或者一个复杂的shell脚本。在shell脚本或命令中可以用 “%p” 表示将要归档的wal文件包含完整路径的信息的文件名，用“%f” 代表不包含路径信息的wal文件的文件名
注意：wal_level和archive_mode参数修改都需要重新启动数据库才可以生效。而修改archive_command则不需要。所以一般配置新系统时，无论当时是否需要归档，这要建议将这两个参数开启
归档策略脚本
这次分享的策略是使用一个shell脚本来管理归档：
在postgres家目录下，分别创建bin，log目录。bin目录存放此脚本，log则记录执行日志
归档策略是 在/data下的 archivedir中，按日期为名归档日志，保留20天。20天前的自动删除
archive_command参数的配置为：
修改postgresql.conf文件以下参数，然后执行pg_ctl reload就可以生效了
archive_command ='/bin/bash /home/postgres/bin/pg_archive.sh %p %f'
archive_command命令在归档时不再使用简单的cp命令，而是使用预定好的脚本来备份，脚本内容为
#!/bin/bash
source /home/postgres/.bash_profile
DATE=`date +%Y%m%d`
DIR="/data/archivedir/$DATE"
BACK="/data/archivedir/"`date -d '-5 day' +%Y%m%d`
if [ -d "$BACK" ]; then
rm -rf $BACK
echo "success rm $BACK" > /home/postgres/logs/pg_archive_logs
else
echo "the old backup file not exists!" > /home/postgres/logs/pg_archive_logs
fi
(test -d $DIR || mkdir -p $DIR) && cp $1 $DIR/$2
物理全备
使用pg_basebackup
PG数据库自带全备的备份命令，提供方便基础备份的工具，这个命令会把整个数据库实例的数据都拷贝出来。经常用来搭建主备 ，做全量备份
需要注意的是，在使用 pg_basebackup 的备份终端服务器上，必须要有访问数据库的权限，即在pg_hba.conf中要指定好权限
每一次的备份都会使用一个wal槽max_wal_senders。
备份脚本
每天1点备份，保留2周的全备
#!/bin/bash
back="/data/sas_pgbackup/"`date -d '-2 week' +%Y-%m-%d`
backf="/data/sas_pgbackup/"`date +%F`"/base.tar"
echo "starting backup....."
pg_basebackup -D /data/sas_pgbackup/`date +%F` -Xf -R -P -F t -U postgres -h 127.0.0.1
if [ -f "$backf" ]; then
echo "success backup to $backf"
if [ -d "$back" ]; then
rm -rf $back
echo "success rm $back"
else
echo "the old backup file not exists!"
fi
else
echo "backup failed, because of the file $backf not found"
fi
00 00 */1 * * /home/postgres/bin/pgbackup.sh >/home/postgres/logs/pgbackup.log 2>&1 &
其他生产备份工具：
备份管理工具
pg_rman
pg_rman是一款专门为PostgreSQL数据库打造的优秀开源备份软件，其使用的思路类似Oracle的rman，是一个非常方便的备份管理工具
pg_rman最大的亮点就是实现了增量备份，注意不是基于WAL日志的增量备份，是基于上次全量备份之后发生的变化数据块的增量备份
pg_rman的备份原理给介绍一下:
pg_rman不是使用流复制协议进行拷份的，而是使用文件拷贝，即先在主库上执行pg_start_backup()函数,然后拷贝整个数据库的数据目录，最后再执行 pg_stop_backup()函数来结束备份。
所以pg_rman必须和数据库节点跑在一起。否则可以想的到是是无法拷贝数据库的数据文件，从而无法备份。所以通常备份时，需要使用NAS等NFS共享方法把备份机的文件系统mount到数据库机器上。
pg_rman可以在standby节点上做全库的备份，但需要通过网络连接到主库上执行pg_start_backup和pg_stop_backup。
pg_rman需要主库打开了归档才能更好的备份，所以需要在postgresql.conf中打开归档，以便让pg_rman判断出数据库的归档目录在哪里。pg_rman主要是读取postgresql.conf文件中archive_command参数的值。
pg_rman还能备份PostgreSQL数据库的一般程序日志，即pg_log目录的日志文件。所以pg_rman会读取postgresql.conf中的log_directory参数以确定这些日志在哪个目录下。当然这些程序日志通常不是太重要，可以不备份。
pg_rman使用
pg_rman的安装不再赘述，说说它的简单使用
做一次全备
$ pg_rman backup --backup-mode=full
INFO: copying database files
INFO: copying archived WAL files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
全备完之后要校验一下：
$ pg_rman validate
INFO: validate: "2020-6-13 17:45:07" backup and archive log files by CRC
INFO: backup "2020-6-13 17:45:07" is valid
然后用pg_rman show查看状态：
$ pg_rman show
=====================================================================
StartTime EndTime Mode Size TLI Status
=====================================================================
2020-6-13 17:45:07 2020-6-13 17:45:09 FULL 50MB 1 OK
此时便可看到full备份已经ok
在增量完成之后，如果过了一段时间，数据库产生很多变化，还可以做全量备份
$ pg_rman backup --backup-mode=incremental --with-serverlog
INFO: copying database files
INFO: copying archived WAL files
INFO: copying server log files
INFO: backup complete
INFO: Please execute 'pg_rman validate' to verify the files are correctly copied.
[postgres@pg01 ~]$ pg_rman show
=====================================================================
StartTime EndTime Mode Size TLI Status
=====================================================================
2020-6-13 17:47:44 2020-6-13 17:47:46 INCR 67MB 1 DONE
2020-6-13 17:45:07 2020-6-13 17:45:09 FULL 50MB 1 OK
每次备份完成，不要忘了做pg_rman validate:
$ pg_rman validate
INFO: validate: "2020-6-13 17:47:44" backup, archive log files and server log files by CRC
INFO: backup "2020-6-13 17:47:44" is valid
$ pg_rman show
=====================================================================
StartTime EndTime Mode Size TLI Status
=====================================================================
2020-6-13 17:47:44 2020-6-13 17:47:46 INCR 67MB 1 OK
2020-6-13 17:45:07 2020-6-13 17:45:09 FULL 50MB 1 OK
-----------------------------------二、日常运维备份方式：pg_dump/pg_dumpall
备份与恢复
- postgresql自带了两个备份工具：
| 1 2 |
|
使用pg_dump备份
| 1 2 |
|
- pg_dump 常用选项
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
|
- 备份某个database，备份结果以自定义压缩格式输出：
| 1 |
|
- 备份某个database，备份结果以SQL文本格式输出，输出结果要包含CREATE DATABASE 语句：
| 1 |
|
- 备份某个database中所有名称以"pay"开头的表，备份结果以自定义压缩格式输出：
| 1 |
|
- 备份某个database中hr和payroll这两个schema中的所有数据，备份结果以自定义压缩格式输出：
| 1 |
|
- 备份某个database中除public schema中的数据以外的所有数据，备份结果以自定义压缩格式输出：
| 1 |
|
- 将数据备份为SQL文本文件，且生成INSERT语句，该文件可以用于低版本的PostgreSQL或其它支持SQL的非PostgreSQL数据库
| 1 |
|
- 目录备份格式：备份时会先创建一个新目录，然后逐一将一个gzip格式的压缩文件和一个列出所有包含结构的文件填充到该目录。
| 1 |
|
- 支持使用备份选项 --jobs（-j），会启动多个线程并行执行当前备份操作
| 1 |
|
使用pg_dumpall进行全库备份
| 1 2 3 4 |
|
- pg_dumpall 常用选项
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
|
- pg_dumpall备份数据库
| 1 |
|
- pg_dumpall仅备份角色和表空间定义
| 1 |
|
- pg_dumpall仅需备份角色定义而无需备份表空间
| 1 |
|
数据恢复
- PostgreSQL支持以下两种数据恢复方式：
| 1 2 |
|
使用pgsql恢复SQL文本格式的数据备份
- 恢复一个SQL备份文件并忽略过程中可能发生的所有错误
| 1 |
|
- 恢复一个SQL备份文件，如遇错误则立即停止恢复
| 1 |
|
- 将SQL文本中的数据恢复到某个指定的database
| 1 |
|
使用pg_restore进行恢复
- pg_restore 常用参数
| 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
|
- 先创建目标数据库
| 1 |
|
- 执行恢复
| 1 |
|
- 如果备份和恢复使用相同的database，则可以通过添加--create选项省去单独创建库的过程
| 1 2 |
|
- 9.2版本或更新的pg_restore支持--section选项，实现仅恢复表结构而不恢复表数据，创建模板数据库时可以使用这个方法
| 1 2 3 |
|
转载：
147
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
