Backup
Base Backup
16
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.
2
Topics
What is Physical Backup
Cold and Рot Backup
Replication Protocol and Replication Slots
Cluster Cloning and Backup
Integrity Verification
Backup API
3
Physical Backup
Physical base copy – copy of all cluster files
+ faster than logical backup
+ restores statistics
- can restore only to compatible systems
− and major PostgreSQL versions
− whole cluster backup only, no partial backups
Physical backup involves copying all files related to the database cluster
(both data and service files), that is, creating a full binary copy.
Copying files is faster than dumping all SQL commands, as you do for a
logical backup. Likewise, starting a server from a physical backup is a matter
of minutes, while recovery from a logical backup takes much longer.
Statistical data is also restored from the physical copy, so you don't need to
rebuild it.
But there are shortcomings. A physical backup can be used to restore the
system only on a compatible platform (that has the same OS, architecture,
etc.) with the same PostgreSQL major version installed.
Additionally, you cannot create a physical backup of individual databases
within a cluster – only the entire cluster can be backed up.
4
Cold Backup
Server is off
if the checkpoint is completed, you can just copy the files
the backup can be deployed on a different server, regardless of the PGDATA
location
+ simplicity
− requires service interruption
File system snapshot
Similar to an unexpected shutdown: recovery required on startup, but the
backup has all necessary WAL files
all data, including tablespaces, must be captured in a single snapshot
+ no server downtime required
− file systems with snapshot support are generally slower
A cold backup is made when the data on disk is in a consistent state: that is,
all pages are stored on disk and are up to date.
Restoring from such a backup is straightforward: just extract the files, start
the server, and it's good to go.
But to create such a backup, you must perform a checkpoint and a clean
server shutdown first.
This is a major drawback: shutdowns can take a while, especially with large
volumes of data.
The downtime can be reduced by running rsync (or a similar tool)
beforehand while the server is still running. After the server is shut down,
rsync will only need to copy the changes (which should be relatively few).
However, some downtime is still unavoidable.
Another option is to make an inconsistent copy, which will include pages
saved at different times. This inconsistency is inevitable when the server is
shut down incorrectly or when creating a snapshot of the file system. In the
latter case, all cluster files should fall into one snapshot. This may not be
possible if the files are on different file systems.
When recovering from inconsistent data, the server will need to restore
consistency at startup. This is the standard automatic crash recovery
procedure. This isn't a problem since the necessary WAL files are
guaranteed to be backed up, though the recovery process can take some
time.
6
Hot Backup
Server is running
pages are not consistent, as in case of incorrect shutdown
pages may change while being copied
(the non-atomic read and and write problem)
restoring consistency requires all WAL records
from the last checkpoint till the backup completion
(some files may get deleted during backup)
+ no service interruption
− specialized tools required
Hot backup is performed on a running server, so it will probably contain
inconsistent pages.
In addition to that, during backup, data is read directly from files rather than
through the buffer cache. The contents of the files on disk obviously may
change while the backup process is running, and there may not be a
guarantee of the atomicity of reading/writing PostgreSQL pages (this
depends on the file system and hardware. See "WAL Configuration" section
of the DBA2 course for more). As a result, the backup will contain corrupted
pages that can't be recovered even using WAL. This is the first challenge.
The second challenge is that copying data files can take a while. However,
after completing a checkpoint, the server may delete some log files that are
no longer needed for crash recovery but are still required for the backup.
Without proper safeguards, the resulting backup will be unusable for
recovery. Hot backups therefore require specialized tools. PostgreSQL
provides a low-level interface that enables reliable backup implementation.
This interface is used by both the built-in pg_basebackup tool and various
third-party backup solutions.
7
Replication Protocol
Purposes
backup and replication management
specifically, receiving a stream of WAL records
Handled by the wal_sender process
max_wal_senders = 10
wal_level = replica or logical, but not minimal
Connection
A role with the REPLICATION attribute or the SUPERUSER attribute
permission to connect granted in pg_hba.conf
PostgreSQL provides a specialized protocol for managing both backup and
replication (covered in a dedicated module). Among other things, it facilitates
receiving a stream of WAL records generated by the server.
On the server side, replication protocol connections are handled by the
wal_sender process. It behaves like a regular backend process that starts
when a client connects, but instead of SQL, it processes special replication
commands. The number of concurrent processes (wal_sender) is limited by
the max_wal_senders parameter (default is 10).
The WAL level must be set to replica or higher. On minimal WAL level,
operations like CREATE TABLE AS SELECT, CREATE INDEX, and COPY
FROM bypass WAL logging entirely: durability is ensured by writing data
directly to disk rather than keeping it in RAM.
This is sufficient for crash recovery and recovery from a cold backup, but not
for hot backup.
To use the replication protocol, the client must connect to the server using a
role that has the REPLICATION attribute, or as a superuser. In pg_hba.conf,
you need to allow this role to connect to the `replication` database (which is
actually a keyword, not a database name).
Moreover, the permission for `all` is not enough, the connection to
`replication` must be allowed explicitly.
8
Replication slot
A server object that guarantees the retrieval of WAL records
Tracks which record was last sent to the client
The number of slots is limited bymax_replication_slots = 10
Retains WAL segments until all records have been delivered to
the client
Allows the client to retrieve data at its own pace, including the ability
to disconnect temporarily
WAL files are accumulated on the server when the client is disconnected
status monitoring: pg_replication_slots
You can use replication slots to prevent the server from deleting WAL files
prematurely while a checkpoint is being created. When streaming WAL
records through a replication slot, the slot keeps track of what records were
delivered to the client, and will not let the server remove the WAL file until
the client has received all records from it.
Using a slot means the client doesn't have to worry about the server deleting
WAL files before they have been received properly. The client can even
disconnect and then reconnect after some time to continue receiving WAL
records from where it left off.
However, keep in mind that WAL files will accumulate on the server while the
client is disconnected, so the server could run out of available disk space.
Therefore, every created slot should be added to monitoring (the
pg_replication_slots view) and slots that are no longer needed should be
removed promptly.
The total number of slots that can be created is limited by the
max_replication_slots configuration parameter (default is 10).
The use of slots is covered in more detail in the Replication module.
9
physical backups are created using pg_basebackup
the new server is ready to start as soon as the backup completes
Cluster Cloning
pg_wal
server base backup
pg_global
pg_default
TS
--waldir
--wal-method=stream
--tablespace-mapping
pg_global
pg_default
TS
--pgdata
pg_basebackup
wal senderwal sender
--compress=server-*
(gzip, lz4, zstd)
--format=plain
To immediately deploy a new server from a backup, run pg_basebackup in
plain format (--format=plain – by default) on the target server. It connects to
the source server and copies files from it to the local directory specified by
the --pgdata parameter.
Additional tablespaces are copied to the same absolute paths as on the
source server. Paths can be reassigned by specifying matches in the --
tablespace-mapping utility parameters. This is necessary if pg_basebackup
runs on the source server.
With streaming delivery (--wal-method = stream – default), the utility
establishes a second connection to stream WAL records generated by the
running server to the local directory specified by the --waldir parameter.
By default, the second connection uses a temporary replication slot that is
created on connection and deleted when pg_basebackup completes.
However, you can also specify a permanent slot by name. The slot must
exist at the time the utility is launched or be created by it.
In `plain` format, you can compress data on the server, reducing the network
load. The transmitted data is then decompressed by the utility. But WAL
records are not compressed in streaming mode.
11
Integrity Verification
Data file verification
objective: to ensure that the backup receives consistent data
pg_basebackup and pg_checksums utilities
check relation file page checksums
Backup verification
objective: verify that the integrity of the data in the backup has not
deteriorated over time
manifest file backup_manifest
pg_verifybackup utility checks files from the manifest list
Data can be corrupted both when writing to disk and during storage
It is imperative to verify the integrity of data as it is written, but also that this
integrity is not compromised over time.
Relation file checksum calculation is enabled by default when a cluster is
initialized (or when running pg_checksums utility). The checksum is stored in
the page header and updates whenever the the page changes. The old
checksum and the new one are compared:
●
each time the page is read into the buffer cache;
●
by pg_basebackup when creating a backup;
●
by pg_checksums in verification mode.
pg_basebackup creates a file backup_manifest and puts it into the backup
file
with information about files (names, sizes, calculated checksums) and log
records (WAL start and end positions, time line), as well as the checksum of
the manifest itself.
The utility pg_verifybackup checks the backup against the manifest: the
presence of files, the correctness of checksums, the ability to read and
parse WAL records necessary for recovery.
You should still deploy the backup to make sure it works
and check the health of the server, because the coincidence of checksums
does not guarantee that the data was not damaged.
13
creating a backup for future use
WAL Streaming Delivery
pg_wal
server
pg_global
pg_default
TS
pg_basebackup
wal senderwal sender
--compress=server-*
--format=tar
OID.tar
base.tar
pg_wal.tar
--compress=client-*
client
--pgdata
--wal-method=stream
The tar format is used for archive backups. WAL streaming delivery is used
by default (
wal-method=stream).
The cluster files will be saved as a base.tar file. Each additional tablespace
is copied to a separate tar file with the same name as the tablespace OID.
WAL segments are placed in a separate pg_wal.tar file.
The utility can perform compression both on the client side and on the server
side (in the latter case, the pg_wal.tar file is not compressed).
A suffix corresponding to the compression method will be added to the
names of compressed tar archives. For example, when compressed using
the gzip method, the main file will be called base.tar.gz.
To restore from such a backup, you'll first need to extract the tar files to their
proper locations. You can change the paths of tablespaces by editing the
tablespace_map file before starting the server or using the --tablespace-
mapping command line parameter.
14
creating a backup for future use
WAL File Delivery to Client
pg_wal
server
pg_global
pg_default
TS
pg_basebackup
wal sender
--compress=server-*
OID.tar
--compress=client-*
client
--pgdata
base.tar
--wal-method=fetch
--format=tar
When creating a backup, you can use another mode – WAL file delivery (--
wal-method = fetch).
In this case, no separate connection is created, and the WAL segments are
copied and sent over the main connection. This happens after all cluster files
have been backed up. During checkpoint creation, the server can delete
WAL segments that are no longer needed for recovery. To prevent losing
necessary files, set a sufficiently large value for the wal_keep_size
parameter.
WAL segments are written to the base.tar file together
with the rest of the cluster files.
The fetch mode is almost never used on the client. It remains as a legacy,
and the only viable use case for it now is sending WAL records compressed
– which is impossible in streaming mode.
16
creating a backup for future use
the backup is created on the server, nothing is sent over the network
WAL File Delivery to Server
--wal-method=fetch
--target=server:path
path
pg_wal
pg_global
pg_default
TS
pg_basebackup
wal sender
OID.tar
base.tar
--compress=server-*
server
client
You can create a backup on the server by running pg_basebackup
on the client. No data is transmitted over the network.
To create a backup on the server from a client, use the command line
parameter pg_basebackup --target = server: path, where path is the
directory path
in the server file system (default is
--target=client).
This mode supports compression, but only on the server side, naturally.
When creating a backup on the server, you cannot use WAL streaming
delivery because no WAL records are sent to the client.
Therefore, the fetch delivery mode is used, and the accumulated WAL
segments are placed in the base.tar file along with the rest of the cluster
files.
18
full_page_writes is set to on during the backup process
A checkpoint is performed at the very beginning (either fast or normal)
Low-Level Perspective
time
checkpoint
checkpoint
copying data files
backupstart
checkpoint
backupend
required WAL files
to handle
non-atomic nature
of reads and writes
The low-level backup algorithm is the same whether you're using
pg_basebackup or any third-party tools (which may be necessary since
pg_basebackup only provides the most basic functionality).
1. You need to notify the server that a backup is starting.
During the backup process, the system first enables the full_page_writes
parameter: whenever a page is modified for the first time after a checkpoint,
its complete image is written to the WAL.
During recovery, WAL records will be applied not to pages in the file (due to
non-atomic I/O, parts of the page may be mismatched), but to the page
images from WAL.
Second, a checkpoint is performed. There are two checkpoint modes: fast
(which can cause the I/O load to peak) and spread (controlled by the
checkpoint_completion_target parameter).
2. Once the checkpoint completes, you can copy the data files using any
method that works for you.
3. Once you've copied everything, you need to let the server know that the
backup is complete.
4. Additionally, you need to ensure that all WAL records generated from the
start of the backup until its completion are included in the backup.
19
Backup API
Backup start: pg_backup_start
performs a checkpoint and sets full_page_writes
returns the WAL starting position
Backup end: pg_backup_stop
returns the WAL ending position
Copying files
either the replication protocol or any OS tools
Copying the WAL from the starting to ending position
replication protocol + slot or OS tools + wal_keep_size
You can use this backup algorithm to build your own custom backup utility.
The backup starts by calling the pg_backup_start function. It sets the
full_page_writes parameter and performs a checkpoint, as discussed earlier.
The function returns a specific position in the WAL. Records starting from
that position must be included into the backup.
At the end of the backup, the pg_backup_stop function is called. It returns
the final position in the WAL.
All WAL records between the starting and ending positions need to be
included into the backup. Since the server may delete WAL files that aren't
needed for recovery during operation, you should either use the replication
protocol with a replication slot to receive records during the backup process
(like pg_basebackup does in --wal-method=stream mode), or alternatively —
though less convenient — copy the files using OS tools after setting the
wal_keep_size parameter.
You can copy files in parallel, use rsync, and so on.
20
Takeaways
A base backup is a complete copy of the database cluster,
including tablespaces, along with the WAL needed to restore
consistency
Hot backup allows the server to remain running during the
backup process
Tools
replication protocol
replication slot
low-level backup API
21
Practice
1. In the first cluster, create a tablespace and a database
containing a table within that tablespace.
2. Create a compressed tar-format backup of the cluster using
pg_basebackup.
3. Deploy a second cluster from this backup. Place the tablespace in
a different directory by modifying the tablespace_map file.
4. Start the second server and verify that it's working properly.
After the practice is complete, delete the database and tablespace in both
clusters.
22
Practice+
1. Load the basebackup_to_shell module to create a base backup on
the server using a user-defined command. This command should
compress the tar backup using the bzip2 utility.
2. Back up the server in tar format. Make sure that the archive is
compressed by bzip2.
3. Restore the beta cluster from the resulting compressed archive
and verify that it works.
1. To load the module, add the library of the same name
to the shared_preload_libraries parameter. With the
basebackup_to_shell.comm and parameter, specify the command to run
when backing up:
bzip2 -c> archive.tar.bz2
2. To load the module for backup, specify
pg_basebackup --target=shell
3. To extract files from the tar archive compressed by the bzip2 utility, use
the command:
tar xjf archive.tar.bz2 -C destination directory
After completing the practice, delete the created databases and the backup.
