PostgreSQL предлагает несколько вариантов резервирования данных. Обо всех них уже рассказано не раз, в том числе и на хабре. Но в основном рассказывается про технические особенности методов. Я же хочу постараться рассказать про общую стратегию резервного копирования, объединив все методы в эффективную систему, которая поможет вам сохранить все данные и уменьшить число погибших нервных клеток в критических ситуациях.
Вводные данные: сервер PostgreSQL 9.2, База размером >100Gb.
Как следует из мануалов, есть 3 метода резервирования данных:
Все они имеют свои особенности, поэтому мы используем все эти методы.
Настройка потоковой репликации хорошо описана в статьях здесь и здесь. Смысл этой репликации в том, что если основной сервер упадет, то слейв можно быстро сделать мастером и работать с ним, т.к. на слейве находится полная копия БД.
В потоковой репликации большое значение имеют WAL-файлы. Это файлы, откуда слейв подтягивает недостающие данные, если на мастере их уже не осталось. Отсюда есть необходимость хранить эти WAL-файлы подольше. Мы храним эти файлы 8 дней.
Директория с WAL-файлами должна быть доступна как мастеру (на запись), так и слейву (на чтение). Чтобы обеспечить это, мы создали общее хранилище на базе glusterFS, которое подмонтировали на обоих серверах. Таким образом, во-первых, достигается бОльшая надежность (при падении мастера wal-файлы будут доступны для слейва), а во-вторых есть возможность быстрого создания дополнительных слейвов, которым тоже понадобятся эти wal.
Итог: потоковая репликация защитит от сбоев основного сервера, при этом почти никакие данные не потеряются.
С падением сервера разобрались, теперь разберемся с человеческим фактором, когда по какой-то причине удалены данные в таблицах, либо сами таблицы или базы банных. В этом случае придется восстанавливать данные из резервной копии. Еще копия БД может потребоваться для тестирования приложения. Такую копию можно сделать двумя способами — сделать дамп базы либо скопировать всю директорию с данными. Долгое время мы использовали первый вариант — дампили БД в файл. Но у дампа есть большой минус —процесс блокирует таблицы, и другие процессы уже не могут с ними работать (UPD: pg_dump не блокирует таблицы. Однако, он создает большую нагрузку на БД). Для боль��их БД это критично.
Сейчас для резервного копирования БД мы используем утилиту pg_basebackup, которая по сути копирует все файлы БД в один большой архив.
Мы сохраняем 4 недельных копии и 6 месячных. Копии храним на том же хранилище GlusterFS. Копии мы создаем таким образом, чтобы они были самодостаточны, то есть работали сразу после развертывания, без необходимости подгрузки дополнительных WAL-файлов. Поэтому мы можем без проблем развернуть базу, к примеру, трехмесячной давности.
Примечательно то, что утилиту pg_basebackup можно запускать (при определенных условиях) на слейв-сервере, поэтому создание резервных копий абсолютно никак не нагружает мастер.
Чтобы pg_basebackup работал на слейве, нужно включить сохранение WAL-файлов, для этого установить опции в postgresql.conf:
1000 — это количество wal-файлов, которые postgreSQL хранит на диске. Вам, возможно, нужно будет уменьшить или увеличить этот параметр. Дело в том, что pg_basebackup просто архивирует содержимое БД, однако во время архивации некоторые данные уже изменятся, и эти изменившиеся данные PostgreSQL потом при восстановлении подтянет из WAL-файлов. Для этого pg_basebackup сохранит в архив также все существующие WAL-файлы. Таким образом, чтобы все прошло успешно, нужно, чтобы были в наличии все WAL-файлы с момента начала работы pg_basebackup до момента его завершения. Если нужные WAL-файлы удалятся, то pg_basebackup завершится с ошибкой. Нужное количество wal_keep_segments можно определить опытным путем.
Чтобы создать резервную копию БД, мы запускаем pg_basebackup с такими параметрами:
-F говорит нам, что нужно сохранить все в один файл, -z — что нужно заархивировать, -Xf — включить в архив WAL-файлы. Без включения WAL-файлов бекап отработает, но при восстановлении Postgres потребует предоставить недостающие WAL-файлы.
Бекап мы делаем по крону по субботам вот таким скриптом:
Таким образом создается файл с 3 суффиксами: с номером недели, с названием месяца и служебным last.
Чтобы восстановить БД из копии, нужно остановить PostgreSQL, удалить (или перенести) старые данные из data-директории, распаковать туда содержимое архива и запустить сервер. Тут есть один интересный момент. Так как pg_basebackup мы делали на слейве, то вместе с данными распакуется и файл recovery.conf. Так вот, если мы хотим восстановить данные на последнее возможное состояние, то этот файл нужно оставить, в этом случае после запуска сервера Postgres начнет подтягивать WAL-файлы из места, указанного в recovery.conf. Если взять последний недельный бекап, то у нас будут все нужные WAL-файлы (т.к. мы храним их 8 дней), и мы сможем восстановить БД до последнего нормального состояния.
Если же нам нужны данные по состоянию на время создан��я резервной копии, то перед запуском БД нужно удалить файл recovery.conf.
Резервные копии мы также используем для тестовых целей, за одним проверяется и корректность создания резервной копии.
Имя base.last.tar.gz используется нами для восстановления последней копии в тестовую БД. Тестовая БД у нас восстанавливается каждую ночь вот таким скриптом:
Итог: копирование БД на файловом уровне защитит от программных сбоев и человеческих ошибок. При восстановлении БД из резервной копии потеряются последние данные.
Мы давно не делаем полный SQL-дамп большой БД, зато делаем дампы изменившихся таблиц, 1 таблица — 1 файл. Это позволяет очень быстро восстановить данные в случаях, когда испорчена только одна таблица — не нужно распаковывать всю БД из резервной копии.
PostgreSQL предоставляет нам статистику по количеству изменений в таблицах, и каждую ночь мы смотрим, какие таблицы были изменены, и их дампим. Статистику смотрим примерно таким вот запросом:
Итог: SQL-дамп поможет восстановить незначительные ошибки. При этом данные будут актуальны на момент создания дампа.
Как видим, чтобы максимально защитить себя от потери данных, можно и нужно использовать все возможности PostgreSQL, тем более, что это не так и сложно.
Вводные данные: сервер PostgreSQL 9.2, База размером >100Gb.
Варианты бекапа
Как следует из мануалов, есть 3 метода резервирования данных:
- Потоковая репликация
- Копирование файлов БД
- SQL-дампы
Все они имеют свои особенности, поэтому мы используем все эти методы.
Потоковая репликация
Настройка потоковой репликации хорошо описана в статьях здесь и здесь. Смысл этой репликации в том, что если основной сервер упадет, то слейв можно быстро сделать мастером и работать с ним, т.к. на слейве находится полная копия БД.
В потоковой репликации большое значение имеют WAL-файлы. Это файлы, откуда слейв подтягивает недостающие данные, если на мастере их уже не осталось. Отсюда есть необходимость хранить эти WAL-файлы подольше. Мы храним эти файлы 8 дней.
Директория с WAL-файлами должна быть доступна как мастеру (на запись), так и слейву (на чтение). Чтобы обеспечить это, мы создали общее хранилище на базе glusterFS, которое подмонтировали на обоих серверах. Таким образом, во-первых, достигается бОльшая надежность (при падении мастера wal-файлы будут доступны для слейва), а во-вторых есть возможность быстрого создания дополнительных слейвов, которым тоже понадобятся эти wal.
Итог: потоковая репликация защитит от сбоев основного сервера, при этом почти никакие данные не потеряются.
Копирование файлов БД
С падением сервера разобрались, теперь разберемся с человеческим фактором, когда по какой-то причине удалены данные в таблицах, либо сами таблицы или базы банных. В этом случае придется восстанавливать данные из резервной копии. Еще копия БД может потребоваться для тестирования приложения. Такую копию можно сделать двумя способами — сделать дамп базы либо скопировать всю директорию с данными. Долгое время мы использовали первый вариант — дампили БД в файл. Но у дампа есть большой минус —
Сейчас для резервного копирования БД мы используем утилиту pg_basebackup, которая по сути копирует все файлы БД в один большой архив.
Мы сохраняем 4 недельных копии и 6 месячных. Копии храним на том же хранилище GlusterFS. Копии мы создаем таким образом, чтобы они были самодостаточны, то есть работали сразу после развертывания, без необходимости подгрузки дополнительных WAL-файлов. Поэтому мы можем без проблем развернуть базу, к примеру, трехмесячной давности.
Примечательно то, что утилиту pg_basebackup можно запускать (при определенных условиях) на слейв-сервере, поэтому создание резервных копий абсолютно никак не нагружает мастер.
Чтобы pg_basebackup работал на слейве, нужно включить сохранение WAL-файлов, для этого установить опции в postgresql.conf:
wal_level = hot_standby
wal_keep_segments = 1000
1000 — это количество wal-файлов, которые postgreSQL хранит на диске. Вам, возможно, нужно будет уменьшить или увеличить этот параметр. Дело в том, что pg_basebackup просто архивирует содержимое БД, однако во время архивации некоторые данные уже изменятся, и эти изменившиеся данные PostgreSQL потом при восстановлении подтянет из WAL-файлов. Для этого pg_basebackup сохранит в архив также все существующие WAL-файлы. Таким образом, чтобы все прошло успешно, нужно, чтобы были в наличии все WAL-файлы с момента начала работы pg_basebackup до момента его завершения. Если нужные WAL-файлы удалятся, то pg_basebackup завершится с ошибкой. Нужное количество wal_keep_segments можно определить опытным путем.
Чтобы создать резервную копию БД, мы запускаем pg_basebackup с такими параметрами:
/usr/bin/pg_basebackup -U postgres -D /tmp/pg_backup -Ft -z -Xf
-F говорит нам, что нужно сохранить все в один файл, -z — что нужно заархивировать, -Xf — включить в архив WAL-файлы. Без включения WAL-файлов бекап отработает, но при восстановлении Postgres потребует предоставить недостающие WAL-файлы.
Бекап мы делаем по крону по субботам вот таким скриптом:
#!/bin/bash
mkdir /tmp/pg_backup
/usr/bin/pg_basebackup -U postgres -D /tmp/pg_backup -Ft -z -Xf
WEEK=$(date +"%V")
MONTH=$(date +"%b")
let "INDEX = WEEK % 5"
test -e /collector/db-backup/base.${INDEX}.tar.gz && rm /collector/db-backup/base.${INDEX}.tar.gz
cp /tmp/pg_backup/base.tar.gz /collector/db-backup/base.${INDEX}.tar.gz
test -e /collector/db-backup/base.${MONTH}.tar.gz && rm /collector/db-backup/base.${MONTH}.tar.gz
ln /collector/db-backup/base.${INDEX}.tar.gz /collector/db-backup/base.${MONTH}.tar.gz
test -e /collector/db-backup/base.last.tar.gz && rm /collector/db-backup/base.last.tar.gz
ln /collector/db-backup/base.${INDEX}.tar.gz /collector/db-backup/base.last.tar.gz
rm -r /tmp/pg_backup
Таким образом создается файл с 3 суффиксами: с номером недели, с названием месяца и служебным last.
Чтобы восстановить БД из копии, нужно остановить PostgreSQL, удалить (или перенести) старые данные из data-директории, распаковать туда содержимое архива и запустить сервер. Тут есть один интересный момент. Так как pg_basebackup мы делали на слейве, то вместе с данными распакуется и файл recovery.conf. Так вот, если мы хотим восстановить данные на последнее возможное состояние, то этот файл нужно оставить, в этом случае после запуска сервера Postgres начнет подтягивать WAL-файлы из места, указанного в recovery.conf. Если взять последний недельный бекап, то у нас будут все нужные WAL-файлы (т.к. мы храним их 8 дней), и мы сможем восстановить БД до последнего нормального состояния.
Если же нам нужны данные по состоянию на время создан��я резервной копии, то перед запуском БД нужно удалить файл recovery.conf.
Резервные копии мы также используем для тестовых целей, за одним проверяется и корректность создания резервной копии.
Имя base.last.tar.gz используется нами для восстановления последней копии в тестовую БД. Тестовая БД у нас восстанавливается каждую ночь вот таким скриптом:
#!/bin/bash
/etc/init.d/postgresql stop
rm -r /data/*
tar -zxf /collector/db-backup/base.last.tar.gz -C /data/
rm /data/recovery.conf
/etc/init.d/postgresql start
Итог: копирование БД на файловом уровне защитит от программных сбоев и человеческих ошибок. При восстановлении БД из резервной копии потеряются последние данные.
SQL-дампы
Мы давно не делаем полный SQL-дамп большой БД, зато делаем дампы изменившихся таблиц, 1 таблица — 1 файл. Это позволяет очень быстро восстановить данные в случаях, когда испорчена только одна таблица — не нужно распаковывать всю БД из резервной копии.
PostgreSQL предоставляет нам статистику по количеству изменений в таблицах, и каждую ночь мы смотрим, какие таблицы были изменены, и их дампим. Статистику смотрим примерно таким вот запросом:
select schemaname,relname,n_tup_ins+n_tup_upd+n_tup_del from pg_stat_user_tables ;Итог: SQL-дамп поможет восстановить незначительные ошибки. При этом данные будут актуальны на момент создания дампа.
In conclusion
Как видим, чтобы максимально защитить себя от потери данных, можно и нужно использовать все возможности PostgreSQL, тем более, что это не так и сложно.