PostgreSql 备份恢复

这篇具有很好参考价值的文章主要介绍了PostgreSql 备份恢复。希望对大家有所帮助。如果存在错误或未考虑完全的地方,请大家不吝赐教,您也可以点击"举报违法"按钮提交疑问。

一、概述

  数据库备份一般可分为物理备份和逻辑备份,其中物理备份又可分为物理冷备和物理热备,下面就各种备份方式进行详细说明(一般情况下,生产环境采取的定时物理热备+逻辑备份的方式,均是以下述方式为基础进一步研发编写适合自己业务环境的备份工具或脚本):

  • 物理冷备:在停库的状态下,直接复制 PostgreSQL 的数据文件。备份出来的文件仅可在相同的平台环境中恢复(操作系统+数据库版本相同),仅可全库备份全库恢复。
  • 物理热备:在不停库状态下,拷贝数据库的 data 目录及 wal 归档,基于数据库自身的时间点备份恢复技术(Point-In-Time Recovery),通过不停地重放 wal 日志将数据推到备份结束后的任意一个时间点。备份出来的文件仅可在相同的平台环境中恢复(操作系统+数据库版本相同),仅可全库备份全库恢复。
  • 逻辑备份:利用 PostgreSQL 中自带的 pg_dump、pg_dumpall 进行 sql 转储,或使用客户端管理工具进行备份(实际也是调用的 pg_dump、pg_dumpall 命令),备份出来的文件可跨平台恢复,且可针对具体的对象进行备份恢复。

数据量越大,备份速度越慢,下列表中所列备份速度为相同环境下,三种备份方式的相对速度:

物理冷备 物理热备 逻辑备份
停库 不停库 不停库
全库备份恢复 全库备份恢复 具体数据库对象备份恢复
相同平台环境恢复 相同平台环境恢复 可跨平台数据库版本恢复
备份速度快 备份速度快 二进制转储备份速度较快,sql 文本转储备份速度一般

二、物理冷备

2.1 备份

--停库
pg_ctl stop

--打包数据目录
cd /data/pg13/
tar -cf backup_pg13.tar data

PostgreSql 备份恢复,PostgreSql 学习笔记,postgresql,oracle,数据库

2.2 恢复

备份的机器本地恢复或传到其他相同环境的机器进行恢复,需确保操作系统和数据库版本相同。

--确保恢复环境的数据库没有运行,若运行需关闭
ps -ef|grep postg

--将原 data 目录改名
cd /data/pg13
mv data data_old

--将打的备份包解压至原 data 所在目录下
tar -xvf backup_pg13.tar

--启动数据库
pg_ctl start

PostgreSql 备份恢复,PostgreSql 学习笔记,postgresql,oracle,数据库
PostgreSql 备份恢复,PostgreSql 学习笔记,postgresql,oracle,数据库

三、物理热备

  PostgreSQL 在数据目录的 pg_wal 子目录(10版本之前是 pg_xlog 子目录)中始终维护一个WAL日志文件。该日志文件记录了数据库数据文件的每次改变。最初设计该日志文件的主要目的是为了数据库异常崩溃后,能够通过重放最后一次 Checkpoint 点之后的日志文件,把数据库推到最终的一致状态,避免数据丢失或不一致。此日志文件的机制也提供了一种热备份方案:基础备份加上重放 wal 日志,将数据推到备份结束后的任意一个时间点。

3.1 备份

1)建立 wal 连续归档

  • 准备好归档目录,并确保空间充足。
  • wal_level 需要 replica 或以上级别。
  • 归档模式 archive_mode 开启。
  • archive_command 指定一个 shell 命令,且确保命令中的路径存在。
--在大的磁盘空间下,创建归档目录
mkdir /data/pg13/archive -p

--将下列参数加入 postgresql.conf 文件中,并重启数据库。
wal_level = 'replica'
archive_mode = 'on'
archive_command = 'test ! -f /data/pg13/archive/%f && cp %p /data/pg13/archive/%f'  # Unix
archive_command = 'copy "%p" "C:\\data\\archive\\%f"'  # Windows

