为什么我的 Amazon RDS 数据库实例的时间点恢复需要很长时间？

解决方法

从备份中恢复 RDS 数据库实例后，查看 Amazon RDS 日志文件以跟踪时间点恢复的进度。

Amazon RDS 每 5 分钟将数据库实例的事务日志上传到 Amazon Simple Storage Service (Amazon S3)。在时间点恢复期间，最接近您在时间点恢复中设置的时间的快照将首先恢复。然后，Amazon RDS 会应用事务日志，直到您设置的时间点为止。操作时长取决于事务日志的数量。

例如，对于在 04:00 UTC 创建的数据库实例的自动备份，您必须在当天的 06:15 UTC 执行时间点恢复。在此示例中，实例会从 04:00 UTC 创建的备份中进行恢复。然后，Amazon RDS 会将 06:15 UTC 之前的事务日志应用于已恢复的实例，以完成时间点恢复过程。

要减少将数据库实例恢复到特定时间所需的时间，请遵循以下最佳实践：

• 对于已启用自动备份的源实例，定期创建手动快照以降低恢复点目标。
• 确保在时间点恢复时，源数据库中没有长时间运行的查询。长时间运行的事务可能会延长恢复时间，并导致数据库不可用的时间增加。
• 检查您的事务日志大小。大型事务会一次性写入事务文件，且 Amazon RDS 不会将事务拆分到不同的文件中。
  **注意：**大型事务日志文件会延长崩溃恢复时间。
• 对每个表运行全表扫描（例如 SELECT *），以加速恢复后对重要表的访问。这会提示 Amazon RDS 将所有表数据从 Amazon S3 下载到 Amazon Elastic Block Store (Amazon EBS) 卷中。
  **注意：**如果您在未下载所有表数据的情况下尝试访问 EBS 块，则可能会出现延迟加载。实例快照恢复后，Amazon S3 中快照的数据会复制到 EBS 卷。当您尝试访问尚未复制的块时，Amazon RDS 会从 Amazon S3 中拉取该块，进而导致 I/O 延迟。

当您将 Amazon RDS for PostgreSQL 数据库实例恢复到特定时间点时，您将在日志文件中收到以下信息。

输出示例：

2022-06-01 13:16:19 UTC::@:[8613]:LOG: starting point-in-time recovery to 2022-06-01 12:54:30+002022-06-01 13:16:19 UTC::@:[8613]:LOG: redo starts at 0/48A3220
waiting for 000000010000000000000001 archive /rdsdbdata/log/restore/pg-wal-archive.1.* to be downloaded
2022-06-01 13:17:22 UTC:127.0.0.1(46110):rdsadmin@rdsadmin:[10322]:FATAL: the database system is starting up
2022-06-01 13:17:25 UTC::@:[8613]:LOG: restored log file "000000010000000000000001" from archive
recovering 000000010000000000000002
2022-06-01 13:17:26 UTC::@:[8613]:LOG: restored log file "000000010000000000000002" from archive
recovering 000000010000000000000003
2022-06-01 13:17:28 UTC::@:[8613]:LOG: restored log file "000000010000000000000003" from archive
recovering 000000010000000000000004
2022-06-01 13:18:54 UTC::@:[8613]:LOG: restored log file "000000010000000000000022" from archive
recovering 000000010000000000000023
.
.
2022-06-01 13:33:16 UTC::@:[8613]:LOG: restored log file "00000001000000060000000B" from archive
2022-06-01 13:33:16 UTC::@:[8613]:LOG: recovery stopping before commit of transaction 9266438, time 2022-06-01 12:56:14.648042+00
2022-06-01 13:33:16 UTC::@:[8613]:LOG: redo done at 6/2C0003C0
2022-06-01 13:33:16 UTC::@:[8613]:LOG: last completed transaction was at log time 2022-06-01 12:51:14.646151+00
recovering 00000002.history
2022-06-01 13:33:16 UTC::@:[8613]:LOG: selected new timeline ID: 2
2022-06-01 13:33:16 UTC::@:[8613]:LOG: archive recovery complete
recovering 00000001.history
2022-06-01 13:33:16 UTC::@:[8620]:LOG: checkpoint starting: end-of-recovery immediate wait
2022-06-01 13:33:16 UTC::@:[8620]:LOG: checkpoint complete: wrote 2 buffers (0.0%); 0 WAL file(s) added, 0 removed, 8 recycled; write=0.002 s, sync=0.003 s, total=0.031 s; sync files=2, longest=0.003 s, average=0.002 s; distance=655360 kB, estimate=1611806
kB
2022-06-01 13:33:16 UTC::@:[8607]:LOG: database system is ready to accept connections
2022-06-01 13:37:18 UTC::@:[8607]:LOG: received fast shutdown request
2022-06-01 13:37:18 UTC::@:[8607]:LOG: aborting any active transactions
2022-06-01 13:37:18 UTC::@:[8607]:LOG: background worker "logical replication launcher" (PID 7394) exited with exit code 1
2022-06-01 13:37:18 UTC::@:[8620]:LOG: shutting down
2022-06-01 13:37:18 UTC::@:[8620]:LOG: checkpoint starting: shutdown immediate
2022-06-01 13:37:18 UTC::@:[8620]:LOG: checkpoint complete: wrote 9 buffers (0.0%); 0 WAL file(s) added, 0 removed, 1 recycled; write=0.003 s, sync=0.003 s, total=0.024 s; sync files=7, longest=0.003 s, average=0.001 s; distance=65535 kB, estimate=1457179
        kB
2022-06-01 13:37:20 UTC::@:[8607]:LOG: database system is shut down
2022-06-01 13:37:24 UTC::@:[10870]:LOG: starting PostgreSQL 13.4 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 7.3.1 20180712 (Red Hat 7.3.1-12), 64-bit
2022-06-01 13:37:24 UTC::@:[10870]:LOG: listening on IPv4 address "0.0.0.0", port 5432
2022-06-01 13:37:24 UTC::@:[10870]:LOG: listening on IPv6 address "::", port 5432
2022-06-01 13:37:24 UTC::@:[10870]:LOG: listening on Unix socket "/tmp/.s.PGSQL.5432"
2022-06-01 13:37:24 UTC::@:[10875]:LOG: database system was shut down at 2022-06-01 13:37:18 UTC
2022-06-01 13:37:24 UTC::@:[10870]:LOG: database system is ready to accept connections

相关信息

Amazon EBS 快照

为什么我的 Amazon Aurora 数据库集群克隆、快照恢复或时间点恢复需要这么长时间？