暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

PostgreSQL物理备份与恢复

IT那活儿 2022-04-01
1577

点击上方“IT那活儿”,关注后了解更多内容,不管IT什么活儿,干就完了!!!

基础备份和PITR

本文主要介绍PostgreSQL的基础备份和基于时间点PITR(Point-in-Time Recovery)的恢复方法。
如果数据库在运行过程中发生故障,需要恢复到故障发生前的某个时间点,或者在使用过程中由于误操作导致删除了重要数据,需要将数据还原到误操作前的某个时间点的状态,则可以借助基础备份来实施恢复。

准备工作

1.检查配置信息,版本信息
postgres=# select name,setting,unit from pg_settings where category='Preset Options';postgres=# select version();
2. 在开始备份前,需要配置一些参数
1)调整wal日志级别
  • minimal --不能通过基础备份和wal日志恢复数据库。
  • replica = 9.6版本以前的archive和hot_standby  --该级别支持wal归档和复制。
  • logical --在replica级别的基础上添加了支持逻辑解码所需的信息。
查看当前数据库的wal level。
$ pg_controldata -D pgsql |grep wal_level或者cat postgresql.conf |grep wal_level
2)开启归档模式
vi postgresql.confarchive_mode = on
如果原来是off,调整为on, 需要重启数据库生效。
cat postgresql.conf |grep archive_mode
3)配置归档命令
首先要创建用于存放wal归档文件目录, 数据库启动用户需要写权限。
$ mkdir archive
配置postgresql.conf中的归档命令。
vi postgresql.confarchive_command = 'cp %p pgsql/data/archive/%f'
修改完后reload配置文件即可。
pg_ctl -D /pgsql/data/ reload
4)建议设置work process,sender进程数,这样才可以通过pg_basebackup使用流复制协议进行备份。必须设置大于0,每个流复制连接需要一个wal sender进程。
max_worker_processes = 16max_wal_senders = 8
5)建议设置wal_keep_segments,确保大型的数据库在比较长的备份时间周期内wal日志不被覆盖。注意配置之后,pg_wal至少会占用wal_keep_segments * wal文件大小 的空间。需要预留好空间。
wal_keep_segments = 1024
3. 配置的参数如下
cd /pgsql/datamv postgresql.conf postgresql.conf.bak20220227vi postgresql.conflisten_addresses = '*'port = 5432superuser_reserved_connections = 20wal_level = archivearchive_mode = onarchive_command = 'cp %p pgsql/data/archive/%f'unix_socket_directories = '/var/lib/postgres/'unix_socket_permissions = 0700tcp_keepalives_idle = 60tcp_keepalives_interval = 10tcp_keepalives_count = 10vacuum_cost_delay = 10bgwriter_delay = 10mssynchronous_commit = offwal_writer_delay = 10mslog_destination = 'csvlog'logging_collector = onlog_directory = 'pg_log'log_filename ='postgresql-%Y-%m-%d_%H%M%S.log'log_file_mode = 0600log_truncate_on_rotation = onlog_rotation_age = 1dlog_rotation_size = 10MBlog_timezone = 'PRC'datestyle = 'iso, mdy'timezone = 'PRC'default_text_search_config = 'pg_catalog.english'max_worker_processes = 16max_wal_senders = 8wal_keep_segments = 1024
检查归档是否正常完成。
PostgreSQL提供pg_switch_wal()函数可以手工切换WAL日志,执行pg_switch_wal()后,WAL会切换到新的日志,这时会将WAL日志归档到配置的归档路径。
查看生成的归档文件。

基础备份


完成上述准备工作后, 就可以开始进行基础备份了,我们使用pg_basebackup工具来完成备份。
pg_basebackup备份操作不会影响连接到该数据库的其他客户端,并且可以被用于基于时间点的恢复。pg_basebackup创建的是整个数据库集群文件的一份二进制副本,不能备份单个数据库或数据库对象。
pg_basebackup的主要参数选项如下:
    pg_basebackup [option…]
-D directory 保存备份数据的本地目录。
    --pgdata=directory
-F format 备份文件格式p和t。
    --format=format plain|tar
-r rate 从该服务器传输数据的最大传输速率。
    --max-rate=rate
-R 在输出目录中写入一个最小的recovery.conf恢复配置文件。
    --write-recovery-conf
-S slotanme 该选项需要和-X stream一起使用。它导致WAL流使用指定复制槽。
    --slot=slotname
-X stream 如果为none,则备份不会包含日志,如果fetch,在备份末尾收集日志文件,如果为stream,在备份的同时备份日志。
    --wal-method = none | stream | fetch
