Резервное копирование
Логическое резервирование
12
Авторские права
© Postgres Professional, 2017–2021
Авторы: Егор Рогов, Павел Лузанов
Использование материалов курса
Некоммерческое использование материалов курса (презентации,
демонстрации) разрешается без ограничений. Коммерческое
использование возможно только с письменного разрешения компании
Postgres Professional. Запрещается внесение изменений в материалы
курса.
Обратная связь
Отзывы, замечания и предложения направляйте по адресу:
Отказ от ответственности
Компания Postgres Professional не несет никакой ответственности за
любые повреждения и убытки, включая потерю дохода, нанесенные
прямым или непрямым, специальным или случайным использованием
материалов курса. Компания Postgres Professional не предоставляет
каких-либо гарантий на материалы курса. Материалы курса
предоставляются на основе принципа «как есть» и компания Postgres
Professional не обязана предоставлять сопровождение, поддержку,
обновления, расширения и изменения.
2
Темы
Логические и физические резервные копии
Копирование и восстановление отдельных таблиц
Копирование и восстановление отдельных баз данных
Копирование и восстановление кластера
3
Логическая копия
Команды SQL для создания объектов и наполнения
данными
+ можно сделать копию отдельного объекта или отдельной базы
+ можно восстановиться на другой версии или архитектуре
+ (не требуется двоичная совместимость)
+ простота использования
− невысокая скорость работы
− восстановление только на момент создания резервной копии
Логическая резервная копия — набор команд SQL, восстанавливающих
кластер (или отдельную базу данных, или отдельную таблицу) с нуля:
создаются необходимые объекты и наполняются данными.
Команды можно выполнить на другой версии СУБД (при наличии
совместимости на уровне команд) или на другой платформе и
архитектуре (не требуется двоичная совместимость).
В частности, логическую резервную копию можно использовать для
долговременного хранения: ее можно будет восстановить и после
обновления сервера на новую версию.
Создание логической резервной копии — относительно несложный
процесс. Обычно он требует выполнения одной команды или запуск
одной утилиты.
Однако для большой базы команды могут выполняться очень долго.
Восстановить систему из логической копии можно ровно на момент
начала резервного копирования.
4
Физическая копия
Копия файловой системы кластера баз данных
+ быстрее, чем логическое резервирование
+ восстанавливается статистика
− можно восстановиться только на совместимой системе
− и на той же самой основной версии PostgreSQL
− выборочная копия невозможна, копируется весь кластер
Архив журнала предзаписи
+ возможность восстановления на определенный момент времени
Физическое резервирование подразумевает копирование всех файлов,
относящихся к кластеру БД, то есть создание полной двоичной копии.
Копирование файлов работает быстрее, чем выгрузка SQL-команд при
логическом резервировании; запустить сервер из созданной
физической копии — дело нескольких минут, в отличие от
восстановления из логической копии. Кроме того, нет необходимости
заново собирать статистику — она также восстанавливается из копии.
Но есть и минусы. Из физической резервной копии можно восстановить
систему только на совместимой платформе (та же ОС, та же
разрядность, тот же порядок байтов в представлении чисел и т. п.) и
только на той же основной версии PostgreSQL. Кроме того, невозможно
сделать физическую копию отдельных баз данных кластера, возможно
копирование только всего кластера целиком.
Как правило физические резервные копии используют совместно
с архивом журнала предзаписи. Это позволяет восстанавливать
систему не только на момент создания резервной копии, но и на
произвольный момент времени.
Физическое резервирование — обычный способ создания
периодических резервных копий любой сколько-нибудь серьезной
системы. Создание таких копий — ответственность администратора баз
данных.
5
Копия таблицы в SQL
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.
Вариант COPY TO позволяет записать таблицу (или часть столбцов
таблицы, или даже результат произвольного запроса) либо в файл,
либо на консоль, либо на вход какой-либо программе. При этом можно
указать ряд параметров, таких как формат (текстовый, CSV или
двоичный), разделитель полей, текстовое представление NULL и т. п.
Вариант COPY FROM, наоборот, считывает из файла или из консоли
строки и записывает их в таблицу. Таблица при этом не очищается,
новые строки добавляются к уже существующим.
Команда COPY работает существенно быстрее, чем аналогичные
команды INSERT — клиенту не нужно много раз обращаться к серверу,
а серверу не нужно много раз анализировать команды.
Тонкость: при выполнении команды COPY FROM не применяются
правила (rules), хотя ограничения целостности и триггеры выполняются.
6
Копия таблицы в psql
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 с аналогичным
синтаксисом.
Имя файла, указанное в SQL-команде COPY, соответствует файлу на
сервере БД. У пользователя, под которым работает PostgreSQL (обычно
postgres), должен быть доступ к этому файлу.
В клиентском же варианте команды файл располагается на клиенте,
что позволяет сохранить локальную копию данных, даже не имея
доступа к файловой системе сервера. Содержимое таблиц
автоматически пересылается между клиентом и сервером.
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
$ pg_dump -d база -f файл
$ psql -f файл
формат: команды SQL
при выгрузке можно выбрать отдельные объекты базы данных
новая база должна быть создана из шаблона template0
заранее должны быть созданы роли и табличные пространства
после загрузки имеет смысл выполнить ANALYZE
Для создания полноценной резервной копии базы данных используется
утилита pg_dump.
Если не указать имя файла (-f, --file), то утилита выведет результат на
консоль. А результатом является скрипт, предназначенный для psql,
который содержит команды для создания необходимых объектов
и наполнения их данными.
Дополнительными ключами утилиты можно ограничить набор объектов:
выбрать указанные таблицы, или все объекты в указанных схемах, или
наложить другие фильтры.
Чтобы восстановить объекты из резервной копии, достаточно
выполнить полученный скрипт в 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).
10
Формат directory
$ pg_dump -d база -F d -j N -f каталог
$ pg_restore -d база -j N каталог
каталог с оглавлением и отдельными файлами на каждый объект базы
отдельные объекты базы данных можно выбрать на этапе восстановления
и выгрузка, и загрузка возможны в несколько параллельных потоков
согласованность
обеспечивается общим
снимком данных
Еще один формат резервной копии — directory. В таком случае будет
создан не один файл, а каталог, содержащий объекты и оглавление.
По умолчанию файлы внутри каталога будут сжаты.
Преимущество перед форматом custom состоит в том, что такая
резервная копия может создаваться параллельно в несколько потоков
(количество указывается в ключе -j, --jobs).
Разумеется, несмотря на параллельное выполнение, копия будет
содержать согласованные данные. Это обеспечивается общим снимком
данных для всех параллельно работающих процессов.
Восстановление также возможно в несколько потоков (это работает
и для формата custom).
В остальном возможности по работе с форматом directory не
отличаются от ранее рассмотренных: поддерживаются те же ключи
и подходы.
11
Сравнение форматов
plain custom directory tar
утилита для
восстановления
psql pg_restore
сжатие zlib
выборочное
восстановление
да да да
параллельное
резервирование
да
параллельное
восстановление
да да
В приведенной таблице разные форматы сравниваются с точки зрения
предоставляемых ими возможностей.
Отметим, что имеется и четвертый формат — tar. Он не
рассматривался, так как не привносит ничего нового и не дает
преимуществ перед другими форматами. Фактически он соответствует
созданию tar-файла из каталога в формате directory, но не
поддерживает сжатие и параллелизм.
13
Копия кластера БД
--
-- 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 в параллельном режиме.
15
Итоги
Логическое резервирование позволяет сделать копию
всего кластера, базы данных или отдельных объектов
Хорошо подходит
для данных небольшого объема
для длительного хранения, за время которого меняется версия сервера
для миграции на другую платформу
Плохо подходит
для восстановления после сбоя с минимальной потерей данных
16
Практика
1. Создайте резервную копию базы данных bookstore
в формате custom.
«Случайно» удалите все записи из таблицы authorship.
Проверьте, что приложение перестало отображать названия
книг на вкладках «Магазин», «Книги», «Каталог».
Используйте резервную копию для восстановления
потерянных данных в таблице.
Проверьте, что нормальная работа книжного магазина
восстановилась.
1. При восстановлении используйте ключ --data-only, чтобы избежать
ошибки при попытке создания таблицы.
17
Практика
1. Создайте таблицу с политикой, разрешающей чтение только
части строк. Создайте непривилегированного пользователя
для Алисы и предоставьте ей доступ к таблице.
В обязанности Алисы входит резервное копирование
таблицы. Сможет ли она выполнять их, не являясь
суперпользователем? Проверьте.
2. Команда psql \copy позволяет направить результат на вход
произвольной программы. Воспользуйтесь этим, чтобы
открыть результаты какого-нибудь запроса в электронной
таблице LibreOffice Calc.
1. К роли с правами суперпользователя не применяются политики
защиты строк. Однако Алиса, как непривилегированный пользователь,
сможет прочитать лишь часть строк таблицы и даже не узнает о том,
что это не все данные.
Параметр row_security позволит Алисе хотя бы узнать о том, что не
удалось прочитать все данные. А атрибут роли BYPASSRLS решит
задачу.
2. Команда должна перенаправить результат в файл, а затем запустить
libreoffice, указав этот файл в качестве параметра. Файл должен быть
записан в формате CSV.
Конечно, такой способ зависит от платформы и без модификации не
будет работать, например, в Windows.
