Настройка непрерывного архивирования в PostgreSQL 9.6

Для возможности восстановления кластера СУБД PostgreSQL и его баз данных на момент времени необходимо обеспечить наличие:

• Базовой резервной копии

Следует обратить внимание, что утилиты pg_dump и pg_dumpall создают логическую копию, которая не содержит информации для дальнейшего воспроизведения журнала транзакций и потому не подходит для решения задачи восстановления Point-in-Time.

Наиболее простым способом получения базовой резервной копии является утилита pg_basebackup , создающая копию файловой системы всего кластера.

• Непрерывного архива WAL — журнала транзакций

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

Настройка и выполнение резервного копирования

1 — Включаем архивирование WAL на уровне сервера.

В конфигурационном файле postgresql.conf меняем настройки:

wal_level = replica
archive_mode = on
archive_command = ‘copy «%p» «C:\\PostgreSQLBackup\\%f»‘

— команда, которая будет выполняться при архивировании WAL в момент переключения на его следующий сегмент. Параметр %p автоматически заменяется полным путём к файлу, подлежащему архивации (. \pg_xlog), а %f — именем файла. C:\PostgreSQLBackup\ в данном примере — путь к директории, куда будет производиться архивирование WAL.

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

archive_command = ‘local_backup_script.sh «%p» «%f»‘

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

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

(значение по умолчанию — 0, значение 5 указано в качестве примера и технически может быть любым, отличным от 0)

Необходимо обратить внимание, что в случае, если для кластера существует hot_standby -реплика, которая уже является получателем WAL-архивов, значение параметра max_wal_senders , определяющего количество процессов, выполняющих передачу WAL, должно быть не менее 2.

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

host replication postgres ::1/128 md5
host replication postgres 127.0.0.1/32 md5

Выполняем перезапуск службы сервера.

2 — Приступаем к созданию базовых резервных копий.

