点击上方“IT那活儿”,关注后了解更多内容,不管IT什么活儿,干就完了!!!
本文主要介绍PostgreSQL的基础备份和基于时间点PITR(Point-in-Time Recovery)的恢复方法。如果数据库在运行过程中发生故障,需要恢复到故障发生前的某个时间点,或者在使用过程中由于误操作导致删除了重要数据,需要将数据还原到误操作前的某个时间点的状态,则可以借助基础备份来实施恢复。postgres=# select name,setting,unit from pg_settings where category='Preset Options';postgres=# select version();
- minimal --不能通过基础备份和wal日志恢复数据库。
- replica = 9.6版本以前的archive和hot_standby --该级别支持wal归档和复制。
- logical --在replica级别的基础上添加了支持逻辑解码所需的信息。
$ pg_controldata -D pgsql |grep wal_level或者cat postgresql.conf |grep wal_level
vi postgresql.confarchive_mode = on
如果原来是off,调整为on, 需要重启数据库生效。cat postgresql.conf |grep archive_mode
首先要创建用于存放wal归档文件目录, 数据库启动用户需要写权限。vi postgresql.confarchive_command = 'cp %p pgsql/data/archive/%f'
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文件大小 的空间。需要预留好空间。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创建的是整个数据库集群文件的一份二进制副本,不能备份单个数据库或数据库对象。-D directory 保存备份数据的本地目录。 --format=format plain|tar-r rate 从该服务器传输数据的最大传输速率。-R 在输出目录中写入一个最小的recovery.conf恢复配置文件。-S slotanme 该选项需要和-X stream一起使用。它导致WAL流使用指定复制槽。-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函数来创建的一个还原点,需要超级用户调用这个函数。
既然我们已经知道了数据库可以恢复到指定的位置,接下来我们模拟的就是当数据被误删除后,借助更早时候的基础备份和WAL归档日志来将数据恢复到误操作前的某个时间点。这个文件可能还没有归档,我们可以主动触发归档切换wal日志:删除表ttt中的部分数据,PostgreSQL默认是自动提交,因此DML语句不需要commit。将基础备份和归档wal日志拷贝到***.***.***.129这台服务器。基础备份选择将要恢复的时间点之前的一个备份,归档则选择在此之后的所有归档文件。配置recovery.conf,指定将数据恢复到xid:562。$ 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即可。