一、使用Postgres自带工具进行备份
pg数据库远程备份命令pg_dump
命令参数介绍
dbname #指定要被转储的数据库名。如果没有指定，将使用环境变量PGDATABASE。如果环境变量也没有设置，则使用指定给该连接的用户名
-h #连接数据库IP地址
-p #连接数据库端口
-U #连接数据库用户名
-W #连接密码
--create #备份文件中生存数据库创建命令
备份执行命令
pg_dump 数据库名称 -h 地址 -p 端口 -U postgres >文件名称.sql
恢复命令
psql -h 地址 -p 端口 -U postgres -f eg_app.sql
备份脚本
[17:46:05 root@centos7 ~]#cat pg_backup.sh
#!/bin/bash
PG_HOST="10.202.63.55"
PG_PORT="5000"
PG_USER="postgres"
PG_PWD="Gridsum2022"
export export PGPASSWORD=${PG_PWD}
databases=$(psql -h ${PG_HOST} -p ${PG_PORT} -U ${PG_USER} -q -c "\l" | sed -n 4,/\eof/p | grep -v rows\) | grep -v template0 | grep -v template1 | awk {'print $1'} | grep -v '(' | grep -v "|")
echo "------备份开始------"
for i in ${databases};do
pg_dump ${i} -h ${PG_HOST} -p ${PG_PORT} -U ${PG_USER} --create &> ${i}.sql
if [ $? == 0 ];then
echo -e "------\033[1;32m数据库${i}备份完成\033[0m------"
else
echo -e "------\033[1;31m数据库${i}备份失败\033[0m------"
fi
done
数据导入脚本
PG_HOST="10.28.88.89"
PG_PORT="5000"
PG_USER="postgres"
#数据库密码
export PGPASSWORD="HGyF1HmCamILkLJQ"
DB=`ls *.sql`
for i in $DB;do
psql -h ${PG_HOST} -p ${PG_PORT} -U ${PG_USER} -f ${i}
done
二、使用pgbackrest进行备份
pgBackRest旨在成为一种可靠，易于使用的备份和还原解决方案，可以通过利用针对数据库特定要求进行优化的算法无缝扩展到最大的数据库和工作负载。
特征如下
- 并行备份和恢复
- 本地或远程操作
- 多个存储库
- 完整备份、增量备份和差异备份
pgbackrest安装需要通过源码进行编译[源码包下载](Release v2.40: OpenSSL 3 Support · pgbackrest/pgbackrest · GitHub)
2.1 Pgbackrest编译安装
#将源码包解压
$ tar xf pgbackrest-release-2.40.tar.gz
#安装编译依赖，必须安装
$ yum install centos-release-scl-rh -y
$ yum install make gcc postgresql12-devel openssl-devel libxml2-devel lz4-devel libzstd-devel bzip2-devel libyaml-devel postgresql12-server
#配置环境变量
$ cat /etc/profile
export PATH=${PATH}:/usr/pgsql-12/bin/
#进行编译
$ cd pgbackrest-release-2.40/src/ && ./configure && make
#验证命令,可以打印出帮助表示正常
$ pgbackrest
2.2 Pgbackrest初始化
pgBackRest 需要日志和配置目录以及配置文件。
#创建日志目录
mkdir -p -m 770 /var/log/pgbackrest
chown postgres:postgres /var/log/pgbackrest
#创建配置文件
mkdir -p /etc/pgbackrest
mkdir -p /etc/pgbackrest/conf.d
touch /etc/pgbackrest/pgbackrest.conf
chmod 640 /etc/pgbackrest/pgbackrest.conf
chown postgres:postgres /etc/pgbackrest/pgbackrest.conf
Postgresql配置存档
#配置postgres的wal存档
$ vim /var/lib/pgsql/12/data/postgresql.conf
archive_command = 'pgbackrest --stanza=postgres archive-push %p'
archive_mode = on
log_filename = 'postgresql.log'
max_wal_senders = 3
wal_level = replica
2.3 Pgbackrest存储库
pgbackrest支持多种存储库如S3支持。
1.本地存储库创建
#创建本地存储库数据目录
mkdir -p /var/lib/pgbackrest
chmod 750 /var/lib/pgbackrest
chown postgres:postgres /var/lib/pgbackrest
#配置pgbackrest配置文件
[postgres]
pg1-path=/var/lib/pgsql/12/data
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2 #保存2个完整备份
[global：archive-push]
compress-level=3
#初始化仓库
sudo -u postgres pgbackrest --stanza=postgres --log-level-console=info stanza-create
#检测
sudo -u postgres pgbackrest --stanza=postgres --log-level-console=info check
2.S3存储库创建(Minio)
需要提前准备minio服务，并且创建bucket。minio必须以tls方式提供服务。
#配置文件如下
[postgres]
pg1-path=/var/lib/pgsql/12/data
[global]
repo1-cipher-pass=v+u2xQcT2ITi6pC/l7PmNnewaYgBcKXV2W6W9KRWlGIi6rqSQY0anuDqIwbwnbcO
repo1-cipher-type=aes-256-cbc
repo1-path=/
repo1-type=s3
repo1-s3-endpoint=192.168.10.72:9000
repo1-s3-bucket=postgres
repo1-s3-verify-tls=n
repo1-s3-key=minio
repo1-s3-key-secret=minio123
repo1-s3-region=us-east-1
repo1-s3-uri-style=path
repo1-retention-full=1
[global：archive-push]
compress-level=3
#初始化仓库
sudo -u postgres pgbackrest --stanza=postgres --log-level-console=info stanza-create
#检测
sudo -u postgres pgbackrest --stanza=postgres --log-level-console=info check
3.配置仓库加密
存储库将配置密码类型和密钥以演示加密。即使存储库类型（例如 S3 或其他对象存储）支持加密，也始终在客户端执行加密。
对密码密钥使用长而随机的密码非常重要。生产密码可以使用openssl rand -base64 48命令生成。
配置文件示例如下：
[postgres]
pg1-path=/var/lib/pgsql/12/data
[global]
repo1-cipher-pass=v+u2xQcT2ITi6pC/l7PmNnewaYgBcKXV2W6W9KRWlGIi6rqSQY0anuDqIwbwnbcO
repo1-cipher-type=aes-256-cbc
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2
[global：archive-push]
compress-level=3
2.4 Pgbackrest备份创建
备份是数据库集群的一致副本，可以进行恢复以从硬件故障中恢复、执行时间点恢复或建立新的备用数据库
1.完整备份
pgBackRest将数据库集群的全部内容复制到备份中。数据库集群的第一次备份始终是完整备份。pgBackRest总是能够直接恢复完整备份。完整备份不依赖于完整备份之外的任何文件以保持一致性。完整备份参数为--type=full。默认情况下一个存储库中只会存在一个完整备份除非使用repo1-retention-full进行设置。
完整备份特征
- 如果存储库存在一个完整备份，如果没有进行特殊配置完整备份只能被保留一个，如果再次创建完整备份即之前的完整备份会被删除
- 增量备份与差异备份会依赖与一个完整备份，如果完整备份被删除，依赖他的备份都会被删除。
sudo -u postgres pgbackrest --stanza=postgres --type=full --log-level-console=info backup
2.差异备份
pgBackRest仅复制自上次完整备份以来已更改的那些数据库集群文件。pgBackRest通过复制所选差异备份中的所有文件以及之前完整备份中适当的未更改文件来恢复差异备份。差异备份的优点是它需要的磁盘空间比完整备份少，但是，差异备份和完整备份都必须有效才能还原差异备份。差异备份参数为--type=diff。
差异备份特征
- 差异备份必须基于完整备份。
sudo -u postgres pgbackrest --stanza=postgres --type=diff --log-level-console=info backup
3.增量备份
pgBackRest仅复制自上次备份以来已更改的那些数据库集群文件（可以是另一个增量备份、差异备份或完整备份）。由于增量备份仅包括自上次备份以来更改的文件，它们通常比完整备份或差异备份小得多。与差异备份一样，增量备份依赖于其他备份有效来恢复增量备份。由于增量备份仅包括自上次备份以来的那些文件，所有先前的增量备份返回到先前的差异、先前的差异备份和先前的完整备份都必须有效才能执行增量备份的还原。如果不存在差异备份，则所有先前的增量备份都返回到先前的完整备份，该完整备份必须存在。默认为增量备份。
增量备份特征
- 增量备份必须依赖完整备份或者差异备份，如果完整备份与差异备份都存在则都会依赖。
sudo -u postgres pgbackrest --stanza=postgres --log-level-console=info backup
4.备份信息查看
使用info命令获取有关备份的信息
[15:19:57 root@centos7 ~]#sudo -u postgres pgbackrest --stanza=postgres info
stanza: postgres
status: ok
cipher: aes-256-cbc
db (current)
wal archive min/max (12): 00000003000000000000001A/000000030000000000000022
full backup: 20220726-145633F
timestamp start/stop: 2022-07-26 14:56:33 / 2022-07-26 14:56:42
wal start/stop: 00000003000000000000001A / 00000003000000000000001A
database size: 47.9MB, database backup size: 47.9MB
repo1: backup set size: 5.8MB, backup size: 5.8MB
diff backup: 20220726-145633F_20220726-151754D
timestamp start/stop: 2022-07-26 15:17:54 / 2022-07-26 15:17:55
wal start/stop: 00000003000000000000001C / 00000003000000000000001C
database size: 47.9MB, database backup size: 8.3KB
repo1: backup set size: 5.8MB, backup size: 512B
backup reference list: 20220726-145633F
diff backup: 20220726-145633F_20220726-151800D
timestamp start/stop: 2022-07-26 15:18:00 / 2022-07-26 15:18:02
wal start/stop: 00000003000000000000001D / 00000003000000000000001E
database size: 47.9MB, database backup size: 9.9KB
repo1: backup set size: 5.8MB, backup size: 1.2KB
backup reference list: 20220726-145633F
incr backup: 20220726-145633F_20220726-151822I
timestamp start/stop: 2022-07-26 15:18:22 / 2022-07-26 15:18:24
wal start/stop: 000000030000000000000020 / 000000030000000000000020
database size: 47.9MB, database backup size: 8.3KB
repo1: backup set size: 5.8MB, backup size: 512B
backup reference list: 20220726-145633F, 20220726-145633F_20220726-151800D
查看指定备份信息
[13:49:11 root@centos7 ~]#sudo -u postgres pgbackrest --stanza=postgres --set=20220804-103916F_20220804-105306I info
stanza: postgres
status: ok
cipher: aes-256-cbc
db (current)
wal archive min/max (12): 000000010000000000000004/00000001000000000000000C
incr backup: 20220804-103916F_20220804-105306I
timestamp start/stop: 2022-08-04 10:53:06 / 2022-08-04 10:53:08
wal start/stop: 00000001000000000000000C / 00000001000000000000000C
lsn start/stop: 0/C000028 / 0/C000100
database size: 53.9MB, database backup size: 704.3KB
repo1: backup set size: 6.3MB, backup size: 140.8KB
backup reference list: 20220804-103916F, 20220804-103916F_20220804-104614D
database list: eg_app (16384), eg_audit_app (16630), eg_base_app (16685), postgres (14187)
2.5 Pgbackrest备份恢复
restore命令自动默认为从存在备份的第一个存储库中选择最新的备份进行恢复。检查存储库的顺序由pgbackrest.conf规定（例如，repo1 将在 repo2 之前检查）。要从特定存储库中进行选择，可以传递--repo选项（例如--repo=1）。如果需要不是最新的备份，则可以传递--set选项。
如果指定了--type=time或--type=lsn的 PITR，则必须使用--target选项指定目标时间或目标 lsn。如果未通过--set选项，然后将按顺序检查配置的存储库以获取包含请求的时间或 lsn 的备份。如果没有找到匹配的备份，则包含备份的第一个存储库中的最新备份将用于--type=time而不会为--type=lsn选择备份。对于其他类型的 PITR，例如xid，如果目标在最新备份之前，则必须提供--set选项。
注意数据库在恢复完成后检查没有问题需要新建全量备份。
1.快速恢复数据
Pgbackrest恢复数据需要停止pg数据库并且清空数据目录，进行全量恢复。
#停止pg
systemctl stop postgresql-12.service
#清理数据目录
rm -rf /var/lib/pgsql/12/data/*
#恢复数据
sudo -u postgres pgbackrest --stanza=postgres --set=20220804-103916F_20220804-104321D --type=immediate --log-level-console=info restore
#说明
--set 选择恢复的备份
--type=immediate 12即以下版本恢复数据时最好添加这个参数
2.恢复时不清理数据目录
在恢复之前清理数据库集群目录。delta选项允许pgBackRest自动确定可以保留数据库集群目录中的哪些文件以及需要从备份中恢复哪些文件，它还会删除备份清单中不存在的文件，因此它将处理不同的更改。这是通过为数据库集群目录中的每个文件计算SHA-1加密哈希来实现的如果SHA-1hash 与备份中存储的 hash 不匹配，则该文件将被恢复。当与process-max选项结合使用时，此操作非常有效。由于PostgreSQL服务器在还原期间关闭，因此可以使用比在PostgreSQL服务器运行时备份期间可能需要的更多数量的进程。
sudo -u postgres pgbackrest --stanza=postgres --set=20220804-103916F_20220804-104321D --delta --type=immediate --log-level-console=info restore
3.恢复特定的数据库
#查看备份详细信息
[14:16:32 root@centos7 ~]#sudo -u postgres pgbackrest --stanza=postgres --set=20220804-103916F_20220804-104549I info
stanza: postgres
status: ok
cipher: aes-256-cbc
db (current)
wal archive min/max (12): 000000010000000000000004/00000002000000000000000E
incr backup: 20220804-103916F_20220804-104549I
timestamp start/stop: 2022-08-04 10:45:49 / 2022-08-04 10:46:00
wal start/stop: 000000010000000000000008 / 000000010000000000000008
lsn start/stop: 0/8000028 / 0/8000138
database size: 41.6MB, database backup size: 9MB
repo1: backup set size: 5.0MB, backup size: 1.1MB
backup reference list: 20220804-103916F, 20220804-103916F_20220804-104321D
database list: eg_app (16384), eg_audit_app (16630), postgres (14187)
#恢复eg_audit_app数据库
sudo -u postgres pgbackrest --stanza=postgres --delta --set=20220804-103916F_20220804-104549I --db-include=eg_audit_app --type=immediate --target-action=promote --log-level-console=info restore
#参数说明
--db-include #选择要恢复的数据库
4.基于时间点恢复
执行默认恢复，即一直播放到 WAL 流的末尾。在硬件故障的情况下，这通常是最佳选择，但对于数据损坏情况（无论是机器还是人员），时间点恢复通常更合适。
允许从上次备份到指定的 lsn、时间、事务 id 或恢复点播放 WAL。
sudo -u postgres pgbackrest --stanza=postgres --delta --set=20220804-103916F --type=time "--target=2022-08-04 10:46:14" --target-action=promote --log-level-console=info restore