Как правильно делать бекапы базы Postgresql на продашн сервере?

Question

[getnowtoday]

Есть pg_dump. Как правильно делать бэкапы базы Postgresql на сервере? По крону, примерно раз в день? И потом их закачивать на aws и dropbox? Шифровать? Сколько копий хранить, то есть, за какое время -- за последнюю неделю, месяц или ...? Может, на мыло тоже отправлять на всякий случай? Что-то ещё?

В общем, что и как?

Сторониие сервисы не хочу использовать.

Answer 1

[Anton Kuzmichev]

По крону, примерно раз в день?

Можно и чаще, смотря, насколько чувствительно будет потерять данные между бэкапами. Если совсем-совсем чувствительно, нужно задуматься о поднятии реплики или о постоянном бэкапе (используя WAL-журналы).

И потом их закачивать на aws и dropbox?

Хоть куда, но обязательно куда-нибудь закачивать на совершенно отдельный сервер/сервис, с проверкой, что всё успешно закачалось.
И лучше - на 2 разных хранилища.

Может, на мыло тоже отправлять на всякий случай?

Ну, чисто факультативно можно, хотя смысла в этом я лично не вижу, если вы закачиваете бэкап на другие сервера/сервисы.
Т.е. на мыло вы можете слать, а можете и не слать, но закачивать бэкап на 1-2 других сервера/сервиса - необходимо.

Из личного опыта. Тру-админы, просьба поправить, если где-то косячу.
Написал bash-скрипт, который запускаю по крону раз в 3 часа. Скрипт

• делает бэкап обычным pg_dump, в несколько потоков, в формате папки (--format=d)
  
• проводит тестовое восстановление бэкапа. Чисто в лоб - завершится ли pg_restore с нулевым кодом возврата
  
• сжимает бэкап БД и файлов в архив
  
• отправляет архив на отдельное ftp-хранилище
  
• отправляет архив на ещё одно ftp-хранилище
  
• отправляет краткий лог всего процесса (с метками времени) в корпоративный slack
  

Пока храню всё, не вычищая старого (с местом проблем нет). В идеале, видимо, нужно хранить по схеме

• N (где N - несколько десятков) последних
  
• 4-6 последних еженедельных
  
• 2-4 последних ежемесячных.
  

Comments

[getnowtoday]

зачем format=d, почему не custom?

[Melkij]

задуматься о поднятии реплики

И при этом всё равно не забывать делать бекапы. Реплика спасёт от краха железа, но не спасёт от случайного неправильного запроса (shit happens, да).

зачем format=d, почему не custom?

Ради параллельного дампа pg_dump --jobs=? очевидно. Штука весьма хорошая, особенно если в базе нет одной таблички, занимающей почти весь объём базы.

отправляет краткий лог всего процесса (с метками времени) в корпоративный slack

Если вместе со всем возможным stderr - то подойдёт, pg_restore напишет в stderr, если ему что-то не нравится. Как и pg_dump, в общем.
Для спокойного сна можно добавить какие-нибудь специфичные для проекта метрики, вроде проверки что в таблице пользователей не меньше 10 тыс уникальных email, есть заказы за последние сутки - в общем, что особенно важно для этого проекта. И замечательная вещь - гонять предрелизные тесты на развёрнутом бекапе. Можно с самодельными скриптами анонимизации.

[Anton Kuzmichev]

Melkij: спасибо за суперкоммент!

Ради параллельного дампа pg_dump --jobs=? очевидно

Именно! И нет явно доминирующих 1-2 таблиц, так что очень даже ускоряется процесс.

Если вместе со всем возможным stderr - то подойдёт

Вот так примерно выглядит каждый шаг скрипта:

# архивируем бэкап + файлы и папки конфигов
COMMAND="tar czf $TARGZ_PATH $BACKUP_DIR $BACKUP_FILES"
echo $COMMAND
ERROR=$($COMMAND 2>&1 >> $LOGFILE)
CODE=$?
if [ $CODE -ne 0 ]; then
    post_to_slack "\`$HOSTNAME\`: creating tar.gz failed:\n\`\`\`$ERROR\`\`\`\n\n\`\`\`$REPORT\`\`\`" "ERROR"
    exit 1
fi
REPORT+="$(date +%H:%M:%S.%N): created tar.gz\n"

можно добавить какие-нибудь специфичные для проекта метрики

гонять предрелизные тесты на развёрнутом бекапе

Вот это очень дельно, спасибо!

Answer 2

[Melkij]

Зависит от:
0) какой промежуток времени вам допустимо потерять при восстановлении
1) сколько места вы можете выделить для хранения бекапов
2) какой запас времени вы хотите сохранять для возможности восстановить данные когда-то из более далёкого прошлого

Может, на мыло тоже отправлять на всякий случай?

Как через почту пропихнуть даже мелкие бекапы в десяток гигабайт?
Или вы про совсем крошечные базы спрашиваете?

Шифровать или нет - зависит от требований к безопасности и оценке риска в случае disaster recovery не восстановить ключ шифрования.

Для хорошего point in time recovery - pg_basebackup + pg_receivexlog (archive_command только если уверены, что можете её сделать нормально). Или что-то, работающее поверх них.
Отдельный случай pitr с хранением в амазоне - см. вполне внятный wal-e
Если pitr не обязателен, то pg_dump делать. Да, лучше архивировать, сжимаются дампы весьма неплохо.

Например, политика бекапа из практики: ежедневный pg_dumpall, хранятся за 1 число каждого месяца на протяжении года плюс 8 дней предыдущих ежедневных.

Что-то ещё - регулярно пробовать восстановить и запустить базу из бекапа.

Comments

[getnowtoday]

почему pg_basebackup, а не pg_dump?

[Melkij]

getnowtoday: потому что из pg_dump не сделать PITR. Развернуть можно только только на единственную точку - на время старта pg_dump.
Если PITR делать не надо - то вполне pg_dump уместен и даёт свои плюшки.