PostgreSql 备份恢复,PostgreSql 学习笔记,postgresql,oracle,数据库

2)制作基础备份

方式一:pg_basebackup
pg_basebackup -D /data/pg13/dbbak

PostgreSql 备份恢复,PostgreSql 学习笔记,postgresql,oracle,数据库

方式二:使用低级 API 制作非排他基础备份

允许其他并发备份运行,既包括那些使用同样的 API 开始的备份,也包括那些用 pg_basebackup 开始的备份。

--发出开始备份标识
select pg_start_backup('label', false, false);

--另外窗口备份数据目录
cp /data/pg13/data /data/pg13/databak -rp

--发出备份开始的同一窗口发出终止备份标识
select * FROM pg_stop_backup(false);
select pg_switch_wal();

PostgreSql 备份恢复,PostgreSql 学习笔记,postgresql,oracle,数据库

在 PostgreSQL 9.6 之前,只能通过 API 排他备份方式(排他式备份方法已过时,应避免使用)。

--发出开始备份标识
select pg_start_backup('label',true);

--另外窗口备份数据目录
cp /data/pg13/data  /data/pg13/databak -rp

--发出备份开始的同一窗口发出终止备份标识
select * FROM pg_stop_backup();
select pg_switch_wal();

3.2 恢复

1)停止原数据库

pg_ctl stop

2)基础备份拷贝至原数据目录

mv /data/pg13/data /data/pg13/data_old
cp /data/pg13/dbbak /data/pg13/data -rp
  • 如果没有足够的空间,至少要保存集簇的 pg_wal 子目录的内容,因为它可能包含在系统垮掉之前还未被归档的日志。
  • 若在其他机器上恢复确保数据目录所有权及权限(所有权 postgres,权限 0700)。
  • 若使用了自定义表空间,确保 pg_tblspc 子目录中的软连接指向正确。

PostgreSql 备份恢复,PostgreSql 学习笔记,postgresql,oracle,数据库

3)配置恢复参数

  恢复参数在 PostgreSQL12 版本之前是配置在 recovery.conf 文件中的,而自 PostgreSQL12 版本开始合并到了 postgresql.conf 文件中。默认情况下,恢复将会一直恢复到 WAL 日志的末尾,下面的参数可以被用来指定一个更早的停止点。在 recovery_target、recovery_target_lsn、recovery_target_name、recovery_target_time 和recovery_target_xid 中, 最多只能使用一个。

--postgresql.conf 或 postgresql.auto.conf 中配置均可,当都配置时 postgresql.auto.conf 会覆盖 postgresql.conf 文件中的配置
#archive_mode = 'on'      #恢复期间先关闭归档模式,确认恢复的数据没问题后,再开启
restore_command = 'cp /data/pg13/archive/%f %p'
recovery_target_time = '2023-08-11 15:58:00'    #不配置该参数时,默认恢复到最新时间点

restore_command = 'copy "C:\\data\\archive\\%f" "%p"'  # Windows

PostgreSql 备份恢复,PostgreSql 学习笔记,postgresql,oracle,数据库

其他可选配置

--恢复到指定时间
recovery_target_time = '2023-08-10 14:00:00'
--恢复到一致状态后尽快结束
recovery_target = 'immediate'
--恢复到 pg_create_restore_point() 所创建还原点
recovery_target_name = 'string'
--恢复到指定事务ID
recovery_target_xid = 'string' 
--恢复将继续进行的预写日志位置的LSN
recovery_target_lsn = 'pg_lsn'
--指定是否在指定的恢复目标之后停止(on)或在恢复目标之前停止(off),适用于 recovery_target_lsn、recovery_target_time 或者 recovery_target_xid 被指定的情况。默认 on
recovery_target_inclusive = 'boolean' 
--恢复到指定时间线,可以是数字时间线 ID 或特殊值,默认 latest
recovery_target_timeline = 'string'
--达到恢复目标时服务器应该立刻采取的动作
pause:恢复将会被暂停(默认)。
promote:恢复处理将会结束并且服务器将开始接受连接。
shutdown:将在达到恢复目标之后停止服务器。

