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

Авторская площадка "Наши орбиты" состоит из ряда тематических подразделов, являющихся моими лабораторными дневниками, содержащими записи за разное, иногда продолжительно отличающееся, время. Эти материалы призваны рассказать о прошедшем опыте, они никого ни к чему не призывают и совершенно не обязательно могут быть применимы кем-то ещё. Это только лишь истории о прошлом

Варианты резервного копирования

Статья написана после вдумчивой проработки вторым проходом материалов курса PqSQL. DBA3 как самая выжимка важного и подсказок. Курс за два дня значит потом пару недель вдумчивой работы, чтобы утрясти знания и договориться с ними. Бэкапы - это первая и краеугольная задача инженера по поддержке, именуемого также администратор БД. Если есть работающая система, она может упасть и пропасть. И нужно обеспечить возможность эту работающую систему восстановить. А восстановить можно, только заранее подстелив соломку на место падения, т.е. сделав резервную копию. И эта задача настолько важна, что в серьёзной СУБД Oracle под нее реализованы отдельная утилита набор команд для написания скриптов. В ванильной версии PgSQL есть целых несколько разных команд и методов для создания резервных копий, но нет средства для создания инкрементального бэкапа. Компания PgPRO разрабатывает свою утилиту резервного копирования именно энтерпрайз уровня, и делится ей с сообществом, оставляя самые плюшки в своей закрытой версии. Не то, чтобы они были очень нужны, но они есть

Из методов резервирования есть команда COPY для выгрузки и загрузки отдельных таблиц, команда pg_dump, выгружающие базу и кластер для последующей перегрузки и обеспечивающие целостность на момент старта операции даже в многопоточном режиме. Это уже заявка на резервирование, но опять же это не энтерпрайз решение, требующее обеспечить минимальные потери данных при восстановлении и горячий бэкап на открытой базе. Энетерпрайз решение обеспечивает горячий бэкап без остановки работы БД, и с минимальными потреями данных, т.к. бэкап целостен на конец бэкапа, а не на начало, как в случае с pg_dump. Функции энтерпрайз бэкапа выполняет идущая в составе поставки общедоступной (ванильной) версии утилита pg_basebackup, но она не обеспечивает инкрементальное резервное копирование. Есть ряд свободных решений, которые непонятно, можно ли применять в промышленных контурах на критичных системах, и вот есть ещё утилита от PgPRO (Постгрес ПРО)

Команда COPY. Аналог SQL LOADER. Выгрузка и загрузка отдельных таблиц

Команда COPY есть серверная (документация), а есть встроенная в клиента psql - клиентская (документация). Она может выгружать и загружать данные в таблиц, сохранять выгрузки в двоичном и тестовом формате. Поддерживается также выгрузка по фильтру - запросу SQL. При загрузке данных выполняются триггеры и ограничения целостности, а строки добавляются к удже существующим. Но в курсе говорится, что не выполняются rules (защита на уровне строк). Поддерживается текстовый, csv и binary форматы. Можно воспринимать эту команду как встроенный с PgSQL аналог sql_loader. Пример работы команды:

--COPY contracts_history2 to '/tmp/contracts_history2_back.20240404.04.out' WITH (FORMAT binary) ;
COPY contracts_history2 to '/tmp/contracts_history2_back.20240404.05.out' WITH (FORMAT csv) ;
drop table contracts_history_04 ;
create table contracts_history_04 as select * from contracts_history2 ;
select count(*) from contracts_history_04 ;
delete from contracts_history_04 ;
select count(*) from contracts_history_04 ;
--COPY contracts_history_04 from '/tmp/contracts_history2_back.20240404.04.out' WITH (FORMAT binary) ;
COPY contracts_history_04 from '/tmp/contracts_history2_back.20240404.05.out' WITH (FORMAT csv) ;
select count(*) from contracts_history_04 ;

Команды PG_DUMP, PG_RESTORE и PG_DUMPALL. Аналог exp, imp, expdp, impdp

Команда pg_dump (документация) выгружает базу, но не выгружает роли и табличные пространства, т.к. они относятсяко всему кластеру. Поддерживает частичную выгрузку с включением или исключением схем и таблиц, а для загрузки командой pg_restore из формата custom - ещё и выбор функций, индексов, триггеров. Поддерживается выгрузка только метаданных и данных, различные форматы, в т.ч. обеспечивающие параллельную многопоточную выгрузку (но в 9 версии их нет) и выборочную загрузку (plain можно скормить psql, custom с описаниями для выбора, directory возможна выгрузка в несколько потоков), генерировать вдобавок команды предварительного удаления объектов --clean для заливки начисто. Данные выгружаются целостно на момент начала операции. Изменения из template1 попадают в выгрузку, так что заливать обратно лучше в БД, сделанную из template0, что обеспечивает ключ --create(-C)

