Администрирование PostgreSQL. Резервное копирование и репликация

Курс сохраняет официальный порядок тем. Русские материалы извлечены из PDF в читаемый нативный Markdown для дальнейшей редакторской работы.

Состояние материалов: 官方 PDF 已提取为原生 Markdown

Базовый язык: Базовый язык: English. Текущий английский архив используется как базовый; русская версия сохранена для сверки глав.

О курсе

Логическое и физическое резервное копирование, архивирование WAL, физическая и логическая репликация, архитектуры кластеров.

Состояние материалов

Содержание

Дополнительные материалы

• dba3_new_features.pdf
• dba3_student_guide.pdf

Редакторский статус

Русский комплект доступен в виде нативного Markdown. Если английский комплект не найден, английский маршрут оставлен для будущего источника.

• Матрица версий
• Рабочий список
• Источники и разрешения

00 Занятие 00: Резервное копирование и репликация / PostgreSQL 16

Page 1: Резервное копирование и репликация / PostgreSQL 16

Авторские права

© Postgres Professional, 2017–2025 Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Алексей Береснев Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)

Использование материалов курса

Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса.

Обратная связь

Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru

Отказ от ответственности

Компания Postgres Professional не несет никакой ответственности за любые повреждения и убытки, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием материалов курса. Компания Postgres Professional не предоставляет каких-либо гарантий на материалы курса. Материалы курса предоставляются на основе принципа «как есть» и компания Postgres Professional не обязана предоставлять сопровождение, поддержку, обновления, расширения и изменения.

Page 2: Администраторам

{#source-figure-002 caption=“Source slide page 2: Администраторам”}

• DBA2, 4 дня

Комментарии преподавателя

Для прохождения всех курсов необходимо знание основ языка SQL. Курса по SQL в нашей линейке нет, но имеется множество книг и образовательных ресурсов для освоения SQL. Мы рекомендуем книгу Евгения Моргунова «PostgreSQL. Основы языка SQL»:

https://postgrespro.ru/education/books/sqlprimer

Для администраторов мы предлагаем следующие курсы.

Курс DBA1 – базовый, он дает сведения об архитектуре PostgreSQL, об установке, настройке и управлении сервером. Приводятся обзоры разграничения доступа, резервного копирования и репликации.

В курсе DBA2 обсуждается настройка конфигурационных параметров исходя из понимания внутренней организации сервера; говорится о мониторинге сервера с использованием обратной связи для итеративной настройки параметров.

Курс DBA3 посвящен рассмотрению резервного копирования, а также настройкам физической и логической репликации и сценариям ее использования. Также дается общее представление о способах и сложностях построения высокодоступных, масштабируемых кластеров.

Курс DBS рассказывает про настройку подключений клиентов к СУБД и про управление доступом внутри самой СУБД.

В курсе QPT разбираются планирование и выполнение запросов, настройка параметров экземпляра, связанных с производительностью, и изучается поиск проблемных запросов и их оптимизация.

В курсе PGPRO рассматриваются дополнительные возможности, которые предоставляет СУБД Postgres Pro Enterprise.

Page 3: Разработчикам

{#source-figure-003 caption=“Source slide page 3: Разработчикам”}

• DEV2, 4 дня

Комментарии преподавателя

Для разработчиков серверной части приложений мы предлагаем следующие курсы.

Базовым курсом для разработчиков является курс DEV1, в котором даются общие сведения об архитектуре PostgreSQL и использовании основных объектов базы данных; изучается программирование на стороне сервера на языках SQL и PL/pgSQL с использованием основных типов данных (включая составной тип и массивы), а также рассматривается взаимодействие с клиентской частью.

В курсе DEV2 рассматриваются особенности внутреннего устройства сервера, влияющие на разработку прикладного кода, и всесторонне изучается расширяемость: возможность дополнить серверные механизмы собственным кодом, что позволяет использовать PostgreSQL для решения самых разнообразных задач.

Курсы DBS, QPT и PGPRO являются общими для разработчиков и администраторов.

Разработчикам могут быть интересны и курсы по администрированию, если есть необходимость детальнее изучить внутреннее устройство PostgreSQL, а также в случае, когда на проекте нет выделенной роли администратора.

https://postgrespro.ru/education/courses

Page 4: О курсе DBA3

Продолжительность: 2 дня

• Предварительные знания
  • основы SQL
  • владение ОС Unix
  • знакомство с архитектурой PostgreSQL в объеме курса DBA1
• Какие навыки будут получены
  • выполнение резервного копирования различными способами
  • настройка сервера для физической и логической репликации
  • знакомство со сценариями использования репликации
  • представление о способах построения кластеров

Комментарии преподавателя

В этом курсе рассказывается о возможностях и стратегиях резервного копирования, а также о механизмах физической и логической репликации и сценариях ее использования. Дается общее представление о способах и сложностях построения высокодоступных, масштабируемых кластеров.

Page 5: Используемые материалы

Подготовленная виртуальная машина

• ОС Xubuntu 24.04
• PostgreSQL 16 (три кластера)
• документация на русском языке
• Учебные материалы
  • презентации, демонстрации, практические задания и их решение
  • (в форматах html и pdf)

Комментарии преподавателя

Если вы проходите курс самостоятельно, обязательно начните со знакомства с Руководством слушателя. В числе прочего в нем написано, где скачать и как использовать виртуальную машину курса и прочие материалы. Все материалы курса, включая Руководство слушателя, доступны по адресу: https://postgrespro.ru/education/courses/DBA3

Выполнение практических заданий очень важно для получения навыков работы с PostgreSQL. Обязательно старайтесь сначала самостоятельно выполнить задания, а затем просмотрите предлагаемые нами решения, даже если задание не вызвало вопросов. В решениях могут содержаться дополнительные сведения, которые не упоминаются в презентациях и демонстрациях. После выполнения задания следует удалить созданные объекты и сбросить измененные настройки, иначе они могут помешать выполнению последующих заданий.

Учебные материалы (презентации, демонстрации, практические задания и их решения) доступны в двух форматах. Формат html удобен для онлайн-работы, он позволяет копировать фрагменты текста и кода. Формат pdf разбит на страницы и удобен для печати.

Page 6: Предварительные настройки

В этом курсе мы будем использовать три экземпляра PostgreSQL, названные alpha, beta и gamma:

student$ sudo pg_ctlcluster 16 alpha start

student$ sudo pg_ctlcluster 16 beta start

student$ sudo pg_ctlcluster 16 gamma start

В демонстрациях команды, выполняемые на каждом из серверов, выделены и отличаются приглашением. Все практические задания выполняются под пользователем ОС student. В СУБД создан одноименный пользователь и база данных, чтобы psql можно было запускать без параметров. Сервер alpha работает на стандартном порту 5432:

student$ psql

α=> SHOW server_version;

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

Сервер beta — на порту 5433. Для подключения в psql достаточно указать только номер порта:

student$ psql -p 5433

β=> SHOW server_version;

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

Сервер gamma — на порту 5434:

student$ psql -p 5434

γ=> SHOW server_version;

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

Роль student не является суперпользовательской, но обладает правом создания баз данных и ролей и разрешением на подключение по протоколу репликации, а также включена в некоторые предопределенные групповые роли:

α=> \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)

Также student имеет привилегии на некоторые объекты, которыми по умолчанию обладает только суперпользователь. Это право изменять параметры:

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)