Интервал создания копии выбирается индивидуально исходя из того, сколько места на диске может быть выделено для хранения файлов WAL, и их размера — необходимо будет хранить все файлы с момента создания последней резервной копии. Копии в примере будут создаваться с помощью утилиты pg_basebackup (подробно об ее использовании и опциях можно прочитать в документации PostgreSQL https://www.postgresql.org/docs/9.6/static/app-pgbasebackup.html). Выполнять резервное копирование можно без остановки работы кластера, однако процесс может привести к повышенной нагрузке на CPU и дисковую подсистему, поэтому лучше делать это в периоды с наименьшей нагрузкой.

Если для кластера включена hot_standby-реплика, лучше использовать именно её для создания резервных копий, чтобы не нагружать master-сервер. Алгоритм выполнения на ведомом сервере будет таким же, но есть несколько настроек, которые необходимо дополнительно выполнить на slave-сервере (описаны в документации к утилите pg_basebackup ).

pg_basebackup -D «D:\Backup» -X fetch — F tar

-D — директория, куда будет скопировано содержимое каталога ..\data. Она должна быть пустой

-F — формат. В данном примере значение tar означает, что содержимое будет добавлено в архив

-X — метод копирования файлов WAL, созданных в процессе создания копии. Значение fetch означает, что файлы будут скопированы в конце процесса.

Выполнение восстановления

Для выполнения восстановления с использованием полной резервной копии и архива WAL необходимо:

1. Остановить сервер баз данных PostgreSQL.

2. Удалить (а лучше — скопировать во временную директорию) содержимое текущего каталога кластера баз данных (. \data).

3. Восстановить (скопировать) файлы необходимой архивной копии, созданной ранее, в текущий каталог данных кластера (…\data). Файлы WAL в директории \ pg_xlog нужно удалить (или заменить на содержимое каталога, скопированного в п.2)

4. Создать конфигурационный фай recovery.conf. В качестве основы можно взять расположенный обычно в директории …\share файл recovery.conf.sample. В нем необходимо выполнить настройку:

restore_command = ‘copy «C:\\PostgreSQLBackup\\%f» «%p»‘

— команда, которая будет выполняться для получения созданных ранее архивов WAL (действие, обратное выполняемому командой archive_command в postgresql.conf). Важно, чтобы в случае ошибки restore_command возвращала ненулевой код. По аналогии с archive_command, можно указать в качестве команды скрипт с более сложной логикой.

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

Например, для восстановления на момент времени:

recovery_target_time = ‘2018-03-15 12:00:00’

Или для восстановления на именованную точку:

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

5. Запустить сервер баз данных. Он будет запущен в режиме recovery и начнет процесс восстановления. По завершении сервер переименует файл recovery.conf в recovery.done и начнет работать в обычном режиме, в том числе разрешит подключения к нему. Если на время выполнения проверки после восстановления нужно запретить соединения с сервером, это лучше всего сделать в конфигурационном файле pg_hba.conf.

Обеспечение возможности быстрого возврата системы в состояние «до изменений»

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

Один из самых простых возможных сценариев решения такой задачи предполагает использование функции резервного копирования pg_start_backup() , которая вместе с pg_stop_backup() используется в утилите pg_basebackup , описанной выше, с той разницей, что утилита автоматически выполняет физическое копирование кластера в соответствии с параметрами, а ручной вызов возлагает ответственность за создание копии на администратора системы и позволяет физическое копирование «пропустить».

Перед выполнением изменений системы :

1. Убеждаемся, что архивирование WAL включено.

2. Подключаемся к серверу баз данных в контексте любой из баз и выполняем запрос:

select pg_start_backup(‘our_label’, true);

Первым параметром указываем имя метки, которое потом будем использовать при восстановлении. Второй параметр означает, что checkpoint будет осуществлен как можно скорее независимо от настроек параметра checkpoint_completion_target.

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

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

Если отмену делать не нужно, выводим сервер из режима резервного копирования, выполнив:

Резервное копирование PostgreSQL

В данной инструкции рассмотрены варианты создания резервных копий и восстановления баз СУБД PostgreSQL.

Все команды, которые приводятся ниже, должны выполняться из командной строки. В Linux — это окно терминала, в Windows — командная строка (cmd.exe) с переходом в папку установки PostgreSQL.

Создание резервных копий

Базовая команда

pg_dump users > /tmp/users.dump

Пользователь и пароль

Если резервная копия выполняется не от учетной записи postgres, необходимо добавить опцию -U с указанием пользователя:

pg_dump -U dmosk -W users > /tmp/users.dump

* где dmosk — имя учетной записи; опция W потребует ввода пароля.

Сжатие данных

Для экономии дискового пространства или более быстрой передачи по сети можно сжать наш архив:

pg_dump users | gzip > users.dump.gz

Скрипт для автоматического резервного копирования

Рассмотрим 2 варианта написания скрипта для резервирования баз PostgreSQL. Первый вариант — запуск скрипта от пользователя root для резервирования одной базы. Второй — запуск от пользователя postgres для резервирования всех баз, созданных в СУБД.

Для начала, создадим каталог, в котором разместим скрипт, например:

Вариант 1. Запуск от пользователя root; одна база.

PGPASSWORD=password
export PGPASSWORD
pathB=/backup
dbUser=dbuser
database=db

find $pathB \( -name «*-1[^5].*» -o -name «*-[023]?.*» \) -ctime +61 -delete
pg_dump -U $dbUser $database | gzip > $pathB/pgsql_$(date «+%Y-%m-%d»).sql.gz

* где password — пароль для подключения к postgresql; /backup — каталог, в котором будут храниться резервные копии; dbuser — имя учетной записи для подключения к БУБД; pathB — путь до каталога, где будут храниться резервные копии.
* данный скрипт сначала удалит все резервные копии, старше 61 дня, но оставит от 15-о числа как длительный архив. После при помощи утилиты pg_dump будет выполнено подключение и резервирование базы db. Пароль экспортируется в системную переменную на момент выполнения задачи.

Для запуска резервного копирования по расписанию, сохраняем скрипт в файл, например, /scripts/postgresql_dump.sh и создаем задание в планировщике:

3 0 * * * /scripts/postgresql_dump.sh

* наш скрипт будет запускаться каждый день в 03:00.

Вариант 2. Запуск от пользователя postgres; все базы.

find $pathB \( -name «*-1[^5].*» -o -name «*-[023]?.*» \) -ctime +61 -delete

for dbname in `echo «SELECT datname FROM pg_database;» | psql | tail -n +3 | head -n -2 | egrep -v ‘template0|template1|postgres’`; do
pg_dump $dbname | gzip > $pathB/$dbname-$(date «+%Y-%m-%d»).sql.gz
done;

* где /backup — каталог, в котором будут храниться резервные копии; pathB — путь до каталога, где будут храниться резервные копии.
* данный скрипт сначала удалит все резервные копии, старше 61 дня, но оставит от 15-о числа как длительный архив. После найдет все созданные в СУБД базы, кроме служебных и при помощи утилиты pg_dump будет выполнено резервирование каждой найденной базы. Пароль нам не нужен, так как по умолчанию, пользователь postgres имеет возможность подключаться к базе без пароля.

Необходимо убедиться, что у пользователя postgre будет разрешение на запись в каталог назначения, в нашем примере, /backup/postgres.

Зададим в качестве владельца файла, пользователя postgres:

chown postgres:postgres /scripts/postgresql_dump.sh

Для запуска резервного копирования по расписанию, сохраняем скрипт в файл, например, /scripts/postgresql_dump.sh и создаем задание в планировщике:

crontab -e -u postgres

* мы откроем на редактирование cron для пользователя postgres.

3 0 * * * /scripts/postgresql_dump.sh

* наш скрипт будет запускаться каждый день в 03:00.

Права и запуск

Разрешаем запуск скрипта, как исполняемого файла:

chmod +x /scripts/postgresql_dump.sh

Единоразово можно запустить задание на выполнение резервной копии:

. или от пользователя postgres:

su — postgres -c «/scripts/postgresql_dump.sh»

На удаленном сервере

Если сервер баз данных находится на другом сервере, просто добавляем опцию -h:

pg_dump -h 192.168.0.15 users > /tmp/users.dump

* необходимо убедиться, что сама СУБД разрешает удаленное подключение. Подробнее читайте инструкцию Как настроить удаленное подключение к PostgreSQL.

Дамп определенной таблицы

Запускается с опцией -t или —table= :

pg_dump -t students users > /tmp/students.dump

* где students — таблица; users — база данных.

Размещение каждой таблицы в отдельный файл

Также называется резервированием в каталог. Данный способ удобен при больших размерах базы или необходимости восстанавливать отдельные таблицы. Выполняется с ипользованием ключа -d:

pg_dump -d customers > /tmp/folder

* где /tmp/folder — путь до каталога, в котором разместяться файлы дампа для каждой таблицы.

Только схемы

Для резервного копирования без данных (только таблицы и их структуры):

pg_dump —schema-only users > /tmp/users.schema.dump

Только данные

pg_dump —data-only users > /tmp/users.data.dump

Использование pgAdmin

Данный метод хорошо подойдет для компьютеров с Windows и для быстрого создания резервных копий из графического интерфейса.

Запускаем pgAdmin — подключаемся к серверу — кликаем правой кнопкой мыши по базе, для которой хотим сделать дамп — выбираем Резервная копия:

В открывшемся окне выбираем путь для сохранения данных и настраиваемый формат:

При желании, можно изучить дополнительные параметры для резервного копирования:

После нажимаем Резервная копия — ждем окончания процесса и кликаем по Завершено.

Не текстовые форматы дампа

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

Читайте также:  Самый красивый курсор для windows