<commit_before># 25.3. Continuous Archiving and Point-in-Time Recovery \(PITR\)

At all times, PostgreSQL maintains a _write ahead log_ \(WAL\) in the `pg_wal/` subdirectory of the cluster's data directory. The log records every change made to the database's data files. This log exists primarily for crash-safety purposes: if the system crashes, the database can be restored to consistency by “replaying” the log entries made since the last checkpoint. However, the existence of the log makes it possible to use a third strategy for backing up databases: we can combine a file-system-level backup with backup of the WAL files. If recovery is needed, we restore the file system backup and then replay from the backed-up WAL files to bring the system to a current state. This approach is more complex to administer than either of the previous approaches, but it has some significant benefits:

* We do not need a perfectly consistent file system backup as the starting point. Any internal inconsistency in the backup will be corrected by log replay \(this is not significantly different from what happens during crash recovery\). So we do not need a file system snapshot capability, just tar or a similar archiving tool.
* Since we can combine an indefinitely long sequence of WAL files for replay, continuous backup can be achieved simply by continuing to archive the WAL files. This is particularly valuable for large databases, where it might not be convenient to take a full backup frequently.
* It is not necessary to replay the WAL entries all the way to the end. We could stop the replay at any point and have a consistent snapshot of the database as it was at that time. Thus, this technique supports _point-in-time recovery_: it is possible to restore the database to its state at any time since your base backup was taken.
* If we continuously feed the series of WAL files to another machine that has been loaded with the same base backup file, we have a _warm standby_ system: at any point we can bring up the second machine and it will have a nearly-current copy of the database.

#### Note

pg\_dump and pg\_dumpall do not produce file-system-level backups and cannot be used as part of a continuous-archiving solution. Such dumps are _logical_ and do not contain enough information to be used by WAL replay.

As with the plain file-system-backup technique, this method can only support restoration of an entire database cluster, not a subset. Also, it requires a lot of archival storage: the base backup might be bulky, and a busy system will generate many megabytes of WAL traffic that have to be archived. Still, it is the preferred backup technique in many situations where high reliability is needed.

To recover successfully using continuous archiving \(also called “online backup” by many database vendors\), you need a continuous sequence of archived WAL files that extends back at least as far as the start time of your backup. So to get started, you should set up and test your procedure for archiving WAL files _before_ you take your first base backup. Accordingly, we first discuss the mechanics of archiving WAL files.

## 25.3.1. Setting Up WAL Archiving

In an abstract sense, a running PostgreSQL system produces an indefinitely long sequence of WAL records. The system physically divides this sequence into WAL _segment files_, which are normally 16MB apiece \(although the segment size can be altered during initdb\). The segment files are given numeric names that reflect their position in the abstract WAL sequence. When not using WAL archiving, the system normally creates just a few segment files and then “recycles” them by renaming no-longer-needed segment files to higher segment numbers. It's assumed that segment files whose contents precede the last checkpoint are no longer of interest and can be recycled.

When archiving WAL data, we need to capture the contents of each segment file once it is filled, and save that data somewhere before the segment file is recycled for reuse. Depending on the application and the available hardware, there could be many different ways of “saving the data somewhere”: we could copy the segment files to an NFS-mounted directory on another machine, write them onto a tape drive \(ensuring that you have a way of identifying the original name of each file\), or batch them together and burn them onto CDs, or something else entirely. To provide the database administrator with flexibility, PostgreSQL tries not to make any assumptions about how the archiving will be done. Instead, PostgreSQL lets the administrator specify a shell command to be executed to copy a completed segment file to wherever it needs to go. The command could be as simple as a `cp`, or it could invoke a complex shell script — it's all up to you.
<commit_msg>GitBook: [master] one page modified<commit_after># 25.3. Continuous Archiving and Point-in-Time Recovery \(PITR\)

