Как неПросто сделать холодный бэкап Postgres

Как backup назовешь так он и восстановится. О силе названий.

В предыдущей серии Инструкция по бэкапу одной базы в Postgres - миф или реальность?  мы выяснили, что база данных в Postgres это лукавый термин, и база в Postgres не имеет полноценных средств резервного копирования, в отличии от Instance Postgres. Конечно,  для кого то и Pg_Dump полноценное средство резервного копирования, а не средство переноса информации между Instance с множеством побочных действий.  

Как минимум Pg_dump не копирует индексы (а только дает команду на их перестройку), есть проблемы с переносом Lob объектов – смотрите Pg_dump и Lob объекты.

Раз мы обратились к  терминам, давайте спросим у ChatGPT – определение термина Backup в Postgres.

Как видите

Первоисточники:

PostgreSQL: Documentation: 16: Chapter 26. Backup and Restore

Сначала выделяют четко три термина (SQL Dump, File system level backup, Continuous archiving) а потом скатываются ниже по тексту в утверждение, что всем этим можно делать backup. Дальше уже в курсах начинаются вариации (Логический бэкап, Горячий бэкап и т.д.).

В общем классическая история с крабовыми палочками, крабы к которым отношения не имеют. Проблема в том потом это завернут в ролл калифорния с «крабом» и … ам. Вы попались на крючок ИТ маркетологов.

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

Документация утверждает, что File system level backup это всего лишь копия нужных каталогов Postgres при остановленном Instance.

Что может быть проще? Проще в других СУБД, но только не в Postgres. Дошедшие до конца узнают кое-что новое о своей файловой системе, и то что копия каталогов в Postgres это не совсем копия. 

Отряд не заметил потери слона или как Postgres контролирует файлы

Насколько в Postgres все не просто со структурой хранения, можно проверить просто переименовав каталог tablespace . При старте мы увидим, что Postgres видит, что нет нужного tablespace но при этом стартует, пишет ошибку в лог, и ждет соединений. Это нормально.

Ненормально другое, база при этом никак не обозначается неработоспособной. К ней можно обращаться до момента обращения к нужному ресуру.

А при обращении к приложения  ресурсу , получим:

«Невосстановимая ошибка

Ошибка при выполнении запроса POST к ресурсу /e1cib/login:

по причине:

Ошибка СУБД:

58P01: ERROR:  could not open file "pg_tblspc/16390/PG_15_202209061/499402/530242": No such file or directory»

А если что то не так с таблицей (которая хранится в отдельных файлах):

«SELECT pg_relation_filepath('public._document21655');

При запуске в логе ничего не будет, до момента обращения к таблице

Отсутствие таблицы "pg_tblspc/16390/PG_15_202209061/16396/154483"

Выдаст ошибку без фиксации в лог Ошибка СУБД:\n58P01: ERROR:  could not open file \"pg_tblspc\/16390\/PG_15_202209061\/16396\/154483\": No such file or directory\n", »

Т.е. Postgres при запуске еще контролирует наличие Tablespace, а  что там внутри уже нет -запомните это.

Если сравнить с MS SQL  то он контролирует наличие файлов, в файл группах благо они там создаются не на каждую таблицу, а на группу таблиц. Аналогично в Oracle там к Tablespace привязаны файлы для хранения таблиц. Этот подход дает возможность при старте проверить что все на месте.

На каждый файл  в MS SQL выдается сообщение в логе:

«Unable to open the physical file "D:\db_s\MSSQL\MIS_PROD2_2019\MIS_PROD2_2019.mdf". Operating system error 2: "2(The system cannot find the file specified.)".»

А база переводится в Recovery pending.

Таким образом при восстановлении чего либо в Postgres Вы   можете быть уверены в качестве восстановления, только «потрогав» все таблицы и индексы. А когда их тысячи (как в любой типовой базе 1С ) это занимает значительное время.

Сразу тревожат мысли: если в приложении таблицы находятся в разных tablespace,  и мы успеем что-то сделать в живом tablespace, а в мертвом нет - это отразится на физической и логической целостности базы?  Тут такой простор для накидывания кейсов на вентилятор, что никакой статьи не хватит.

Вопрос: Кто знает каким способом заставить Postgres безусловно контролировать целостность базы при запуске на уровне таблиц?