recovery_target_action = 'enum' 

注意:

  • 由于在 recovery_target_action 被设置为 shutdown 时,recovery.signal 将不会被移除, 任何后续的启动都将会以立刻关闭为终结,除非该配置被改变或者 recovery.signal 文件被手工移除。
  • 如果没有设置恢复目标,如果没有启用 hot_standby,pause设置的动作将和shutdown一样。 如果在升级期间达到恢复目标,pause 的设置将与 promote的行为相同。
  • 在任何情况下,如果已配置了恢复目标,但归档恢复在达到目标之前结束,则服务器将关闭,并出现致命错误。

4)创建恢复标识文件

touch $PGDATA/recovery.signal

5)启动数据库

数据库在启动过程中自动应用配置文件中恢复设置,将数据库推进至指定位置

pg_ctl start

PostgreSql 备份恢复,PostgreSql 学习笔记,postgresql,oracle,数据库

6)核对数据确认是否需要继续往下推进

当指定时间点恢复时,启动数据库后默认为只读状态,此时可查询数据进行检查,若不是想要的数据,可继续往后推进(只能向上次恢复的时间点后推进,不能向之前倒推)

--时间点继续推进
pg_ctl stop

将 postgresql.auto.conf 中之间继续向后改

recovery_target_time = '2023-08-11 15:59:00'

pg_ctl start

数据库为只读状态,recovery.signal 恢复标识文件存在时,就可继续推进。
PostgreSql 备份恢复,PostgreSql 学习笔记,postgresql,oracle,数据库

确认数据没问题后,可使用如下命令,结束恢复,数据库恢复读写模式:

select pg_wal_replay_resume();

此时 recovery.signal 恢复标识文件也会自动删除
PostgreSql 备份恢复,PostgreSql 学习笔记,postgresql,oracle,数据库

四、逻辑备份

  pg_dumpall 工具可以将 PostgreSQL 中的所有数据库转储到一个文本文件,它会对集簇中的每个数据库调用 pg_dump 来完成该工作,由于 pg_dumpall 仅能转储文本文件(sql 脚本格式),当数据量大时,改方式效率较低,故一般仅用其导出全局对象(数据库、角色、表空间)的功能,备份数据库则使用 pg_dump 方式:

--导出数据库、角色、表空间定义
pg_dumpall -h localhost -U postgres --port=5432 -f backup.sql --globals-only  --clean

pg_dump 用法:https://xiaosonggong.blog.csdn.net/article/details/122743398文章来源地址https://www.toymoban.com/news/detail-653930.html

到了这里,关于PostgreSql 备份恢复的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!

本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如若转载,请注明出处: 如若内容造成侵权/违法违规/事实不符,请点击违法举报进行投诉反馈,一经查实,立即删除!

领支付宝红包 赞助服务器费用

