SQL-Ex blog

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

Кажется, это Kimberly Tripp сказал: "Ваши резервные копии так же хороши, как и ваше последнее восстановление". Короче говоря, нужно делать резервные копии, но важна способность восстанавливать данные. Вы можете иметь бесконечное число бэкапов, но если вы не можете восстановить даже один из них, то вы просто тратите место на диске. Итак, прежде чем начинать думать о резервных копиях, следует подумать о том, как вы хотите восстанавливать ваши системы.

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

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

Здесь приходит на помощь процедура восстановления к заданному моменту времени. Как правило, большинство организаций не одобряют потерю данных. Например, создание бэкапа раз в день означает, что если проблема возникла спустя 12 часов, вы потеряете все данные за это время. Восстановление к моменту времени означает, что вы можете получить последний бэкап и все изменения в базе данных после этого бэкапа. Восстановление к моменту времени достигается с помощью журнала упреждающей записи (WAL), поддерживаемого PostgreSQL.

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

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

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

Резервирование базы данных

Если вас интересует лишь простое восстановление базы данных, то наилучшим инструментом для этой работы является pg_dump. Базовый синтаксис прост:

pg_dump имя_базы_данных > файл_бэкапа

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

pg_dump -U postgres postgrelearning > butest1

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

При выполнении PostgreSQL в Docker, вы должны открыть команду оболочки для контейнера, например, такую, где PostgreHome - это имя контейнера:

docker container exec -it PostgreHome /bin/bash

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

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

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

Pg_dump -U postgres -Fc postgrelearning > bucustom.dmp

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

Восстановление базы данных

Интересен вывод по умолчанию команды pg_dump. Однако он очень большой, и быстро проскакивает. У меня очень маленькая база данных, созданная другими скриптами в этой серии. Вот малая часть вывода команды
pg_dump с форматом по умолчанию, которую вы можете просмотреть, используя CAT:

-- PostgreSQL database dump

--

-- Dumped from database version 14.1 (Debian 14.1-1.pgdg110+1)

-- Dumped by pg_dump version 14.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;

--

-- Name: hsr; Type: SCHEMA; Schema: -; Owner: postgres

--

CREATE SCHEMA hsr;

ALTER SCHEMA hsr OWNER TO postgres;

SET default_tablespace = '';

SET default_table_access_method = heap;

--

-- Name: radiobrand; Type: TABLE; Schema: hsr; Owner: postgres

--

CREATE TABLE hsr.radiobrand (

    radiobrandid integer NOT NULL,

    radiobranddesc character varying(50) NOT NULL

);

И далее. Вкратце, pg_dump скриптует базу данных, структуры и данные в файл. Это файл SQL, который вы можете выполнить из psql или любого другого инструмента. Вы получите объекты и данные, создаваемые с помощью SQL в процессе pg_dump. Поскольку файл включает данные, он может быть очень большим в зависимости от объема базы данных.

Тогда восстановление состоит просто в проигрывании скрипта, другими словами, просто выполнения SQL. Вы можете открыть его в GUI по вашему выбору, или можете воспользоваться утилитой командной строки, например:

Psql -U postgres postgrelearning < butest1

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

Если Вы решили выбрать пользовательский формат файла, у вас не будет простого набора скриптов SQL, поэтому проигрывание их требует новой команды, pg_restore

Базовая команда для использования pg_restore так же проста, как и pg_dump:

pg_restore -d postgrelearning -U postgres bucustom.dmp

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

pg_restore -d postgrelearning -U postgres -c bucustom.dmp

Команда -c, или --clean, сначала удалит объекты, однако вы можете столкнуться с сообщениями об ошибках, если создаете новый объект после того, как был выполнен бэкап. Вы также можете сообщить pg_restore сначала создать базу данных:

pg_restore -d postgres -U postgres -C bucustom.dmp

Команда -C, или --create, попытается создать базу данных postgrelearning. Если она существует, вы получите ошибку, поэтому опять таки можно модифицировать команду pg_restore:

pg_restore -d postgres -U postgres -C -c bucustom.dmp

Одновременное указание --clean и --create приведет к тому, что pg_restore удалит и заново создаст вашу базу данных. Обратите внимание, что вы сначала подключаетесь к другой базе данных с опцией -d, а затем восстановление будет использовать имя базы данных из файла дампа.

Хотя запуск SQL-скриптов базовой команды pg_dump прост, вы мало что контролируете. Вы обнаружите еще довольно много вариантов, если используете пользовательский формат файла и pg_restore. Используя pg_restore, вы можете:

• Восстановить только данные, но не схему.


• Восстановить только схему, но не данные.


• Вывести содержимое файла дампа в виде списка объектов.


• Задать конкретный список объектов для восстановления.


• Заблокировать триггеры.


• Использовать несколько конкурирующих заданий для более быстрого восстановления.


• Выбирать отдельные объекты для восстановления.

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

Опции pg_dump

Я уже упоминал одну опцию, применимую к pg_dump - пользовательский формат файла. Несколько опций позволяют вам управлять тем, что и как резервируется.

Опции указываются в командной строке, как было показано ранее. Вы можете использовать сокращения типа -a, что означает только дамп данных для заданной базы данных, или же использовать полное название опции, --data_only. Некоторые опции окончательны, а некоторые потребуют указать дополнительную информацию.

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

Pg_dump -c postgrelearning > pgl.sql

Теперь, если выполнить скрипт, он будет включать команды DROP.

Вы можете использовать шаблоны включения или выключения, чтобы переместить только некоторые из объектов базы данных. Например, для исключения схем, которые начинаются со строки dev, вы можете выполнить это:

Pg_dump -N dev* postgrelearning > nodev.sql

Также имеются команды, которые делают возможным:

• Бэкап только схемы без данных.


• Бэкап только данных без включения определения схемы.


• Задание нескольких конкурирующих заданий для более быстрого выполнения резервирования.


• Включение условных команд, IF EXISTS, для очистки.

И многое другое.

Как зарезервировать все базы данных

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

Единственное но состоит в том, что pg_dumpall выводит результат только в стандартном формате SQL. Вы не можете получить сжатый пользовательский формат. Это также означает, что вы не получите всей функциональности pg_restore. Как и ранее, это означает, что вы можете выполнить восстановление только в пустые базы данных.

Другие механизмы резервирования

Как и почти для всех ведущих РСУБД, имеются решения третьих фирм для PostgreSQL. Также, если вы выполняете AWS RDS, AWS Aurora, Azure Database для PostgreSQL, или GCP SQL Database для PostgreSQL, они все поступают со встроенными инструментами резервирования и восстановления. Вы также можете создавать резервные копии и восстанавливать их из PGAdmin4 или восстанавливать из Azure Data Studio. Кроме того, имеется множество решений для скриптования, предлагаемых сообществом PostgreSQL. Я настоятельно рекомендую изучить использование естественных инструментов резервирования и восстановления ваших баз данных. Однако после того, как вы приобретете эти навыки, можно будет перейти к другим решениям.

Заключение

Возможность восстановления базы данных в PostgreSQL является критически важным. Получить быстрый бэкап довольно просто с помощью команды pg_dump, и это хорошо подходит для разработки. Эта команда создает файл скрипта по умолчанию, который вы можете выполнять непосредственно. Имеется несколько других опций, включая -Fc для создания бэкапа пользовательского формата, который может быть использован с помощью команды pg_restore. Команда pg_dump предоставляет несколько опций, включая возможность удаления и создания базы данных. Если вам нужно зарезервировать все базы данных, используйте команду pg_dumpall, которая, однако, не отличается гибкостью.

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