В открытых источниках почему-то дают примеры, где все tablespace и wal в каталогах по умолчанию. Как например тут Базовая резервная копия . Работа с tablespace затрагивается только в рамках восстановления  горячего бэкапа через pg_basebackup . Однако для восстановления холодного бэкапа tablespace и wal также важны.

Складывается иллюзия что все просто, но только если вы запустили демо базу без своих табличных пространств и не перемещали WAL в другой каталог.

Куда приводит junction – восстановление каталогов

Создавать свои Tablespace это нормально, для управления местом хранения данных на разных массивах. 1С рекомендует создать одно для данных v81c_data а другое для индексов v81c_index. К сожалению варианта – каждой базе свой Tablespace у 1С нет. Если распределите это на уровне СУБД, то при реструктуризациях в 1С все  плавно вернется назад.

В Инструкция по установке Postgres для OLTP приложений все сделали по максимому

1. отдельный Tablespace для временных файлов – прописывается в postgres.conf

2. отдельный каталог для журнала предзаписи initdb waldir="D:\PostgresSQL\15_3\wal"

3. отдельные tablespace для данных и индексов v81c_data, v81c_index.

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

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

PostgreSQL : Документация: 15: 73.1. Размещение файлов базы данных : Компания Postgres Professional

«Табличное пространство делает сценарий более сложным. Каждое пользовательское табличное пространство имеет символическую ссылку внутри каталога PGDATA/pg_tblspc, указывающую на физический каталог табличного пространства (т. е., положение, указанное в команде табличного пространства CREATE TABLESPACE). Эта символическая ссылка получает имя по OID табличного пространства. Внутри физического каталога табличного пространства имеется подкаталог, имя которого зависит от версии сервера PostgreSQL, как например PG_9.0_201008051. (Этот подкаталог используется для того, чтобы последующие версии базы данных могли свободно использовать одно и то же местоположение, заданное в CREATE TABLESPACE.) Внутри каталога конкретной версии находится подкаталог для каждой базы данных, которая имеет элементы в табличном пространстве, названный по OID базы данных. Таблицы и индексы хранятся внутри этого каталога, используя схему именования файловых узлов. Табличное пространство pg_default недоступно через pg_tblspc, но соответствует PGDATA/base. Подобным же образом, табличное пространство pg_global недоступно через pg_tblspc, но соответствует PGDATA/global.»

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

Каталог $PGDATA/pg_tblspc содержит символические ссылки, которые указывают на внешние табличные пространства кластера. Хоть и не рекомендуется, но возможно регулировать табличные пространства вручную, переопределяя эти ссылки. Ни при каких обстоятельствах эти операции нельзя проводить, пока запущен сервер баз данных. Обратите внимание, что в версии PostgreSQL 9.1 и более ранних также необходимо обновить информацию в pg_tablespace о новых расположениях. (Если это не сделать, то pg_dump будет продолжать выводить старые расположения табличных пространств.)»

А теперь попробуйте просто скопировать такой каталог

Как видите магия исчезла (в windows). 

А еще символические ссылки не удаляются командой del , только через rmdir.

https://superuser.com/questions/285581/how-to-delete-a-junction-by-using-command-prompt-in-windows-7

В linux  тоже все не просто:

linux - How to copy symbolic links? - Super User

linux - Remove a symlink to a directory - Stack Overflow

В лучших традициях бэкапов в Postgres шаг вправо шаг влево – проблема.

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

PostgreSQL : Документация: 15: 26.2. Резервное копирование на уровне файлов : Компания Postgres Professional

PostgreSQL : Документация: 15: 73.1. Размещение файлов базы данных : Компания Postgres Professional

Кстати, В документации не указано, но и Wal может быть по символьной ссылке. На самом деле все сложнее – в Windows четыре типа link Shortcut , Hardlink, Junction, Symbolik link.

С несколько разными свойствами см таблицу.

Что реально у Вас используется при создании базы данных можно увидеть через команду Dir:

D:\Postgres\15_3\bin> dir D:\PostgresSQL\15_3 /al /S
Volume in drive D is SSD
 Volume Serial Number is EAD4-A76D

 Directory of D:\PostgresSQL\15_3\data

