# Lesson 03: Backup / WAL Archive

> Native Markdown source page: Backup / WAL Archive

---

LLMS index: [llms.txt](/en/llms.txt)

---

<!-- Generated by scripts/generate_skeleton.py. -->

> [!NOTE]
> This page is extracted from the official PDF into native Markdown. Slide text, code and speaker notes are kept in reading order; the original PDF and HTML remain the layout references.
> **HTML source:** No matching course-page link was captured in the source inventory.
> **Archive:** [DBA3-handouts-16-en-20260917.zip](https://edu.postgrespro.com/16/DBA3-handouts-16-en-20260917.zip)
> **PDF pages:** 31

## Page 1: Backup / WAL Archive {#source-page-001}

### Copyright

© Postgres Professional, 2017–2025 Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Alexey Beresnev Photo: Oleg Bartunov (Phu Monastery and Bhrikuti Peak, Nepal)
### Use of Course Materials

Non-commercial use of course materials (presentations, demonstrations) is allowed without restrictions. Commercial use is possible only with the written permission of Postgres Professional. It is prohibited to make changes to the course materials.
### Feedback

Please send your feedback, comments and suggestions to: edu@postgrespro.ru
### Disclaimer

Postgres Professional assumes no responsibility for any damages and losses, including loss of income, caused by direct or indirect, intentional or accidental use of course materials. Postgres Professional company specifically disclaims any warranties on course materials. Course materials are provided “as is,” and Postgres Professional company has no obligations to provide maintenance, support, updates, enhancements, or modifications.

## Page 2: Topics {#source-page-002}

- File-Based Archiving – Continuous Archiving
- Stream Archiving – pg\_receivewal
- Recovery from Archive
- Archive Cleanup

## Page 3: Continuous Archiving {#source-page-003}

![Source slide page 3: Continuous Archiving](images/page-003.png){#source-figure-003 caption="Source slide page 3: Continuous Archiving"}

  - archive\_mode = on
  - archive\_command
  - archive\_library
  - archive\_timeout

### Speaker notes

The base backup discussed in the previous topic contains all the WAL records needed to restore data consistency. The resulting backup is called standalone because it contains all the data necessary for recovery up to the point in time when the backup was created.

If you add new WAL files generated by the server to the standalone backup, you can restore the cluster to an arbitrary point in time. In practice, the WAL segments are not copied to the backup file itself, but to a separate archive. This is done by the archiver process (if archive\_mode = on).

There are two ways to archive a WAL segment:

1. Use an OS command. When switching to the next WAL segment, the process executes the command stored in the archive\_command parameter.

2. Use the archiving module functions (PostgreSQL 15 and up). The module name is set in the archive\_library parameter. <https://postgrespro.com/docs/postgresql/16/archive-modules>

If the server load is light, the archive\_timeout parameter is commonly used to set the maximum time that can pass before switching to a new WAL segment. Switching can also be done manually using the pg\_switch\_wal function.

<https://postgrespro.com/docs/postgresql/16/continuous-archiving>

## Page 4: archive_command {#source-page-004}

An operating system command that archives completed WAL segments
  - copies file%p to archive named%f,
  - the archive structure can be organized in any way you choose.
  - must return 0 only if successful
  - must not overwrite files that already exist in the archive
  - must ensure data is written to persistent storage
  - conveniently supports custom scripts
- Monitoring
  - pg\_stat\_archiver view shows current status
  - can enable the message collector (logging\_collector = on) and write messages to
  - the server log

### Speaker notes

The archive command must copy the specified file to an archive. The archive can be organized in any way you choose. For example, this could be a file system on a separate server.

The command must return a zero exit status only when it succeeds. In this case, the copied segment can be deleted by the checkpoint process. With a non-zero status, the segment that failed to copy and subsequent segments are not deleted, and the server periodically retries the archive command until it receives 0.

The command should never overwrite existing files, as this likely indicates an error, and overwriting a file could invalidate the archive.

To ensure reliability, the command must guarantee that the file is written to persistent storage; otherwise, if the archiving command fails, you could lose a file segment if the PostgreSQL server deletes it in the meantime.

The current archiving status is shown in the pg\_stat\_archiver view. The message collector process logging\_collector can conveniently write error and other messages from archive\_command execution to the server log.

If complex logic is required, it can be scripted in a separate file, and the archiving command can be set to run it.

<https://postgrespro.com/docs/postgresql/16/runtime-config-wal#GUC-ARCHI> VE-COMMAND

## Page 5: Setting up continuous archiving {#source-page-005}

We will store the archive in the /var/lib/postgresql/archive directory. It must be accessible to the PostgreSQL owner user.
```sh
postgres$ mkdir /var/lib/postgresql/archive
```
In practice, you can keep the archive on a separate server or disk system accessible over the network.
Let’s enable archiving mode and set the command for copying completed WAL segments.
```sql
α=> ALTER SYSTEM SET archive_mode = on;
```
```sql
ALTER SYSTEM
```
In archive\_command, we first check whether a file with the specified name already exists in the archive and only copy it if it’s not there.
```sql
α=> ALTER SYSTEM SET archive_command =
'test ! -f /var/lib/postgresql/archive/%f && cp %p /var/lib/postgresql/archive/%f';
```
```sql
ALTER SYSTEM
```
You can specify any command in archive\_command, as long as it returns 0 only when successful. For example, you can set up compression of archived segments:
```text
SET archive_command =
'test ! -f /var/lib/postgresql/archive/%f && gzip <%p >/var/lib/postgresql/archive/%f';
```
Ideally, the archiving command should perform a sync to ensure the file is safely written to persistent storage. Otherwise, in case of a failure, the file might be lost from the archive while the server has already deleted it from the pg\_wal directory.
It’s usually convenient to put all the necessary archiving logic in a separate script and call the entire script:
```text
SET archive_command = 'archive.sh "%f" "%p"';
```
Changing archive\_mode requires a server restart.
```sh
student$ sudo pg_ctlcluster 16 alpha restart
```
```sh
student$ psql
```
The archiver process now exists:
```sql
α=> SELECT pid, backend_type, backend_start
FROM pg_stat_activity
WHERE backend_type='archiver';
```
```text
  pid  | backend_type |         backend_start
-------+--------------+-------------------------------
 71779 | archiver     | 2026-09-16 23:56:16.851718+03
(1 row)
```
Let’s verify that archiving is working. Create a database and a table.
```sql
α=> CREATE DATABASE backup_archive;
```
```sql
CREATE DATABASE
```
```sql
α=> \c backup_archive
```
```text
You are now connected to database "backup_archive" as user "student".
```
```sql
α=> CREATE TABLE t(s text);
```
```sql
CREATE TABLE
```
```sql
α=> INSERT INTO t VALUES ('Row #1');
```
```sql
INSERT 0 1
```
Here’s the WAL segment currently in use:
```sql
α=> SELECT pg_walfile_name(pg_current_wal_lsn());
```
```text
     pg_walfile_name
--------------------------
 000000010000000000000001
(1 row)
```

## Page 6: Page 6 {#source-page-006}

To fill out the file, we would need to perform a large number of operations. For testing purposes, it’s easier to force a segment switch:
```sql
α=> SELECT pg_switch_wal();
```
```text
 pg_switch_wal
---------------
 0/1FB6108
(1 row)
```
```sql
α=> INSERT INTO t VALUES ('Another row');
```
```sql
INSERT 0 1
```
The segment has switched:
```sql
α=> SELECT pg_walfile_name(pg_current_wal_lsn());
```
```text
     pg_walfile_name
--------------------------
 000000010000000000000002
(1 row)
```
The previous one should have made it to the archive. Check:
```sh
student$ sudo ls -lh /var/lib/postgresql/archive
```
```text
total 16M
-rw------- 1 postgres postgres 16M Sep 16 23:56 000000010000000000000001
```
The current archiving status is shown in the pg\_stat\_archiver view:
```sql
α=> SELECT * FROM pg_stat_archiver \gx
```
```text
-[ RECORD 1 ]------+------------------------------
archived_count     | 1
last_archived_wal  | 000000010000000000000001
last_archived_time | 2026-09-16 23:56:24.776632+03
failed_count       | 0
last_failed_wal    |
last_failed_time   |
stats_reset        | 2026-07-15 23:46:12.428787+03
```
File-based archiving is now configured and working.

## Page 7: pg_receivewal {#source-page-007}

![Source slide page 7: pg_receivewal](images/page-007.png){#source-figure-007 caption="Source slide page 7: pg_receivewal"}

  - max\_wal\_senders
  - max\_replication\_slots

### Speaker notes

Alternatively, you can populate the archive using the replication protocol and the pg\_receivewal utility.

Typically, the utility runs on a separate server that stores the archive. It generates files in the same way as the main server itself, and writes them to the specified directory. Incomplete segments have the suffix .partial. Cache synchronization with the file system occurs only when the WAL segment is closed, but you can make it synchronous with the -- synchronous parameter.

The utility can compress WAL segments. Use the parameter --compress with the algorithm (gzip or lz4) and the compression level as arguments.

When running the utility for the first time (when the archive is empty), archiving starts from the beginning of the current WAL segment. Otherwise, it starts from the beginning of the segment following the last completed segment in the archive. When using a replication slot, this ensures that there are no gaps in the archive, even if the utility was disabled for a while.

By default, the utility receives records indefinitely, but you can set it to terminate when receiving a record with a specific LSN: --endpos = lsn.

Note that the utility does not start automatically as a service and cannot be a daemon.

<https://postgrespro.com/docs/postgresql/16/app-pgreceivewal>

## Page 8: Streaming Archive {#source-page-008}

To set up archive population via the streaming replication protocol, let’s first stop file-based archiving.
```sql
α=> ALTER SYSTEM RESET archive_mode;
```
```sql
ALTER SYSTEM
```
```sql
α=> ALTER SYSTEM RESET archive_command;
```
```sql
ALTER SYSTEM
```
```sh
student$ sudo pg_ctlcluster 16 alpha restart
```
The current state of the archive.
```sh
student$ sudo ls -lh /var/lib/postgresql/archive
```
```text
total 32M
-rw------- 1 postgres postgres 16M Sep 16 23:56 000000010000000000000001
-rw------- 1 postgres postgres 16M Sep 16 23:56 000000010000000000000002
```
First, let’s have the pg\_receivewal utility create a slot to ensure all WAL records are received.
```sh
postgres$ /usr/lib/postgresql/16/bin/pg_receivewal --create-slot --slot=archive
```
Start it in the background in archiving mode. Upon detecting existing files in the archive, the utility will request the next segment from the server to ensure there are no gaps in the archive.
```sh
postgres$ /usr/lib/postgresql/16/bin/pg_receivewal -D /var/lib/postgresql/archive --slot=archive &
```
Let’s add a ton of rows to the table and then delete them.
```sh
student$ psql -d backup_archive
```
```sql
α=> INSERT INTO t SELECT 'Hello again.' FROM generate_series(1,200_000);
```
```sql
INSERT 0 200000
```
```sql
α=> DELETE FROM t WHERE s = 'Hello again.';
```
```sql
DELETE 200000
```
```sql
α=> VACUUM t;
```
```sql
VACUUM
```
New files have appeared in the archive.
```sh
student$ sudo ls -lh /var/lib/postgresql/archive
```
```text
total 64M
-rw------- 1 postgres postgres 16M Sep 16 23:56 000000010000000000000001
-rw------- 1 postgres postgres 16M Sep 16 23:56 000000010000000000000002
-rw------- 1 postgres postgres 16M Sep 16 23:56 000000010000000000000003
-rw------- 1 postgres postgres 16M Sep 16 23:56 000000010000000000000004.partial
```
The last file most likely has a .partial suffix – it is currently being written to.
Stream archiving is now configured and working.

## Page 9: Base backup {#source-page-009}

![Source slide page 9: Base backup](images/page-009.png){#source-figure-009 caption="Source slide page 9: Base backup"}


### Speaker notes

If we have a configured write-ahead log archive, then WAL files become optional in the base backup — since they can be retrieved from the archive during recovery.

Therefore, pg\_basebackup can be run with --wal-method=none.

(But there is no harm in having WAL files in the base copy, except for the space they occupy. In fact, they enable system recovery if the archive becomes unavailable.)

## Page 10: Base Backup {#source-page-010}

Since the archive is populated automatically, let’s instruct pg\_basebackup not to include WAL files in the backup:
```sh
student$ pg_basebackup \
  --checkpoint=fast \
  --wal-method=none \
  --pgdata /home/student/tmp/backup
```
```text
NOTICE:  WAL archiving is not enabled; you must ensure that all required WAL segments are
copied through other means to complete the backup
```
There are no WAL segments in the backup’s pg\_wal directory:
```sh
student$ ls -l /home/student/tmp/backup/pg_wal/
```
```text
total 4
drwx------ 2 student student 4096 Sep 16 23:56 archive_status
```
The archive, however, has gained new files:
```sh
student$ sudo ls -lh /var/lib/postgresql/archive
```
```text
total 96M
-rw------- 1 postgres postgres 16M Sep 16 23:56 000000010000000000000001
-rw------- 1 postgres postgres 16M Sep 16 23:56 000000010000000000000002
-rw------- 1 postgres postgres 16M Sep 16 23:56 000000010000000000000003
-rw------- 1 postgres postgres 16M Sep 16 23:56 000000010000000000000004
-rw------- 1 postgres postgres 16M Sep 16 23:56 000000010000000000000005
-rw------- 1 postgres postgres 16M Sep 16 23:56 000000010000000000000006.partial
```

## Page 11: Recovery {#source-page-011}

![Source slide page 11: Recovery](images/page-011.png){#source-figure-011 caption="Source slide page 11: Recovery"}

**recovery.signal**
- signals recovery from backup tablespace\_map
  - tablespace paths
- backup\_label
  - starting position and other details
- postgresql.conf
  - restore\_command
  - target recovery point
  - etc.

### Speaker notes

The recovery process is controlled by three files and parameters.

The recovery.signal file is created manually. If it exists, the server starts in recovery mode. The file's contents are ignored. If recovery.signal is missing, PostgreSQL assumes it's performing automatic crash recovery. If the file exists, the server understands that a user-controlled restore from a backup is taking place, and checks two other files that are generated when a base backup is created:

● The tablespace\_map file contains paths for table spaces. If the symlincs in pg\_tblspc are missing (which is true for tar format), the server will create them at startup based on the information from tablespace\_map.

We've already encountered this file in the "Base Backup" topic.

● The backup\_label file stores the backup name, creation time, and, most importantly, the name of the WAL segment and the position in it to start recovery from.

During recovery, the `startup` process reads files from the archive using the command from restore\_command and applies WAL records from the starting position specified in the label file up to the target recovery position.

## Page 12: Recovery Configuration {#source-page-012}

Let’s start the recovery configuration by creating a signal file.
```sh
student$ touch /home/student/tmp/backup/recovery.signal
```
When the base backup was created, a backup\_label file was generated.
```sh
student$ cat /home/student/tmp/backup/backup_label
```
```sql
START WAL LOCATION: 0/5000028 (file 000000010000000000000005)
CHECKPOINT LOCATION: 0/5000060
BACKUP METHOD: streamed
BACKUP FROM: primary
START TIME: 2026-09-16 23:56:35 MSK
LABEL: pg_basebackup base backup
START TIMELINE: 1
```
The key piece of information in this file is the starting point for recovery (START WAL LOCATION). Theoretically, recovery can begin from an earlier position (but not a later one), though this will take more time.
Since our cluster has no additional tablespaces, the tablespace\_map file is not necessary. All that remains is to set a restore\_command and other parameters in the configuration file.

## Page 13: restore_command {#source-page-013}

**OS command restoring WAL segment**
  - performs the reverse of archive\_command
```sql
copies file%f from archive to server at path%p
```
  - must return 0 only if successful
  - (can be called for non-existent files)
- Monitoring
  - log\_startup\_progress\_interval = 10s
  - logging of `startup` process status

### Speaker notes

The restore\_command parameter specifies the command to "reverse" the archive\_command. This command must copy the file from the archive to the pg\_wal directory and return a status of 0 only when successful. This is very important, since during the recovery process the server can execute a restore command for files that will not be in the archive – this is not an error, but the command must end with non-zero status.

Starting with version PostgreSQL 14, changing the restore\_command parameter does not require a server restart.

<https://postgrespro.com/docs/postgresql/16/runtime-config-wal#RUNTIME-C> ONFIG-WAL-ARCHIVE-RECOVERY

In PostgreSQL 15 and higher, the status of the recovery process can be monitored in the server message log with the log\_startup\_progress\_interval option enabled. It logs the progress every 10 seconds by default.

In earlier versions, information about the current recovery state was displayed only in the name of the `startup` unix process.

## Page 14: Restartpoint {#source-page-014}

**Checkpoint equivalent for recovery**
  - flush dirty buffers
  - write to control file
  - remove unnecessary WAL segments
- Performed when a control point record is applied
  - if at least checkpoint\_timeout has elapsed since the restartpoint
  - or WAL volume approaching max\_wal\_size
- Monitoring
  - log\_checkpoints
  - pg\_stat\_bgwriter

### Speaker notes

When the startup process applies WAL records during recovery, the data in the server's shared memory changes in the same way as in normal operation: dirty buffers appear. WAL recods must be retained in case a failure occurs and recovery must restart. To limit the amount of stored records and not start recovery from the very beginning, the server periodically performs restartpoints, resetting, as at a checkpoint, dirty buffers of shared memory structures: buffer cache, clog cache and others. If the restartpoint completes successfully, the server writes to the control file and deletes the WAL segments that entirely precede the restartpoint start.

The restartpoint is executed when the startup process encounters a checkpoint record in the WAL, if at least checkpoint\_timeout time has passed since the last restartpoint, or if the WAL size approaches the max\_wal\_size value. In other words, the restartpoint is triggered by the checkpoint WAL record, but if none of the conditions are met, the record is skipped.

If log\_checkpoints is enabled, all restartpoints will be recorded in the message log, and the pg\_stat\_bgwriter view will show accumulated statistics.

<https://postgrespro.com/docs/postgresql/16/wal-configuration>

## Page 15: Recovery Target {#source-page-015}

![Source slide page 15: Recovery Target](images/page-015.png){#source-figure-015 caption="Source slide page 15: Recovery Target"}

Point-in-Time Recovery (PITR)
  - recovery\_target = 'immediate'
  - restore consistency only
  - recovery\_target\_name = 'name'
  - to a named restore point created with
  - pg\_create\_restore\_point('name')
  - recovery\_target\_time = 'timestamp' up to the specified timestamp
  - recovery\_target\_xid = 'xid'
  - to the specified transaction
  - recovery\_target\_lsn = 'lsn'
  - to the specified LSN
  - recovery\_target\_inclusive = on|off
  - whether to include the target point
  - recovery\_target\_action = pause
### pause
  - promote
  - accepts connections
  - shutdown
  - pauses

### Speaker notes

If the recovery target is not set, the cluster will be restored as close as possible to the time of failure. However, you can stop the recovery process at any other point by setting a recovery target.

● The recovery\_target = 'immediate' parameter will stop recovery as soon as consistency is achieved. This is effectively the same as recovering from a base backup without using the archive.

● The recovery\_target\_name parameter lets you specify a named recovery point that was previously created using the pg\_create\_restore\_point function.

● The recovery\_target\_time parameter allows you to specify any point in time (timestamp), recovery\_target\_xid allows you to specify any transaction, and recovery\_target\_lsn allows you to specify an LSN. The recovery\_target\_inclusive parameter lets you choose whether to include or exclude the target point itself.

The recovery\_target\_action option allows you to select a state in which the server will be on reaching the target point:

● `pause` suspends recovery. The administrator can query the data to verify whether the recovery target was chosen correctly, and then either complete the recovery by calling the pg\_wal\_replay\_resume function, or set a new target, restart the server, and continue applying WAL records.

● With `promote`, the server will immediately be able to accept connection requests.

● `shutdown` will stop the server. In this case, the recovery.signal file is not removed.

## Page 16: Completion of Recovery {#source-page-016}

![Source slide page 16: Completion of Recovery](images/page-016.png){#source-figure-016 caption="Source slide page 16: Completion of Recovery"}

remove recovery.signal
- backup\_label backup\_label.old
- perform recovery\_end\_command
- problems are diagnosed using the server log

### Speaker notes

Once recovery is complete, the `startup` process terminates, `postmaster` launches the remaining service processes needed for the server instance to operate, and the server begins running in normal mode. The recovery.signal file is deleted, and backup\_label is renamed to backup\_label.old.

In the recovery\_end\_command parameter, you can specify a command that will be executed at the end of recovery. These may be file system operations, archive cleanup, notifications, etc.

The server may fail to start. In this case, no processes will be running in the operating system (and the postmaster.pid file will be absent), while the recovery.signal file will remain in place. The reasons can be found in the server log (for example, invalid configuration parameters). Once the issue has been resolved, restart the server.

Important note: if continuous archiving was set up on the origin server, it must either be disabled on the restored server or redirected to a different archive location.

## Page 17: Recovery from a Base Backup {#source-page-017}

Let’s configure recovery settings on beta. In the simplest case, all you need is a restore command that copies the specified WAL segment from the archive to the specified path:
```sh
student$ echo "restore_command = 'cp /var/lib/postgresql/archive/%f %p'" \
  >> /home/student/tmp/backup/postgresql.auto.conf
```
If no recovery target is specified (via one of the recovery\_target\* parameters), WAL records from all files in the archive will be applied to the base backup.
Place the backup in the beta server’s data directory and start it.
```sh
student$ sudo pg_ctlcluster 16 beta stop
```
```text
Cluster is not running.
```
```sh
student$ sudo rm -rf /var/lib/postgresql/16/beta
```
```sh
student$ sudo mv /home/student/tmp/backup /var/lib/postgresql/16/beta
```
```sh
student$ sudo chown -R postgres:postgres /var/lib/postgresql/16/beta
```
```sh
student$ sudo pg_ctlcluster 16 beta start
```
After a successful recovery, the recovery.signal file is removed and the label file is renamed to backup\_label.old:
```sh
student$ sudo ls -l /var/lib/postgresql/16/beta | egrep 'recovery|backup_label'
```
```text
-rw------- 1 postgres postgres    225 Sep 16 23:56 backup_label.old
```
Since all WAL records have been applied, the beta server immediately starts accepting connections. Let’s verify what was restored in the table:
```sh
student$ psql -p 5433 -d backup_archive
```
```sql
β=> SELECT * FROM t;
```
```text
      s
-------------
 Row #1
 Another row
(2 rows)
```
The server’s cumulative statistics contain information about restart points:
```sql
β=> SELECT checkpoints_timed, checkpoints_req, checkpoint_write_time, checkpoint_sync_time, buffers_checkpoint
FROM pg_stat_bgwriter \gx
```
```text
-[ RECORD 1 ]---------+---
checkpoints_timed     | 0
checkpoints_req       | 1
checkpoint_write_time | 29
checkpoint_sync_time  | 15
buffers_checkpoint    | 0
```

## Page 18: Timelines {#source-page-018}

![Source slide page 18: Timelines](images/page-018.png){#source-figure-018 caption="Source slide page 18: Timelines"}

**Sequential number, +1 with each recovery**
  - Timeline number N is included in WAL segment name
  - Historical data is stored in the pg\_wal/N.history and archived
- recovery\_target\_timeline = 'latest' restore to the last timeline (by default)
- recovery\_target\_timeline = 'current' restore to the current timeline
- recovery\_target\_timeline = '1' restore to the specified timeline

### Speaker notes

After restoring to a point in the past, the server begins to generate new WAL records. The record IDs will repeat the record IDs "from the past." To preserve these segments and maintain the ability to recover to different points in time, PostgreSQL introduces the concept of timelines. After each recovery using recovery.signal, the timeline number increments, and this number becomes part of the WAL segment numbering.

Timelines form a tree structure, as shown in the diagrams. Timeline 1 is the initial timeline. A recovery was performed to a point in the past, creating timeline 2.

When performing recovery with the recovery\_target\_timeline = 'latest' parameter (the default), WAL replay will follow the second (newer) timeline once it reaches the branch point.

Settingrecovery\_target\_timeline = '1' will continue recovery along timeline 1 (the specified timeline), while recovery\_target\_timeline = 'current' will follow the current timeline (which in this case is also timeline 1).

PostgreSQL retrieves branch point information from history files located at pg\_wal/N.history. Therefore, these files should never be removed from the archive.

## Page 19: Timelines {#source-page-019}

What happened to the timeline after recovery?
```sql
β=> SELECT pg_walfile_name(pg_current_wal_lsn());
```
```text
     pg_walfile_name
--------------------------
 000000020000000000000006
(1 row)
```
The number incremented by one.
A history file corresponding to this branch has appeared in the pg\_wal directory:
```sh
postgres$ ls /var/lib/postgresql/16/beta/pg_wal/00000002.history
```
```text
/var/lib/postgresql/16/beta/pg_wal/00000002.history
```
It contains information about the branch points through which we arrived at this timeline:
```sh
student$ sudo cat /var/lib/postgresql/16/beta/pg_wal/00000002.history
```
```text
1
0/6000000
no recovery target specified
```
PostgreSQL uses these files when a timeline is specified in the recovery\_target\_timeline parameter. History files must be archived alongside WAL file segments and should not be removed from the archive.

## Page 20: Archive Cleanup {#source-page-020}

![Source slide page 20: Archive Cleanup](images/page-020.png){#source-figure-020 caption="Source slide page 20: Archive Cleanup"}


### Speaker notes

The archive grows over time and needs to be cleaned of unnecessary files every now and then.

The pg\_archivecleanup tool does just that. It takes a WAL segment name and deletes all preceding segments.

<https://postgrespro.com/docs/postgresql/16/pgarchivecleanup>

The question is which archive position to use as the cutoff point.

If you are only using the archive to restore from a single base backup, you can clean up as soon as you update the backup.

However, if the archive needs to support recovery from multiple base backups, you'll need to either write a custom cleanup script or, better yet, use a third-party backup tool like pg\_probackup that offers flexible backup retention policies.

## Page 21: Archive Cleanup {#source-page-021}

The first server’s archive currently contains unnecessary files that were added before the base backup was created:
```sh
student$ sudo ls -lh /var/lib/postgresql/archive
```
```text
total 96M
-rw------- 1 postgres postgres 16M Sep 16 23:56 000000010000000000000001
-rw------- 1 postgres postgres 16M Sep 16 23:56 000000010000000000000002
-rw------- 1 postgres postgres 16M Sep 16 23:56 000000010000000000000003
-rw------- 1 postgres postgres 16M Sep 16 23:56 000000010000000000000004
-rw------- 1 postgres postgres 16M Sep 16 23:56 000000010000000000000005
-rw------- 1 postgres postgres 16M Sep 16 23:57 000000010000000000000006.partial
```
The pg\_archivecleanup utility takes the archive path and the name of the last WAL segment to keep (which can be found in the backup\_label file).
```sh
student$ sudo head -n 1 /var/lib/postgresql/16/beta/backup_label.old
```
```text
START WAL LOCATION: 0/5000028 (file 000000010000000000000005)
```
```sh
student$ sudo pg_archivecleanup /var/lib/postgresql/archive 000000010000000000000005
```
Let’s check which files remain in the archive:
```sh
student$ sudo ls -lh /var/lib/postgresql/archive
```
```text
total 32M
-rw------- 1 postgres postgres 16M Sep 16 23:56 000000010000000000000005
-rw------- 1 postgres postgres 16M Sep 16 23:57 000000010000000000000006.partial
```
The archive has been cleaned up.
The utility can be called immediately after creating the base backup by placing both commands in a script.

## Page 22: Takeaways {#source-page-022}

**Physical backups: base backup + WAL archive**
- Good for
  - ongoing periodic backups
  - crash recovery with minimal data loss
  - point-in-time recovery
  - backing up large volumes of data
- Not so good for
  - long-term storage
- Not suitable for
  - migration to a different platform

## Page 23: Practice {#source-page-023}

**1. On the alpha server, create a database and a data table. 2. Set up continuous archiving. 3. Make a copy of the cluster without log files. 4. Insert a few more rows into the table and verify that the current WAL segment has been archived.**
- 5. Restore the beta server from the backup, specifying only the restore command in the configuration.
- 6. Restore the beta server again from the same backup, this time with `immediate` as the recovery target.

### Speaker notes

After completing the practice tasks, delete the databases and reset the configuration.

## Page 24: 1. Database and Table {#source-page-024}

```sql
α=> CREATE DATABASE backup_archive;
```
```sql
CREATE DATABASE
```
```sql
α=> \c backup_archive
```
```text
You are now connected to database "backup_archive" as user "student".
```
```sql
α=> CREATE TABLE t(s text);
```
```sql
CREATE TABLE
```
```sql
α=> INSERT INTO t VALUES ('Hello, world!');
```
```sql
INSERT 0 1
```
### 2. Setting Up Continuous Archiving
```sh
postgres$ mkdir /var/lib/postgresql/archive
```
```sql
α=> ALTER SYSTEM SET archive_mode = on;
```
```sql
ALTER SYSTEM
```
```sql
α=> ALTER SYSTEM SET archive_command = 'test ! -f /var/lib/postgresql/archive/%f && cp %p /var/lib/postgresql/archive/%f';
```
```sql
ALTER SYSTEM
```
```sh
student$ sudo pg_ctlcluster 16 alpha restart
```
### 3. Base Backup
Since we’ll need to restore from the same backup twice, let’s create it in compressed tar format.
```sh
student$ pg_basebackup \
  --checkpoint=fast \
  --wal-method=none \
  --format=tar \
  --gzip \
  --pgdata=/home/student/tmp/backup
```
```text
NOTICE:  all required WAL segments have been archived
```
```sh
student$ ls -l /home/student/tmp/backup
```
```text
total 5328
-rw------- 1 student student  224439 Sep 17 00:08 backup_manifest
-rw------- 1 student student 5229378 Sep 17 00:08 base.tar.gz
```
### 4. Populating a Table With Rows
```sh
student$ psql -d backup_archive
```
```sql
α=> INSERT INTO t VALUES ('Another row');
```
```sql
INSERT 0 1
```
Switch the segment:
```sql
α=> SELECT pg_walfile_name(pg_current_wal_lsn()), pg_switch_wal();
```
```text
     pg_walfile_name      | pg_switch_wal
--------------------------+---------------
 000000010000000000000003 | 0/3006640
(1 row)
```
Check the archiving status:
```sql
α=> SELECT last_archived_wal FROM pg_stat_archiver;
```
```text
    last_archived_wal
--------------------------
 000000010000000000000003
(1 row)
```
### 5. Recovery from a Base Backup
```sh
student$ sudo pg_ctlcluster 16 beta stop
```
```text
Cluster is not running.
```

## Page 25: Page 25 {#source-page-025}

```sh
postgres$ rm -rf /var/lib/postgresql/16/beta
```
```sh
postgres$ mkdir /var/lib/postgresql/16/beta
```
```sh
postgres$ chmod 700 /var/lib/postgresql/16/beta
```
```sh
student$ sudo tar xzf /home/student/tmp/backup/base.tar.gz -C /var/lib/postgresql/16/beta
```
To make it simple, disable continuous archiving on the standby server and set just the restore command:
```sh
postgres$ echo "restore_command = 'cp /var/lib/postgresql/archive/%f %p' " \
  | tee /var/lib/postgresql/16/beta/postgresql.auto.conf
```
```text
restore_command = 'cp /var/lib/postgresql/archive/%f %p'
```
Create recovery.signal.
```sh
postgres$ touch /var/lib/postgresql/16/beta/recovery.signal
```
Start the server.
```sh
student$ sudo pg_ctlcluster 16 beta start
```
```sh
student$ psql -p 5433 -d backup_archive
```
```sql
β=> SELECT * FROM t;
```
```text
       s
---------------
 Hello, world!
 Another row
(2 rows)
```
All rows were restored. Since all available WAL records are applied by default, the server immediately switched to normal mode and is ready to accept write queries:
```sql
β=> SELECT pg_is_in_recovery();
```
```text
 pg_is_in_recovery
-------------------
 f
(1 row)
```
### 6. Recovery from Base Backup (immediate)
```sh
student$ sudo pg_ctlcluster 16 beta stop
```
```sh
postgres$ rm -rf /var/lib/postgresql/16/beta
```
```sh
postgres$ mkdir /var/lib/postgresql/16/beta
```
```sh
postgres$ chmod 700 /var/lib/postgresql/16/beta
```
```sh
student$ sudo tar xzf /home/student/tmp/backup/base.tar.gz -C /var/lib/postgresql/16/beta
```
Create recovery.signal, and add the restore command and recovery target to the configuration file:
```sh
postgres$ touch /var/lib/postgresql/16/beta/recovery.signal
```
```sh
postgres$ cat << EOF | tee /var/lib/postgresql/16/beta/postgresql.auto.conf
restore_command = 'cp /var/lib/postgresql/archive/%f %p'
recovery_target = 'immediate'
EOF
```
```text
restore_command = 'cp /var/lib/postgresql/archive/%f %p'
recovery_target = 'immediate'
```
Start the server.
```sh
student$ sudo pg_ctlcluster 16 beta start
```
```sh
student$ psql -p 5433 -d backup_archive
```
```sql
β=> SELECT * FROM t;
```
```text
       s
---------------
 Hello, world!
(1 row)
```
Only the first row was restored.
This time, only the WAL records required to bring the data to a consistent state were applied. The server is already accepting read queries, and recovery can be continued if needed:
```sql
β=> SELECT pg_is_in_recovery(), pg_get_wal_replay_pause_state();
```

## Page 26: Page 26 {#source-page-026}

![Source slide page 26: Page 26](images/page-026.png){#source-figure-026 caption="Source slide page 26: Page 26"}

```text
 pg_is_in_recovery | pg_get_wal_replay_pause_state
-------------------+-------------------------------
 t                 | paused
(1 row)
```
The pg\_get\_wal\_replay\_pause\_state() function returns:
not paused – no pause was requested pause requested – a pause has been requested paused – recovery is paused
Since we don't need to apply any further records, we complete the recovery by promoting the server to normal mode (superuser privileges required):
```sql
β=> SELECT pg_wal_replay_resume();
```
```text
 pg_wal_replay_resume
----------------------
(1 row)
```
```sql
β=> SELECT pg_is_in_recovery();
```
```text
 pg_is_in_recovery
-------------------
 f
(1 row)
```
To avoid manually promoting the server out of recovery mode, we could have set recovery\_target\_action = promote in advance.
### Cleanup
Remove the backup and databases, reset the configuration.
```sh
student$ rm -rf /home/student/tmp/backup
```
```sql
β=> ALTER SYSTEM RESET archive_mode;
```
```sql
ALTER SYSTEM
```
```sql
β=> ALTER SYSTEM RESET archive_command;
```
```sql
ALTER SYSTEM
```
```sh
student$ sudo pg_ctlcluster 16 beta stop
```
```sql
α=> \c student postgres
```
```text
You are now connected to database "student" as user "postgres".
```
```sql
α=> DROP DATABASE backup_archive;
```
```sql
DROP DATABASE
```
```sql
α=> ALTER SYSTEM RESET archive_mode;
```
```sql
ALTER SYSTEM
```
```sql
α=> ALTER SYSTEM RESET archive_command;
```
```sql
ALTER SYSTEM
```
```sh
student$ sudo pg_ctlcluster 16 alpha restart
```
```sh
postgres$ rm -rf /var/lib/postgresql/archive
```

## Page 27: Practice+ {#source-page-027}

**1. On the alpha server, create a table in which data will be periodically written. Create a physical backup and configure continuous WAL archiving. 2. Insert 100 rows into the prepared table three times (data for January, February and March), marking the time immediately after insertion. Delete some lines between inserts: simulate a situation in which the user accidentally deletes part of the data at an unknown moment, but the problem is not noticed immediately. 3. Recover the deleted data without stopping the alpha server.**
3. Use the beta server as an auxiliary.

## Page 28: 1. Table and Backup with WAL Archive {#source-page-028}

```sql
α=> CREATE DATABASE backup_archive;
```
```sql
CREATE DATABASE
```
```sql
α=> \c backup_archive
```
```text
You are now connected to database "backup_archive" as user "student".
```
Let’s prepare the table:
```sql
α=> CREATE TABLE t (
  id integer PRIMARY KEY,
  month text
);
```
```sql
CREATE TABLE
```
After setting up the data structure, create a physical backup:
```sh
postgres$ pg_basebackup \
  --checkpoint=fast \
  --pgdata /var/lib/postgresql/backup
```
WAL segment archive:
```sh
postgres$ mkdir /var/lib/postgresql/archive
```
Add the archiving parameters file to the supplementary configuration directory.
```sh
postgres$ cat << EOF | tee /etc/postgresql/16/alpha/conf.d/arch.conf
archive_mode = 'on'
archive_command = 'test ! -f /var/lib/postgresql/archive/%f && cp %p /var/lib/postgresql/archive/%f'
archive_timeout = '30s'
EOF
```
```text
archive_mode = 'on'
archive_command = 'test ! -f /var/lib/postgresql/archive/%f && cp %p
/var/lib/postgresql/archive/%f'
archive_timeout = '30s'
```
Restart the server.
```sh
student$ sudo pg_ctlcluster 16 alpha restart
```
```sh
student$ psql -d backup_archive
```
### 2. Inserting Data into the Table
Our application inserts 100 rows into the table at the end of each month.
Insert rows for January:
```sql
α=> INSERT INTO t SELECT g.i, 'January' FROM generate_series(1,100) AS g(i);
```
```sql
INSERT 0 100
```
Save the timestamp after the insert completes:
```sql
α=> SELECT now();
```
```text
             now
------------------------------
 2026-09-17 00:09:53.91657+03
(1 row)
```
A user accidentally deletes some of the data:
```sql
α=> DELETE FROM t WHERE id BETWEEN 42 AND 111;
```
```sql
DELETE 59
```
Insert rows for February:
```sql
α=> INSERT INTO t SELECT g.i, 'February' FROM generate_series(101,200) AS g(i);
```
```sql
INSERT 0 100
```

## Page 29: Page 29 {#source-page-029}

Save the timestamp again after the transaction completes:
```sql
α=> SELECT now();
```
```text
              now
-------------------------------
 2026-09-17 00:10:04.242256+03
(1 row)
```
The user runs the same command again for some reason:
```sql
α=> DELETE FROM t WHERE id BETWEEN 42 AND 111;
```
```sql
DELETE 11
```
The error goes unnoticed for now. Insert another batch of rows:
```sql
α=> INSERT INTO t SELECT g.i, 'March' FROM generate_series(201,300) AS g(i);
```
```sql
INSERT 0 100
```
Let’s record the timestamp once more:
```sql
α=> SELECT now();
```
```text
              now
-------------------------------
 2026-09-17 00:10:14.540194+03
(1 row)
```
The error has been discovered:
```sql
α=> SELECT month, count(*) FROM t GROUP BY month;
```
```text
  month   | count
----------+-------
 February |    89
 March    |   100
 January  |    41
(3 rows)
```
### 3. Recovery Using the Archive
Let’s try to recover the data without stopping the alpha server.
Since the January and February data is missing, the recovery plan is as follows:
Restore the beta server from the WAL archive to the point when the January rows finished committing Export a logical backup of the January data Restore the beta server again, this time to the point when the February rows finished committing Export a logical backup of the February data (the January data may be corrupted at this point) Load the recovered data into the application table on the alpha server
Stop beta and place the physical backup into its data directory.
```sh
student$ sudo pg_ctlcluster 16 beta stop
```
```text
Cluster is not running.
```
```sh
postgres$ rm -rf /var/lib/postgresql/16/beta
```
```sh
postgres$ mv /var/lib/postgresql/backup /var/lib/postgresql/16/beta
```
We will need a recovery.signal file:
```sh
postgres$ touch /var/lib/postgresql/16/beta/recovery.signal
```
The plan is to restore the server to a specific point in time – that is, perform point-in-time recovery (PITR). Set the restore command and specify the recovery target as the timestamp created right after the January data finished committing:
```sh
postgres$ cat << EOF | tee /etc/postgresql/16/beta/conf.d/recovery.conf
restore_command = 'cp /var/lib/postgresql/archive/%f %p'
recovery_target_time = '2026-09-17 00:09:53.91657+03'
EOF
```
```text
restore_command = 'cp /var/lib/postgresql/archive/%f %p'
recovery_target_time = '2026-09-17 00:09:53.91657+03'
```
Before starting recovery, force a segment switch on alpha to ensure all WAL records make it into the archive. Archiving takes place when writing to the next segment begins.

## Page 30: Page 30 {#source-page-030}

```sql
α=> SELECT pg_switch_wal();
```
```text
 pg_switch_wal
---------------
 0/301D308
(1 row)
```
```text
α=> CHECKPOINT;
```
```text
CHECKPOINT
```
Start the beta server.
```sh
student$ sudo pg_ctlcluster 16 beta start
```
```sh
student$ psql -p 5433 -d backup_archive
```
Verify that the January data has been restored:
```sql
β=> SELECT month, count(*) FROM t GROUP BY month;
```
```text
  month  | count
---------+-------
 January |   100
(1 row)
```
The beta server is in recovery mode:
```sql
β=> SELECT pg_is_in_recovery();
```
```text
 pg_is_in_recovery
-------------------
 t
(1 row)
```
This doesn’t prevent us from exporting a logical backup.
```sh
postgres$ mkdir /var/lib/postgresql/tmp
```
```sql
β=> COPY (SELECT * FROM t WHERE month = 'January') TO '/var/lib/postgresql/tmp/jan.dump';
```
```text
COPY 100
```
Now let’s move on to February. Set a new target time:
```sh
postgres$ sed -i "/recovery_target_time/c\recovery_target_time = '2026-09-17 00:10:04.242256+03'"
/etc/postgresql/16/beta/conf.d/recovery.conf
```
```sh
postgres$ cat /etc/postgresql/16/beta/conf.d/recovery.conf
```
```text
restore_command = 'cp /var/lib/postgresql/archive/%f %p'
recovery_target_time = '2026-09-17 00:10:04.242256+03'
```
Restart beta to continue recovery.
```sh
student$ sudo pg_ctlcluster 16 beta restart
```
```sh
student$ psql -p 5433 -d backup_archive
```
Verify the recovered data:
```sql
β=> SELECT month, count(*) FROM t GROUP BY month;
```
```text
  month   | count
----------+-------
 February |   100
 January  |    41
(2 rows)
```
The February rows are where they should be, but the January rows are already corrupted. Export the February data:
```sql
β=> COPY (SELECT * FROM t WHERE month = 'February') TO '/var/lib/postgresql/tmp/feb.dump';
```
```text
COPY 100
```
Restore the exported data for both months on alpha.
```sql
α=> DELETE FROM t WHERE month IN ('January', 'February');
```
```sql
DELETE 130
```
```sql
α=> COPY t FROM '/var/lib/postgresql/tmp/jan.dump';
```

## Page 31: Page 31 {#source-page-031}

```text
COPY 100
```
```sql
α=> COPY t FROM '/var/lib/postgresql/tmp/feb.dump';
```
```text
COPY 100
```
It worked:
```sql
α=> SELECT month, count(*) FROM t GROUP BY month;
```
```text
  month   | count
----------+-------
 February |   100
 March    |   100
 January  |   100
(3 rows)
```
In PostgreSQL 17 and later, you can skip the delete step and use the COPY command with the ON ERROR ignore parameter to quietly skip rows that already exist in the table.
### Cleanup
Remove the databases and configuration changes.
```sh
student$ sudo pg_ctlcluster 16 beta stop
```
```sql
α=> \c postgres
```
```text
You are now connected to database "postgres" as user "student".
```
```sql
α=> DROP DATABASE backup_archive;
```
```sql
DROP DATABASE
```
```sh
postgres$ rm /etc/postgresql/16/beta/conf.d/recovery.conf
```
```sh
student$ sudo pg_ctlcluster 16 alpha restart
```
Remove any unnecessary files.
```sh
postgres$ rm -rf /var/lib/postgresql/{tmp,archive,backup}
```