Дисклеймер
Я — разработчик. Я пишу код, с базой данных взаимодействую лишь как пользователь. Я ни в коем случае не претендую на должность системного администратора и, тем более, dba. Но…
Так вышло, что мне нужно было организовать резервное копирование postgresql базы данных. Никаких облаков — держи SSH и сделай, чтобы все работало и не просило денег. Что мы делаем в таких случаях? Правильно, пихаем pgdump в cron, каждый день бэкапим все в архив и если совсем разошлись — отправляем этот архив куда-нибудь подальше.
В этот раз сложность состояла в том, что по планам база должна была расти примерно на +- 100 МБ в день. Разумеется, уже через пару недель желание бэкапить все pgdump'ом отпадет. Тут на помощь приходят инкрементальные бэкапы.
Интересно? Добро пожаловать под кат.
Погуглив я нашел два инструмента pgbarman и pgbackrest. С первым у меня просто не задалось (очень скудная документация, пытался все поднять по старинным мануалам), а вот у второго документация оказалась на уровне, но и не без изъяна. Чтобы упростить работу тем, кто столкнется с подобной задачей и была написана данная статья.
Для воспроизведения мануала вам понадобятся два VPS. Первый будет хранилищем (репозиторием, на котором будут лежат бэкапы), а второй, собственно, сам сервер с postgres (в моем случае 11 версия postgres).
Подразумевается, что на сервере с postgres у вас есть root, sudo пользователь, пользователь postgres и сам postgres установлен (пользователь postgres создается автоматически при установке postgresql), а на сервере-репозитории есть root и sudo пользователь (в мануале будет использоваться имя пользователя pgbackrest).
Чтобы у вас было меньше проблем при воспроизведении инструкции — курсивом я прописываю где, каким пользователем и с какими правами я исполнял команду во время написания и проверки статьи.
Репозиторий (пользователь pgbackrest):
1. Скачиваем архив с pgbackrest и переносим его содержимое в папку /build:
2. Устанавливаем необходимые для сборки зависимости:
3. Собираем pgbackrest:
4. Копируем исполняемый файл в директорию /usr/bin:
5. Pgbackrest требует наличие perl. Устанавливаем:
6. Создаем директории для логов, даем им определенные права:
7. Проверяем:
Postgres сервер (sudo пользователь или root):
Процесс установки pgbackrest на сервере с postgres аналогичен процессу установки на репозитории (да, pgbackrest должен стоять на обоих серверах), но в 6-ом пункте вторую и последнюю команды:
заменяем на:
Для того, чтобы pgbackrest корректно работал, необходимо настроить взаимодействие между postgres сервером и репозиторием по файлу-ключу.
Репозиторий (пользователь pgbackrest):
Создаем пару ключей:
Внимание! Указанные выше команды выполняем без sudo.
Postgres сервер (sudo пользователь или root):
Создаем пару ключей:
Репозиторий (sudo пользователь):
Копируем публичный ключ postgres сервера на сервер-репозиторий:
На данном шаге попросит пароль от root пользователя. Вводить нужно именно пароль root пользователя postgres сервера!
Postgres сервер (sudo пользователь):
Копируем публичный ключ репозитория на сервер с postgres:
На данном шаге попросит пароль от root пользователя. Вводить нужно именно пароль root пользователя репозитория!
Проверяем:
Репозиторий (root пользователь, для чистоты эксперимента):
Postgres сервер (root пользователь, для чистоты эксперимента):
Убеждаемся, что без проблем получаем доступ.
Postgres сервер (sudo пользователь или root):
1. Разрешим «стучаться» на postgres сервер с внешних ip. Для этого отредактируем файл postgresql.conf (находится в папке /etc/postgresql/11/main), добавив в него строчку:
Если такая строка уже есть — либо раскомментируйте ее, либо установите значение параметра как '*'.
В файле pg_hba.conf (так же находится в папке /etc/postgresql/11/main) добавляем следующие строчки:
где:
2. Внесем необходимые настройки в postgresql.conf (он находится в папке /etc/postgresql/11/main) для работы pgbackrest:
3. Внесем необходимые настройки в файл конфигурации pgbackrest (/etc/pgbackrest/pgbackrest.conf):
4. Перезагрузим postgresql:
Репозиторий (pgbackrest пользователь):
Внесем необходимые настройки в файл конфигурации pgbackrest
(/etc/pgbackrest/pgbackrest.conf):
Репозиторий (pgbackrest пользователь):
Создаем новое хранилище для кластера main:
Postgres сервер (sudo пользователь или root):
Проверяем на postgres сервере:
Репозиторий (pgbackrest пользователь):
Проверяем на сервере-репозитории:
Убеждаемся, что в выводе видим строку «check command end: completed successfully».
Устали? Переходим к самому интересному.
Репозиторий (pgbackrest пользователь):
1. Выполняем резервное копирование:
2. Убеждаемся, что бэкап был создан:
Pgbackrest создаст первый полный бэкап. При желании вы можете запустить команду бэкапа повторно и убедиться, что система создаст инкрементальный бэкап.
Если вы хотите повторно сделать полный бэкап, то укажите дополнительный флаг:
Если вы хотите подробный вывод в консоль, то также укажите:
Postgres сервер (sudo пользователь или root):
1. Останавливаем работающий кластер:
2. Восстанавливаемся из бэкапа:
Чтобы восстановить базу в состояние последнего ПОЛНОГО бэкапа используйте команду без указания recovery_target:
Важно! После восстановления может оказаться так, что база зависнет в режиме восстановления (будут ошибки в духе ERROR: cannot execute DROP DATABASE in a read-only transaction). Честно говоря, я еще не понял, с чем это связано. Решается следующим образом (нужно будет малость подождать после исполнения команды):
На самом деле, есть возможность восстановить конкретный бэкап по его имени. Здесь я лишь укажу ссылку на описание данной фичи в документации. Разработчики советуют использовать данный параметр с осторожностью и объясняют почему. От себя могу добавить, что я его использовал. Если очень нужно — убедитесь, что после восстановления база вышла из recovery mode (select pg_is_in_recovery() должен показать «f») и на всякий случай сделайте полный бэкап после восстановления.
3. Запускаем кластер:
После восстановления бэкапа нам необходимо выполнить повторный бэкап:
Репозиторий (pgbackrest пользователь):
На этом все. В заключение хочу напомнить, что я ни в коем случае не пытаюсь строить из себя senior dba и при малейшей возможности буду использовать облака. В настоящее время сам начинаю изучать различные темы вроде резервного копирования, репликаций, мониторинга и т.п. и о результатах пишу небольшие отчеты, дабы сделать небольшой вклад в сообщество и оставить для себя небольшие шпаргалки.
В следующих статьях постараюсь рассказать о дополнительных фичах — восстановление данных на чистый кластер, шифрование бэкапов и публикацию на S3, бэкапы через rsync.
Я — разработчик. Я пишу код, с базой данных взаимодействую лишь как пользователь. Я ни в коем случае не претендую на должность системного администратора и, тем более, dba. Но…
Так вышло, что мне нужно было организовать резервное копирование postgresql базы данных. Никаких облаков — держи SSH и сделай, чтобы все работало и не просило денег. Что мы делаем в таких случаях? Правильно, пихаем pgdump в cron, каждый день бэкапим все в архив и если совсем разошлись — отправляем этот архив куда-нибудь подальше.
В этот раз сложность состояла в том, что по планам база должна была расти примерно на +- 100 МБ в день. Разумеется, уже через пару недель желание бэкапить все pgdump'ом отпадет. Тут на помощь приходят инкрементальные бэкапы.
Интересно? Добро пожаловать под кат.
Инкрементальный бэкап — разновидность резервной копии, когда копируются не все файлы источника, а только новые и измененные с момента создания предыдущей копии.Как и любой разработчик, СОВЕРШЕННО не желающий (на тот момент) разбираться в тонкостях postgres я хотел найти зеленую кнопку. Ну, знаете, как в AWS, DigitalOcean: нажал одну кнопку — получил репликацию, нажал вторую — настроил бэкапы, третью — все откатил на пару часов назад. Кнопки и красивого GUIшного инструмента я не нашел. Если вы знаете такой (бесплатный или дешевый) — напишите об этом в комментариях.
Погуглив я нашел два инструмента pgbarman и pgbackrest. С первым у меня просто не задалось (очень скудная документация, пытался все поднять по старинным мануалам), а вот у второго документация оказалась на уровне, но и не без изъяна. Чтобы упростить работу тем, кто столкнется с подобной задачей и была написана данная статья.
Дочитав данную статью вы научитесь делать инкрементальные бекапы, сохранять их на удаленный сервер (репозиторий с бэкапами) и восстанавливать их в случае утери данных или иных проблем на основном сервере.
Подготовка
Для воспроизведения мануала вам понадобятся два VPS. Первый будет хранилищем (репозиторием, на котором будут лежат бэкапы), а второй, собственно, сам сервер с postgres (в моем случае 11 версия postgres).
Подразумевается, что на сервере с postgres у вас есть root, sudo пользователь, пользователь postgres и сам postgres установлен (пользователь postgres создается автоматически при установке postgresql), а на сервере-репозитории есть root и sudo пользователь (в мануале будет использоваться имя пользователя pgbackrest).
Чтобы у вас было меньше проблем при воспроизведении инструкции — курсивом я прописываю где, каким пользователем и с какими правами я исполнял команду во время написания и проверки статьи.
Установка pgbackrest
Репозиторий (пользователь pgbackrest):
1. Скачиваем архив с pgbackrest и переносим его содержимое в папку /build:
sudo mkdir /build
sudo wget -q -O - \
https://github.com/pgbackrest/pgbackrest/archive/release/2.18.tar.gz | \
sudo tar zx -C /build2. Устанавливаем необходимые для сборки зависимости:
sudo apt-get update
sudo apt-get install build-essential libssl-dev libxml2-dev libperl-dev zlib1g-dev \
libpq-dev3. Собираем pgbackrest:
cd /build/pgbackrest-release-2.18/src && sudo ./configure
sudo make -s -C /build/pgbackrest-release-2.18/src4. Копируем исполняемый файл в директорию /usr/bin:
sudo cp /build/pgbackrest-release-2.18/src/pgbackrest /usr/bin
sudo chmod 755 /usr/bin/pgbackrest5. Pgbackrest требует наличие perl. Устанавливаем:
sudo apt-get install perl6. Создаем директории для логов, даем им определенные права:
sudo mkdir -p -m 770 /var/log/pgbackrest
sudo chown pgbackrest:pgbackrest /var/log/pgbackrest
sudo mkdir -p /etc/pgbackrest
sudo mkdir -p /etc/pgbackrest/conf.d
sudo touch /etc/pgbackrest/pgbackrest.conf
sudo chmod 640 /etc/pgbackrest/pgbackrest.conf
sudo chown pgbackrest:pgbackrest /etc/pgbackrest/pgbackrest.conf7. Проверяем:
pgbackrest versionPostgres сервер (sudo пользователь или root):
Процесс установки pgbackrest на сервере с postgres аналогичен процессу установки на репозитории (да, pgbackrest должен стоять на обоих серверах), но в 6-ом пункте вторую и последнюю команды:
sudo chown pgbackrest:pgbackrest /var/log/pgbackrest
sudo chown pgbackrest:pgbackrest /etc/pgbackrest/pgbackrest.confзаменяем на:
sudo chown postgres:postgres /var/log/pgbackrest
sudo chown postgres:postgres /etc/pgbackrest/pgbackrest.confНастройка взаимодействия между серверами через passwordless SSH
Для того, чтобы pgbackrest корректно работал, необходимо настроить взаимодействие между postgres сервером и репозиторием по файлу-ключу.
Репозиторий (пользователь pgbackrest):
Создаем пару ключей:
mkdir -m 750 /home/pgbackrest/.ssh
ssh-keygen -f /home/pgbackrest/.ssh/id_rsa \
-t rsa -b 4096 -N ""Внимание! Указанные выше команды выполняем без sudo.
Postgres сервер (sudo пользователь или root):
Создаем пару ключей:
sudo -u postgres mkdir -m 750 -p /var/lib/postgresql/.ssh
sudo -u postgres ssh-keygen -f /var/lib/postgresql/.ssh/id_rsa \
-t rsa -b 4096 -N ""Репозиторий (sudo пользователь):
Копируем публичный ключ postgres сервера на сервер-репозиторий:
(echo -n 'no-agent-forwarding,no-X11-forwarding,no-port-forwarding,' && \
echo -n 'command="/usr/bin/pgbackrest ${SSH_ORIGINAL_COMMAND#* }" ' && \
sudo ssh root@<postgres_server_ip> cat /var/lib/postgresql/.ssh/id_rsa.pub) | \
sudo -u pgbackrest tee -a /home/pgbackrest/.ssh/authorized_keysНа данном шаге попросит пароль от root пользователя. Вводить нужно именно пароль root пользователя postgres сервера!
Postgres сервер (sudo пользователь):
Копируем публичный ключ репозитория на сервер с postgres:
(echo -n 'no-agent-forwarding,no-X11-forwarding,no-port-forwarding,' && \
echo -n 'command="/usr/bin/pgbackrest ${SSH_ORIGINAL_COMMAND#* }" ' && \
sudo ssh root@<repository_server_ip> cat /home/pgbackrest/.ssh/id_rsa.pub) | \
sudo -u postgres tee -a /var/lib/postgresql/.ssh/authorized_keys
На данном шаге попросит пароль от root пользователя. Вводить нужно именно пароль root пользователя репозитория!
Проверяем:
Репозиторий (root пользователь, для чистоты эксперимента):
sudo -u pgbackrest ssh postgres@<postgres_server_ip>Postgres сервер (root пользователь, для чистоты эксперимента):
sudo -u postgres ssh pgbackrest@<repository_server_ip>Убеждаемся, что без проблем получаем доступ.
Настройка postgres сервера
Postgres сервер (sudo пользователь или root):
1. Разрешим «стучаться» на postgres сервер с внешних ip. Для этого отредактируем файл postgresql.conf (находится в папке /etc/postgresql/11/main), добавив в него строчку:
listen_addresses = '*'Если такая строка уже есть — либо раскомментируйте ее, либо установите значение параметра как '*'.
В файле pg_hba.conf (так же находится в папке /etc/postgresql/11/main) добавляем следующие строчки:
hostssl all all 0.0.0.0/0 md5
host all all 0.0.0.0/0 md5где:
hostssl/host - подключаемся через SSL (или нет)
all - разрешаем подключение ко всем базам
all - имя пользователя, которому разрешаем подключение (всем)
0.0.0.0/0 - маска сети с которой можно подключаться
md5 - способ шифрования пароля2. Внесем необходимые настройки в postgresql.conf (он находится в папке /etc/postgresql/11/main) для работы pgbackrest:
archive_command = 'pgbackrest --stanza=main archive-push %p' # Где main - название кластера. При установке postgres автоматически создает кластер main.
archive_mode = on
max_wal_senders = 3
wal_level = replica3. Внесем необходимые настройки в файл конфигурации pgbackrest (/etc/pgbackrest/pgbackrest.conf):
[main]
pg1-path=/var/lib/postgresql/11/main
[global]
log-level-file=detail
repo1-host=<repository_server_ip>4. Перезагрузим postgresql:
sudo service postgresql restartНастройка сервера-репозитория
Репозиторий (pgbackrest пользователь):
Внесем необходимые настройки в файл конфигурации pgbackrest
(/etc/pgbackrest/pgbackrest.conf):
[main]
pg1-host=<postgres_server_ip>
pg1-path=/var/lib/postgresql/11/main
[global]
repo1-path=/var/lib/pgbackrest
repo1-retention-full=2 # Параметр, указывающий сколько хранить полных бэкапов. Т.е. если у вас есть два полных бэкапа и вы создаете третий, то самый старый бэкап будет удален. Можно произносить как "хранить не более двух бэкапов" - по аналогии с ротациями логов. Спасибо @Aytuar за исправление ошибки.
start-fast=y # Начинает резервное копирование немедленно, прочитать про этот параметр можно тут https://postgrespro.ru/docs/postgrespro/9.5/continuous-archivingСоздание хранилища
Репозиторий (pgbackrest пользователь):
Создаем новое хранилище для кластера main:
sudo mkdir -m 770 /var/lib/pgbackrest
sudo chown -R pgbackrest /var/lib/pgbackrest/
sudo -u pgbackrest pgbackrest --stanza=main stanza-create
Проверка
Postgres сервер (sudo пользователь или root):
Проверяем на postgres сервере:
sudo -u postgres pgbackrest --stanza=main --log-level-console=info checkРепозиторий (pgbackrest пользователь):
Проверяем на сервере-репозитории:
sudo -u pgbackrest pgbackrest --stanza=main --log-level-console=info checkУбеждаемся, что в выводе видим строку «check command end: completed successfully».
Устали? Переходим к самому интересному.
Делаем бэкап
Репозиторий (pgbackrest пользователь):
1. Выполняем резервное копирование:
sudo -u pgbackrest pgbackrest --stanza=main backup
2. Убеждаемся, что бэкап был создан:
ls /var/lib/pgbackrest/backup/main/Pgbackrest создаст первый полный бэкап. При желании вы можете запустить команду бэкапа повторно и убедиться, что система создаст инкрементальный бэкап.
Если вы хотите повторно сделать полный бэкап, то укажите дополнительный флаг:
sudo -u pgbackrest pgbackrest --stanza=main --type=full backupЕсли вы хотите подробный вывод в консоль, то также укажите:
sudo -u pgbackrest pgbackrest --stanza=main --type=full --log-level-console=info backupВосстанавливаем бэкап
Postgres сервер (sudo пользователь или root):
1. Останавливаем работающий кластер:
sudo pg_ctlcluster 11 main stop2. Восстанавливаемся из бэкапа:
sudo -u postgres pgbackrest --stanza=main --log-level-console=info --delta --recovery-option=recovery_target=immediate restoreЧтобы восстановить базу в состояние последнего ПОЛНОГО бэкапа используйте команду без указания recovery_target:
sudo -u postgres pgbackrest --stanza=main --log-level-console=info --delta restoreВажно! После восстановления может оказаться так, что база зависнет в режиме восстановления (будут ошибки в духе ERROR: cannot execute DROP DATABASE in a read-only transaction). Честно говоря, я еще не понял, с чем это связано. Решается следующим образом (нужно будет малость подождать после исполнения команды):
sudo -u postgres psql -c "select pg_wal_replay_resume()"На самом деле, есть возможность восстановить конкретный бэкап по его имени. Здесь я лишь укажу ссылку на описание данной фичи в документации. Разработчики советуют использовать данный параметр с осторожностью и объясняют почему. От себя могу добавить, что я его использовал. Если очень нужно — убедитесь, что после восстановления база вышла из recovery mode (select pg_is_in_recovery() должен показать «f») и на всякий случай сделайте полный бэкап после восстановления.
3. Запускаем кластер:
sudo pg_ctlcluster 11 main startПосле восстановления бэкапа нам необходимо выполнить повторный бэкап:
Репозиторий (pgbackrest пользователь):
sudo pgbackrest --stanza=main backupНа этом все. В заключение хочу напомнить, что я ни в коем случае не пытаюсь строить из себя senior dba и при малейшей возможности буду использовать облака. В настоящее время сам начинаю изучать различные темы вроде резервного копирования, репликаций, мониторинга и т.п. и о результатах пишу небольшие отчеты, дабы сделать небольшой вклад в сообщество и оставить для себя небольшие шпаргалки.
В следующих статьях постараюсь рассказать о дополнительных фичах — восстановление данных на чистый кластер, шифрование бэкапов и публикацию на S3, бэкапы через rsync.
