Backup and Replication

This course keeps the official topic order and exposes the English handout as readable, native Markdown for review and future editorial work.

Source status: 官方 PDF 已提取为原生 Markdown

Translation baseline: English baseline. The current English archive is the working baseline; the Russian edition is retained for chapter-level cross-checking.

Course scope

Logical and physical backup, WAL archiving, physical and logical replication, and cluster architectures.

Source status

Contents

Supplementary materials

• dba3_new_features.pdf
• dba3_student_guide.pdf

Editorial status

The available English handout is extracted from the official PDF. Where no current English archive was found, the route remains ready for a later source edition.

• Version matrix
• Work checklist
• Sources and permissions

00 Lesson 00: Backup and Replication / PostgreSQL 16

Page 1: Backup and Replication / PostgreSQL 16

Copyright © Postgres Professional, 2017–2026 Authors: Egor Rogov, Pavel Luzanov, Ilya Bashtanov, Alexey Beresnev Translated by: Liudmila Mantrova, Alexander Meleshko, Elena Sharafutdinova 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.com

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: For Administrators

{#source-figure-002 caption=“Source slide page 2: For Administrators”}

• DBA2, 4 days

Speaker notes

Prior knowledge of the basics of the SQL language is required to complete all courses. While we don’t provide a dedicated SQL course, there are plenty of books and resources for self-study.

For administrators, we offer the following courses.

The DBA1 basic course provides information about the PostgreSQL architecture, installation process, server configuration and management. It covers the topics of access control, backup and replication, among others.

The DBA2 course focuses on determining appropriate configuration parameters based on an understanding of the internals of the server. It also talks about monitoring the server and using feedback for iterative parameter tweaking.

The DBA3 covers backups, as well as configuration and usage scenarios of physical and logical replication. It also provides an overview of the methods and pitfalls of designing highly accessible, scalable clusters.

The DBS course focuses on client-server connections and access control features within the database system itself.

The QPT course examines the mechanisms for scheduling and executing queries, the configuration of instance parameters related to performance, and the options for finding problematic queries and optimizing them.

The PGPRO course discusses additional features provided by the Postgres Pro Enterprise DBMS.

Page 3: For Backend Developers

{#source-figure-003 caption=“Source slide page 3: For Backend Developers”}

• DEV2, 4 days

Speaker notes

For backend developers, we offer the following courses.

The basic course is the DEV1 course. It provides general information about the architecture of PostgreSQL and the use of basic database objects; server-side programming in SQL and PL/pgSQL languages using basic data types (including the composite type and arrays), as well as fronted interactions.

The DEV2 course examines the features of the server’s internal structure that affect the development of application code, and comprehensively discusses extensibility: the ability to extend server mechanisms with your own code, which allows you to use PostgreSQL to solve a wide variety of tasks.

The DBS, QPT, and PGPRO courses are intended for both developers and administrators.

Developers may also be interested in administration courses if there is a need to study the internal structure of PostgreSQL in more detail, as well as in the case when there is no dedicated administrator role on the project.

https://postgrespro.com/community/courses

Page 4: About DBA3 course

Duration: 2 days

• Prior knowledge requirements
  • SQL fundamentals
  • proficiency in Unix operating systems
  • familiarity with PostgreSQL architecture as covered in the DBA1 course
• What skills will be acquired
  • performing backups using various methods
  • configuring servers for physical and logical replication
  • understanding replication use cases
  • understanding approaches to building clusters

Speaker notes

This course covers backup capabilities and strategies, as well as physical and logical replication mechanisms and their use cases. It also provides an overview of the methods and pitfalls of designing highly available, scalable clusters.

Page 5: Materials Used

Pre-configured virtual machine

• Xubuntu 24.04 OS
• PostgreSQL 16 (three database clusters)
• documentation in English
• Course Handouts
  • presentations, demonstrations, practice assignments and assignment keys
  • (in html and pdf)

Speaker notes

If you are taking the course on your own, start with the Student’s guide. Among other things, it shows where to download and how to use the virtual machine

and other materials. All course materials, including the Student’s Guide, are available at: https://postgrespro.com/community/courses/DBA3

Completing practical assignments is very important to gain the basic skills in working with PostgreSQL. Be sure to try to complete the assignments yourself first, and then review the provided keys, even if the assignment did not raise questions. Assignment keys may contain additional information that is not mentioned in presentations and demonstrations. After completing a task, make sure to remove any objects you created and reset any settings you changed, as they might interfere with later tasks.

Course handouts (presentations, demonstrations, practical assignments and keys) are available in two formats. The HTML is good for browsing and copying code snippets, while pdf is paginated and convenient for printing.

Page 6: Setup and Configuration

The course uses three PostgreSQL instances: alpha, beta, and gamma:

student$ sudo pg_ctlcluster 16 alpha start

student$ sudo pg_ctlcluster 16 beta start

student$ sudo pg_ctlcluster 16 gamma start

In the demonstrations, commands on different instances have different prompts. All practice assignments are run as OS user student. A user and a database with the same name are already created in the PostgreSQL instances in order to allow you to run psql with no parameters. The server aplha runs on the standard port 5432:

student$ psql

α=> SHOW server_version;

           server_version
------------------------------------
 16.14 (Ubuntu 16.14-1.pgdg24.04+1)
(1 row)

The server beta is on 5433. Just the port is enough to connect in psql:

student$ psql -p 5433

β=> SHOW server_version;

           server_version
------------------------------------
 16.14 (Ubuntu 16.14-1.pgdg24.04+1)
(1 row)

The server gamma is on 5434.

student$ psql -p 5434

γ=> SHOW server_version;

           server_version
------------------------------------
 16.14 (Ubuntu 16.14-1.pgdg24.04+1)
(1 row)

While student is not a superuser, it can create databases and roles and can connect over the replication protocol. It also belongs to some predefined group roles:

α=> \du student

                  List of roles
 Role name |             Attributes
-----------+-------------------------------------
 student   | Create role, Create DB, Replication

α=> \drg student

                     List of role grants
 Role name |       Member of        |   Options    | Grantor
-----------+------------------------+--------------+----------
 student   | pg_checkpoint          | INHERIT, SET | postgres
 student   | pg_create_subscription | INHERIT, SET | postgres
 student   | pg_monitor             | INHERIT, SET | postgres
 student   | pg_read_server_files   | INHERIT, SET | postgres
 student   | pg_write_server_files  | INHERIT, SET | postgres
(5 rows)

In addition, student has privileges on some objects that are only available to superusers by default. Specifically, it can change parameters:

Page 7: Page 7

α=> SELECT parname, (acl).privilege_type
FROM (

  SELECT parname, aclexplode(paracl) acl
  FROM pg_parameter_acl
)
WHERE (acl).grantee = user::regrole;

           parname            | privilege_type
------------------------------+----------------
 archive_command              | ALTER SYSTEM
 archive_mode                 | ALTER SYSTEM
 archive_timeout              | ALTER SYSTEM
 checkpoint_timeout           | ALTER SYSTEM
 hot_standby_feedback         | ALTER SYSTEM
 listen_addresses             | ALTER SYSTEM
 log_recovery_conflict_waits  | ALTER SYSTEM
 max_connections              | ALTER SYSTEM
 max_slot_wal_keep_size       | ALTER SYSTEM
 max_standby_streaming_delay  | ALTER SYSTEM
 max_wal_size                 | ALTER SYSTEM
 min_wal_size                 | ALTER SYSTEM
 restore_command              | ALTER SYSTEM
 synchronous_commit           | ALTER SYSTEM
 synchronous_standby_names    | ALTER SYSTEM
 wal_keep_size                | ALTER SYSTEM
 wal_level                    | ALTER SYSTEM
 wal_receiver_status_interval | ALTER SYSTEM
(18 rows)

And can run system functions (permitted in template1 database and, as a result, in any database created with CREATE DATABASE, unless another template is specified):

α=> SELECT proname, (acl).privilege_type
FROM (
  SELECT proname, aclexplode(proacl) acl
  FROM pg_proc
)
WHERE (acl).grantee = user::regrole;

            proname             | privilege_type
--------------------------------+----------------
 pg_hba_file_rules              | EXECUTE
 pg_read_binary_file            | EXECUTE
 pg_stat_file                   | EXECUTE
 pg_stat_reset_replication_slot | EXECUTE
 pg_log_standby_snapshot        | EXECUTE
 pg_switch_wal                  | EXECUTE
 pg_wal_replay_resume           | EXECUTE
 pg_ls_dir                      | EXECUTE
 pg_read_binary_file            | EXECUTE
(9 rows)

Local connections for all users do not require a password:

α=> \c - postgres

You are now connected to database "student" as user "postgres".

α=> SELECT type, database, user_name, auth_method FROM pg_hba_file_rules();

 type  |   database    | user_name |  auth_method
-------+---------------+-----------+---------------
 local | {all}         | {all}     | trust
 host  | {all}         | {all}     | scram-sha-256
 host  | {all}         | {all}     | scram-sha-256
 local | {replication} | {all}     | trust
 host  | {replication} | {all}     | scram-sha-256
 host  | {replication} | {all}     | scram-sha-256
(6 rows)

Page 8: Course Outline and Schedule

Each training day is approximately 8 hours + lunch (1 hour)

• Each topic usually consists of
  • presentation and demonstration: ~40-70 minutes
  • practice: ~20-40 minutes, including a break

Page 9: Day 1

Backup

• 1. Logical Backup
• 2. Base Backup
• 3. Write-Ahead Log Archive
• Replication
  • 4. Physical Replication

Page 10: Day 2

Replication (continued)

• 5. Switchover to Replica 06. Logical Replication 07. Use Cases Clustering Technologies
  • 8. Overview

01 Lesson 01: Backup / Logical Backup

Page 1: Backup / Logical Backup

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

• What is Logical Backup
• Backup and Restore of Individual Tables
• Backup and Restore of Databases
• Backup and Restore of the Whole Cluster

Page 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

Speaker notes

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.

https://postgrespro.com/docs/postgresql/16/backup-dump

Page 4: The SQL COPY command

{#source-figure-004 caption=“Source slide page 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

• 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

Speaker notes

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. https://postgrespro.com/docs/postgresql/16/sql-copy

Page 5: The psql \copy Command

{#source-figure-005 caption=“Source slide page 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

• 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

Speaker notes

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.

https://postgrespro.com/docs/postgresql/16/app-psql#APP-PSQL-META-CO MMANDS-COPY

Page 6: The COPY command

Create a database and a table.

α=> CREATE DATABASE backup_logical;

CREATE DATABASE

α=> \c backup_logical

You are now connected to database "backup_logical" as user "student".

α=> CREATE TABLE t(
  id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  s text
);

CREATE TABLE

α=> INSERT INTO t (s) VALUES ('Hello World!'), (''), (NULL);

INSERT 0 3

α=> SELECT * FROM t;

 id |      s
----+--------------
  1 | Hello World!
  2 |
  3 |
(3 rows)

Print the table contents:

α=> COPY t TO stdout;

1
Hello World!
2
3
\N

You can see how empty rows are different from the NULL values. The output format can be configured quite flexibly. You can change the separator, the NULL display, etc. For example:

α=> COPY t TO stdout WITH (NULL '<NULL>', DELIMITER ',');

1,Hello World!
2,
3,<NULL>

α=> COPY (SELECT * FROM t WHERE s IS NOT NULL) TO stdout;

1
Hello World!
2

This way, you can save query results, presentation data, etc. The command supports output in CSV format, which is supported almost universally.

α=> COPY t TO stdout WITH (FORMAT csv);

1,Hello World!
2,""
3,

Input from file or console works in a similar manner.

α=> TRUNCATE TABLE t;

TRUNCATE TABLE

However, for input from console, the end of file character (a backslash and a period) is required. In order for the data to load, you must specify the same parameters during input as were specified during output. You can also add:

Page 7: Page 7

α=> COPY t FROM stdin WHERE id != 2;
1
Привет, мир!
2
3
\N
\.

COPY 2

Here are the table contents (with NULL output configured in psql for clarity):

α=> \pset null '\\N'

Null display is "\N".

α=> SELECT * FROM t;

 id |      s
----+--------------
  1 | Привет, мир!
  3 | \N
(2 rows)

Load all the rows:

α=> TRUNCATE TABLE t;

TRUNCATE TABLE

α=> COPY t FROM stdin;
1
Привет, мир!
2
3
\N
\.

COPY 3

α=> SELECT * FROM t;

 id |      s
----+--------------
  1 | Привет, мир!
  2 |
  3 | \N
(3 rows)

Page 8: Database Backup

{#source-figure-008 caption=“Source slide page 8: 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

• 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

Speaker notes

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.

https://postgrespro.com/docs/postgresql/16/app-pgdump

Page 9: pg_dumpall Utility

pg_dump, ran without additional parameters, issues SQL commands that create all objects in the current database: To get commands to recover to a system with a different set of roles, use the options: -O, –no-owner — do not generate commands to set the owner of objects; -x, –no-acl — do not generate commands to set privileges

student$ pg_dump -d backup_logical --no-acl

--
-- PostgreSQL database dump
--

\restrict gJnV8i7WLYQP14BMyhefNauB3FcoS7S01BBdaZbOzlEf6c8cdx6vlOAs0g7GdGX

-- Dumped from database version 16.14 (Ubuntu 16.14-1.pgdg24.04+1)
-- Dumped by pg_dump version 16.14 (Ubuntu 16.14-1.pgdg24.04+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: t; Type: TABLE; Schema: public; Owner: student
--

CREATE TABLE public.t (
    id integer NOT NULL,
    s text
);

ALTER TABLE public.t OWNER TO student;

--
-- Name: t_id_seq; Type: SEQUENCE; Schema: public; Owner: student
--

ALTER TABLE public.t ALTER COLUMN id ADD GENERATED ALWAYS AS IDENTITY (
    SEQUENCE NAME public.t_id_seq
    START WITH 1
    INCREMENT BY 1
    NO MINVALUE
    NO MAXVALUE
    CACHE 1
);

--
-- Data for Name: t; Type: TABLE DATA; Schema: public; Owner: student
--

COPY public.t (id, s) FROM stdin;
1
Привет, мир!
2
3
\N
\.

--
-- Name: t_id_seq; Type: SEQUENCE SET; Schema: public; Owner: student
--

Page 10: Page 10

SELECT pg_catalog.setval('public.t_id_seq', 3, true);

--
-- Name: t t_pkey; Type: CONSTRAINT; Schema: public; Owner: student
--

ALTER TABLE ONLY public.t
    ADD CONSTRAINT t_pkey PRIMARY KEY (id);

--
-- PostgreSQL database dump complete
--

\unrestrict gJnV8i7WLYQP14BMyhefNauB3FcoS7S01BBdaZbOzlEf6c8cdx6vlOAs0g7GdGX

Here, pg_dump has created the table t and populated it using the COPY command from before. The \--column-inserts\\ flag enables the use of INSERT commands, but the process will take significantly longer. Logical backups in plain format may include malicious metacommands, for example, \gexec. To avoid such issues when running the script in psql, pg_dump prepends it with the \restrict metacommand with the key: This command switches on the restricted mode, making psql ignore all metacommands except for \unrestrict with the same key. The keys are handy for dumping and uploading data in segments: -s, –schema-only — back up only object definitions without data -a, –data-only — back up only data without creating objects For recovering to a system already containing some data (or, on the other hand, to a completely clean system): -c, –clean — generate DROP commands for created objects -C, –create — generate commands for creating a database and connecting to it

student$ pg_dump --create -d backup_logical | grep 'CREATE DATABASE'

CREATE DATABASE backup_logical WITH TEMPLATE = template0 ENCODING = 'UTF8'
LOCALE_PROVIDER = libc LOCALE = 'en_US.UTF-8';

Some flags specify the objects to be dumped: -n, –schema — template for schema names -t, –table — template for table names Or, conversely, not to be, while dumping everything else: -N, –exclude-schema — template for schema names -T, –exclude-table — template for table names For example, recover the table ’t’ to another database on another server.

student$ psql -p 5433

β=> CREATE DATABASE backup_logical_1;

CREATE DATABASE

student$ pg_dump --table=t -d backup_logical | psql -p 5433 -d backup_logical_1

SET
SET
SET
SET
SET
 set_config
------------
(1 row)

SET
SET
SET
SET
SET
SET
CREATE TABLE

Page 11: Page 11

ALTER TABLE
ALTER TABLE
COPY 3
 setval
--------
      3
(1 row)

ALTER TABLE

β=> \c backup_logical_1

You are now connected to database "backup_logical_1" as user "student".

β=> SELECT * FROM t;

 id |      s
----+--------------
  1 | Привет, мир!
  2 |
  3 |
(3 rows)

Page 12: The Custom Format

{#source-figure-012 caption=“Source slide page 12: 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

• 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

Speaker notes

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.

https://postgrespro.com/docs/postgresql/16/app-pgdump

Page 13: pg_dump: the \’custom\’ format

{#source-figure-013 caption=“Source slide page 13: pg_dump: the \’custom\’ format”}

The ’plain’ format has a serious limitation: you have to specify all the objects you will need in your backup before making the dump. The ’custom’ format allows you to make a full dump first, then specify which objects to recover from it later.

student$ pg_dump --format=custom -d backup_logical -f /home/student/tmp/backup_logical.custom

This is done with the pg_resore tool. Time to recover ’t’ again.

β=> DROP TABLE t;

DROP TABLE

student$ pg_restore --table=t -p 5433 -d backup_logical_1 --no-acl
/home/student/tmp/backup_logical.custom

pg_restore will recognize the dump format automatically, there is no need to specify it. pg_restore also understands all the flags that pg_dump does, and more: -I, –index — load specific indexes -P, –function — load certain functions -T, –trigger — load specific triggers. Let’s check how the table recovered:

β=> SELECT * FROM t;

 id |      s
----+--------------
  1 | Привет, мир!
  2 |
  3 |
(3 rows)

Another example: restore the entire original backup_logical database to another server.

student$ pg_restore --create -p 5433 -d postgres --no-acl /home/student/tmp/backup_logical.custom

Here, we have specified the postgres database. If another database was specified, pg_restore would have created the necessary database and immediately switched to it. Check:

β=> \c backup_logical

You are now connected to database "backup_logical" as user "student".

β=> SELECT * FROM t;

 id |      s
----+--------------
  1 | Привет, мир!
  2 |
  3 |
(3 rows)

Page 14: The Directory Format

{#source-figure-014 caption=“Source slide page 14: The Directory Format”}

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

Speaker notes

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.

https://postgrespro.com/docs/postgresql/16/functions-admin#FUNCTIONS-S NAPSHOT-SYNCHRONIZATION

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.

Page 15: pg_dump: the ’directory’ format

The \’direcotry\’ format, notably, supports dumping in parallel.

student$ pg_dump --format=directory \
  --jobs=2 \
  -d backup_logical \
  -f /home/student/tmp/backup_logical.directory

Consistency is guaranteed: all processes use the same snapshot. Take a look inside the directory:

student$ ls -l /home/student/tmp/backup_logical.directory

total 12
-rw-rw-r-- 1 student student   60 Sep 16 23:52 3440.dat.gz
-rw-rw-r-- 1 student student 4985 Sep 16 23:52 toc.dat

There is a table of contents file, and a file for each dumped object (we have only one):

student$ zcat /home/student/tmp/backup_logical.directory/3440.dat.gz

1
Привет, мир!
2
3
\N
\.

To restore from a backup, first disconnect from the backup_logical database:

β=> \q

The --clean key generates a command to delete the existing database:

student$ pg_restore --clean \
  --create \
  --jobs=2 \
  -p 5433 \
  -d postgres /home/student/tmp/backup_logical.directory \
  --no-acl

student$ psql --pset null='\N' -p 5433 -d backup_logical -c 'SELECT * FROM t'

 id |      s
----+--------------
  1 | Привет, мир!
  2 |
  3 | \N
(3 rows)

Page 16: 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

Speaker notes

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.

Page 17: Cluster Backup

{#source-figure-017 caption=“Source slide page 17: Cluster Backup”}

--
-- PostgreSQL database clu
--

SET default_transaction_re
SET client_encoding = 'UTF
SET standard_conforming_st

--
-- Roles
--

CREATE ROLE postgres;

• 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

Speaker notes

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.

https://postgrespro.com/docs/postgresql/16/app-pg-dumpall

Page 18: The pg_dumpall Utility

The pg_dump utility is good for dumping a single database, but it never dumps shared database cluster objects such as roles and tablespaces. To create a full cluster backup, you need to use pg_dumpall. The pg_dumpall, pg_dump, and pg_restore utilities do not require any separate privileges, but the role performing them must have privileges to read (create) all affected objects. For example, pg_dump can be used by the database owner. But since it is necessary to have access to all databases to copy the cluster, we run pg_dumpall as the superuser.

student$ pg_dumpall --clean -U postgres -f /home/student/tmp/alpha.sql

The backup includes: commands that the pg_dump backs up for each database commands for shared cluster objects (roles and tablespaces) Commands for shared objects can be obtained separately:

student$ pg_dumpall --clean --globals-only -U postgres

--
-- PostgreSQL database cluster dump
--

\restrict e0B6e88kQ1UH20c5WvNCp9aKNhv1EmAjMAub1ie8JhGQUF6v4A2nXO4FH1ZQzZa

SET default_transaction_read_only = off;

SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;

--
-- Drop roles
--

DROP ROLE postgres;
DROP ROLE student;

--
-- Roles
--

CREATE ROLE postgres;
ALTER ROLE postgres WITH SUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION
BYPASSRLS PASSWORD
'SCRAM-SHA-256$4096:AsXBHJhp+lTo5cMW8rTWVA==$w9/d0pa/3sFrh+sLgBYnALVW+k7RETzghidz4iOF850=:
A0khfLkZ1ZZrQUD39l/Uiro+tu/0FoROEOk9UNyzqx0=';
CREATE ROLE student;
ALTER ROLE student WITH NOSUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION
NOBYPASSRLS PASSWORD
'SCRAM-SHA-256$4096:79AZA/AciQ7/18/dXmAZZQ==$iy9RopOHolUKGAK/JK2K/pUdhj2GMsZLTgbWc8vH6sc=:
ZiyLRbFp1NxhgDuzBGithNum7+5zdbGG8XeAZJ6MR7A=';

--
-- User Configurations
--

--
-- Role memberships
--

GRANT pg_checkpoint TO student WITH INHERIT TRUE GRANTED BY postgres;
GRANT pg_create_subscription TO student WITH INHERIT TRUE GRANTED BY postgres;
GRANT pg_monitor TO student WITH INHERIT TRUE GRANTED BY postgres;
GRANT pg_read_server_files TO student WITH INHERIT TRUE GRANTED BY postgres;
GRANT pg_write_server_files TO student WITH INHERIT TRUE GRANTED BY postgres;

--
-- Role privileges on configuration parameters
--

Page 19: Page 19

GRANT ALTER SYSTEM ON PARAMETER archive_command TO student;
GRANT ALTER SYSTEM ON PARAMETER archive_mode TO student;
GRANT ALTER SYSTEM ON PARAMETER archive_timeout TO student;
GRANT ALTER SYSTEM ON PARAMETER checkpoint_timeout TO student;
GRANT ALTER SYSTEM ON PARAMETER hot_standby_feedback TO student;
GRANT ALTER SYSTEM ON PARAMETER listen_addresses TO student;
GRANT ALTER SYSTEM ON PARAMETER log_recovery_conflict_waits TO student;
GRANT ALTER SYSTEM ON PARAMETER max_connections TO student;
GRANT ALTER SYSTEM ON PARAMETER max_slot_wal_keep_size TO student;
GRANT ALTER SYSTEM ON PARAMETER max_standby_streaming_delay TO student;
GRANT ALTER SYSTEM ON PARAMETER max_wal_size TO student;
GRANT ALTER SYSTEM ON PARAMETER min_wal_size TO student;
GRANT ALTER SYSTEM ON PARAMETER restore_command TO student;
GRANT ALTER SYSTEM ON PARAMETER synchronous_commit TO student;
GRANT ALTER SYSTEM ON PARAMETER synchronous_standby_names TO student;
GRANT ALTER SYSTEM ON PARAMETER wal_keep_size TO student;
GRANT ALTER SYSTEM ON PARAMETER wal_level TO student;
GRANT ALTER SYSTEM ON PARAMETER wal_receiver_status_interval TO student;

\unrestrict e0B6e88kQ1UH20c5WvNCp9aKNhv1EmAjMAub1ie8JhGQUF6v4A2nXO4FH1ZQzZa

--
-- PostgreSQL database cluster dump complete
--

Recovery is done via psql, there is no other way around it.

student$ psql -p 5433 -U postgres -f /home/student/tmp/alpha.sql

SET
SET
SET
DROP DATABASE
DROP DATABASE
psql:/home/student/tmp/alpha.sql:26: ERROR:  current user cannot be dropped
psql:/home/student/tmp/alpha.sql:27: ERROR:  role "student" cannot be dropped because
some objects depend on it
DETAIL:  owner of database backup_logical_1
1 object in database backup_logical_1
psql:/home/student/tmp/alpha.sql:34: ERROR:  role "postgres" already exists
ALTER ROLE
psql:/home/student/tmp/alpha.sql:36: ERROR:  role "student" already exists
ALTER ROLE
psql:/home/student/tmp/alpha.sql:48: NOTICE:  role "student" has already been granted
membership in role "pg_checkpoint" by role "postgres"
GRANT ROLE
psql:/home/student/tmp/alpha.sql:49: NOTICE:  role "student" has already been granted
membership in role "pg_create_subscription" by role "postgres"
GRANT ROLE
psql:/home/student/tmp/alpha.sql:50: NOTICE:  role "student" has already been granted
membership in role "pg_monitor" by role "postgres"
GRANT ROLE
psql:/home/student/tmp/alpha.sql:51: NOTICE:  role "student" has already been granted
membership in role "pg_read_server_files" by role "postgres"
GRANT ROLE
psql:/home/student/tmp/alpha.sql:52: NOTICE:  role "student" has already been granted
membership in role "pg_write_server_files" by role "postgres"
GRANT ROLE
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
SET

Page 20: Page 20

SET
SET
SET
SET
 set_config
------------
(1 row)

SET
SET
SET
SET
UPDATE 1
DROP DATABASE
CREATE DATABASE
ALTER DATABASE
You are now connected to database "template1" as user "postgres".
SET
SET
SET
SET
SET
 set_config
------------
(1 row)

SET
SET
SET
SET
COMMENT
ALTER DATABASE
You are now connected to database "template1" as user "postgres".
SET
SET
SET
SET
SET
 set_config
------------
(1 row)

SET
SET
SET
SET
REVOKE
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
SET
SET
SET
SET
SET
 set_config
------------
(1 row)

SET
SET
SET
SET
CREATE DATABASE
ALTER DATABASE
You are now connected to database "backup_logical" as user "postgres".
SET
SET
SET

Page 21: Page 21

SET
SET
 set_config
------------
(1 row)

SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
ALTER TABLE
COPY 3
 setval
--------
      3
(1 row)

ALTER TABLE
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
SET
SET
SET
SET
SET
 set_config
------------
(1 row)

SET
SET
SET
SET
DROP DATABASE
CREATE DATABASE
ALTER DATABASE
You are now connected to database "postgres" as user "postgres".
SET
SET
SET
SET
SET
 set_config
------------
(1 row)

SET
SET
SET
SET
COMMENT
SET
SET
SET
SET
SET
 set_config
------------
(1 row)

SET
SET
SET
SET
CREATE DATABASE

Page 22: Page 22

ALTER DATABASE
You are now connected to database "student" as user "postgres".
SET
SET
SET
SET
SET
 set_config
------------
(1 row)

SET
SET
SET
SET
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT

During recovery, errors related to irremovable objects or objects already existing may come up. These are normal and do not affect recovery in this case.

student$ psql -p 5433

β=> SELECT datname FROM pg_database;

     datname
------------------
 template0
 backup_logical_1
 template1
 backup_logical
 postgres
 student
(6 rows)

β=> \c backup_logical

You are now connected to database "backup_logical" as user "student".

β=> SELECT * FROM t;

 id |      s
----+--------------
  1 | Привет, мир!
  2 |
  3 |
(3 rows)

The impact of row level security

If some tables have row level security on (more on that in the Row Level Security topic in the DBS course), you could end up with incomplete data and not even notice. To avoid that, before running COPY, set row\_security to off. This way, when a row level security policy triggers, the process will throw an explicit error. A simple example. Set up a policy that prohibits displaying empty rows, then enable it for the table owner.

α=> CREATE POLICY t_s_not_null ON t USING (s IS NOT NULL);

CREATE POLICY

α=> ALTER TABLE t ENABLE ROW LEVEL SECURITY;

ALTER TABLE

α=> ALTER TABLE t FORCE ROW LEVEL SECURITY;

ALTER TABLE

Now COPY returns just two rows:

α=> COPY t TO stdout;

Page 23: Page 23

1
Привет, мир!
2

But when the parameter is set to off, we get an error:

α=> SET row_security = off;

SET

α=> COPY t TO stdout;

ERROR:  query would be affected by row-level security policy for table "t"
HINT:  To disable the policy for the table's owner, use ALTER TABLE NO FORCE ROW LEVEL
SECURITY.

Both pg_dump and pg_dumpall automatically set row\_security = off, no additional actions needed:

student$ pg_dump -d backup_logical -t t > /dev/null

pg_dump: error: query failed: ERROR:  query would be affected by row-level security
policy for table "t"
HINT:  To disable the policy for the table's owner, use ALTER TABLE NO FORCE ROW LEVEL
SECURITY.
pg_dump: detail: Query was: COPY public.t (id, s) TO stdout;

The tool attempts to COPY the rows and returns the error: unable to copy all rows from t. If you want to copy just the rows that are available, you can use –enable-row-security:

student$ pg_dump -d backup_logical -t t --enable-row-security | grep -A3 '^COPY'

COPY public.t (id, s) FROM stdin;
1
Привет, мир!
2
\.

To bypass row level security, the user must have the BYPASSRLS attribute:

α=> \c - postgres

You are now connected to database "backup_logical" as user "postgres".

α=> ALTER USER student BYPASSRLS;

ALTER ROLE

Now, all queries by student ignore RLS policies:

student$ pg_dump -d backup_logical -t t --data-only

--
-- PostgreSQL database dump
--

\restrict wtj1ko2okrK5ZUoPNI4415UxcGzgIzm5dFN3ump9PbcyOrMHALPxQvSN500tX8d

-- Dumped from database version 16.14 (Ubuntu 16.14-1.pgdg24.04+1)
-- Dumped by pg_dump version 16.14 (Ubuntu 16.14-1.pgdg24.04+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

--
-- Data for Name: t; Type: TABLE DATA; Schema: public; Owner: student
--

COPY public.t (id, s) FROM stdin;
1
Привет, мир!
2
3
\N
\.

--
-- Name: t_id_seq; Type: SEQUENCE SET; Schema: public; Owner: student
--

Page 24: Page 24

SELECT pg_catalog.setval('public.t_id_seq', 3, true);

--
-- PostgreSQL database dump complete
--

\unrestrict wtj1ko2okrK5ZUoPNI4415UxcGzgIzm5dFN3ump9PbcyOrMHALPxQvSN500tX8d

Page 25: 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

Page 26: 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.

Speaker notes

2. Declare a column as GENERATED ALWAYS AS IDENTITY.

After completing the practice tasks, delete the created databases and logical backup files.

Page 27: 1. Logical Backup

Let’s create some databases, a couple of tables, and some views.

α=> CREATE DATABASE backup_logical1;

CREATE DATABASE

α=> \c backup_logical1

You are now connected to database "backup_logical1" as user "student".

α=> CREATE TABLE t1(n integer);

CREATE TABLE

α=> INSERT INTO t1 VALUES (1), (2), (3);

INSERT 0 3

α=> CREATE VIEW v1 AS SELECT * FROM t1;

CREATE VIEW

α=> CREATE DATABASE backup_logical2;

CREATE DATABASE

α=> \c backup_logical2

You are now connected to database "backup_logical2" as user "student".

α=> CREATE TABLE t2(n integer);

CREATE TABLE

α=> INSERT INTO t2 VALUES (1), (2), (3);

INSERT 0 3

α=> CREATE VIEW v2 AS SELECT * FROM t2;

CREATE VIEW

Back up the global objects.

student$ pg_dumpall --clean --globals-only -U postgres -f /home/student/tmp/alpha_globals.sql

Parallel logical database backups:

student$ pg_dump --jobs=2 \
  --format=directory \
  -d backup_logical1 \
  -f /home/student/tmp/backup_logical1.directory

student$ pg_dump --jobs=2 \
  --format=directory \
  -d backup_logical2 \
  -f /home/student/tmp/backup_logical2.directory

Now restore the cluster. Restore global objects first:

student$ psql -p 5433 -U postgres -f /home/student/tmp/alpha_globals.sql 2> /dev/null

SET
SET
SET
ALTER ROLE
ALTER ROLE
GRANT ROLE
GRANT ROLE
GRANT ROLE
GRANT ROLE
GRANT ROLE
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT

Page 28: Page 28

GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT

Then restore the two databases in parallel:

student$ pg_restore -p 5433 \
  -U postgres \
  -d postgres \
  --create \
  --jobs=2 \
  /home/student/tmp/backup_logical1.directory

student$ pg_restore -p 5433 \
  -U postgres \
  -d postgres \
  --create \
  --jobs=2 \
  /home/student/tmp/backup_logical2.directory

Check:

student$ psql -p 5433

β=> \c backup_logical1

You are now connected to database "backup_logical1" as user "student".

β=> \d

        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+---------
 public | t1   | table | student
 public | v1   | view  | student
(2 rows)

β=> \c backup_logical2

You are now connected to database "backup_logical2" as user "student".

β=> \d

        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+---------
 public | t2   | table | student
 public | v2   | view  | student
(2 rows)

Drop the databases we created.

α=> \c postgres

You are now connected to database "postgres" as user "student".

α=> DROP DATABASE backup_logical1;

DROP DATABASE

α=> DROP DATABASE backup_logical2;

DROP DATABASE

2. COPY with a Sequence-Based Table

A table with two columns:

α=> CREATE DATABASE backup_logical;

CREATE DATABASE

α=> \c backup_logical

You are now connected to database "backup_logical" as user "student".

Page 29: Page 29

α=> CREATE TABLE t(
  id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  txt text
);

CREATE TABLE

A table and a sequence have been created:

α=> \d

           List of relations
 Schema |   Name   |   Type   |  Owner
--------+----------+----------+---------
 public | t        | table    | student
 public | t_id_seq | sequence | student
(2 rows)

Load rows without specifying values for the first column.

α=> COPY t(txt) FROM STDIN;
Один
Два
Три
\.

COPY 3

The table contents are as expected:

α=> SELECT * FROM t;

 id | txt
----+------
  1 | Один
  2 | Два
  3 | Три
(3 rows)

The sequence counter is correct:

α=> SELECT currval('t_id_seq');

 currval
---------
       3
(1 row)

Now insert another row:

α=> INSERT INTO t(txt) VALUES('Four');

INSERT 0 1

When using INSERT, you cannot specify a value for a column declared as GENERATED ALWAYS. It must be generated by the sequence:

α=> INSERT INTO t(id,txt) VALUES(5,'Five');

ERROR:  cannot insert a non-DEFAULT value into column "id"
DETAIL:  Column "id" is an identity column defined as GENERATED ALWAYS.
HINT:  Use OVERRIDING SYSTEM VALUE to override.

However, COPY allows this:

α=> COPY t FROM STDIN;
5
Пять
6
Шесть
7
Семь
\.

COPY 3

At first glance, everything looks fine…

α=> SELECT * FROM t;

Page 30: Page 30

 id |  txt
----+-------
  1 | Один
  2 | Два
  3 | Три
  4 | Four
  5 | Пять
  6 | Шесть
  7 | Семь
(7 rows)

But it is not:

α=> INSERT INTO t(txt) VALUES('Eight');

ERROR:  duplicate key value violates unique constraint "t_pkey"
DETAIL:  Key (id)=(5) already exists.

The primary key violation occurred because COPY does not use the sequence:

α=> SELECT currval('t_id_seq');

 currval
---------
       5
(1 row)

Let’s fix this by updating the sequence state.

α=> SELECT setval('t_id_seq', max(id)) FROM t;

 setval
--------
      7
(1 row)

Now the insert will succeed:

α=> INSERT INTO t(txt) VALUES('Eight');

INSERT 0 1

α=> SELECT * FROM t;

 id |  txt
----+-------
  1 | Один
  2 | Два
  3 | Три
  4 | Four
  5 | Пять
  6 | Шесть
  7 | Семь
  8 | Eight
(8 rows)

Cleanup

Drop the database.

α=> \c postgres

You are now connected to database "postgres" as user "student".

α=> DROP DATABASE backup_logical;

DROP DATABASE

Page 31: 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.

Speaker notes

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.

2. 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.

Page 32: 1. Breaking COPY

Create a database.

α=> CREATE DATABASE backup_logical;

CREATE DATABASE

α=> \c backup_logical

You are now connected to database "backup_logical" as user "student".

For this example, set the display of undefined values that match some data:

α=> CREATE TABLE anticopy(s text);

CREATE TABLE

α=> INSERT INTO anticopy(s) VALUES ('N'), (NULL);

INSERT 0 2

Set the null value representation to N:

α=> \copy anticopy to '/home/student/tmp/anticopy.dump' with (null 'N');

COPY 2

The output of two different values now looks identical. Properly recovering data from such a copy is impossible.

student$ cat ${HOME}/tmp/anticopy.dump

N
N

Be careful when changing the default format!

2. Loading Data with Missing Values

Create a file where missing values are marked as N/A.

α=> \copy (VALUES (0,'N/A'), (1,'One'), (2,'N/A'), (3,'Three')) to /home/student/tmp/backup_logical.txt

COPY 4

α=> \! cat /home/student/tmp/backup_logical.txt

0
N/A
1
One
2
N/A
3
Three

Create a table to load the data into.

α=> CREATE TABLE t (k int, v text);

CREATE TABLE

Load the data from the file into the table. The N/A value should be loaded as NULL.

α=> \copy t from /home/student/tmp/backup_logical.txt null 'N/A'

COPY 4

α=> SELECT *, v IS NULL "IsNull" FROM t;

 k |   v   | IsNull
---+-------+--------
 0 |       | t
 1 | One   | f
 2 |       | t
 3 | Three | f
(4 rows)

3. Compression in pg\_dump

Create a large enough table.

Page 33: Page 33

α=> CREATE TABLE mb AS
  SELECT (random()::numeric*(g.i))^(g.i) AS n
  FROM generate_series(1,2_500) AS g(i);

SELECT 2500

α=> SELECT pg_size_pretty(pg_table_size('mb'));

 pg_size_pretty
----------------
 5128 kB
(1 row)

Measure the time it takes to dump data without compression (and warm up the cache in the process).

student$ /usr/bin/time -f %e pg_dump \
  --format=custom --dbname=backup_logical \
  --compress=none --file=/home/student/tmp/backup_logical.none

0.45

How long will the dump take with gzip compression (used by default)?

student$ /usr/bin/time -f %e pg_dump \
  --format=custom --dbname=backup_logical \
  --compress=gzip --file=/home/student/tmp/backup_logical.gzip

2.01

Now try lz4.

student$ /usr/bin/time -f %e pg_dump \
  --format=custom --dbname=backup_logical \
  --compress=lz4 --file=/home/student/tmp/backup_logical.lz4

0.33

Now zstd.

student$ /usr/bin/time -f %e pg_dump \
  --format=custom --dbname=backup_logical \
  --compress=zstd --file=/home/student/tmp/backup_logical.zstd

0.64

Compare the file sizes:

student$ ls -lhS ~/tmp/backup_logical.*

-rw-rw-r-- 1 student student 8.3M Sep 17 00:06 /home/student/tmp/backup_logical.none
-rw-rw-r-- 1 student student 7.9M Sep 17 00:06 /home/student/tmp/backup_logical.lz4
-rw-rw-r-- 1 student student 4.1M Sep 17 00:06 /home/student/tmp/backup_logical.zstd
-rw-rw-r-- 1 student student 3.9M Sep 17 00:06 /home/student/tmp/backup_logical.gzip
-rw-rw-r-- 1 student student   26 Sep 17 00:06 /home/student/tmp/backup_logical.txt

Conclusions: gzip has the best compression ratio, but takes several times longer to run lz4 is the fastest, but offers very little compression benefit zstd strikes a good balance: its compression ratio is only slightly behind gzip, while its speed is comparable to lz4

Cleanup

Delete the databases and temporary files we created.

student$ rm ~/tmp/backup_logical.*

student$ rm ~/tmp/anticopy.dump

α=> \c postgres

You are now connected to database "postgres" as user "student".

α=> DROP DATABASE backup_logical;

DROP DATABASE

02 Lesson 02: Backup / Base Backup

Page 1: Backup / Base Backup

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

• What is Physical Backup
• Cold and Рot Backup
• Replication Protocol and Replication Slots
• Cluster Cloning and Backup
• Integrity Verification
• Backup API

Page 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

Speaker notes

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.

Page 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

Speaker notes

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.

https://postgrespro.com/docs/postgresql/16/backup-file

Page 5: Cold File-Based Backup

You can copy the files of a stopped cluster and start a second server with them. It doesn’t matter whether the server was shut down cleanly or not. Create a database and a table.

student$ psql

α=> CREATE DATABASE backup_base;

CREATE DATABASE

α=> \c backup_base

You are now connected to database "backup_base" as user "student".

α=> CREATE TABLE t(s text);

CREATE TABLE

α=> INSERT INTO t VALUES ('This row was inserted on α...');

INSERT 0 1

Crash the server and copy the files to beta:

student$ sudo kill -QUIT $(sudo head -n 1 /var/lib/postgresql/16/alpha/postmaster.pid)

student$ sudo pg_ctlcluster 16 beta stop

Cluster is not running.

student$ sudo rm -rf /var/lib/postgresql/16/beta

student$ sudo cp -a /var/lib/postgresql/16/alpha /var/lib/postgresql/16/beta

Beta starts and restores consistency:

student$ sudo pg_ctlcluster 16 beta start

student$ sudo tail -n 7 /var/log/postgresql/postgresql-16-beta.log

2026-09-16 23:54:12.254 MSK [70228] LOG:  database system was not properly shut down;
automatic recovery in progress
2026-09-16 23:54:12.272 MSK [70228] LOG:  redo starts at 0/1B82F10
2026-09-16 23:54:12.344 MSK [70228] LOG:  invalid record length at 0/1FB5138: expected at
least 24, got 0
2026-09-16 23:54:12.344 MSK [70228] LOG:  redo done at 0/1FB5110 system usage: CPU: user:
0.00 s, system: 0.00 s, elapsed: 0.07 s
2026-09-16 23:54:12.483 MSK [70226] LOG:  checkpoint starting: end-of-recovery immediate
wait
2026-09-16 23:54:15.888 MSK [70226] LOG:  checkpoint complete: wrote 932 buffers (5.7%);
0 WAL file(s) added, 0 removed, 0 recycled; write=0.041 s, sync=3.293 s, total=3.421 s;
sync files=308, longest=0.026 s, average=0.011 s; distance=4296 kB, estimate=4296 kB;
lsn=0/1FB5138, redo lsn=0/1FB5138
2026-09-16 23:54:15.913 MSK [70225] LOG:  database system is ready to accept connections

student$ psql -p 5433 -d backup_base

β=> SELECT * FROM t;

               s
-------------------------------
 This row was inserted on α...
(1 row)

Page 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

Speaker notes

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.

https://postgrespro.com/docs/postgresql/16/continuous-archiving#BACKUP-BASE-BACKUP

Page 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

Speaker notes

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.

https://postgrespro.com/docs/postgresql/16/protocol-replication

Page 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

Speaker notes

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.

https://postgrespro.com/docs/postgresql/16/warm-standby#STREAMING-RE PLICATION-SLOTS

Page 9: Cluster Cloning

{#source-figure-009 caption=“Source slide page 9: Cluster Cloning”}

physical backups are created using pg_basebackup the new server is ready to start as soon as the backup completes

Speaker notes

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.

https://postgrespro.com/docs/postgresql/16/app-pgbasebackup

Page 10: Cluster Cloning

Now let’s create a base backup of the running server alpha and use it to restore server beta.

student$ sudo pg_ctlcluster 16 alpha start

student$ psql backup_base

Permission for local connection via the replication protocol is already present in pg_hba.conf:

student$ psql -U postgres << EOF
SELECT type, database, user_name, address, auth_method
FROM pg_hba_file_rules() WHERE 'replication' = ANY(database);
EOF

 type  |   database    | user_name |  address  |  auth_method
-------+---------------+-----------+-----------+---------------
 local | {replication} | {all}     |           | trust
 host  | {replication} | {all}     | 127.0.0.1 | scram-sha-256
 host  | {replication} | {all}     | ::1       | scram-sha-256
(3 rows)

For the pg_basebackup tool to connect to the server as the student role, this role must have the REPLICATION attribute:

α=> \du student

                  List of roles
 Role name |             Attributes
-----------+-------------------------------------
 student   | Create role, Create DB, Replication