PostgreSQL 在執行過程中不斷地在叢集資料目錄的 pg\_wal/ 子目錄中維護一個交易日誌（Write Ahead Log, WAL）。日誌記錄了對資料庫資料檔案所做的所有變更。該日誌主要用於意外災難還原的目的：如果系統意外損毁，則可以透過「重播」自上一個檢查點以來所建立的日誌項目來恢復資料庫的一致性。然而，日誌的存在使得可以使用第三種策略來備份數據庫：我們可以將檔案系統級備份與 WAL 檔案備份結合在一起。 如果需要復原，我們將還原檔案系統備份，然後從備份的 WAL 檔案中重播以使系統進入當下的狀態。 與前面所介紹的方法相比，這種方法的管理更為複雜，但具有一些明顯的好處：

* 我們不需要完美一致的檔案系統備份作為起點。備份中的任何內部不一致都將透過日誌重播進行糾正（這與損毁復原期間發生的變化沒有太大不同）。因此，我們不需要檔案系統的快照功能，而只需要 tar 或類似的封存工具。
* 由於我們可以結合無限長的 WAL 檔案序列進行重播，因此只需繼續封存 WAL 檔案就可以實現連續備份。這對於大型資料庫來說尤其具有價值，在大型資料庫中，經常性進行完整備份可能不太方便。
* 不必一直重複播放 WAL 項目。我們可以隨時停止重播，並獲得當時的資料庫快照。因此，此技術支持時間點還原：自從進行基本備份以來，可以隨時將資料庫還原到其狀態。
* 如果我們將一系列 WAL 檔案連續提供給另一台已載入了相同基本備份檔案的伺服器，則我們將擁有一個熱備份系統：在任何時候，我們都可以啟動第二台伺服器，而該伺服器將具有近乎最新的資料庫副本。

#### Note

pg\_dump 和 pg\_dumpall 並不會產生檔案系統層級的備份，因此不能用於連續歸檔解決方案的一部分。這樣的備份是邏輯上的，並且沒有包含足夠的資訊供 WAL 重播使用。

與普通資料系統備份技術一樣，此方法只能支援還原整個資料庫叢集，而不支援部份還原。此外，它還需要大量的檔案儲存空間：基本備份可能會很龐大，繁忙的系統將產生成許多數 MegaByte 等級的 WAL 流量，必須對其進行封存。儘管如此，在許多需要高可靠性的情況下，它還是備份技術中的首選。

要使用連續歸檔（許多資料庫供應商也將其稱為「線上備份」）成功恢復，您需要連續的 WAL 歸檔序列，該序列至少可以延伸到備份的開始時間。因此，在開始第一次基本備份之前，應先設定並測試用於封存 WAL 檔案的程序。因此，我們首先討論封存 WAL 檔案的機制。

## 25.3.1. Setting Up WAL Archiving

In an abstract sense, a running PostgreSQL system produces an indefinitely long sequence of WAL records. The system physically divides this sequence into WAL _segment files_, which are normally 16MB apiece \(although the segment size can be altered during initdb\). The segment files are given numeric names that reflect their position in the abstract WAL sequence. When not using WAL archiving, the system normally creates just a few segment files and then “recycles” them by renaming no-longer-needed segment files to higher segment numbers. It's assumed that segment files whose contents precede the last checkpoint are no longer of interest and can be recycled.

When archiving WAL data, we need to capture the contents of each segment file once it is filled, and save that data somewhere before the segment file is recycled for reuse. Depending on the application and the available hardware, there could be many different ways of “saving the data somewhere”: we could copy the segment files to an NFS-mounted directory on another machine, write them onto a tape drive \(ensuring that you have a way of identifying the original name of each file\), or batch them together and burn them onto CDs, or something else entirely. To provide the database administrator with flexibility, PostgreSQL tries not to make any assumptions about how the archiving will be done. Instead, PostgreSQL lets the administrator specify a shell command to be executed to copy a completed segment file to wherever it needs to go. The command could be as simple as a `cp`, or it could invoke a complex shell script — it's all up to you.

{'fraction_non_alphanumeric': 0.03853716083366103, 'fraction_numerical': 0.0023594180102241447, 'mean_word_length': 5.094249201277956, 'pattern_counts': {'":': 0, '<': 3, '<?xml version=': 0, '>': 3, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'dir': 'git-commits-cleaned', 'id': '1799634', 'n_tokens_mistral': 2604, 'n_tokens_neox': 2798, 'n_words': 1123}