Для выгрузки всего кластера есть команда pg_dumpall (документация), но она выгружает однопоточно, только plain для скармливания psql, выгружает роли и табличные пространства, а затем обходит базы кластера и выгружает их утилитой pg_dump. Поэтому для многопоточной выгрузки есть рекомендация использовать ключ --globals-only, а потом перегружать данные через pg_dump/pg_restore

Команда PG_BASEBACKUP. Аналог USER MANAGED бэкапов

Холодный бэкап корректно выключенного экземпляра поддерживается. При некорректном выключении потребуется автоматическая процедура восстановления. Аналога RMAN в ванильной версии СУБД Pg нет. Однако есть почти полный аналог USER MANAGED бэкапа, когда БД переводится в специальный режим расширенной записи в архивные журналы, после чего делаются копии файлов данных и готовится комплект для восстановления - файлы данных и требуемые для перезаписи изменений поверх них файлы журналов WAL. С учётом того, что третий уровень физики вынесен в Pg в файловую систему и файлов - аналогов экстентов - с каталоге БД может быть очень много, все эти действия делает выделенная утилита pg_basebackup (докумекнтация), делая это на горячей БД, обслуживающей в это время клиентские запросы. Горячее резервирование имеет свои ограничения, описанные в документации по ввылке ниже. Также стоит отметить активные рекомендации предодавателей не выключать параметр full_page_writes при настройке WAL (redo) подсистемы для нормальной работы горячего резервирования в разных сценариях

Важным моментом является архитектурное отличие методов журналирования у Oracle и PgSQL. У Oracle оперативные журналы REDO закольцованы в буфер, и поэтому при создании горячего бэкапа, который может быть долгим, обязательно включения режима архивных журналов. В PgSQL журналы WAL не закольцовываются, накапливаются и удаляются по мере отработки очередной контрольной точки. Их также можно относить в сторону в режиме ARCHIVELOG, что для продуктового режима норма, но кроме этого есть механизм репликационных слотов (документация репликации, документация слотов репликации) обеспечивающий задержку в удалении журналов WAL при необходимости. Независимо от того, активирован ARCHIVELOG или нет. Именно этот механизм используется для создания горячих бэкапов и для создания физических реплик, т.е. физических стэндбаев в терминологии Oracle. Основные параметры настройки репликационных слотов - [1] max_wal_senders, [2] wal_level = replica или logical, [3] max_replication_slots (>=2 для pg_basebackup, использующего два временных слота), [4] max_slot_wal_keep_size, [5] наличие роли с атрибутом REPLICATION и [6] настройка в hba.conf репликационных подключений, мониторинг накапливающихся журналов WAL - в представлении pg_replication_slots. С 10 версии используется временный репликационный слот. С помошью ключа -R можно создать копию кластера для развёртывания стэндбая. Примкр запуска утилиты для создания обычного бэкапа :

pg_basebackup -F t -D /var/lib/pgsql/tmp_back/cp1 --progress

Восстановление из такого бэкапа происходит методом разворачивания бэкапа в каталог кластера БД и старта сервера. Можно проверить пригодность бэкапа утилитой pg_verifybackup (документация). При последующем старте кластера из созданного бэкапа можно смапировать в новые пути пользовательские табличные пространства - каталоги в новые пути, заменив их в файле tablespace_map в конте бэкапа, при этом символическая ссылка появится в подкаталоге /pg_tblspc/

Организация архивирования журналов, режим ARCHIVELOG

Для обеспечения подката данных из бэкапа на точку времени реализован механизм архивирования журналов, включаемый опцией archive_mode = on, и archive_commend = пользовательский_скрипт, с подстановкой переменныз %p (полный пут к файлу журнала) и %f (куда копировать). Команда должна возвращать 0 при успешном копировании и сохранении очередного файла архива. Принудительное переключение происходит по параметру archive_timeout или функцией pg_switch_wal(), мониторинг через представление pg_stat_archive, можно получать сообщения в журнал сервера, включив параметр logging_collector = on. Этот режим называется непрерывным копированием, но реально копирование происходит только в момент переключения журнала, т.е. с задержками и возможными потерями информации. Посмотреть имя текущего журнала можно, вызвав процедуру pg_walfile_name(pg_current_wal_lsn()). Концентуальный вариант скрипта выноса архивных журналов представлен ниже:

- добавляем запись о журналировании в свой текстовый журнал
- проверяем, что в целевом каталоге такого файла нет
- копируем журнал WAL
  test ! -f /var/lib/pgsql/tmp_back/archive/%f && cp %p /var/lib/pgsql/tmp_back/archive/%f
  [ ! -f /var/lib/pgsql/tmp_back/archive/%f ] && cp %p /var/lib/pgsql/tmp_back/archive/%f
- расчитываем и сравниваем хэш источника пи приёмника
- проверяем идентичность хэшей, добавляем запись результате проверки в свой текстовый журнал
- если хэши идентичны - возварщаем 0, иначе возвращаем -1