И право выполнять системные функции (оно выдано в базе template1 и, следовательно, во всех базах, созданных командой CREATE DATABASE без указания шаблона):

α=> 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)

Локальные подключения любых пользователей разрешены без ввода пароля:

α=> \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: Организационные вопросы

День: ~8 академических часов + обед (1час)

• Каждая тема, как правило, состоит из
  • презентации и демонстрации: ~40–70 мин
  • практических заданий: ~30–40 мин, включая перерыв

Page 9: День 1

Резервное копирование

• 1. Логическое резервирование
• 2. Базовая резервная копия
• 3. Архив журнала предзаписи
• Репликация
  • 4. Физическая репликация

Page 10: День 2

Репликация (продолжение)

• 5. Переключение на реплику 06. Логическая репликация 07. Сценарии использования Кластерные технологии
  • 8. Обзор

01 Занятие 01: Резервное копирование / Логическое резервирование

Page 1: Резервное копирование / Логическое резервирование

Авторские права

© Postgres Professional, 2017–2025 Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Алексей Береснев Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)

Использование материалов курса

Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса.

Обратная связь

Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru

Отказ от ответственности

Компания Postgres Professional не несет никакой ответственности за любые повреждения и убытки, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием материалов курса. Компания Postgres Professional не предоставляет каких-либо гарантий на материалы курса. Материалы курса предоставляются на основе принципа «как есть» и компания Postgres Professional не обязана предоставлять сопровождение, поддержку, обновления, расширения и изменения.

Page 2: Темы

• Понятие логической резервной копии
• Копирование и восстановление отдельных таблиц
• Копирование и восстановление баз данных
• Копирование и восстановление кластера

Page 3: Логическая копия

Команды SQL для создания объектов и наполнения данными

• • можно сделать копию отдельного объекта или отдельной базы
• • можно восстановиться на другой версии или архитектуре
• • (не требуется двоичная совместимость)
• − невысокая скорость работы
• − восстановление только на момент создания резервной копии

Комментарии преподавателя

Логическая копия содержит набор команд SQL, выполнив которые можно восстановить кластер, базу данных или выбранные таблицы. В процессе восстановления из логической копии создаются необходимые объекты и наполняются данными.

Команды можно выполнить на другой версии СУБД или даже на другой платформе и архитектуре, так как не требуется двоичная совместимость.

В частности, логическую резервную копию можно использовать для долговременного хранения: ее можно будет восстановить и после обновления сервера на новую версию.

Однако для большой базы команды могут выполняться очень долго. Восстановить систему из логической копии можно ровно на момент начала резервного копирования.

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

Page 4: SQL-команда COPY

