Backup
Logical 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 Logical Backup
Backup and Restore of Individual Tables
Backup and Restore of Databases
Backup and Restore of the Whole Cluster
3
Logical Backup
SQL commands that create objects and fill them with data
+ can back up a separate object or database
+ can be restored to a different version or architecture
+(binary compatibility is not required)
− modest operation speed
− no point-in-time recovery
A logical backup contains a set of SQL commands that can be used to
restore a cluster, database, or specific tables.
During the recovery process, the necessary objects are created from the
logical backup and filled with data.
These commands can be run on a different server version or on a different
platform or architecture (binary compatibility is not required).
In particular, a logical backup can be used for long-term storage: you can
restore it even after upgrading the server to a higher version.
However, for a large database, these commands may take a while to
execute. In addition to that, a logical backup can only restore your database
to the point in time when the backup process was initiated.
4
The SQL COPY command
YKS Yakutsk (129.770
MJZ Mirny (114.039
KHV Khabarovsk (1
PKC Yelizovo (158.453
UUS Khomutovo (142.718
VVO Vladivostok (1
LED Pulkovo (30.2625
KGD Khrabrovo (20.5925
KEJ Kemerovo (86.1072
CEK Chelyabinsk (61.5033
MQF Magnitogorsk (5
PEE Perm (56.0211
SGC Surgut (73.4018
=# COPY table TO 'file';
=# COPY table FROM 'file';
file in the server file system owned by the PostgreSQL instance owner
you can select specific columns or use any query you need
new rows are added to already existing ones during recovery
the format
is adjustable
The COPY TO command writes the contents of a table or a query result to a
file or an output stream. Rows of views or partitioned tables can be copied
using a query.
COPY TO requires the pg_write_server_file role.
The COPY FROM command adds rows from a file or standard input stream
to the table. It is significantly faster than similar INSERT commands. You can
monitor the process in the pg_stat_progress_copy view.
COPY FROM requires the pg_read_server_files role (or read input stream
pg_execute_server_program).
Note: the COPY FROM command ignores the defined rules, although
integrity constraints and triggers are respected. If a table column should
receive values generated by a sequence when inserted, but COPY fills that
column explicitly, the sequence will not change its state (counter).
Both variants of the COPY command allow you to select the columns of the
table
and specify options such as:
●
FORMAT – data format (text, CSV or binary);
●
DELIMITER – field delimiter;
●
NULL – NULL text representation;
●
ENCODING – encoding;
●
HEADER – header describing the columns.
5
The psql \copy Command
YKS Yakutsk (129.770
MJZ Mirny (114.039
KHV Khabarovsk (1
PKC Yelizovo (158.453
UUS Khomutovo (142.718
VVO Vladivostok (1
LED Pulkovo (30.2625
KGD Khrabrovo (20.5925
KEJ Kemerovo (86.1072
CEK Chelyabinsk (61.5033
MQF Magnitogorsk (5
PEE Perm (56.0211
SGC Surgut (73.4018
=# \copy table to 'file'
=# \copy table from 'file'
the file is located in the client file system and can be accessed by the OS user who
has started psql
the data is transferred between the client and the server
the syntax and the supported features are the same as with COPY
There is a\copy meta command in psql – a variant of the COPY command
with the same syntax – that you can run on the client side.
The file must be available to the OS user running psql. The file is accessed
on the client, and only the content is sent to the server.
7
Database Backup
--
-- PostgreSQL database dum
--
-- Dumped from database ve
-- Dumped by pg_dump versi
SET statement_timeout = 0;
SET lock_timeout = 0;
SET
idle_in_transaction_sessio
SET client_encoding = 'UTF
SET standard_conforming_st
$ pg_dump -d base -f file
$ psql -f file
format: SQL commands
you can specify the database objects to be dumped
the new database must be created from the template0 template
roles and tablespaces must be created in advance
you should usually run ANALYZE after recovery
The pg_dump utility creates a full-scale database backup.
If you do not specify a file name (-f, --file), the utility will display the result
to the standard output stream.
The produced output is a script to be run in psql; it contains the commands
that will create the objects and fill them with data.
Additional keys can limit the set of objects: select specified tables or all
objects in specified schemas, upload only object definitions or only data, etc.
Note that the database to be restored should be cloned from template0
since all the changes made
in template1 will also make it into the backup.
Besides, all the required roles and tablespaces must be set up in advance.
Since these objects do not belong
to any particular database, they won’t be included into the dump.
Once the database is restored, it is a good idea to run the ANALYZE
command: it will collect statistics that the optimizer requires for query
planning.
9
The Custom Format
;
; Archive created at 2017-
; dbname: demo
; TOC Entries: 146
; Compression: -1
; Dump Version: 1.12-0
; Format: CUSTOM
;
; Selected TOC Entries:
;
2297; 1262 475453 DATABASE
5; 2615 475454 SCHEMA - bo
2298; 0 0 COMMENT - SCHEMA
$ pg_dump -d base -F c -f file
$ pg_restore -d base -j N file
an internal format with a table of contents
database objects to be restored can be selected at the time of recovery
recovery can be performed in parallel mode
The pg_dump utility supports several backup formats. By default, the plain
format is used; it provides pure psql commands.
The custom (-F c, --format = custom) format creates a backup
in a special format containing not only objects, but also a table of contents.
Having a TOC allows you to choose the objects to be restored right at the
time of recovery, not while making the dump. By default, the the custom
format output is compressed.
To restore the database, you need to run another utility: pg_restore.
It reads the file and converts it to psql commands. If you do not explicitly
provide the database name (in the -d option), all commands will be output
to the standard output stream. If you do specify a database name, the utility
will connect to that database and execute the commands directly, without
using psql.
To restore only some of the objects, you can use one of the following
approaches. The first one is to filter the objects to be restored, just like it is
done in pg_dump. pg_restore actually supports many of the same options
as pg_dump.
The second option is to use the TOC to retrieve the list of objects included
into the backup (via the --list option). Then you can edit this list manually:
delete the objects you do not need and pass the modified list to pg_restore
(via the --use-list option).
Uploading can be performed in parallel by specifying the number of threads
with the -j switch – this is another advantage of the custom format.
11
The Directory Format
$ pg_dump -d base -F d -j N -f directory
$ pg_restore -d base -j N directory
the directory contains a a TOC file and a separate file for each database object
database objects to be restored can be selected at the time of recovery
both dump and restore operations can be performed in the parallel mode
consistency
is guaranteed
by single data snapshot
Another backup format is Directry. Using this format, pg_dump produces a
whole directory instead of a single file; it contains the backed up objects and
the table of contents.
By default, all files in the directory are compressed.
Its advantage over the custom format is that such a backup can be created
concurrently using several processes (the number of processes is specified
in option -j, --jobs).
Naturally, the backup will contain consistent data even though it has been
created concurrently. Consistency is ensured by using a single data
snapshot for all parallel processes.
Recovery, as with the custom format, supports parallel streaming.
Otherwise, working with the directory format offers the same capabilities as
the formats discussed earlier: it supports the same options and approaches.
13
Format Comparison
plain custom directory tar
utility for
of recovery
psql pg_restore
compression gzip, lz4, or zstd
partial restore possible possible possible
parallel backup possible
parallel restore possible possible
This slide compares different features provided by different backup formats.
There is also a fourth format – tar. It was not discussed here, since
it does not introduce anything new and does not provide advantages over
other formats. In fact, this format is simply a tar-ed version of the directory
format, but it does not support compression or parallel execution.
14
Cluster Backup
--
-- PostgreSQL database clu
--
SET default_transaction_re
SET client_encoding = 'UTF
SET standard_conforming_st
--
-- Roles
--
CREATE ROLE postgres;
$ pg_dumpall -f file
$ psql -f file
format: SQL commands
dumps the whole cluster, including roles and tablespaces
the user must have access to all objects in the database cluster
parallel backups are not supported
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 should
be ran 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.
To start this process, pg_dumpall has to establish a connection with a
database.
By default, postgres or template1 is selected, but you can specify another
one.
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 table spaces, and unload the databases themselves separately
using parallel pg_dump.
16
Takeaways
You can make a logical backup of a whole cluster, a specific
database or a database object
Good for
small amounts of data
long-term storage during which the server can been upgraded
migration to a different platform
Not so good for
crash recovery with minimal data loss
17
Practice
1. Create two databases with tables and views, use pg_dumpall
to dump global cluster objects only, then create database
backups using pg_dump in parallel mode.
Restore the entire cluster on a different server using the backups
you created.
2. Create a table with two columns, one of which is the primary key
containing a number sequence. Simulate a case when during
uploading with the COPY command, new sequence values
are not generated, leading to a unique constraint violation
error when attempting to insert new rows.
2. Declare a column as GENERATED ALWAYS AS IDENTITY.
After completing the practice tasks, delete the created databases and logical
backup files.
18
Practice+
1. Create a table with multiple rows. Select such \copy parameters
so that when writing to a file, the correct data and NULL values
are mixed up due to unsuccessful code selection.
2. Using \copy, create a text file that denotes the missing values as
N/A. Load rows from this file into a table where missing values
are NULL.
3. Create a data table several MB in size
and get the sizes of logical backups in the custom format with
different compression methods. Also measure the time required
for each compression method during the backup process.
1. If you mistakenly choose a NULL code that matches actual data values in
the table columns, the resulting backup will prevent proper data restoration.
3. A way to generate the data:
SELECT (random()::numeric*(g.i))^(g.i) AS n
FROM generate_series(1,2_500) AS g(i);
You can measure the time using this command: /usr/bin/time -f% e
pg_dump...
After completing the practice tasks, delete the created databases and
temporary files.
