资讯专栏INFORMATION COLUMN

上云采购季！| 2核2G4M爆款云服务器低至59元/年，更有多台、长期优惠，快来选购！

立即前往
首页/文章专栏/PostgreSQL物理备份与恢复

PostgreSQL物理备份与恢复

IT那活儿 / 3744人阅读
PostgreSQL物理备份与恢复

点击上方“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.conf
archive_mode = on
如果原来是off,调整为on, 需要重启数据库生效。
cat postgresql.conf |grep archive_mode
3）配置归档命令
首先要创建用于存放wal归档文件目录, 数据库启动用户需要写权限。
$ mkdir archive
配置postgresql.conf中的归档命令。
vi postgresql.conf
archive_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 = 16
max_wal_senders = 8
5）建议设置wal_keep_segments,确保大型的数据库在比较长的备份时间周期内wal日志不被覆盖。注意配置之后，pg_wal至少会占用wal_keep_segments * wal文件大小 的空间。需要预留好空间。
wal_keep_segments = 1024
3. 配置的参数如下
cd /pgsql/data
mv postgresql.conf postgresql.conf.bak20220227

vi postgresql.conf

listen_addresses = *
port = 5432
superuser_reserved_connections = 20
wal_level = archive
archive_mode = on
archive_command = cp %p /pgsql/data/archive/%f
unix_socket_directories = /var/lib/postgres/
unix_socket_permissions = 0700
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 10
vacuum_cost_delay = 10
bgwriter_delay = 10ms
synchronous_commit = off
wal_writer_delay = 10ms
log_destination = csvlog
logging_collector = on
log_directory = pg_log
log_filename =postgresql-%Y-%m-%d_%H%M%S.log
log_file_mode = 0600
log_truncate_on_rotation = on
log_rotation_age = 1d
log_rotation_size = 10MB
log_timezone = PRC
datestyle = iso, mdy
timezone = PRC
default_text_search_config = pg_catalog.english
max_worker_processes = 16
max_wal_senders = 8
wal_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权限的用户发起。
$ psql
psql.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 recovery
postgres=#
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那活儿（上海新炬王翦团队）

GPU云服务器 云服务器 备份与恢复 系统备份与恢复方案 rds备份与恢复 ubuntu备份与恢复

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/129592.html

相关文章

  • 新书推荐 |《PostgreSQL实战》出版(提供样章下载)

    摘要：作者谭峰张文升出版日期年月页数页定价元本书特色中国开源软件推进联盟分会特聘专家撰写，国内多位开源数据库专家鼎力推荐。张文升中国开源软件推进联盟分会核心成员之一。 很高兴《PostgreSQL实战》一书终于出版，本书大体上系统总结了笔者 PostgreSQL DBA 职业生涯的经验总结，本书的另一位作者张文升拥有丰富的PostgreSQL运维经验，目前就职于探探科技任首席PostgreS...

    Martin91 评论0 收藏0

  • Postgresql 备份与恢复

    摘要：指定要用于查找的口令文件的名称。前四个字段可以是确定的字面值，也可以使用通配符匹配所有。利用环境变量引用的文件权限也要满足这个要求，否则同样会被忽略。在上，该文件被假定存储在一个安全的目录中，因此不会进行特别的权限检查。 pg_dump pg_dump 把一个数据库转储为纯文本文件或者是其它格式. 用法: pg_dump [选项]... [数据库名字] 一般选项: -f, --fi...

    阿罗 评论0 收藏0

发表评论

登陆后可评论

0条评论

IT那活儿

男|高级讲师
我要私信

TA的文章

阅读更多
最新活动
阅读需要支付1元查看
<