web

Надо заводить личный stackoverflow

Задача - организовать бэкап для базы данных. Дёшево и надёжно. PostgreSQL. Таблица, в которой очень много очень мелких записей, много записей добавляется ежедневно. Какие решения для такого случая оптимальны (начиная с физических носителей)?
Метки:
Насколько я понимаю, для начала желательно иметь асинхронную реплику.

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

По этим соображением реплику лучше делать не через streaming replication а через копирование WAL средствами ОС.

Соответсвенно потом мы регулярно делаем с этой реплики дамп pg_dump-ом который заведомо восстанавливается на более другую версию postgres, на какой угодно архитектуре, а то и вообще на другую базу (было дело я дампы от 7.4 постгеса в sqlite загружал), и уже его копируем на внешние носители. Или пожатый чем угодно, или с применением средств инкрементального бэкапа.

В 90% случаев аварий этот внешний носитель окажется лишним. Достаточно будет промотить реплику в мастера а на месте бывшего мастера организовать новую реплику. Ну или скопировать базу-реплику pg_basebackup-ом обратно на место упавшей. В оставшихся 10% случаев придется восстанавливаться на совсем другое железо, или с бэкапа несколькогодичной давности.

В принципе, у pg_dump есть опция --inserts которая делает человеко-читаемый, человеко-редактируемый и в принципе загружаемый в другие базы SQL-скрипт. Но если размер дампа будет несколько превосходить возможности типичного текстового редактора, то стоит использовать --format=directory. Кстати, если в базе помимо большой и часто меняющейся таблицы есть большие редко меняющиеся, то --format=directory позволит потом использвать средства инкрементального бэкапа файлов.

P.S. "Очень много" это сколько? Десятки миллионов в день? Даже десятки миллионов мелких записей это где-то первые гигабайты. Для ручного редактирования SQL-скрипта вроде как и много, а для терабайтных внешних дисков немного.
Ну вообще расшифровке этой фразы посвящена вся 25-я глава документации к постгресу.

Если кратко, то сервер БД (не только Postgres, у Oracle то же самое) работает так:

1. Сначала все операции изменения данных пишутся во write-ahead log (WAL).
2. Потом этот лог накатывается на основное хранилище. Если что упало, то при перезапуске сервера последние незавершенные транзакции бывает можно восстановить посредством повторного накатывания этих логов (и это автоматически делается).

Соответственно, репликация работает таким образом, что мы берем эти логи и передаем тем или иным способом на реплику. Если мы передаем те же записи, что попадают в лог, по TCP-соединению, то это streaming replication.
Если мы просто берем и копируем записанные в файлы WAL (а они все равно пишутся в файлы, на случай если сервер упадет, и при подъеме потребуется переприменять), а потом сервер реплики их подбирает то это как раз и есть log-shipping replication, копирование логов внешними по отношению к постгресу средствами.

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

В общем про это читать в главе 24.3
а про то, как накатывать WAL на реплику - в главе 26.

Работа с pg_dump описана в первой части главы 24.
Витус рассказал почти исчерпвающе. Для самого простого хранения бинарных бэкапов я бы использовал zfs и снапшоты.

pg_start_backup

сделать снапшот

pg_stop_backup

Восстановление такого бэкапа занимает десятки секунд, не более
А zfs позволяет потом, после восстановления нормальной работы сервера, утащить содержимое сделанного снапшота куда-то на внешний носитель? Что-то я этого в хэндбуке не увидал.

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

Конечно, сочетание zfs с RAID-5 эту проблему где-то как-то решает. Но накладывает сильно жесткие требования на качество работы системного администратора. То есть не проконтролировать вовремя, что один из дисков в RAID сдох и не заменить его - может оказаться фатальным, особенно если учесть что однотипные диски склонны дохнуть пачками. Дамп на внешнем носителе, который может быть восстановлен куда угодно, а сам носитель вынесен за пределы датацентра имеет свои преимущества перед сколь угодно надежным RAID.

Сдыхание нахрен материнской платы сервера и пожар в дата-центре с полным выгоранием стойки я бы тоже рассматривал как угрозы, от которых стоит пытаться защититься, пусть с увеличенным даунтаймом и, возможно, потерей бэкапа за последние пару-тройку (но не более) дней.
Ну в итоге получается примерно то же самое что с WAL-репликацией, только на уровне ФС, а не БД Т.е. все равно надо иметь второй сервер, куда накатывать снапшоты. Имеет смысл, если у тебя далеко не весь изменяемый контент лежит в базе.

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

(а) криптолокер ничего не сделает со снапшотами
(б) криптолокер на сервере все еще носит отчетливые следы албанского вируса
(в) врожденная инкрементальность снапшотов zfs и фиксированное время востановления до любого снапшота исключает потребность в ханойских дедушках