{#source-figure-004 caption=“Source slide page 4: SQL-команда COPY”}

YKS   Якутск      (129.770
MJZ   Мирный      (114.039
KHV   Хабаровск-Новый   (1
PKC   Елизово     (158.453
UUS   Хомутово    (142.718
VVO   Владивосток       (1
LED   Пулково     (30.2625
KGD   Храброво    (20.5925
KEJ   Кемерово    (86.1072
CEK   Челябинск   (61.5033
MQF   Магнитогорск      (5
PEE   Пермь       (56.0211
SGC   Сургут      (73.4018

• файл в ФС сервера, принадлежащий владельцу экземпляра PostgreSQL можно выбрать столбцы или использовать произвольный запрос при восстановлении строки добавляются к имеющимся в таблице

Комментарии преподавателя

Команда COPY TO записывает содержимое таблицы или результат запроса в файл или поток вывода. Строки представлений или секционированных таблиц можно скопировать с помощью запроса.

Для использования COPY TO требуется роль pg_write_server_file.

Команда COPY FROM добавляет в таблицу строки из файла или стандартного потока ввода. Она работает существенно быстрее, чем аналогичные команды INSERT. Следить за процессом копирования можно в представлении pg_stat_progress_copy.

Для COPY FROM требуется роль pg_read_server_files (а для чтения потока ввода — pg_execute_server_program).

Тонкость: при выполнении команды COPY FROM не применяются правила (rules), хотя ограничения целостности и триггеры выполняются. Если столбец таблицы при вставке должен получать значения, генерируемые последовательностью, но COPY заполняет этот столбец явно, последовательность не будет изменять свое состояние (счетчик).

Оба варианта команды COPY позволяют выбрать столбцы таблицы, а также указать такие параметры, как:

● FORMAT — формат данных (текстовый, CSV или двоичный);

● DELIMITER — разделитель полей;

● NULL — текстовое представление NULL;

● ENCODING — кодировку;

● HEADER — заголовок, описывающий столбцы. https://postgrespro.ru/docs/postgresql/16/sql-copy

Page 5: Команда psql \copy

{#source-figure-005 caption=“Source slide page 5: Команда psql \copy”}

YKS   Якутск      (129.770
MJZ   Мирный      (114.039
KHV   Хабаровск-Новый   (1
PKC   Елизово     (158.453
UUS   Хомутово    (142.718
VVO   Владивосток       (1
LED   Пулково     (30.2625
KGD   Храброво    (20.5925
KEJ   Кемерово    (86.1072
CEK   Челябинск   (61.5033
MQF   Магнитогорск      (5
PEE   Пермь       (56.0211
SGC   Сургут      (73.4018

• файл в ФС клиента и доступен пользователю ОС, запустившему psql происходит пересылка данных между клиентом и сервером синтаксис и возможности аналогичны команде COPY

Комментарии преподавателя

В psql существует метакоманда \copy — вариант команды COPY с аналогичным синтаксисом, — выполняющаяся на стороне клиента.

Файл должен быть доступен пользователю операционной системы, запустившему psql. Обращение к файлу происходит на клиенте, а на сервер передается только содержимое.

https://postgrespro.ru/docs/postgresql/16/app-psql#APP-PSQL-META-COM MANDS-COPY

Page 6: Команда COPY

Создадим базу данных и таблицу.

α=> 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 ('Привет, мир!'), (''), (NULL);

INSERT 0 3

α=> SELECT * FROM t;

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

Выведем содержимое таблицы в терминал:

α=> COPY t TO stdout;

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

Видно, как различаются в выводе пустые строки и неопределенные значения. Формат вывода настраивается достаточно гибко. Можно изменить разделитель, представление неопределенных значений и т. п. Например:

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

1,Привет\, мир!
2,
3,<NULL>

Обратите внимание, что символ-разделитель внутри строки был экранирован (символ для экранирования тоже настраивается). Вместо таблицы можно указать произвольный запрос.

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

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

Таким образом можно сохранить результат запроса, данные представления и т. п. Команда поддерживает вывод в формате CSV, который поддерживается множеством программ.

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

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

Аналогично работает и ввод данных из файла или с терминала.

α=> TRUNCATE TABLE t;

TRUNCATE TABLE

Но при вводе с терминала требуется маркер конца файла — обратная косая черта с точкой; в обычном файле он не нужен. Чтобы данные загрузились, при вводе надо указать те же параметры, что были указаны при выводе.

Page 7: Page 7

При загрузке также можно указать условие:

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

COPY 2

Вот что загрузилось в таблицу (для наглядности настроим в psql вывод неопределенных значений):

α=> \pset null '\\N'

Null display is "\N".

α=> SELECT * FROM t;

 id |      s
----+--------------
  1 | Привет, мир!
  3 | \N
(2 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: Копия базы данных

{#source-figure-008 caption=“Source slide page 8: Копия базы данных”}

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

• формат: команды SQL при выгрузке можно выбрать отдельные объекты базы данных новая база должна быть создана из шаблона template0 заранее должны быть созданы роли и табличные пространства после загрузки имеет смысл выполнить ANALYZE

Комментарии преподавателя

Для создания полноценной резервной копии базы данных используется утилита pg_dump.

Если не указать имя файла (-f, –file), то утилита выведет результат в стандартный поток вывода.

Результатом является скрипт, предназначенный для psql, который содержит команды для создания объектов и наполнения их данными.

Дополнительными ключами утилиты можно ограничить набор объектов: выбрать указанные таблицы или все объекты в указанных схемах, выгрузить только определения объектов или только данные и т. п.

Следует иметь в виду, что базу данных для восстановления надо создавать из шаблона template0, так как все изменения, сделанные в template1, попадут в резервную копию.

Кроме того, заранее должны быть созданы необходимые роли и табличные пространства. Поскольку эти объекты не относятся к конкретной БД, они не будут выгружены в резервную копию.

После восстановления базы данных имеет смысл выполнить команду ANALYZE — она соберет статистику, необходимую оптимизатору для планирования запросов.

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

Page 9: Утилита pg_dump

При запуске без дополнительных параметров утилита pg_dump выдает команды SQL, создающие все объекты в текущей базе данных. Чтобы получить команды, позволяющие восстановить копию на системе с другим набором ролей, могут пригодиться ключи: -O, –no-owner — не генерировать команды для установки владельца объектов; -x, –no-acl — не генерировать команды для установки привилегий.

student$ pg_dump -d backup_logical --no-acl

--
-- PostgreSQL database dump
--

\restrict 9SVmaQgVs0mNXwAdBl23jRpxukDiAurf2WaDlfki9mmjnWo9zicdFaR4DvxXuxG

-- Dumped from database version 16.11 (Ubuntu 16.11-1.pgdg24.04+1)
-- Dumped by pg_dump version 16.11 (Ubuntu 16.11-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
\.

--

Page 10: Page 10

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

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 9SVmaQgVs0mNXwAdBl23jRpxukDiAurf2WaDlfki9mmjnWo9zicdFaR4DvxXuxG

Видно, что pg_dump создал таблицу t и заполнил ее с помощью уже рассмотренной нами команды COPY. Ключ –column-inserts позволяет использовать команды INSERT, но загрузка будет работать существенно дольше. Логическая резервная копия в формате plain может содержать злонамеренно вставленные метакоманды, например \gexec. Чтобы избежать проблем при выполнении скрипта в psql, pg_dump добавляет в его начало метакоманду \restrict с последующим ключом. Эта команда включает ограниченный режим, в котором psql игнорирует все метакоманды, кроме команды \unrestrict с таким же ключом, отменяющей ограниченный режим. Эти ключи полезны для выгрузки и загрузки данных частями: -s, –schema-only — выгрузить только определения объектов без данных; -a, –data-only — выгрузить только данные, без создания объектов. Удобны, если восстанавливать копию на системе, в которой уже есть данные (и наоборот, на чистой системе): -c, –clean — генерировать команды DROP для создаваемых объектов; -C, –create — генерировать команды создания БД и подключения к ней. Важный момент: в выгрузку попадают и изменения, сделанные в шаблонной БД template1. Поэтому восстанавливать резервную копию лучше на базе данных, созданной из template0. При использовании ключа –create это учитывается автоматически:

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';

Существуют ключи для выбора объектов, которые должны попасть в резервную копию: -n, –schema — шаблон для имен схем; -t, –table — шаблон для имен таблиц. И наоборот, включить в копию все, кроме указанного: -N, –exclude-schema — шаблон для имен схем; -T, –exclude-table — шаблон для имен таблиц. Например, восстановим таблицу t в другой базе данных на другом сервере.

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

Page 11: Page 11

SET
SET
SET
SET
CREATE TABLE
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: Формат custom

{#source-figure-012 caption=“Source slide page 12: Формат custom”}

;
; Archive created at 2017-
;     dbname: demo
;     TOC Entries: 146
;     Compression: -1
;     Dump Version: 1.12-0
;     Format: CUSTOM
;
; Selected TOC Entries:
;
2297; 1262 475453 DATABASE
5; 2615 475454 SCHEMA - bo
2298; 0 0 COMMENT - SCHEMA

• внутренний формат с оглавлением отдельные объекты базы данных можно выбрать на этапе восстановления возможна загрузка в несколько параллельных потоков

Комментарии преподавателя

Утилита pg_dump поддерживает несколько форматов резервной копии. По умолчанию это plain — простые команды для psql.

Формат custom (-F c, –format=custom) создает резервную копию в специальном формате, содержащем не только объекты, но и оглавление. Наличие оглавления позволяет выбирать объекты для восстановления не при создании копии, а непосредственно при восстановлении. Файл формата custom по умолчанию сжат.

Для восстановления потребуется другая утилита — pg_restore. Она читает файл и преобразует его в команды psql. Если не указать явно имя базы данных (в ключе -d), то команды будут выведены в стандартный поток вывода. Если же база данных указана — утилита соединится с этой БД и выполнит команды без участия psql.

Чтобы восстановить только часть объектов, можно воспользоваться одним из двух подходов. Во-первых, можно ограничить объекты аналогично тому, как они ограничиваются в pg_dump. Вообще, pg_restore использует многие параметры утилиты pg_dump.

Во-вторых, можно получить из оглавления список объектов, содержащихся в резервной копии (ключ –list). Затем этот список можно отредактировать вручную, удалив ненужное и передать измененный список на вход pg_restore (ключ –use-list).

Загрузку можно выполнить параллельно, указав количество потоков ключом -j — это еще одно преимущество формата custom.

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

Page 13: Утилита pg_dump — формат custom

{#source-figure-013 caption=“Source slide page 13: Утилита pg_dump — формат custom”}

Серьезное ограничение обычного формата (plain) состоит в том, что выбирать объекты нужно в момент выгрузки. Формат custom позволяет сначала сделать полную копию, а выбирать объекты уже при загрузке.

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

Для восстановления объектов из такой копии предназначена утилита pg_restore. Повторим восстановление таблицы t:

β=> 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 понимает те же ключи для фильтрации объектов, что и pg_dump, и даже больше: -I, –index — загрузить определенные индексы; -P, –function — загрузить определенные функции; -T, –trigger — загрузить определенные триггеры. Проверим, как восстановилась таблица:

β=> SELECT * FROM t;

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

Еще один пример: восстановим целиком исходную базу данных backup_logical на другом сервере.

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

Здесь мы указали БД postgres, но могли указать любую — утилита сама создаст нужную БД и тут же переключится в нее. Проверим:

β=> \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: Формат directory

{#source-figure-014 caption=“Source slide page 14: Формат directory”}

каталог с оглавлением и отдельными файлами на каждый объект базы отдельные объекты базы данных можно выбрать на этапе восстановления и выгрузка, и загрузка возможны в несколько параллельных потоков

Комментарии преподавателя

Еще один формат резервной копии — directory. В таком случае будет создан не один файл, а каталог, содержащий объекты и оглавление. По умолчанию файлы внутри каталога будут сжаты.

Преимущество перед форматом custom состоит в том, что такая резервная копия может создаваться параллельно в несколько потоков (количество указывается в ключе -j, –jobs).

Разумеется, несмотря на параллельное выполнение, копия будет содержать согласованные данные. Это обеспечивается общим снимком данных для всех параллельно работающих процессов.

https://postgrespro.ru/docs/postgresql/16/functions-admin#FUNCTIONS-SN APSHOT-SYNCHRONIZATION

Восстановление, как и для формата custom, возможно в несколько потоков.

В остальном возможности по работе с форматом directory не отличаются от ранее рассмотренных: поддерживаются те же ключи и подходы.

Page 15: Утилита pg_dump — формат directory

Формат directory интересен тем, что позволяет выгружать данные в несколько параллельных потоков.

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

При этом гарантируется согласованность данных: все параллельные потоки будут использовать один и тот же снимок данных. Заглянем внутрь каталога:

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

total 12
-rw-rw-r-- 1 student student   60 апр 10 17:59 3442.dat.gz
-rw-rw-r-- 1 student student 4985 апр 10 17:59 toc.dat

В нем находится файл оглавления и по одному файлу на каждый выгружаемый объект (у нас он всего один):

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

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

Для восстановления из резервной копии предварительно отключимся от базы данных backup_logical:

β=> \q

В команду восстановления добавляем ключ –clean, который генерирует команду удаления существующей БД:

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: Сравнение форматов

plain custom directory tar

• утилита для восстановления psql pg_restore
• сжатие gzip, lz4 или zstd
• выборочное восстановление да да да
• параллельное резервирование да
• параллельное восстановление да да

Комментарии преподавателя

В приведенной таблице разные форматы сравниваются с точки зрения предоставляемых ими возможностей.

Имеется и четвертый формат — tar. Он не рассматривался, так как не привносит ничего нового и не дает преимуществ перед другими форматами. Фактически он соответствует созданию tar-файла из каталога в формате directory, но не поддерживает сжатие и параллелизм.

Page 17: Копия кластера БД

{#source-figure-017 caption=“Source slide page 17: Копия кластера БД”}

--
-- PostgreSQL database clu
--

SET default_transaction_re
SET client_encoding = 'UTF
SET standard_conforming_st

--
-- Roles
--

CREATE ROLE postgres;

• формат: команды SQL выгружает весь кластер, включая роли и табличные пространства пользователь должен иметь доступ ко всем объектам кластера не поддерживает параллельную выгрузку

Комментарии преподавателя

Чтобы создать резервную копию всего кластера, включая роли и табличные пространства, можно воспользоваться утилитой pg_dumpall.

Поскольку pg_dumpall требуется доступ ко всем объектам всех БД, имеет смысл запускать ее от имени суперпользователя. Утилита по очереди подключается к каждой БД кластера и выгружает информацию с помощью pg_dump. Кроме того, она сохраняет и данные, относящиеся к кластеру в целом.

Чтобы начать работу, утилите требуется подключиться хотя бы к какой-то базе данных. По умолчанию выбирается postgres или template1, но можно указать и другую.

Результатом работы pg_dumpall является скрипт для psql. Другие форматы не поддерживаются. Это означает, что pg_dumpall не поддерживает параллельную выгрузку данных, что может оказаться проблемой при больших объемах данных. В таком случае можно воспользоваться ключом –globals-only, чтобы выгрузить только роли и табличные пространства, а сами базы данных выгрузить отдельно с помощью pg_dump в параллельном режиме.

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

Page 18: Утилита pg_dumpall

Утилита pg_dump годится для выгрузки одной базы данных, но никогда не выгружает общие объекты кластера БД, такие, как роли и табличные пространства. Чтобы сделать полную копию кластера, нужна утилита pg_dumpall. Утилиты pg_dumpall, pg_dump и pg_restore не требуют каких-то отдельных привилегий, но у выполняющей их роли должны быть привилегии на чтение (создание) всех затронутых объектов. Утилитой pg_dump может, например, пользоваться владелец базы данных. Но поскольку для копирования кластера надо иметь доступ ко всем БД, мы выполняем pg_dumpall под суперпользовательской ролью.

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

В копию кластера попадают: команды, которые выгружает pg_dump для каждой базы; команды для общих объектов кластера (ролей и табличных пространств). Команды для общих объектов можно получить отдельно:

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

--
-- PostgreSQL database cluster dump
--

\restrict 3es50MMtas3DuQEICBlRybTIYFuQo6UjJ6QSte0gHU6YC3Cu9FPpqeA5bz5XfUS

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:yfk/5FFxV407/S3oyBsCgw==$OXcRX+r8Qf1rHG4twj/5+MvsSQCLZ3Coe4dpq05MMow=:
v0NHWjIxeJKzuvakkOGY42c9o2EtM/wkNcgJuhVXE7E=';
CREATE ROLE student;
ALTER ROLE student WITH NOSUPERUSER INHERIT CREATEROLE CREATEDB LOGIN REPLICATION
NOBYPASSRLS PASSWORD
'SCRAM-SHA-256$4096:G2Dm/mJ7z4+gqTtmhCcqZA==$C/rauObLdXjclYh6ggwRIaRneqQs5QjUDjRIjHKx/t8=:
VGpRpwauhFGf5KaEVb7adV9+KZuEEZ0vGKrIa80yuJM=';

--
-- 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 3es50MMtas3DuQEICBlRybTIYFuQo6UjJ6QSte0gHU6YC3Cu9FPpqeA5bz5XfUS

--
-- PostgreSQL database cluster dump complete
--

Восстановление выполняется с помощью psql — никакой другой формат не поддерживается.

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

Page 20: Page 20

SET
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

Page 21: Page 21

SET
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

Page 22: Page 22

CREATE DATABASE
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

В процессе восстановления могут возникать ошибки из-за невозможности удалить некоторые объекты или создать уже существующие, в данном случае это нормально и не мешает процессу.

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)

Влияние политик защиты строк

Если на таблицах определены политики защиты строк (подробно рассматриваются в теме «Доступ на уровне строк» курса DBS), то есть опасность выгрузить неполные данные и даже не узнать об этом. Чтобы этого не произошло, перед выполнением команды COPY можно установить параметр row_security в значение off — в этом случае применение политики приведет к явной ошибке. Простой пример. Настроим политику так, чтобы не были видны пустые строки, и включим ее для владельца таблицы:

α=> 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

Теперь COPY выгрузит только две строки:

Page 23: Page 23

α=> COPY t TO stdout;

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

Но если установить параметр в off, получим ошибку:

α=> 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.

Утилиты pg_dump и pg_dumpall автоматически устанавливают row_security = off, так что дополнительные действия не нужны:

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;

Утилита попыталась выгрузить строки таблицы командой COPY и завершилась ошибкой — она сообщает, что выгрузить все строки таблицы t не удается. Если все-таки решим выгрузить только доступные строки, можно использовать –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
\.

Чтобы обойти политики защиты строк и выгрузить все данные, пользователь должен иметь атрибут BYPASSRLS:

α=> \c - postgres

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

α=> ALTER USER student BYPASSRLS;

ALTER ROLE

Теперь запросы от роли student будут игнорировать политики защиты строк:

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

--
-- PostgreSQL database dump
--

\restrict 7lfgcqAyFvDojclaH2Z2g2Q0XjsgfiBgIufeXBMlBtwEvtm604zA95pzqmRtbh2

-- Dumped from database version 16.11 (Ubuntu 16.11-1.pgdg24.04+1)
-- Dumped by pg_dump version 16.11 (Ubuntu 16.11-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
\.

Page 24: Page 24

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

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

--
-- PostgreSQL database dump complete
--

\unrestrict 7lfgcqAyFvDojclaH2Z2g2Q0XjsgfiBgIufeXBMlBtwEvtm604zA95pzqmRtbh2

Page 25: Итоги

Логическое резервирование позволяет сделать копию всего кластера, базы данных или отдельных объектов

• Хорошо подходит
  • для данных небольшого объема
  • для длительного хранения, за время которого меняется версия сервера
  • для миграции на другую платформу
• Плохо подходит
  • для восстановления после сбоя с минимальной потерей данных

Page 26: Практика

1. Создайте пару баз данных с таблицами и представлениями, получите с помощью pg_dumpall копию только глобальных объектов кластера, а затем сделайте копии баз данных с помощью утилиты pg_dump в параллельном режиме. Восстановите кластер на другом сервере, используя созданные резервные копии. 2. Создайте таблицу с двумя столбцами, один из которых является первичным ключом и заполняется последовательностью. Воспроизведите ситуацию, когда при загрузке с помощью COPY последовательность не генерирует новые значения, вызывая нарушение уникальности при последующей вставке строк.

Комментарии преподавателя

2. Объявите столбец как GENERATED ALWAYS AS IDENTITY.

После выполнения практических заданий удалите созданные базы данных и файлы логических копий.

Page 27: 1. Логическое копирование

Создадим базы данных, пару таблиц и представлений.

α=> 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

Сделаем копию глобальных объектов.

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

Логические копии баз данных в параллельном режиме:

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

Теперь восстановим кластер. Сначала восстанавливаем глобальные объекты:

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

Затем — две базы данных в параллельном режиме:

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

Проверим:

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)

Удалим созданные базы данных.

α=> \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 для таблицы с последовательностью

Таблица с двумя столбцами:

α=> 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

Созданы таблица и последовательность:

α=> \d

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

Загружаем строки, не указывая значения для первого столбца.

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

COPY 3

Содержимое таблицы соответствует ожиданиям:

α=> SELECT * FROM t;

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

Счетчик последовательности корректен:

α=> SELECT currval('t_id_seq');

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

Можно добавить очередную строку:

α=> INSERT INTO t(txt) VALUES('Четыре');

INSERT 0 1

Вставляя строки с помощью команды INSERT, мы не можем указывать значение столбца, объявленного как GENERATED ALWAYS. Оно должно генерироваться последовательностью:

α=> INSERT INTO t(id,txt) VALUES(5,'Пять');

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.

Однако COPY дает такую возможность:

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

COPY 3

На первый взгляд все нормально…

α=> SELECT * FROM t;

Page 30: Page 30

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

Но это не так:

α=> INSERT INTO t(txt) VALUES('Восемь');

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

Нарушение первичного ключа произошло из-за того, что COPY не обращается к последовательности:

α=> SELECT currval('t_id_seq');

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

Исправим ситуацию, изменив состояние последовательности.

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

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

Теперь добавление удастся:

α=> INSERT INTO t(txt) VALUES('Восемь');

INSERT 0 1

α=> SELECT * FROM t;

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

Уборка

Удалим базу данных.

α=> \c postgres

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

α=> DROP DATABASE backup_logical;

DROP DATABASE

Page 31: Практика+

1. Создайте таблицу с несколькими строками. Подберите такие параметры \copy, чтобы при записи копии в файл перепутались корректные данные и неопределенные значения (NULL) из-за неудачного выбора кода. 2. Используя \copy, создайте текстовый файл, в котором отсутствие значения обозначается как N/A. Загрузите строки из этого файла в таблицу, в которой отсутствие значения представляет NULL. 3. Создайте таблицу с данными размером несколько мегабайт и получите размеры логических копий в пользовательском формате с разными способами сжатия. Также измерьте время, затраченное при копировании с каждым методом сжатия.

Комментарии преподавателя

1. Если ошибочно использовать код для NULL, который будет совпадать с каким-либо нормальным содержимым столбцов таблицы, получившаяся копия не позволит корректно восстановить данные.

2. Сгенерировать данные можно, например, запросом SELECT (random()::numeric*(g.i))^(g.i) AS n FROM generate_series(1,2_500) AS g(i);

Измерить время можно командой /usr/bin/time -f %e pg_dump … .

После выполнения практических заданий удалите созданные базы данных и временные файлы.

Page 32: 1. Ломаем COPY

Создадим базу данных.

α=> CREATE DATABASE backup_logical;

CREATE DATABASE

α=> \c backup_logical

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

Например, можно установить отображение неопределенных значений, совпадающее с какими-либо данными:

α=> CREATE TABLE anticopy(s text);

CREATE TABLE

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

INSERT 0 2

Установим для неопределенных значений код N:

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

COPY 2

Вывод двух разных значений теперь неотличим друг от друга. Корректно восстановить из такой копии данные невозможно.

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

N
N

Осторожнее с изменением формата по умолчанию!

2. Загрузка данных с пропусками

Создадим файл, в котором отсутствие данных будет отмечено как N/A.

α=> \copy (VALUES (0,'N/A'), (1,'Раз'), (2,'N/A'), (3,'Три')) to /home/student/tmp/backup_logical.txt

COPY 4

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

0
N/A
1
Раз
2
N/A
3
Три

Создадим таблицу для загрузки данных.

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

CREATE TABLE

Загрузим данные в таблицу из файла. Значение N/A должно загружаться как 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 | Раз | f
 2 |     | t
 3 | Три | f
(4 rows)

3. Сжатие в pg\_dump

Создадим таблицу достаточно большого размера.

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)

Измерим время выгрузки данных без сжатия (а также прогреем кеш).

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

0.44

Сколько потребуется времени на выгрузку со сжатием gzip (используется по умолчанию)?

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

1.81

Теперь испытаем lz4.

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

0.35

А теперь — zstd.

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

0.58

Сравним размеры полученных копий:

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

-rw-rw-r-- 1 student student 8,3M апр 10 18:12 /home/student/tmp/backup_logical.none
-rw-rw-r-- 1 student student 7,9M апр 10 18:12 /home/student/tmp/backup_logical.lz4
-rw-rw-r-- 1 student student 4,1M апр 10 18:12 /home/student/tmp/backup_logical.zstd
-rw-rw-r-- 1 student student 3,9M апр 10 18:12 /home/student/tmp/backup_logical.gzip
-rw-rw-r-- 1 student student   30 апр 10 18:12 /home/student/tmp/backup_logical.txt

Выводы: gzip сжимает лучше всех, однако работает в несколько раз дольше; lz4 — самый быстрый, но дает весьма небольшой эффект; zstd — хороший компромисс: степень сжатия лишь немного уступает gzip, а скорость сравнима с lz4.

Уборка

Удалим созданные базы данных и временные файлы.

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 Занятие 02: Резервное копирование / Базовая резервная копия

Page 1: Резервное копирование / Базовая резервная копия

Авторские права

© Postgres Professional, 2017–2025 Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Алексей Береснев Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)

Использование материалов курса

Некоммерческое использование материалов курса (презентации, демонстрации) разрешается без ограничений. Коммерческое использование возможно только с письменного разрешения компании Postgres Professional. Запрещается внесение изменений в материалы курса.

Обратная связь

Отзывы, замечания и предложения направляйте по адресу: edu@postgrespro.ru

Отказ от ответственности

Компания Postgres Professional не несет никакой ответственности за любые повреждения и убытки, включая потерю дохода, нанесенные прямым или непрямым, специальным или случайным использованием материалов курса. Компания Postgres Professional не предоставляет каких-либо гарантий на материалы курса. Материалы курса предоставляются на основе принципа «как есть» и компания Postgres Professional не обязана предоставлять сопровождение, поддержку, обновления, расширения и изменения.

Page 2: Темы

• Понятие физической резервной копии
• Холодное и горячее резервирование
• Протокол репликации и слот
• Клонирование кластера и архивная копия
• Проверка целостности
• API резервирования

Page 3: Физическая копия

Базовая физическая копия — копия всех файлов кластера

• • быстрее, чем логическое резервирование
• • восстанавливается статистика
• − можно восстановиться только на совместимой системе
• − и на той же самой основной версии PostgreSQL
• − выборочная копия невозможна, копируется весь кластер

Комментарии преподавателя

Физическое резервирование подразумевает копирование всех файлов, относящихся к кластеру БД (как файлов данных, так и служебных файлов), то есть создание полной двоичной копии.

Копирование файлов работает быстрее, чем выгрузка SQL-команд при логическом резервировании; запустить сервер из созданной физической копии — дело нескольких минут, в отличие от восстановления из логической копии. Кроме того, нет необходимости заново собирать статистику — она также восстанавливается из копии.

Но есть и минусы. Из физической резервной копии можно восстановить систему только на совместимой платформе (та же ОС, та же разрядность, тот же порядок байтов в представлении чисел и т. п.) и только на той же основной версии PostgreSQL. Кроме того, невозможно сделать физическую копию отдельных баз данных кластера, возможно копирование только всего кластера целиком.

Page 4: Холодное резервирование

Выключенный сервер

• если выполнена контрольная точка, то достаточно скопировать файлы
• копию можно развернуть на другом сервере, независимо от PGDATA
• • простота
• − требуется прерывание обслуживания
• Снимок файловой системы
  • как при неаккуратном выключении: при старте потребуется
  • восстановление, но в копию войдут все нужные файлы журнала
  • данные, в т. ч. табличные пространства, должны войти в один снимок
  • • не надо останавливать сервер
  • − файловые системы, поддерживающие снимки, работают медленней

Комментарии преподавателя

Смысл холодного резервирования состоит в том, чтобы сделать копию файлов кластера в тот момент, когда данные на диске согласованы, то есть на диск сброшено актуальное состояние всех страниц. Восстановление из такой копии происходит просто: файлы разворачиваются, запускается сервер — и он сразу же готов к работе.

Сделать такую копию можно лишь корректно остановив сервер с выполнением контрольной точки. Минус понятен: необходима остановка сервера (надолго при большом объеме данных).

Время простоя можно сократить за счет предварительного выполнения rsync (или аналогичного инструмента) при работающем сервере. Тогда после останова сервера rsync докопирует только изменения (которых, предположительно, будет немного). Но простоя все равно не избежать.

Другой вариант — сделать несогласованную копию, в которую попадут страницы, сохраненные в разное время. Такая рассогласованность неизбежна при некорректном отключении сервера или при создании снимка файловой системы. В последнем случае все файлы кластера должны попасть в один снимок. Это может оказаться невозможным, если файлы расположены в разных файловых системах.

При восстановлении из несогласованных данных серверу при старте потребуется согласовать страницы. Это обычная автоматическая процедура восстановления после сбоя. Она не представляет проблемы, так как необходимые файлы журнала гарантированно попадут в копию, но потребует некоторого времени.

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

Page 5: Холодная файловая копия

Файлы остановленного кластера можно скопировать и запустить с ними второй сервер. При этом не важно, был ли сервер остановлен корректно. Создадим базу данных и таблицу.

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 ('Эта строка вставлена на α...');

INSERT 0 1

Аварийно останавливаем сервер alpha и копируем файлы на сервер 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 запускается, восстанавливая согласованность:

student$ sudo pg_ctlcluster 16 beta start

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

2026-04-10 18:00:49.842 MSK [66007] LOG:  database system was not properly shut down;
automatic recovery in progress
2026-04-10 18:00:49.858 MSK [66007] LOG:  redo starts at 0/1B83330
2026-04-10 18:00:49.932 MSK [66007] LOG:  invalid record length at 0/1FB5708: expected at
least 24, got 0
2026-04-10 18:00:49.932 MSK [66007] LOG:  redo done at 0/1FB56E0 system usage: CPU: user:
0.00 s, system: 0.01 s, elapsed: 0.07 s
2026-04-10 18:00:50.063 MSK [66005] LOG:  checkpoint starting: end-of-recovery immediate
wait
2026-04-10 18:00:53.423 MSK [66005] LOG:  checkpoint complete: wrote 932 buffers (5.7%);
0 WAL file(s) added, 0 removed, 0 recycled; write=0.046 s, sync=3.242 s, total=3.375 s;
sync files=308, longest=0.026 s, average=0.011 s; distance=4297 kB, estimate=4297 kB;
lsn=0/1FB5708, redo lsn=0/1FB5708
2026-04-10 18:00:53.448 MSK [66004] LOG:  database system is ready to accept connections

student$ psql -p 5433 -d backup_base

β=> SELECT * FROM t;

              s
------------------------------
 Эта строка вставлена на α...
(1 row)

Page 6: Горячее резервирование

Работающий сервер

• страницы не согласованы между собой, как при неаккуратном
• выключении
• страница может меняться во время ее копирования
• (проблема неатомарного чтения и записи)
• для восстановления согласованности необходимы журнальные записи
• от последней контрольной точки за все время копирования
• (в процессе копирования сервер может удалить часть файлов)
• • не требуется прерывание обслуживания
• − нужны специальные инструменты

Комментарии преподавателя

Горячее резервирование выполняется на работающем сервере, поэтому в копию наверняка попадут несогласованные между собой страницы.

Более того. При резервном копировании данные читаются не через буферный кеш, а напрямую из файлов. Содержимое файлов на диске, очевидно, изменяется во время копирования, а гарантии атомарности чтения/записи страниц PostgreSQL может и не быть (это зависит от файловой системы и оборудования и обсуждается в теме «Настройка журнала» курса DBA2). Поэтому в резервную копию могут попадать «безнадежные» страницы, к которым даже нельзя применить журнальные записи. Это первая сложность.

Вторая сложность состоит в том, что копирование файлов данных может занимать достаточно много времени. Но сервер, после выполнения очередной контрольной точки, может удалить часть файлов журнала, которые уже не нужны ему для восстановления после сбоя, но нужны для резервной копии.

Если не принять специальных мер, сделанная резервная копия будет непригодна для восстановления. Поэтому для горячего резервирования требуются специальные инструменты. PostgreSQL предоставляет низкоуровневый интерфейс, используя который можно реализовать надежное копирование. Этот интерфейс использует и штатная утилита pg_basebackup, и другие сторонние средства резервного копирования.

https://postgrespro.ru/docs/postgresql/16/continuous-archiving#BACKUP-BA SE-BACKUP

Page 7: Протокол репликации

Задачи

• управление резервным копированием и репликацией
• в частности, получение потока журнальных записей
• Обслуживается процессом wal sender
  • max_wal_senders = 10
  • wal_level = replica или logical, но не minimal
• Подключение
  • роль с атрибутом REPLICATION или SUPERUSER
  • разрешение на подключение в pg_hba.conf

Комментарии преподавателя

Для упрощения задачи сервер PostgreSQL предоставляет протокол репликации — специальный протокол для управления как собственно репликацией (рассматривается в одноименном модуле), так и резервным копированием. В частности, он позволяет получать поток журнальных записей, которые генерирует сервер.

На сервере подключение по протоколу репликации обслуживается процессом wal sender. Он похож на обычный обслуживающий процесс, который запускается при обычном подключении клиента, но понимает не SQL, а специальные команды. Число одновременно работающих процессов wal_sender ограничено параметром max_wal_senders (значение по умолчанию — 10).

Уровень журнала должен быть не ниже, чем replica. Дело в том, что на уровне minimal такие команды, как CREATE TABLE AS SELECT, CREATE INDEX или COPY FROM не попадают в журнал: долговечность обеспечивается тем, что данные не остаются в оперативной памяти, а сразу записываются на диск. Этого достаточно для восстановления после сбоя и из холодной копии, но недостаточно для восстановления из горячей копии.

Чтобы использовать протокол репликации, клиент должен подключаться к серверу под ролью, имеющей атрибут REPLICATION (либо под суперпользователем). В pg_hba.conf надо разрешить подключение этой роли к базе данных replication (это не название БД, а ключевое слово). Причем разрешения для all недостаточно, подключение к replication должно быть разрешено отдельно.

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

Page 8: Слот репликации

Серверный объект для гарантии получения журнальных записей

• помнит, какая запись была передана клиенту последней
• число слотов ограничено max_replication_slots = 10
• Не позволяет удалить сегмент WAL, пока все его записи не переданы клиенту
  • клиент может получать данные в удобном режиме,
  • в том числе отключаться на время
  • при отключении клиента журнальные файлы накапливаются на сервере
  • мониторинг состояния: pg_replication_slots

Комментарии преподавателя

Чтобы при выполнении контрольной точки сервер не удалил файлы WAL преждевременно, можно применять слот репликации. Если поток журнальных записей идет через слот, то слот помнит, какие записи уже были переданы клиенту, и не позволяет серверу удалять файл WAL до тех пор, пока клиент не получит все записи из этого файла.

Использование слота позволяет клиенту не беспокоиться о том, что сервер сотрет файл журнала раньше времени. Клиент даже может отключиться и затем через какое-то время подключиться вновь и продолжить получать журнальные записи с того момента, на котором остановился.

Но надо иметь в виду, что при отключении клиента файлы журнала будут накапливаться на сервере и могут занять все свободное место. Поэтому каждый созданный слот следует добавлять в мониторинг (представление pg_replication_slots) и своевременно удалять ненужные слоты.

Общее количество слотов, которые могут быть созданы, ограничено конфигурационным параметром max_replication_slots (значение по умолчанию — 10).

Подробнее применение слотов обсуждается в модуле «Репликация».

https://postgrespro.ru/docs/postgresql/16/warm-standby#STREAMING-REP LICATION-SLOTS

Page 9: Клонирование кластера

{#source-figure-009 caption=“Source slide page 9: Клонирование кластера”}

для физического копирования используется утилита pg_basebackup после копирования новый сервер готов к запуску

Комментарии преподавателя

Для немедленного развертывания нового сервера из копии вызывают pg_basebackup в обычном формате (–format=plain — по умолчанию), запуская утилиту на целевом сервере. Она подключается к серверу-источнику и копирует с него файлы в локальный каталог, указанный параметром –pgdata.

Дополнительные табличные пространства копируются по тем же абсолютным путям, что и на сервере-источнике. Пути можно переназначить, указав соответствия в параметрах –tablespace-mapping утилиты. Это необходимо, если pg_basebackup запускается на самом сервере-источнике.

При потоковой доставке (–wal-method=stream — по умолчанию) утилита устанавливает второе соединение для передачи потока журнальных записей, которые генерирует работающий сервер, в локальный каталог, указанный параметром –waldir.

По умолчанию второе соединение использует временный слот репликации, который создается при подключении и удаляется при завершении работы pg_basebackup. Однако можно указать и имя постоянного слота, который должен существовать на момент запуска утилиты или создаваться ей.

В формате plain можно сжимать данные на сервере, уменьшая нагрузку на сеть. Переданные данные затем распаковываются утилитой. Но журнальные записи в потоковом режиме не сжимаются.

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

Page 10: Клонирование кластера

Сделаем базовую копию работающего сервера alpha и восстановим с помощью этой копии сервер beta.

student$ sudo pg_ctlcluster 16 alpha start

student$ psql backup_base

Разрешение на локальное подключение по протоколу репликации в 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)

Чтобы утилита pg_basebackup могла подключиться к серверу под ролью student, эта роль должна иметь атрибут REPLICATION:

α=> \du student

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