PostgreSQL备份恢复
一、PostgreSQL备份方案
一)PostgreSQL两种备份方案
- 方案一:逻辑备份——使用pg_dump
- 方案二:物理备份——使用pg_rman
二、PostgreSQL逻辑备份恢复
一)逻辑备份:pg_dump
1、pg_dump简介
pg_dump 支持备份表,备份用户,备份数据库
pg_dumpall 支持导出全库的数据
pg_dump 可以把数据备份成SQL文本的形式,也可以自定义为tar包等二进制
2、备份数据操作
常用备份命令(查看帮助信息)
pg_dump --help
备份实操
# 备份准备 mkdir -p /pgbak chown postgres.postgres /pgbak #本机备份 ## 方式一:使用重定向符号 pg_dump db_name > /pgbak/db_name.sql ## 方式二:使用pg参数 pg_dump db_name --file=/pgbak/db_name1.sql # 远程备份,备份文件存放到远程主机上 pg_dump --username=postgres --host=host_ip --port=5432 --format=tar --file=/data/backup/pgbak/db_name.tar # 压缩备份 若遇到数据库容量比较大时,可以选择压缩备份 pg_dump --dbname=db_name --format=custom --file=/pgbak/db_bame.dump
二)恢复
1、恢复方式
psql恢复:一般恢复SQL文本
pg_restore恢复:一般恢复压缩的二进制文件
2、恢复实现
# 基于sql文件恢复 pgsql --dbname=db_name --file=db_name.sql # 基于dump压缩文件恢复 pg_restore --dbname=db_name db_name.dump
三、PostgreSQL物理备份恢复
一)物理备份:pg_rman
1、pg_rman 简介
pg_rman是一款专门为postgresql设计的在线备份恢复的工具。其支持在线和基于时间点备份方式,还可以通过创建backup catalog来维护DB cluster备份信息。
pg_rman的特点
- 使用简单.一个命令即可完成备份和恢复.
- 支持在线全备,增量备份,归档备份.
- 支持备份压缩.通过gzip工具实现页内压缩.
- 自动备份维护.自动删除过期的WAL备份文件.
- 支持备份验证.
- 恢复期间无事务丢失.支持基于PITR的配置文件生成器
pg_rman 注意事项:
- pg_rman 基于 pg_start_backup
- pg_rman 基于 需要在本地安装,不能远程德份
- pg_rman 需要下载对应的版本
2、pg_rman安装
pg_rman下载
下载地址: https://github.com/ossc-db/pg_rman/ PostgreSQL Rpm相关依赖、插件包下载地址: https://yum.postgresql.org/10/redhat/rhel-6.7-x86_64/ 手册地址: http://ossc-db.github.io/pg_rman/index.html
pg_rman安装、配置
源码包安装方法: 1、下载源码包 wget https://github.com/ossc-db/pg_rman/releases/download/V1.3.7/pg_rman-1.3.7-pg10.tar.gz 2、解压到指定目录 tar -zxvf pg_rman-1.3.14-pg12.tar.gz -C opt/ 3、进入到rman目录进行编译安装 cd opt/pg_rman-1.3.14-pg12/ make && make install
设置环境变量:/etc/profile 或/etc/bashrc
# 设置环境变量: PATH=$PATH:$HOME/bin export PG_RMAN=/u01/postgresql/pg12 export PGHOME=/u01/postgresql/pg12 export PGDATA=/u01/postgresql/data export PGLIB=/u01/postgresql/pg12/lib export SRVLOG_PATH=/u01/postgresql/data/log export ARCLOG_PATH=/u01/postgresql/archive export BACKUP_PATH=/opt/pg_rman export PATH=$PGHOME/bin:$PATH:$PG_RMAN/bin export PATH
使设置的环境变量生效
source /etc/profile
3、pg_rman的用法
pg_rman的使用相对简单核心8个命令 pg_rman manage backup/recovery of PostgreSQL database. Usage: pg_rman OPTION init pg_rman OPTION backup pg_rman OPTION restore pg_rman OPTION show [DATE] pg_rman OPTION show detail [DATE] pg_rman OPTION validate [DATE] pg_rman OPTION delete DATE pg_rman OPTION purge init 初始化备份目录 backup 在线备份 restore 恢复 show 查看备份历史 validate 验证备份 delete 删除备份文件 purge 从备份目录中删除已删除备份
4、备份
初始化备份目录
先需要初始化一个backup catalog,目录将用于存放备份的文件,目录也会存放一些元数据,例如备份的配置文件,数据库的systemid,时间线文件历史 注意:在初始化的时候,要先确认环境变量是否设置正确。 # pg_rman init -B opt/pg_rman 总用量 8 drwx------ 4 root root 35 11月 23 13:30 backup -rw-r--r-- 1 root root 83 11月 23 13:30 pg_rman.ini -rw-r--r-- 1 root root 40 11月 23 13:30 system_identifier drwx------ 2 root root 6 11月 23 13:30 timeline_history
查看pg_rman的备份配置文件
$ cat pg_rman.ini ARCLOG_PATH=‘/home/postgres/arch’ --归档目录 SRVLOG_PATH=‘/home/postgres/data/log’ --数据库错误日志目录 COMPRESS_DATA = YES --压缩数据 KEEP_ARCLOG_FILES = 10 --保存归档文件个数 KEEP_ARCLOG_DAYS = 10 --保存归档的天数 KEEP_DATA_GENERATIONS = 3 --备份冗余度 KEEP_DATA_DAYS = 10 --保存备份集时间 KEEP_SRVLOG_FILES = 10 --保存日志文件个数 KEEP_SRVLOG_DAYS = 10 --保存日志文件天数
pg_rman的全量备份
如果没有调设置export BACKUP_PATH=/opt/pg_rman ,需要手动指明备份路径 --backup-path=/opt/pg_rman
postgres@s2ahumysqlpg01-> pg_rman backup --backup-mode=full --with-serverlog -h 127.0.0.1
INFO:cyidbfi INFO:cyichivdWALfi INFO:cyivfi INFO:bckuc INFO:Pcu'vid'vifyhficcycid. 一、pg_rman 校验备份集:
postgres@s2ahumysqlpg01-> pg_rman show
=====================================================================
StartTime EndTime Mode Size TLI Status
=====================================================================
2022-02-22 17:18:39 2022-02-22 17:19:12 FULL 942MB 5 DONE
二、未校验备份集 Status 显示为 DONE,开始校验
[postgres@mxl pg_rman]$ pg_rman validate
INFO: validate: "2019-12-03 11:53:39" backup, archive log files and server log files by CRC
INFO: backup "2019-12-03 11:53:39" is valid
[postgres@mxl pg_rman]$ pg_rman show
=====================================================================
StartTime EndTime Mode Size TLI Status
=====================================================================
2019-12-03 11:53:39 2018-12-03 11:54:00 FULL 461MB 1 OK
三、删除备份集
# pg_rman 删除只是逻辑删除,备份文件还存在;删除语法:pg_rman delete 备份的结束时间
pg_rman delete 2022-02-22 17:19:12
# pg_rman 删除备份文件;删除语法:pg_rman purge
pg_rman purge
增量备份
增量备份是基于文件系统的update time时间线,增量备份必须有个对应的全库备份 postgres@s2ahumysqlpg01-> pg_rman backup --backup-mode=incremental --progress --compress-data -h 127.0.0.1 INFO: copying database files INFO: copying archived WAL filesipped 3028 Processed 16 of 16 files, skipped 13 INFO: backup complete INFO: Please execute 'pg_rman validate' to verify the files are correctly copied. 备份集查看: $ pg_rman show postgres@s2ahumysqlpg01-> pg_rman show ===================================================================== StartTime EndTime Mode Size TLI Status ===================================================================== 2022-02-22 17:22:54 2022-02-22 17:22:58 INCR 33kB 5 DONE 2022-02-22 17:18:39 2022-02-22 17:19:12 FULL 942MB 5 OK
# 校验 postgres@s2ahumysqlpg01-> pg_rman validate INFO: validate: "2022-02-22 17:22:54" backup and archive log files by CRC INFO: backup "2022-02-22 17:22:54" is valid
# 查看备份集 postgres@s2ahumysqlpg01-> pg_rman show ===================================================================== StartTime EndTime Mode Size TLI Status ===================================================================== 2022-02-22 17:22:54 2022-02-22 17:22:58 INCR 33kB 5 OK 2022-02-22 17:18:39 2022-02-22 17:19:12 FULL 942MB 5 OK
pg_rman 归档备份
一、pg_rman 归档备份: 归档备份就是静态文件的拷贝。 $ pg_rman backup --backup-mode=archive --progress --compress-data $ pg_rman validate $ pg_rman show 二、删除备份集 删除备份集指定日期之前的,不需要用来指定时间点之前的备份删除掉 $ pg_rman delete " pg_rman delete "2022-02-22 17:22:58"" 三、清除备份集 物理删除已从catalog删除的备份集 上面从备份集中删除的备份,备份集文件夹并没有一起删除。 $ pg_rman purge
二)恢复
1、数据恢复步骤
- 停止数据库:pg_ctl stop
- 恢复数据:
- 全量恢复:pg_rman restore
- 增量恢复(基于时间点恢复):pg_rman restore --recovery-targe-time="2022-02-22 17:22:58"
- 启动数据库:pg_ctl start
2、数据恢复操作方法
pg_rman 恢复默认原地恢复(覆盖式),或者使用新的$PGDATA作为恢复目标。需注意如果在本机恢复pg_rman可能会覆盖原有的数据文件arch,pg_xlog目录中的文件, 可以先将原数据文件做下备份,默认pg_rman restore会在$PGDATA下产生recovery.conf根据实际需要修改相关恢复参数即可。 1、recovery-target-timeline TIMELINE
Specifies recovering into a particular timeline. If not specified, the current timeline from ($PGDATA/global/pg_control) is used. 如果不指定时间线,则使用$PGDATA/global/pg_control,如果没有$PGDATA/global/pg_control,则使用最新的全量备份集的时间线。 2、recovery-target-time TIMESTAMP
This parameter specifies the time stamp up to which recovery will proceed. If not specified, continue recovery to the latest time. 如果不指定,则恢复到最新时间 3、recovery-target-xid XID
This parameter specifies the transaction ID up to which recovery will proceed. If not specified, continue recovery to the latest xid. 如果不指定,则恢复到最新xid 4、recovery-target-inclusive Specifies whether we stop just after the specified recovery target (true), or just before the recovery target (false). Default is true. 是否在指定的恢复目标(true)之后停止,默认为true,如果指定false意识是在恢复目标之前停止 • The following parameter determines the behavior of restore. --hard-copy The archive WAL are copied to archive WAL storage area. If not specified, pg_rman makes symbolic link to archive WAL where are in the backup catalog directory. 是否使用硬链接复制archive log,如果不指定使用符号连接(软连接)的方式。 #示例: #pg_rman restore --recovery-target-time "2022-02-22 17:25:30"; 下面是没有指定的恢复 [postgres@mxl~]$ pg_rman restore INFO: backup "2022-02-22 17:25:30" is valid INFO: the recovery target timeline ID is not given INFO: use timeline ID of current database cluster as recovery target: 5 INFO: calculating timeline branches to be used to recovery target point INFO: searching latest full backup which can be used as restore start point INFO: found the full backup can be used as base in recovery: "2022-02-22 17:25:30" INFO: copying online WAL files and server log files INFO: clearing restore destination INFO: validate: "2022-02-22 17:25:30" backup, archive log files and server log files by SIZE INFO: backup "2022-02-22 17:25:30" is valid INFO: restoring database files from the full mode backup "2022-02-22 17:25:30" INFO: searching incremental backup to be restored INFO: searching backup which contained archived WAL files to be restored INFO: backup "2022-02-22 17:25:30" is valid INFO: restoring WAL files from backup "2022-02-22 17:25:30" INFO: restoring online WAL files and server log files INFO: create pg_rman_recovery.conf for recovery-related parameters. INFO: remove an 'include' directive added by pg_rman in postgresql.conf if exists INFO: append an 'include' directive in postgresql.conf for pg_rman_recovery.conf INFO: generating recovery.signal INFO: removing standby.signal if exists to restore as primary INFO: restore complete HINT: Recovery will start automatically when the PostgreSQL server is started. After the recovery is done, we recommend to remove recovery-related parameters configured by pg_rman. pg_ctl start