Резервное копирование
Логическое резервирование
16
Авторские права
© Postgres Professional, 2017–2025
Авторы: Егор Рогов, Павел Лузанов, Илья Баштанов, Алексей Береснев
Фото: Олег Бартунов (монастырь Пху и пик Бхрикути, Непал)
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
Postgres Professional. Запрещается внесение изменений в материалы
курса.
Обратная связь
Отзывы, замечания и предложения направляйте по адресу:
edu@postgrespro.ru
Отказ от ответственности
Компания Postgres Professional не несет никакой ответственности за
любые повреждения и убытки, включая потерю дохода, нанесенные
прямым или непрямым, специальным или случайным использованием
материалов курса. Компания Postgres Professional не предоставляет
каких-либо гарантий на материалы курса. Материалы курса
предоставляются на основе принципа «как есть» и компания Postgres
Professional не обязана предоставлять сопровождение, поддержку,
обновления, расширения и изменения.
2
Темы
Понятие логической резервной копии
Копирование и восстановление отдельных таблиц
Копирование и восстановление баз данных
Копирование и восстановление кластера
3
Логическая копия
Команды SQL для создания объектов и наполнения
данными
+ можно сделать копию отдельного объекта или отдельной базы
+ можно восстановиться на другой версии или архитектуре
+ (не требуется двоичная совместимость)
− невысокая скорость работы
− восстановление только на момент создания резервной копии
Логическая копия содержит набор команд SQL, выполнив которые
можно восстановить кластер, базу данных или выбранные таблицы.
В процессе восстановления из логической копии создаются
необходимые объекты и наполняются данными.
Команды можно выполнить на другой версии СУБД или даже на другой
платформе и архитектуре, так как не требуется двоичная
совместимость.
В частности, логическую резервную копию можно использовать для
долговременного хранения: ее можно будет восстановить и после
обновления сервера на новую версию.
Однако для большой базы команды могут выполняться очень долго.
Восстановить систему из логической копии можно ровно на момент
начала резервного копирования.
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
=# COPY таблица TO 'файл';
=# COPY таблица FROM 'файл';
файл в ФС сервера, принадлежащий владельцу экземпляра 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 — заголовок, описывающий столбцы.
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
=# \copy таблица to 'файл'
=# \copy таблица from 'файл'
файл в ФС клиента и доступен пользователю ОС, запустившему psql
происходит пересылка данных между клиентом и сервером
синтаксис и возможности аналогичны команде COPY
В psql существует метакоманда \copy — вариант команды COPY
с аналогичным синтаксисом, — выполняющаяся на стороне клиента.
Файл должен быть доступен пользователю операционной системы,
запустившему psql. Обращение к файлу происходит на клиенте, а на
сервер передается только содержимое.
7
Копия базы данных
--
-- PostgreSQL database dum
--
-- Dumped from database ve
-- Dumped by pg_dump versi
SET statement_timeout = 0;
SET lock_timeout = 0;
SET
idle_in_transaction_sessio
SET client_encoding = 'UTF
SET standard_conforming_st
$ pg_dump -d база -f файл
$ psql -f файл
формат: команды SQL
при выгрузке можно выбрать отдельные объекты базы данных
новая база должна быть создана из шаблона template0
заранее должны быть созданы роли и табличные пространства
после загрузки имеет смысл выполнить ANALYZE
Для создания полноценной резервной копии базы данных используется
утилита pg_dump.
Если не указать имя файла (-f, --file), то утилита выведет результат
в стандартный поток вывода.
Результатом является скрипт, предназначенный для psql, который
содержит команды для создания объектов и наполнения их данными.
Дополнительными ключами утилиты можно ограничить набор объектов:
выбрать указанные таблицы или все объекты в указанных схемах,
выгрузить только определения объектов или только данные и т. п.
Следует иметь в виду, что базу данных для восстановления надо
создавать из шаблона template0, так как все изменения, сделанные
в template1, попадут в резервную копию.
Кроме того, заранее должны быть созданы необходимые роли
и табличные пространства. Поскольку эти объекты не относятся
к конкретной БД, они не будут выгружены в резервную копию.
После восстановления базы данных имеет смысл выполнить команду
ANALYZE — она соберет статистику, необходимую оптимизатору для
планирования запросов.
9
Формат 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 -d база -F c -f файл
$ pg_restore -d база -j N файл
внутренний формат с оглавлением
отдельные объекты базы данных можно выбрать на этапе восстановления
возможна загрузка в несколько параллельных потоков
Утилита 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.
11
Формат directory
$ pg_dump -d база -F d -j N -f каталог
$ pg_restore -d база -j N каталог
каталог с оглавлением и отдельными файлами на каждый объект базы
отдельные объекты базы данных можно выбрать на этапе восстановления
и выгрузка, и загрузка возможны в несколько параллельных потоков
согласованность
обеспечивается общим
снимком данных
Еще один формат резервной копии — directory. В таком случае будет
создан не один файл, а каталог, содержащий объекты и оглавление.
По умолчанию файлы внутри каталога будут сжаты.
Преимущество перед форматом custom состоит в том, что такая
резервная копия может создаваться параллельно в несколько потоков
(количество указывается в ключе -j, --jobs).
Разумеется, несмотря на параллельное выполнение, копия будет
содержать согласованные данные. Это обеспечивается общим снимком
данных для всех параллельно работающих процессов.
Восстановление, как и для формата custom, возможно в несколько
потоков.
В остальном возможности по работе с форматом directory не
отличаются от ранее рассмотренных: поддерживаются те же ключи и
подходы.
13
Сравнение форматов
plain custom directory tar
утилита для
восстановления
psql pg_restore
сжатие gzip, lz4 или zstd
выборочное
восстановление
да да да
параллельное
резервирование
да
параллельное
восстановление
да да
В приведенной таблице разные форматы сравниваются с точки зрения
предоставляемых ими возможностей.
Имеется и четвертый формат — tar. Он не рассматривался, так как
не привносит ничего нового и не дает преимуществ перед другими
форматами. Фактически он соответствует созданию tar-файла из
каталога в формате directory, но не поддерживает сжатие и
параллелизм.
14
Копия кластера БД
--
-- PostgreSQL database clu
--
SET default_transaction_re
SET client_encoding = 'UTF
SET standard_conforming_st
--
-- Roles
--
CREATE ROLE postgres;
$ pg_dumpall -f файл
$ psql -f файл
формат: команды SQL
выгружает весь кластер, включая роли и табличные пространства
пользователь должен иметь доступ ко всем объектам кластера
не поддерживает параллельную выгрузку
Чтобы создать резервную копию всего кластера, включая роли и
табличные пространства, можно воспользоваться утилитой pg_dumpall.
Поскольку pg_dumpall требуется доступ ко всем объектам всех БД,
имеет смысл запускать ее от имени суперпользователя. Утилита по
очереди подключается к каждой БД кластера и выгружает информацию
с помощью pg_dump. Кроме того, она сохраняет и данные, относящиеся
к кластеру в целом.
Чтобы начать работу, утилите требуется подключиться хотя бы
к какой-то базе данных. По умолчанию выбирается postgres или
template1, но можно указать и другую.
Результатом работы pg_dumpall является скрипт для psql. Другие
форматы не поддерживаются. Это означает, что pg_dumpall не
поддерживает параллельную выгрузку данных, что может оказаться
проблемой при больших объемах данных. В таком случае можно
воспользоваться ключом --globals-only, чтобы выгрузить только роли
и табличные пространства, а сами базы данных выгрузить отдельно
с помощью pg_dump в параллельном режиме.
16
Итоги
Логическое резервирование позволяет сделать копию
всего кластера, базы данных или отдельных объектов
Хорошо подходит
для данных небольшого объема
для длительного хранения, за время которого меняется версия сервера
для миграции на другую платформу
Плохо подходит
для восстановления после сбоя с минимальной потерей данных
17
Практика
1. Создайте пару баз данных с таблицами и представлениями,
получите с помощью pg_dumpall копию только глобальных
объектов кластера, а затем сделайте копии баз данных
с помощью утилиты pg_dump в параллельном режиме.
Восстановите кластер на другом сервере, используя
созданные резервные копии.
2. Создайте таблицу с двумя столбцами, один из которых
является первичным ключом и заполняется
последовательностью. Воспроизведите ситуацию, когда
при загрузке с помощью COPY последовательность не
генерирует новые значения, вызывая нарушение
уникальности при последующей вставке строк.
2. Объявите столбец как GENERATED ALWAYS AS IDENTITY.
После выполнения практических заданий удалите созданные базы
данных и файлы логических копий.
18
Практика+
1. Создайте таблицу с несколькими строками. Подберите такие
параметры \copy, чтобы при записи копии в файл
перепутались корректные данные и неопределенные значения
(NULL) из-за неудачного выбора кода.
2. Используя \copy, создайте текстовый файл, в котором
отсутствие значения обозначается как N/A. Загрузите строки
из этого файла в таблицу, в которой отсутствие значения
представляет NULL.
3. Создайте таблицу с данными размером несколько мегабайт
и получите размеры логических копий в пользовательском
формате с разными способами сжатия. Также измерьте время,
затраченное при копировании с каждым методом сжатия.
1. Если ошибочно использовать код для NULL, который будет совпадать
с каким-либо нормальным содержимым столбцов таблицы,
получившаяся копия не позволит корректно восстановить данные.
3. Сгенерировать данные можно, например, запросом
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 … .
После выполнения практических заданий удалите созданные базы
данных и временные файлы.