12.10.2023  12:37    <JUNCTION>     pg_wal [\??\D:\PostgresSQL\15_3\wal]
               0 File(s)              0 bytes

 Directory of D:\PostgresSQL\15_3\data\pg_tblspc

05.12.2023  14:18    <JUNCTION>     16389 [D:\PostgresSQL\15_3\tbs\pg_ssdtemp]
05.12.2023  14:21    <JUNCTION>     16390 [D:\PostgresSQL\15_3\tbs\pg_1cdata]
05.12.2023  14:22    <JUNCTION>     16391 [D:\PostgresSQL\15_3\tbs\pg_1cindex]
05.12.2023  14:23    <JUNCTION>     75827 [D:\PostgresSQL\15_3\tbs\pg_1cservice]
               0 File(s)              0 bytes

В моем случае используется Junction . Значит с ним и будем работать.

Скрипт копирования базы на уровне файлов

Скрипт прост по смыслу, но не прост в реализации. Тут приведена общая схема для случая когда копия на том же сервере, если копия на другом сервере , то команды запуска сервисов нужно инициировать удаленно через Task scheduler:

D:
cd "C:\Program Files\PostgreSQL\15.4-1.1C\bin"
rem %1 каталог кластера откуда копируем (исходный), их может быть больше если на разных дисках
IF [%1]==[]  GOTO NoParam 
Rem %2 каталог кластера куда копируем (целевой), их может быть больше если на разных дисках
IF [%2]==[]  GOTO NoParam.

Rem Сначала останавливаем сервисы, мы же делаем бэкап на уровне файлов
net stop "1C:Enterprise 8.3 Server Agent (x86-64)"
net stop PostgreSQL_Main
net stop PostgresSQL_Test
TIMEOUT /T 10 /NOBREAK
rem Далее чистим каталог в который копируем файлы базы. Обратите внимание что junction не удаляются через del поэтому приходится делать rmdir . В линуксе похожие особенности.
rmdir /S /Q %2\data
rmdir /S /Q %2\tbs
rmdir /S /Q %2\trace
rmdir /S /Q %2\wal
rem Чистим целевой каталог через del . Через rmdir его лучше не удалять, вдруг на нем права доступа
del %2\*.* /S /F /Q
rem копируем параллельно 18 потоками, DATSO означает сохранить права доступа, иногда приходится опускать O owner если возникают проблемы. /XJ пропускаем Junction поскольку нам их создавать заново
robocopy %1 %2 /COPY:DATS /E /MT:18 /XJ
rem Теперь мы можем стартовать исходный кластер, но с целевым работа еще продолжается
net start PostgreSQL_Main
SET TARGEDIR=%2
rem Восстанавливаем junction но уже на новые каталоги
mklink /j /D  %TARGEDIR%\data\pg_tblspc\16390 %TARGEDIR%\tbs\pg_ssdtemp
mklink /j /D  %TARGEDIR%\data\pg_tblspc\16391 %TARGEDIR%\tbs\pg_1cdata
mklink /j /D  %TARGEDIR%\data\pg_tblspc\16392 %TARGEDIR%\tbs\pg_1cindex
mklink /j /D  %TARGEDIR%\data\pg_tblspc\16393 %TARGEDIR%\tbs\pg_1c_service
mklink /j /D  %TARGEDIR%\data\pg_wal %TARGEDIR%\wal

rem Копируем в целевую базу заранее подготовленный  postgresql.conf ведь там будут и другие каталоги, и другие порты для нового расположения кластера

copy D:\ColdDumpPostgres\postgresql.conf %TARGEDIR%\data\postgresql.conf /Y
rem Теперь можно стартовать
net start PostgresSQL_Test 
net start "1C:Enterprise 8.3 Server Agent (x86-64)"
TIMEOUT /T 10 /NOBREAK
Rem Начинаем проверки, сначала статус кластера
pg_ctl -D "%2\data"   -o "-p 5632" status 
pg_ctl -D "%1\data"   -o "-p 5432" status 
Rem потом трогаем все таблицы , если гдето ин не найдут будет ошибка
chcp 1251
SET PGPASSWORD=y@urpassw@rd
rem touch every table to check restore
rem --log-file=%3 does not work why?
psql --host=myserver --port=5632  --username=postgres --dbname=1CDatabase  --command="VACUUM (ANALYZE, VERBOSE, PARALLEL 5);"   