Альтернативный метод архивирования журналов - использование механизма репликации и утилиты pg_receivewal (документация, если не указать --endpos=lsn, она будет получать архивы бесконечно). Опции конфигурирования те же max_wal_senders, max_replication_slots и по списку, как и для pg_basebackup. Каким бы методом не делалось архивирование, очистка архива проводится утилитой pg_archivecleanup (документация), которой нужно скормить номер последнего сохраняемого WAL и путь к архиву, чтобы были удалены все более старые. Пример настройки архивирования журналов через репликацию:

pg_receivewal --create-slot --slot=arch_log_slot
pg_receivewal -D /var/lib/pgsql/tmp_back/arch_log_02 --slot=arch_log_slot

Растущий архив удобно чистить утилитой pg_archivecleanup документация, которой передаётся путь к архиву и имя последнего сохраняемого сегмента WAL (или %r). При разовом восстановлении можно задать команду очистки параметром recovery_end_command, который принимает метапоследовательность %r, содержащую файл с указателем на последнюю завершившуюся контрольную точку

Восстановление на точку времени

Для начала управляемого пользователем восстановления БД нужно создать в корне кластера БД сигнальный файл recovery.signal, указать параметром restore_command обратную ARCHIVE_COMMAND команду восстановления WAL из архивных журналов, восстанавливающаю из %f в %p и возвращающую 0 только при успехе. Да, при запросе несуществующего файла тоже должна вернуть ненулевое значение. Пример команды копирования жрналов для восстановления:

[ -f /var/lib/pgsql/tmp_back/archive/%f ] && cp /var/lib/pgsql/tmp_back/archive/%f  %p

Также указывают при необходимости - параметры точки восстановления (recovery_target = 'immediate', recovery_target_name = 'имя_ТО', ТочкаОтката создаётся заранее командой pg_create_restore_point('имя'), recovery_target_time = 'время_восстановления', recovery_target_xid = 'xid', recovery_target_lsn = 'lsn'. До 12 версии цели помещались в отдельный recovery.conf. Также есть команда уточняющая, включать или нет саму точку recovery_target_inclusive = on|off (документация). Также можно переназначить пути к табличным пространствам в файле tablespace_map. А параметр recovery_target_action =[shutdown|pause|promoute] отвечает за окончание восстановления. Shutdown по завершению наката тушит сервер, но файл - флаг не удаляет, promoute открывает соединения, pause позволяет или задать новую цель и рестартом сервера продолжить восстановление, или завершить его вызовом процедуры pg_wal_replay_resume()

Линии времени (аналог инкарнаций БД в Oracle)

Линии времени - это аналог инкарнаций в Oracle. Если бэкап достаточно старый, то восстановление может пройти по нескольким инкарнациям последовательно, за что отвечает параметр recovery_target_timeline, который может принимать значения 'latest', 'current', '<номер линии времени>'. Информация о линиях времени хранится в каталоге PGDATA/pg_wal/N.hostory, которые также нужно архивировать и не стоит удалять ...

Команды проверки целостности БД

Важно, что USER MANAGED бэкап, как и в Oracle, не проверяет логическую целостность блоков, копируя содержимое файлов данных средствами файловой системы. Однако при включении режима проверки контрольных сумм блоков утилита pg_basebackup проверяет их. А утилита проверки бэкапов pg_verifydb (документация) проверяет тоже не данные в бэкапе, а контрольные суммы файлов бэкапов, возможность прочитать требуемые WAL и наличие всех файлов бэкапа, перечисленных в создаваемом при бэкапе утилитой pg_basebackup файле - манифесте (документация). Для борьбы с этим явлением критически важно использовать проверки контрольных сумм блоков, а также периодически вычитывать данные в базе командой pg_dump, которая как раз для подготовки SQL запросов пересоздания данных вынуждена проводить честные логические чтения. Контрольные суммы блоков можно включить при создании кластера БД, добавив ключ -k в команду inintdb. Для промышленных систем такое включение является разумным и обязательным. Включить и выключить контрольные суммы можно и потом утилитой pg_checksums (документация), но с простоем сервера

Недостатки штатного горячего бэкапа ванильной версии

Однопоточность утилиты pg_basebackup - резко увеличивает время бэкапа на крупных и средних БД. Как вариант решения предлагают вводить БД в режим BEGIN BACKUP и делать копирование файлов данных средствами rsync. Когда то мы и в Oracle в user managed backup параллелили копирование файлов данных скриптами. Однако тут появляется и вторая проблема - отсутствие инкрементального бэкапа. А это проблема. Если вы делаете полный бэкап раз в неделю, а данные меняются за день десятики и сотни тысяч раз - вам придётся при накате журналов на точку времени пройти в восстанавливаемой БД все те же изменения, вместо того, чтобы накатить на полный бэкап один разностный, и докатить только суточные журналы. Это в разы увеличивает время восстановления из бэкапа, и на продуктовых контурах не очень приемлемо ...

Белонин С.С. (С), февраль 2024 года