18
Backup and Replication
Backup Overview
Copyright
© Postgres Professional, 2017–2026
Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Aleksei Beresnev
Translated by: Liudmila Mantrova, Alexander Meleshko, Elena Sharafutdinova
Cover photo by Oleg Bartunov (Nepal peak, 7143 m, Lhonak)
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
Logical Backup
Physical Backup
3
Logical Backup
What Is Logical Backup
Table Backup
Database Backup
Cluster Backup
4
Logical Backup
SQL commands to restore data from scratch
+ can backup a separate object or database
+ can recover on a cluster running different major version
+ can recover on a different architecture
− low speed
− can restore to the moment of the backup only
There are two types of backup: logical and physical.
Logical backup is a set of SQL commands that restores a cluster (or
database, or a separate object) from scratch.
Such a backup is, in fact, a plain text file, which gives a certain flexibility.
For example, you can make a copy of only those objects that are needed;
you can edit the file by changing the names or data types, etc.
In addition, SQL commands can be executed on a different version of the
DBMS or on a different architecture. Only compatibility at the command level
is required, binary compatibility is not necessary.
However, for a large database, this mechanism is inefficient, since executing
the commands (in particular, creation of indexes) will take a long time.
Moreover, it is possible to restore the system from such a backup only to the
moment at which the backup was made.
5
COPY: Table Backup
Backup
output of a table contents or a query results into a file, stream or program
Restore
insertion of rows from a file or input stream into an existing table
Server variant Client variant
SQL COPY command psql \COPY command
the file must be accessible the file must be accessible
to the postgres user to the user who has launched psql
on the server on the client
If you want to save only the contents of one table, you can use the COPY
command.
The command writes a table (or the result of an arbitrary query) either to a
file or to an output stream, or sends it as input to another program. You can
specify options such as format (plain text, csv or binary), field separator,
NULL string representaion, etc.
The opposite variant of the COPY command reads fields from a file or input
stream and inserts them into a table. The table isn’t cleared, the new rows
are simply appended to the existing ones.
The COPY command is significantly faster than similar INSERT commands,
because the client does not need to access the server repeatedly, and the
server does not have to analyze the commands multiple times.
In psql, there is a client version of the COPY command with a similar syntax.
Unlike the server version, which is an SQL command, the client version is a
psql command.
The file name in the SQL command corresponds to a file on the database
server. The user running PostgreSQL (usually postgres) must have access
to this file. In the client version, the file is accessed on the client, and only
the content is transmitted to the server.
7
pg_dump: Database Backup
Backup
outputs an SQL script or an archive
in a special format with a TOC to a stream or file
supports parallel execution
can define what objects to backup
(tables, schemas, only DML or only DDL, etc.)
Restore
SQL script via psql
archive with TOC via pg_restore
(can define what objects to restore and supports parallel execution)
the new database must be created from template0
roles and tablespaces must be created in advance
The pg_dump utility creates a full-scale database backup. Depending on the
options, it provides either an SQL script containing commands that create
the required objects, or a file in a special format with a table of contents.
Restoring from an SQL script is as simple as executing it in psql.
Restoring from an archive is done using the pg_restore tool. It reads the file
and translates it into regular psql commands. The advantage is that it allows
you to specify what objects to restore at the recovery stage, not just at the
backup stage. Moreover, this type of backup and restore supports parallel
execution.
The database for restore must be created from the database template0,
since all changes made in template1 will also be backed up. In addition, the
necessary roles and tablespaces must be created in advance, since these
objects belong to the entire cluster. After restore, it is recommended to run
the ANALYZE command to collect fresh statistics.
9
pg_dumpall: Cluster Backup
Backup
makes a backup of the entire cluster, including roles and tablespaces
outputs an SQL script to the console or to a file
parallel execution is not supported, but you can dump only the global objects
and then use pg_dump
Restore
via psql
pg_dumpall creates a backup of the entire cluster, including roles and
tablespaces.
Since pg_dumpall requires access to all objects of all databases, it is usually
run by the superuser. pg_dumpall connects to each database in the cluster
one by one and backups them using pg_dump. In addition, it also stores
data related to the cluster as a whole.
The result of pg_dumpall is a script for psql. Other formats are not
supported. This means that pg_dumpall does not support parallel execution,
which can be a problem for larger clusters. In this case, you can use the
--globals-only option to backup only roles and tablespaces, and then backup
all the databases using pg_dump.
10
Physical Backup
What Is Physical Backup
Cold and Hot Backups
Replication Protocol
Creating Standalone Backup and Restoring From It
Continuous Archiving
Backup and Restore with Continuous Archiving
11
Physical Backup
Crash recovery mechanism is used: copy of data and WAL
+ restore speed
+ can restore a cluster to a certain point in time
− cannot restore a separate database, only the cluster as a whole
− can restore only on the same architecture and major version
Physical backup uses the crash recovery mechanism. This requires:
●
Base backup — a copy of cluster files (data files and system files)
●
A set of write-ahead logs needed to restore consistency
If the file system is already consistent (the backup was made when the
server was stopped correctly), then the WALs are not required.
However, with the WAL archive, it is possible to get cluster state at any point
in time. This way, the cluster can be restored to the state right before the
crash (or at any moment before that, if needed).
High restore speed and the ability to create a backup on the fly without
stopping the server make physical backup the main choice for routine
backup needs.
12
Hot or Cold
Cold backup Hot backup
Cluster files are
backed up when...
the server is off the server was shut
down incorrectly
the server is
running
WALs are...
not required required since the last
checkpoint
required for the
duration of the file
copy
special means
required
stored in
the file system
server
must not delete WALs
too early
Physical backup creates a copy of the database cluster files at some point.
If a backup is created while the server is stopped, it’s called “cold”. A cold
backup either contains consistent data (if the server was shut down
correctly), or contains all the logs necessary for recovery (for example, if the
OS has done a data snapshot). This simplifies restore, but requires that the
server is stopped.
If a backup is created while the server is running (which requires certain
additional actions since you can’t copy files just like that), it is called “hot”.
The procedure is more complicated, but can be performed without stopping
the server.
For hot backup, the copy of the cluster files will be inconsistent. However,
the crash recovery mechanism can also be successfully applied to restore
from backup. This will require the WALs for at least the duration of the file
copy.
13
Hot Backup
base backup
+
WAL
main server
WAL segments
select, insert
update, delete
p
g
_
b
a
s
e
b
a
c
k
u
p
Hot backups are created with the pg_basebackup tool. The image on the left
shows the main server. It processes incoming queries. At the same time,
WAL records are generated and the state of the databases changes (first in
the buffer cache, then on disk). WAL segments are cyclically reused.
At the bottom of the picture is a backup copy (usually located on another
server). It contains a copy of cluster files and a set of WAL segments.
14
Replication Protocol
Protocol
receiving the cluster files and WAL stream
backup and replication control commands
Served by walsender process
wal_level = replica
Replication slot
a server object for keeping track of sent WAL records
WAL segment is not deleted until fully read through the slot
To get cluster files and all WAL segments generated during file copying,
pg_basebackup connects to the server via the replication protocol. Despite
the name, the protocol is used not only for replication (which will be
discussed in the next lesson), but also for backup. The protocol allows to
request necessary files and to stream WAL records while files are being
copied.
To prevent the server from deleting WAL files too early, the replication slot
can be employed, which keeps track of the last WAL record received by the
client.
Establishing a connection via the replication protocol requires a certain
configuration.
First, the initiating role must have the REPLICATION attribute (or be a
superuser). This role must also have the necessary permission in the
pg_hba.conf configuration file.
Second, the max_wal_senders parameter must be set sufficiently high.
This parameter limits the number of simultaneously running walsender
processes serving replication protocol connections.
Third, the wal_level parameter, which determines the amount of information
in the WAL, must be set to replica.
The default settings already satisfy all these requirements (for a local
connection).
15
Standalone Backup
Full backup
connect to the server over the replication protocol
perform a checkpoint
copy the database cluster files into a specified directory
save all WAL segments generated during the copying process
Incremental backup
the same, but copy only pages changed since the last backup
So, what’s happening when pg_basebackup creates a backup?
As we already discussed, the utility connects to the server over the
replication protocol. First, it requests a checkpoint to ensure that all pages
are flushed to data files. After that, the cluster files are read from the server
and copied to the specified directory.
All WAL files generated by the server during the time from the checkpoint to
the end of file copying are also added to the backup. By default,
pg_basebackup is using a temporary replication slot to make sure that the
server won’t delete yet necessary WAL segments.
The resulting backup contains all the data necessary for restoring from it and
therefore is called standalone.
PostgreSQL 17+ supports incremental backups. Instead of copying all
database files, only pages that have changed since the previous backup are
copied. This requires much less time and disk space. In this case, backups
form a chain which starts from a full backup and continues with subsequent
incremental backups. To restore from an incremental backup, you need the
entire backup chain up to the full backup.
17
Restore From Backup
main server
WAL segments
select, insert
update, delete
select, insert
update, delete
backup server
base backup
+
WAL
During restore from a standalone backup, the cluster files and the included
WAL segments are deployed, for example, on another server (shown on the
right).
After the startup, PostgreSQL instance uses the WAL to perform recovery
and restore consistency, and is ready to go. The system is restored to the
point in time when the backup was made. Of course, the main server can go
far ahead in the meantime.
19
Continuous Archiving
Archiver process
Parameters
archive_mode = on
archive_command shell command to copy a WAL segment
to a separate storage
Algorithm
when switching to a new WAL segment, the archive_command is called
if the command is completed with the status 0, the segment is deleted
if the command returns anything else (or if the command is not specified),
the segment remains until the attempt is successful
The hot backup concept can be improved upon even further. Since we have
a copy of the database cluster files and WAL segments, then by constantly
saving new WAL records, we will be able to restore the system not only at
the time of copying files, but also at any point in time after that.
There usual way to go is to archive old WAL files before deleting them. This
is call continuous archiving and managed by the archiver background
process.
An arbitrary shell command can be defined as the archive_command
parameter to be used for copying. The mechanism itself is enabled by the
archive_mode = on.
The algorithm goes as follows. When a WAL segment is filled, the copy
command is called. If it is completed with a zero status, the segment can be
deleted safely. Otherwise, the segment (and the ones following it) will not be
deleted, and the server will periodically try to execute the command until it
returns 0.
Note that this method implies that WAL records get into the archive with
some delay.
20
WAL Archive
archive_command
WAL archive
+
base backup
main server
WAL segments
select, insert
update, delete
continuous
archiving
This figure shows the main server with continuous archiving set up. Filled
WAL segments are copied to a separate archive using the command defined
by the archive_command parameter. Usually, the archive is located on a
separate server, and it also stores the base backup (or several, from multiple
points in time).
21
Base Backup + Archive
Configured continuous WAL archiving
Backup via pg_basebackup
connects to the server over the replication protocol
performs a checkpoint
copies the cluster files into a specified directory
Restore
deploy the backup
set configuration parameters
(recovery_command to read WAL from the archive, recovery target)
create a recovery.signal file
start the server
WAL segments
are not required
To create a backup with continuous archiving configured, the same
pg_basebackup tool is used, but with a different set of parameters. The
difference is that the WAL files are not saved to the backup, since they are
already in the archive.
Restore is more complicated in this case. In addition to deploying the base
backup, some recovery settings must be specified:
●
restore_command (inverse of archive_command, it copies files from the
archive to the server)
●
Recovery target (recovery stopping point)
In addition, a recovery.signal file is needed. If present at server startup (the
contents of the file is ignored), the file tells the server to enter the managed
recovery mode.
22
Restore From Backup
restore_command
backup server
WAL archive
+
base backup
main server
WAL segments
did not make it
into the archive
The restore procedure (for example, after main server crash) is performed
as follows. A base backup is deployed on another (or the same) server and a
recovery.signal file is created. The server starts up and begins reading WAL
segments from the archive using restore_command and applying them.
Note that during file archiving, the last incomplete WAL segment at the main
server will not be archived. However, the segment can be manually added to
the pg_wal directory on the backup server, if this option is available. There
may be several such segments in case of archiving failure.
23
Restore From Backup
restore_command
WAL archive
+
base backup
WAL segments
recovery
target
backup server
main server
The backup server reads WAL segments from the pg_wal directory and
applies them (in the absence of a segment, making an attempt to copy it
from the archive), ultimately bringing the state of the databases up to date.
The maximum possible loss is the last incomplete WAL segment that has
not been archived, and only if it cannot be copied manually for some reason.
By default, all available log entries are applied. If a recovery target is
specified, recovery will stop after reaching it.
24
Restore From Backup
select, insert
update, delete
archive_command
backup server
WAL archive
+
base backup
main server
WAL segments
After that, the backup server goes into normal operation: processing
incoming queries, archiving WAL segments, and so on.
The restored server can act as the primary server from now on, but in this
case it should be deployed on sufficiently powerful hardware to avoid
performance degradation.
25
Takeaways
Logical backup creates SQL commands to restore the state of
database objects
COPY command, pg_dump and pg_dumpall utilities
Physical backup creates a copy of the cluster files + a set of
WAL files
pg_basebackup utility
WAL archive
continuous archiving
ability to restore the system to an arbitrary point in time
26
Practice
1. Create a database and a table in it with several rows.
2. Make a logical backup of the database using pg_dump.
Delete the database and restore it from the backup you made.
3. Make a standalone physical backup of the cluster using
pg_basebackup.
Modify the table.
Restore into a new cluster from the backup you made and verify
that the restored database does not contain any of the later
changes.
3. replica cluster has already been created in the course VM on port 5433.
Use this cluster to restore into.
The cluster data directory is /var/lib/postgresql/18/replica.
To connect to it, specify the port number: psql -p 5433