相关文章

  • 三、python Django ORM postgresql[数据定时备份、数据恢复]

    解释:备份指定数据库,能有效在发生错误时,预防错误,进行恢复 免密 cron 日志查看: tail -f -n 200 /var/log/syslog systemd timer 解释:因为博主在自己电脑上怎么用cron都不行,故使用了这个,其效果同cron类型,但效率甚至比cron高出一点点 vim /etc/systemd/system/mytask.service vim /etc/

    2024年02月13日
    浏览(38)
  • PG-DBA培训12:PostgreSQL物理备份与恢复实战

    一、风哥PG-DBA培训12:PostgreSQL物理备份与恢复实战 课程目标: 本课程由风哥发布的基于PostgreSQL数据库的系列课程,本课程属于PostgreSQL备份恢复与迁移升级阶段之PostgreSQL物理备份与恢复实战,学完本课程可以掌握: 1)风哥PostgreSQL物理备份工具与归档模式,PostgreSQL物理备份

    2024年02月12日
    浏览(42)
  • PostgreSQL数据库定时备份脚本

          大多数数据库管理系统都提供了自带的备份工具,可以使用这些工具来进行备份操作。     例如: MySQL:使用 mysqldump 命令进行备份。 PostgreSQL:使用 pg_dump 命令进行备份。       以下是一个用于定时备份 PostgreSQL 数据库的示例脚本。这个脚本将使用 pg_dump 工具来创建

    2024年02月11日
    浏览(46)
  • PostgreSQL从小白到高手教程 - 第41讲:postgres表空间备份与恢复

    PostgreSQL从小白到专家,是从入门逐渐能力提升的一个系列教程,内容包括对PG基础的认知、包括安装使用、包括角色权限、包括维护管理、、等内容,希望对热爱PG、学习PG的同学们有帮助,欢迎持续关注CUUG PG技术大讲堂。 第41讲:表空间备份与恢复 PostgreSQL第41讲:1月13日

    2024年01月22日
    浏览(40)
  • postgresql数据库定时备份到远程数据库

    1.老规矩,服务器目录结构: conf目录无内容 profile: 其中: 最后一行 export PGPASSWORD=‘root’ 是需要备份的数据库的密码,因为直接用 pg_dump 命令备份需要输入密码交互,而我们需要达到自动备份,所以借助这种方式不需要输入密码 docker-compose.yml: 启动容器: 然后再data目录下面

    2024年02月09日
    浏览(45)
  • PostgreSQL学习笔记(一):数据库、模式、表空间、用户、用户角色

        本文讨论所说的都是 PostgreSQL 数据库的相关定义内容,其中名词以及翻译如下: 数据库: database 模式: schema 表: table 表空间: tablespace 用户: use 用户角色: role PostgreSQL 通用的语法:     在 PostgreSQL 数据库中,数据库(database)可以认为是物理层面数据集合。当使

    2024年02月11日
    浏览(39)
  • openGauss学习笔记-149 openGauss 数据库运维-备份与恢复-逻辑备份与恢复之gs_restore

    149.1 背景信息 gs_restore是openGauss提供的针对gs_dump导出数据的导入工具。通过此工具可由gs_dump生成的导出文件进行导入。 gs_restore工具由操作系统用户omm执行。 主要功能包含: 导入到数据库 如果连接参数中指定了数据库,则数据将被导入到指定的数据库中。其中,并行导入必

    2024年02月04日
    浏览(47)
  • Oracle到PostgreSQL数据库的语法迁移手册(建议收藏)

    异构数据库的迁移(譬如从Oracle迁移到PostgreSQL)工作主要包括三个方面, 数据库对象的迁移,包括库、模式、表、索引、视图、触发器、存储过程等等; 数据内容的迁移,主要指的是数据表中的数据; 数据应用的迁移,主要指的是应用中SQL语句的迁移。 目前对于数据库对

    2024年04月08日
    浏览(74)
  • 阿里云PolarDB自研数据库详细介绍_兼容MySQL、PostgreSQL和Oracle语法

    阿里云PolarDB数据库是阿里巴巴自研的关系型分布式云原生数据库,PolarDB兼容三种数据库引擎:MySQL、PostgreSQL、Oracle(语法兼容),目前提供云原生数据库PolarDB MySQL版、云原生数据库PolarDB PostgreSQL版和云原生数据库PolarDB分布式版,阿里云百科aliyunbaike.com分享阿里云PolarDB数据

    2024年02月07日
    浏览(56)
  • Sqlserver_Oracle_Mysql_Postgresql不同关系型数据库之主从延迟的理解和实验

    关系型数据库主从节点的延迟是否和隔离级别有关联,个人认为两者没有直接关系,主从延迟在关系型数据库中一般和这两个时间有关:事务日志从主节点传输到从节点的时间+事务日志在从节点的应用时间 事务日志从主节点传输到从节点的时间,相关因素有以下2点: 1、事

    2024年02月14日
    浏览(51)

觉得文章有用就打赏一下文章作者

支付宝扫一扫打赏

博客赞助

微信扫一扫打赏

请作者喝杯咖啡吧~博客赞助

支付宝扫一扫领取红包,优惠每天领

二维码1

领取红包

二维码2

领红包