EXIT
echo Stop backup cluster

:NoParam
echo Batch parameters missing
EXIT

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

Основное правило резервного копирования – нужно убедится, что бэкап валиден. В случае Postgres это можно сделать только подняв бэкап.

Желательно эту копию поднимать на резервном сервере каждый день, но если это невозможно,  то можно поднять ее на той же машине, но после успешного старта и проверки уже с этой копии и сделать архив на внешний носитель\каталог . Почему с нее, а не с продуктивной базы (исходной)?

Если с продуктивным кластером что- то случается (что-то испортили, рухнула и т.д.) лучше оставить его как есть для спокойного разбора полетов, а бэкап кластера восстановить на резервном кластере. В этом случае можно его поместить в архив сразу настроенным и готовым к работе. Кроме того, копию на резервном кластере просто проверить сразу после бэкапа. Только junction после разархивирования лучше пересоздать, архиватор может с ними некорректно работать.

Структура хранения, как основа архитектуры. А что так можно было?

Тут начинают в голову лезть вопросы о смысле такой архитектуры. Почему нельзя было положить информацию о расположении tablespace в какой либо Conltrolfile как в Oracle ? или в Postgres собирались пристегивать tablespace на горячую? Кроме известных симптомов от сидения на рабочем месте я не понимаю причины такой реализации. Тем более, что эти junction собраны не в одном месте. Зачем?

Можно задать еще более фундаментальный вопрос – зачем в Postgres каждая таблица, индекс хранятся в отдельных файлах? Это какой то привет из dbf формата?

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

5.4. Символическая ссылка на каталог «1С:Предприятия» :: 1С:Облачная подсистема Фреш. Демонстрационный пример № 2 развертывания сервиса Фреш (1c.ru)

А использование символических ссылок для хранения данных - при бэкапе требует

А) Сделать сначала копию аккуратно их исключив – ведь они показывают на исходный каталог

Б) При восстановлении , спец коммандами удалить старые символические ссылки.

В)  Собрать символические ссылки заново и указать новые каталоги.

Вы не задумывались почему MS SQL и Oracle хранит таблицы в больших файлах, объединенные в файловые группы или tablespace соответственно. Там много плюсов:

• Вы можете сразу выделить нужное место под экстенты для всех таблиц в tablespace

• Вы можете проверять десяток файлов наличие перед стартом, в отличие от Postgres, где для типовой 1С больше тысячи файлов таблиц, индексов, карт свободного пространства и т.д..

• Кластер может эффективно управлять расположением данных по экстентам внутри файла данных, в отличии от Postgres, где все разбито на отдельные файлы и только от ОС зависит рядом это попадет или нет.   В Oracle можно даже вручную выделять экстенты

• И к этому добавляется много плюсов по производительности Why and how do Databases use a single file to store all data? - Stack Overflow

• Резервное копирование и восстановление проще

Вопрос: Ну и кто видит плюсы для Postgres в этом распределении всего и вся по отдельным файлам?

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

В MS SQL и Oracle особой надобности в холодном бэкапе нет поскольку, есть хорошие средства  Online копии баз (ms sql) или Instance (oracle)  с валидацией, где при восстановлении достаточно указать целевые каталоги. В Postgres pg_basebackup даже с --tablespace-mapping не предотвратит танцы с junction. Поэтому навыки холодного бэкапа в Postgres это необходимый навык. Кроме того, холодный бэкап он в разы быстрее горячего через pg_dump (про pg_restore я уже не говорю) поскольку копирует все файлы в нужном количестве потоков. Pg_dump копирует только данные таблицы с командами перестройки индексов, последовательным обходом таблицы и многопоточность по таблицам тут не спасет. В холодном бэкапе даже немного битая на логическом или физическом уровне подлежит восстановлению, а с pg_dump как повезет.

Анонс – увеличение скорости копирования на уровне файлов, это отдельная интересная тема.

Если холодный бэкап может сделать любой программист, который администрирует Postgres «по совместительству»  у клиента, то горячий бэкап рекомендуется настраивать только выделенному Администратору СУБД  иначе импортозамещение может произойти не на 100%

Копируйте кластер Postgres с нами копируйте лучше нас, на нашем канале t.me/Chat1CUnlimited. До новых встреч.