© Postgres Professional, 2015–2022
Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov
Translated by Alexander Meleshko
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.
Please send your feedback, comments and suggestions to:
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.
Logical backup
Physical backup
Logical backup
What is logical backup
Table backup
Database backup
Cluster backup
Logical backup
SQL commands to restore data from scratch
+ can backup a separate object or database
+ can recover on a cluster running another major PostgreSQL version
+ can recover on a different architecture
− low speed
There are two types of backup: logical and physical.
Logical backup is a set of SQL commands that restores a cluster (or a
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 (if there is compatibility at the command level) or on a different
architecture (so binary compatibility is not required).
However, for a large database, this mechanism is inefficient, since executing
all the commands 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
COPY: table backup
output a table or a query into a file, console or another program
insertion of rows from a file or console 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
The command writes a table (or the result of an arbitrary query) either to a
file or to the console, or sends it as input to another program. You can
specify parameters such as format (plain text, csv or binary), field
separators, NULL representation etc.
The alternative variant of the COPY command reads fields from a file or the
console and inserts them into a table. The table isn’t truncated, 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 parse 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.
pg_dump: database backup
outputs either an SQL script or an archive in a special format with a TOC
outputs either to the console or to a file
supports parallel execution
can define what objects to backup
(tables, schemas, only DML or only DDL, etc.)
SQL script via psql
custom format archive via pg_restore
(can define what objects to recover and supports parallel execution)
the new database must be created from template0 database roles and
tablespaces must be created in advance
The pg_dump utility creates a full-scale database backup. Depending on the
parameters, it provides either an SQL script containing commands that
create the required objects, or an archive in a custom format with a table of
Restoring using 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
archive 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 recovery supports
parallel execution.
The database for recovery 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 recovery, it is recommended to run
the ANALYZE command to collect fresh statistics.
pg_dumpall: cluster 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
via psql
pg_dumpall creates a backup of the entire cluster, including roles and
Since pg_dumpall requires access to all objects of all databases, it should
be ran by the superuser. pg_dumpall connects to each database in the
cluster one by one and makes a backup 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 key to backup only roles and tablespaces, and then backup all
the databases using pg_dump.
Physical backup
What is physical backup
Cold and hot backups
Replication protocol
Standalone backup
Continuous WAL archiving
Physical backup
Crash recovery mode: base backup + WAL
+ recovery speed
+ can recover to a certain point in time
− cannot recover a separate database, only the cluster as a whole
− can recover only on the same architecture and major PostgreSQL version
Physical backup uses the crash recovery mechanism. This requires:
a copy of cluster files (base backup),
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 WALs together with the base backup, you can recover the
system state at any point in time. This way, the database can be recovered
to the state right before the crash (or at any moment before that, if needed).
High recovery 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.
Hot or cold
Cold backup Hot backup
The file system is
backed up when...
the server is off the server was shut
down incorrectly
the server
is running
not required required after the last
required for the
duration of the
backup creation
special tools
stored in the
file system server must not
delete WALs too soon
Physical backup creates a copy of the file system at some point.
If the copy is created while the server is stopped, it’s called a “cold backup”.
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 recovery, but requires that
the server is stopped.
If a copy is created while the server is running (which requires certain
additional actions since you can't copy files just like that), it is called a “hot
backup”. The process is more complicated, but can be performed without
stopping the server.
During a hot backup, the file system will be inconsistent. However, the crash
recovery mechanism can also be successfully applied to backup recovery.
This will require the WALs for at least the time it takes to back up the OS
Standalone backup
A standalone backup contains both data files and WALs
Backup via pg_basebackup
connects to the server over the replication protocol
performs a checkpoint
copies the file system into a specified directory
saves all WAL segments generated during the copying process
deploy the standalone backup
start the server
Hot backups are created with the pg_basebackup tool.
First, it performs a checkpoint. Then, the cluster file system is copied.
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. The resulting backup is
called standalone because it contains all the data necessary for recovery.
All you need to restore using a standalone backup is to deploy the backup
and start the server. It will use the WALs to recover consistency on startup if
necessary, and will be ready to go.
Replication protocol
receiving the WAL stream
backup and recovery control commands
Managed by the wal_sender process
wal_level = replica
Replication slot
a server object that consumes WAL records
remembers which record was read last
WAL segments are not deleted until fully read through the slot
The replication protocol allows processes to connect to the server and
collect all WAL files generated during file copying. Despite the name, the
protocol is used not only for replication (which will be discussed in the next
topic), but also for backup. The protocol can stream WAL entries while data
files are being copied.
To prevent the server from deleting WAL files too early, the replication slot
can be employed.
Establishing a connection over the replication protocol requires a certain
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 wal_sender
processes serving replication protocol connections.
Third, the wal_level parameter, which determines the amount of information
in the WAL, must be set to replica.
Starting from PostgreSQL 10, the default settings already satisfy all these
requirements (for a local connection).
Standalone backup
base backup
main server
WAL segments
select, insert
update, delete
The image on the left shows the main server. It processes incoming queries.
At the same time, WAL records are formed and the state of the databases
changes (first in the buffer cache, then on disk). WAL segments are
cyclically overwritten (that is, old segments are deleted as new ones are
created, since the file names are unique).
At the bottom of the picture is a backup archive (usually located on another
server). It contains a base copy of the data and a set of WAL files.
main server
WAL segments
select, insert
update, delete
select, insert
update, delete
backup server
base backup
During recovery, the base backup, including the necessary WAL files, is
deployed, for example, on another server (shown on the right).
After the server starts, it restores consistency and is ready to go.
The system is recovered to the point in time when the backup was made.
Of course, the main server can go far ahead in the meantime.
WAL archive
File archive
WAL segments are archived as they are filled
controlled by the server
archiving happens with a delay
Streaming archive
a stream of WAL records is continuously recorded into the archive
external tools required
delays are minimal
The hot backup concept can be improved upon even further. Since we have
a copy of the file system and WALs, then by constantly saving new logs, 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 are two ways to go about it. The first is to archive old WAL files before
reusing them. There's a server setting for that. Unfortunately, with this
option, an incomplete WAL file will not be archived until the server switches
to writing to another WAL file.
The second way is to continuously read WAL entries using the replication
protocol and write them into the same archive. This way, delays are minimal,
but a separate tool is needed to receive the data stream and archive it.
WAL file archive
archiver process
archive_mode = on
archive_command a shell command to copy a WAL segment
to a separate storage
when switching to a new WAL segment, the archive_command command is
called for the filled segment
if the command terminates with the status 0, the segment is deleted
if the command returns anything else (in particular, if the command is not
specified), the segment remains until the attempt is successful
The WAL files archive is managed by the archiver background process.
An arbitrary shell command to be used for copying can be defined in the
archive_command parameter. The mechanism itself is enabled by the
parameter archive_mode = on.
The algorithm goes as follows. When a WAL segment is filled, the copy
command is called. If it terminates with a 0 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.