使用pg_basebackup备份数据库必须由超级用户或者具有REPLICATION权限的用户发起。
$ psqlpsql.bin (10.12)Type "help" for help.
pg_basebackup使用replication协议连接到数据库实例上,所以pg_hba.conf必须允许replication连接。
$ cp pg_hba.conf pg_hba.conf.bak20200920$ vi pg_hba.conf
重新加载配置。
注意:能通过pg_ctl reload方式生效的参数,不需要重启数据库;需要通过重启来生效的,则无法通过pg_ctl reload方式生效。
记录当前xid,在数据库中以超级用户执行如下命令:
创建一个备份标签:
我们可以使用pg_start_backup()函数在$PGDATA目录中创建一个关于备份信息的备份标签文件,也被称为backup_label,其中包括了开始时间和标签字符串。该函数也会在$PGDATA目录中创建一个 名为tablespace_map的表空间映射文件,如果在pg_tblspc/中有一个或者多个表空间符号链接存在,该文件会包含它们的信息。如果你需要从备份中恢复,这两个文件对于备份的 完整性都至关重要。
使用一个具有运行 pg_start_backup 权利的用户(超级用户,或者被授予在该 函数上 EXECUTE 的用户)连接到服务器(不在乎是哪个数据库)并且发出命令:
默认情况下,pg_start_backup会花费很长时间来完成。
这是因为它会执行一个检查点,而检查点所需要的I/O在相当一段时间内将会被传播,默认情况下这段时间是内部检查点间隔的一半(参见配置参数checkpoint_completion_target)。这通常是你所希望的,因为它能将对查询处理的影响最小化。
如果你要尽快开始备份,可使用:
SELECT pg_start_backup('label', true);
这会使检查点尽可能快地被完成。
注意,如果服务器在备份期间崩溃,必须从$PGDATA 目录手动删除backup_label文件,才能重新启动。
查看备份标签文件的内容:
创建数据库服务器的一个基础备份并将它存储在本地目录/pgsql/data/backup。
pg_basebackup -h ***.***.223.128 -D /pgsql/data/backup
备份目录文件信息:
要使用备份来恢复数据,你需要保留所有在备份期间以及之后生成的WAL段文件。
为了更方便地进行这些工作,基础备份过程中会创建一个备份历史文件,它将被立刻存储到WAL归档目录。该文件以备份中你需要的第一个WAL段文件命名。
例如:
如果开始的WAL文件是000000010000000000000059,则备份历史文件将被命名为000000010000000000000059. 00000060.backup.(文件名的第二部分表明WAL文件中的一个准确位置,一般可以被忽略)。一旦你已经安全地归档了备份和在备份过程中被使用的WAL段文件(如备份历史文件中所指定的) ,所有名字在数字上低于备份历史文件中记录值的已归档WAL段对于恢复文件系统备份就不再需要了,可以将它们删除。
备份历史文件是一个很小的文本文件。它包含你指定给pg_basebackup的标签字符串,以及备份的起止时间以及起止WAL段。
如果你使用该标签来标识相关备份文件,则已归档的历史文件可以说明需要哪个备份文件进行恢复。
备份完成后,在pg_wal目录下生成了一个000000010000000000000059.00000060.backup文件。


模拟数据库恢复


接下来,我们将演示如何实现以及如何设计一个恢复方案。
要恢复到任意时间点,我们就必须告诉恢复进程一个点,这个点可以是时间,字符串,或者xid。
  • 时间点,恢复到这个时间点之后的WAL中的第一个事务结束位置。
  • 字符串,这个是需要通过pg_create_restore_point函数来创建的一个还原点,需要超级用户调用这个函数。
  • xid,就是恢复到指定事务的结束位置。
既然我们已经知道了数据库可以恢复到指定的位置,接下来我们模拟的就是当数据被误删除后,借助更早时候的基础备份和WAL归档日志来将数据恢复到误操作前的某个时间点。
1. 创建测试数据
创建测试表ttt并生成测试数据。
记录当前时间T1:
记录当前xid:
这个文件可能还没有归档,我们可以主动触发归档切换wal日志:
记录当前哈希值,用于恢复后的比对。
2. 模拟误删除操作
删除表ttt中的部分数据,PostgreSQL默认是自动提交,因此DML语句不需要commit。
记录第一次删除数据后的时间T2:
记录第一次删除数据后的xid:
这个文件可能还没有归档,我们可以主动触发归档:
继续删除表ttt数据:
记录第二次删除数据后的xid:
手动切换wal日志:
备份文件和归档文件如下:
3. 恢复到指定XID
将基础备份和归档wal日志拷贝到***.***.***.129这台服务器。
基础备份选择将要恢复的时间点之前的一个备份,归档则选择在此之后的所有归档文件。
配置recovery.conf,指定将数据恢复到xid:562。
$ vi recovery.conf
启动数据库。
$ pg_ctl -D /pgsql/data start
这时候数据库还是read only模式,recovery还没有结束,如果不执行select pg_wal_replay_resume();开启新事务将会报错:
postgres=# select txid_current();ERROR: cannot execute txid_current() during recoverypostgres=#postgres=# select pg_wal_replay_resume();pg_wal_replay_resume----------------------(1 row)
检查数据已经恢复到 delete from ttt where relacl is null 之后的状态。
postgres=# select count(*) from ttt;
recovery.conf已被自动重命名为recovery.done。
如果你还要执行其他后续的恢复操作,recovery.conf必须被重置。另外可以根据时间点来进行恢复,在recovery.conf文件里面配置recovery_target_time即可。

本文作者:汤 杰

本文来源:IT那活儿(上海新炬王翦团队)

文章转载自IT那活儿,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论