# Lesson 01: Backup / Logical Backup

> Native Markdown source page: Backup / Logical Backup

---

LLMS index: [llms.txt](/en/llms.txt)

---

<!-- Generated by scripts/generate_skeleton.py. -->

> [!NOTE]
> This page is extracted from the official PDF into native Markdown. Slide text, code and speaker notes are kept in reading order; the original PDF and HTML remain the layout references.
> **HTML source:** No matching course-page link was captured in the source inventory.
> **Archive:** [DBA3-handouts-16-en-20260917.zip](https://edu.postgrespro.com/16/DBA3-handouts-16-en-20260917.zip)
> **PDF pages:** 33

## Page 1: Backup / Logical Backup {#source-page-001}

### 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 {#source-page-002}

- 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 {#source-page-003}

**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-page-004}

![Source slide page 4: The SQL COPY command](images/page-004.png){#source-figure-004 caption="Source slide page 4: The SQL COPY command"}

```text
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-page-005}

![Source slide page 5: The psql \copy Command](images/page-005.png){#source-figure-005 caption="Source slide page 5: The psql \copy Command"}

```text
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 {#source-page-006}

Create a database and a table.
```sql
α=> CREATE DATABASE backup_logical;
```
```sql
CREATE DATABASE
```
```sql
α=> \c backup_logical
```
```text
You are now connected to database "backup_logical" as user "student".
```
```sql
α=> CREATE TABLE t(
  id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  s text
);
```
```sql
CREATE TABLE
```
```sql
α=> INSERT INTO t (s) VALUES ('Hello World!'), (''), (NULL);
```
```sql
INSERT 0 3
```
```sql
α=> SELECT * FROM t;
```
```text
 id |      s
----+--------------
  1 | Hello World!
  2 |
  3 |
(3 rows)
```
Print the table contents:
```text
α=> COPY t TO stdout;
```
```text
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:
```text
α=> COPY t TO stdout WITH (NULL '<NULL>', DELIMITER ',');
```
```text
1,Hello World!
2,
3,<NULL>
```
> [!NOTE]
> **Note**
> Note that the inline separator character has been escaped (the escape character is also configurable).
Instead of a table, you can specify an arbitrary query.
```sql
α=> COPY (SELECT * FROM t WHERE s IS NOT NULL) TO stdout;
```
```text
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.
```text
α=> COPY t TO stdout WITH (FORMAT csv);
```
```text
1,Hello World!
2,""
3,
```
Input from file or console works in a similar manner.
```text
α=> TRUNCATE TABLE t;
```
```text
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 {#source-page-007}

```sql
α=> COPY t FROM stdin WHERE id != 2;
1
Привет, мир!
2
3
\N
\.
```
```text
COPY 2
```
Here are the table contents (with NULL output configured in psql for clarity):
```text
α=> \pset null '\\N'
```
```text
Null display is "\N".
```
```sql
α=> SELECT * FROM t;
```
```text
 id |      s
----+--------------
  1 | Привет, мир!
  3 | \N
(2 rows)
```
Load all the rows:
```text
α=> TRUNCATE TABLE t;
```
```text
TRUNCATE TABLE
```
```sql
α=> COPY t FROM stdin;
1
Привет, мир!
2
3
\N
\.
```
```text
COPY 3
```
```sql
α=> SELECT * FROM t;
```
```text
 id |      s
----+--------------
  1 | Привет, мир!
  2 |
  3 | \N
(3 rows)
```

## Page 8: Database Backup {#source-page-008}

![Source slide page 8: Database Backup](images/page-008.png){#source-figure-008 caption="Source slide page 8: Database Backup"}

```text
--
-- PostgreSQL database dum
--
```
```sql
-- Dumped from database ve
-- Dumped by pg_dump versi
```
```text
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 {#source-page-009}

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
```sh
student$ pg_dump -d backup_logical --no-acl
```
```text
--
-- PostgreSQL database dump
--
```
```text
\restrict gJnV8i7WLYQP14BMyhefNauB3FcoS7S01BBdaZbOzlEf6c8cdx6vlOAs0g7GdGX
```
```sql
-- 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)
```
```sql
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;
```
```text
SET default_tablespace = '';
```
```text
SET default_table_access_method = heap;
```
```text
--
-- Name: t; Type: TABLE; Schema: public; Owner: student
--
```
```sql
CREATE TABLE public.t (
    id integer NOT NULL,
    s text
);
```
```sql
ALTER TABLE public.t OWNER TO student;
```
```text
--
-- Name: t_id_seq; Type: SEQUENCE; Schema: public; Owner: student
--
```
```sql
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
);
```
```text
--
-- Data for Name: t; Type: TABLE DATA; Schema: public; Owner: student
--
```
```sql
COPY public.t (id, s) FROM stdin;
1
Привет, мир!
2
3
\N
\.
```
```text
--
-- Name: t_id_seq; Type: SEQUENCE SET; Schema: public; Owner: student
--
```

## Page 10: Page 10 {#source-page-010}

```sql
SELECT pg_catalog.setval('public.t_id_seq', 3, true);
```
```text
--
-- Name: t t_pkey; Type: CONSTRAINT; Schema: public; Owner: student
--
```
```sql
ALTER TABLE ONLY public.t
    ADD CONSTRAINT t_pkey PRIMARY KEY (id);
```
```text
--
-- PostgreSQL database dump complete
--
```
```text
\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
> [!NOTE]
> **Important:**
> changes to the `template1` database are dumped too. Therefore, you should recover to a database created from ’template0’. The \\’--create\\’ flag takes it into account automatically:
```sh
student$ pg_dump --create -d backup_logical | grep 'CREATE DATABASE'
```
```sql
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.
```sh
student$ psql -p 5433
```
```sql
β=> CREATE DATABASE backup_logical_1;
```
```sql
CREATE DATABASE
```
```sh
student$ pg_dump --table=t -d backup_logical | psql -p 5433 -d backup_logical_1
```
```text
SET
SET
SET
SET
SET
 set_config
------------
(1 row)
```
```sql
SET
SET
SET
SET
SET
SET
CREATE TABLE
```

## Page 11: Page 11 {#source-page-011}

```sql
ALTER TABLE
ALTER TABLE
COPY 3
 setval
--------
      3
(1 row)
```
```sql
ALTER TABLE
```
```sql
β=> \c backup_logical_1
```
```text
You are now connected to database "backup_logical_1" as user "student".
```
```sql
β=> SELECT * FROM t;
```
```text
 id |      s
----+--------------
  1 | Привет, мир!
  2 |
  3 |
(3 rows)
```

## Page 12: The Custom Format {#source-page-012}

![Source slide page 12: The Custom Format](images/page-012.png){#source-figure-012 caption="Source slide page 12: The Custom Format"}

```text
;
; 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-page-013}

![Source slide page 13: pg_dump: the \’custom\’ format](images/page-013.png){#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.
```sh
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.
```sql
β=> DROP TABLE t;
```
```sql
DROP TABLE
```
```sh
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:
```sql
β=> SELECT * FROM t;
```
```text
 id |      s
----+--------------
  1 | Привет, мир!
  2 |
  3 |
(3 rows)
```
Another example: restore the entire original backup\_logical database to another server.
```sh
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:
```sql
β=> \c backup_logical
```
```text
You are now connected to database "backup_logical" as user "student".
```
```sql
β=> SELECT * FROM t;
```
```text
 id |      s
----+--------------
  1 | Привет, мир!
  2 |
  3 |
(3 rows)
```

## Page 14: The Directory Format {#source-page-014}

![Source slide page 14: The Directory Format](images/page-014.png){#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 {#source-page-015}

The \\’direcotry\\’ format, notably, supports dumping in parallel.
```sh
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:
```sh
student$ ls -l /home/student/tmp/backup_logical.directory
```
```text
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):
```sh
student$ zcat /home/student/tmp/backup_logical.directory/3440.dat.gz
```
```text
1
Привет, мир!
2
3
\N
\.
```
To restore from a backup, first disconnect from the backup\_logical database:
```sql
β=> \q
```
The `--clean` key generates a command to delete the existing database:
```sh
student$ pg_restore --clean \
  --create \
  --jobs=2 \
  -p 5433 \
  -d postgres /home/student/tmp/backup_logical.directory \
  --no-acl
```
```sh
student$ psql --pset null='\N' -p 5433 -d backup_logical -c 'SELECT * FROM t'
```
```text
 id |      s
----+--------------
  1 | Привет, мир!
  2 |
  3 | \N
(3 rows)
```

## Page 16: Format Comparison {#source-page-016}

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-page-017}

![Source slide page 17: Cluster Backup](images/page-017.png){#source-figure-017 caption="Source slide page 17: Cluster Backup"}

```text
--
-- PostgreSQL database clu
--
```
```text
SET default_transaction_re
SET client_encoding = 'UTF
SET standard_conforming_st
```
```text
--
-- Roles
--
```
```sql
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 {#source-page-018}

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.
```sh
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:
```sh
student$ pg_dumpall --clean --globals-only -U postgres
```
```text
--
-- PostgreSQL database cluster dump
--
```
```text
\restrict e0B6e88kQ1UH20c5WvNCp9aKNhv1EmAjMAub1ie8JhGQUF6v4A2nXO4FH1ZQzZa
```
```text
SET default_transaction_read_only = off;
```
```text
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
```
```sql
--
-- Drop roles
--
```
```sql
DROP ROLE postgres;
DROP ROLE student;
```
```text
--
-- Roles
--
```
```sql
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=';
```
```text
--
-- User Configurations
--
```
```text
--
-- Role memberships
--
```
```text
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;
```
```text
--
-- Role privileges on configuration parameters
--
```

## Page 19: Page 19 {#source-page-019}

```sql
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;
```
```text
\unrestrict e0B6e88kQ1UH20c5WvNCp9aKNhv1EmAjMAub1ie8JhGQUF6v4A2nXO4FH1ZQzZa
```
```text
--
-- PostgreSQL database cluster dump complete
--
```
Recovery is done via psql, there is no other way around it.
```sh
student$ psql -p 5433 -U postgres -f /home/student/tmp/alpha.sql
```
```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 {#source-page-020}

```text
SET
SET
SET
SET
 set_config
------------
(1 row)
```
```sql
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)
```
```sql
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)
```
```text
SET
SET
SET
SET
REVOKE
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
SET
SET
SET
SET
SET
 set_config
------------
(1 row)
```
```sql
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 {#source-page-021}

```text
SET
SET
 set_config
------------
(1 row)
```
```sql
SET
SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
ALTER TABLE
COPY 3
 setval
--------
      3
(1 row)
```
```sql
ALTER TABLE
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
SET
SET
SET
SET
SET
 set_config
------------
(1 row)
```
```sql
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)
```
```text
SET
SET
SET
SET
COMMENT
SET
SET
SET
SET
SET
 set_config
------------
(1 row)
```
```sql
SET
SET
SET
SET
CREATE DATABASE
```

## Page 22: Page 22 {#source-page-022}

```sql
ALTER DATABASE
You are now connected to database "student" as user "postgres".
SET
SET
SET
SET
SET
 set_config
------------
(1 row)
```
```text
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.
```sh
student$ psql -p 5433
```
```sql
β=> SELECT datname FROM pg_database;
```
```text
     datname
------------------
 template0
 backup_logical_1
 template1
 backup_logical
 postgres
 student
(6 rows)
```
```sql
β=> \c backup_logical
```
```text
You are now connected to database "backup_logical" as user "student".
```
```sql
β=> SELECT * FROM t;
```
```text
 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.
```sql
α=> CREATE POLICY t_s_not_null ON t USING (s IS NOT NULL);
```
```sql
CREATE POLICY
```
```sql
α=> ALTER TABLE t ENABLE ROW LEVEL SECURITY;
```
```sql
ALTER TABLE
```
```sql
α=> ALTER TABLE t FORCE ROW LEVEL SECURITY;
```
```sql
ALTER TABLE
```
Now COPY returns just two rows:
```text
α=> COPY t TO stdout;
```

## Page 23: Page 23 {#source-page-023}

```text
1
Привет, мир!
2
```
But when the parameter is set to off, we get an error:
```text
α=> SET row_security = off;
```
```text
SET
```
```text
α=> COPY t TO stdout;
```
```sql
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:
```sh
student$ pg_dump -d backup_logical -t t > /dev/null
```
```sql
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:
```sh
student$ pg_dump -d backup_logical -t t --enable-row-security | grep -A3 '^COPY'
```
```sql
COPY public.t (id, s) FROM stdin;
1
Привет, мир!
2
\.
```
To bypass row level security, the user must have the BYPASSRLS attribute:
```sql
α=> \c - postgres
```
```text
You are now connected to database "backup_logical" as user "postgres".
```
```sql
α=> ALTER USER student BYPASSRLS;
```
```sql
ALTER ROLE
```
Now, all queries by `student` ignore RLS policies:
```sh
student$ pg_dump -d backup_logical -t t --data-only
```
```text
--
-- PostgreSQL database dump
--
```
```text
\restrict wtj1ko2okrK5ZUoPNI4415UxcGzgIzm5dFN3ump9PbcyOrMHALPxQvSN500tX8d
```
```sql
-- 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)
```
```sql
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;
```
```text
--
-- Data for Name: t; Type: TABLE DATA; Schema: public; Owner: student
--
```
```sql
COPY public.t (id, s) FROM stdin;
1
Привет, мир!
2
3
\N
\.
```
```text
--
-- Name: t_id_seq; Type: SEQUENCE SET; Schema: public; Owner: student
--
```

## Page 24: Page 24 {#source-page-024}

```sql
SELECT pg_catalog.setval('public.t_id_seq', 3, true);
```
```text
--
-- PostgreSQL database dump complete
--
```
```text
\unrestrict wtj1ko2okrK5ZUoPNI4415UxcGzgIzm5dFN3ump9PbcyOrMHALPxQvSN500tX8d
```

## Page 25: Takeaways {#source-page-025}

**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 {#source-page-026}

**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 {#source-page-027}

Let’s create some databases, a couple of tables, and some views.
```sql
α=> CREATE DATABASE backup_logical1;
```
```sql
CREATE DATABASE
```
```sql
α=> \c backup_logical1
```
```text
You are now connected to database "backup_logical1" as user "student".
```
```sql
α=> CREATE TABLE t1(n integer);
```
```sql
CREATE TABLE
```
```sql
α=> INSERT INTO t1 VALUES (1), (2), (3);
```
```sql
INSERT 0 3
```
```sql
α=> CREATE VIEW v1 AS SELECT * FROM t1;
```
```sql
CREATE VIEW
```
```sql
α=> CREATE DATABASE backup_logical2;
```
```sql
CREATE DATABASE
```
```sql
α=> \c backup_logical2
```
```text
You are now connected to database "backup_logical2" as user "student".
```
```sql
α=> CREATE TABLE t2(n integer);
```
```sql
CREATE TABLE
```
```sql
α=> INSERT INTO t2 VALUES (1), (2), (3);
```
```sql
INSERT 0 3
```
```sql
α=> CREATE VIEW v2 AS SELECT * FROM t2;
```
```sql
CREATE VIEW
```
Back up the global objects.
```sh
student$ pg_dumpall --clean --globals-only -U postgres -f /home/student/tmp/alpha_globals.sql
```
Parallel logical database backups:
```sh
student$ pg_dump --jobs=2 \
  --format=directory \
  -d backup_logical1 \
  -f /home/student/tmp/backup_logical1.directory
```
```sh
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:
```sh
student$ psql -p 5433 -U postgres -f /home/student/tmp/alpha_globals.sql 2> /dev/null
```
```sql
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 {#source-page-028}

```text
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
GRANT
```
Then restore the two databases in parallel:
```sh
student$ pg_restore -p 5433 \
  -U postgres \
  -d postgres \
  --create \
  --jobs=2 \
  /home/student/tmp/backup_logical1.directory
```
```sh
student$ pg_restore -p 5433 \
  -U postgres \
  -d postgres \
  --create \
  --jobs=2 \
  /home/student/tmp/backup_logical2.directory
```
Check:
```sh
student$ psql -p 5433
```
```sql
β=> \c backup_logical1
```
```text
You are now connected to database "backup_logical1" as user "student".
```
```sql
β=> \d
```
```text
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+---------
 public | t1   | table | student
 public | v1   | view  | student
(2 rows)
```
```sql
β=> \c backup_logical2
```
```text
You are now connected to database "backup_logical2" as user "student".
```
```sql
β=> \d
```
```text
        List of relations
 Schema | Name | Type  |  Owner
--------+------+-------+---------
 public | t2   | table | student
 public | v2   | view  | student
(2 rows)
```
Drop the databases we created.
```sql
α=> \c postgres
```
```text
You are now connected to database "postgres" as user "student".
```
```sql
α=> DROP DATABASE backup_logical1;
```
```sql
DROP DATABASE
```
```sql
α=> DROP DATABASE backup_logical2;
```
```sql
DROP DATABASE
```
### 2. COPY with a Sequence-Based Table
A table with two columns:
```sql
α=> CREATE DATABASE backup_logical;
```
```sql
CREATE DATABASE
```
```sql
α=> \c backup_logical
```
```text
You are now connected to database "backup_logical" as user "student".
```

## Page 29: Page 29 {#source-page-029}

```sql
α=> CREATE TABLE t(
  id int PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  txt text
);
```
```sql
CREATE TABLE
```
A table and a sequence have been created:
```sql
α=> \d
```
```text
           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.
```sql
α=> COPY t(txt) FROM STDIN;
Один
Два
Три
\.
```
```text
COPY 3
```
The table contents are as expected:
```sql
α=> SELECT * FROM t;
```
```text
 id | txt
----+------
  1 | Один
  2 | Два
  3 | Три
(3 rows)
```
The sequence counter is correct:
```sql
α=> SELECT currval('t_id_seq');
```
```text
 currval
---------
       3
(1 row)
```
Now insert another row:
```sql
α=> INSERT INTO t(txt) VALUES('Four');
```
```sql
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:
```sql
α=> INSERT INTO t(id,txt) VALUES(5,'Five');
```
```sql
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:
```sql
α=> COPY t FROM STDIN;
5
Пять
6
Шесть
7
Семь
\.
```
```text
COPY 3
```
At first glance, everything looks fine...
```sql
α=> SELECT * FROM t;
```

## Page 30: Page 30 {#source-page-030}

```text
 id |  txt
----+-------
  1 | Один
  2 | Два
  3 | Три
  4 | Four
  5 | Пять
  6 | Шесть
  7 | Семь
(7 rows)
```
But it is not:
```sql
α=> INSERT INTO t(txt) VALUES('Eight');
```
```text
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:
```sql
α=> SELECT currval('t_id_seq');
```
```text
 currval
---------
       5
(1 row)
```
Let’s fix this by updating the sequence state.
```sql
α=> SELECT setval('t_id_seq', max(id)) FROM t;
```
```text
 setval
--------
      7
(1 row)
```
Now the insert will succeed:
```sql
α=> INSERT INTO t(txt) VALUES('Eight');
```
```sql
INSERT 0 1
```
```sql
α=> SELECT * FROM t;
```
```text
 id |  txt
----+-------
  1 | Один
  2 | Два
  3 | Три
  4 | Four
  5 | Пять
  6 | Шесть
  7 | Семь
  8 | Eight
(8 rows)
```
### Cleanup
Drop the database.
```sql
α=> \c postgres
```
```text
You are now connected to database "postgres" as user "student".
```
```sql
α=> DROP DATABASE backup_logical;
```
```sql
DROP DATABASE
```

## Page 31: Practice+ {#source-page-031}

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

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.

## Page 32: 1. Breaking COPY {#source-page-032}

Create a database.
```sql
α=> CREATE DATABASE backup_logical;
```
```sql
CREATE DATABASE
```
```sql
α=> \c backup_logical
```
```text
You are now connected to database "backup_logical" as user "student".
```
For this example, set the display of undefined values that match some data:
```sql
α=> CREATE TABLE anticopy(s text);
```
```sql
CREATE TABLE
```
```sql
α=> INSERT INTO anticopy(s) VALUES ('N'), (NULL);
```
```sql
INSERT 0 2
```
Set the null value representation to N:
```text
α=> \copy anticopy to '/home/student/tmp/anticopy.dump' with (null 'N');
```
```text
COPY 2
```
The output of two different values now looks identical. Properly recovering data from such a copy is impossible.
```sh
student$ cat ${HOME}/tmp/anticopy.dump
```
```text
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.
```text
α=> \copy (VALUES (0,'N/A'), (1,'One'), (2,'N/A'), (3,'Three')) to /home/student/tmp/backup_logical.txt
```
```text
COPY 4
```
```text
α=> \! cat /home/student/tmp/backup_logical.txt
```
```text
0
N/A
1
One
2
N/A
3
Three
```
Create a table to load the data into.
```sql
α=> CREATE TABLE t (k int, v text);
```
```sql
CREATE TABLE
```
Load the data from the file into the table. The N/A value should be loaded as NULL.
```sql
α=> \copy t from /home/student/tmp/backup_logical.txt null 'N/A'
```
```text
COPY 4
```
```sql
α=> SELECT *, v IS NULL "IsNull" FROM t;
```
```text
 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 {#source-page-033}

```sql
α=> CREATE TABLE mb AS
  SELECT (random()::numeric*(g.i))^(g.i) AS n
  FROM generate_series(1,2_500) AS g(i);
```
```sql
SELECT 2500
```
```sql
α=> SELECT pg_size_pretty(pg_table_size('mb'));
```
```text
 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).
```sh
student$ /usr/bin/time -f %e pg_dump \
  --format=custom --dbname=backup_logical \
  --compress=none --file=/home/student/tmp/backup_logical.none
```
```text
0.45
```
How long will the dump take with gzip compression (used by default)?
```sh
student$ /usr/bin/time -f %e pg_dump \
  --format=custom --dbname=backup_logical \
  --compress=gzip --file=/home/student/tmp/backup_logical.gzip
```
```text
2.01
```
Now try lz4.
```sh
student$ /usr/bin/time -f %e pg_dump \
  --format=custom --dbname=backup_logical \
  --compress=lz4 --file=/home/student/tmp/backup_logical.lz4
```
```text
0.33
```
Now zstd.
```sh
student$ /usr/bin/time -f %e pg_dump \
  --format=custom --dbname=backup_logical \
  --compress=zstd --file=/home/student/tmp/backup_logical.zstd
```
```text
0.64
```
Compare the file sizes:
```sh
student$ ls -lhS ~/tmp/backup_logical.*
```
```text
-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.
```sh
student$ rm ~/tmp/backup_logical.*
```
```sh
student$ rm ~/tmp/anticopy.dump
```
```sql
α=> \c postgres
```
```text
You are now connected to database "postgres" as user "student".
```
```sql
α=> DROP DATABASE backup_logical;
```
```sql
DROP DATABASE
```