Резервное копирование баз данных в СУБД PostgreSQL (On-line backup)

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

Немного теории.

На текущий момент в СУБД PostgreSQL существует два способа резервного копирования баз данных без остановки сервера СУБД или блокировки (lock) работы с данными (On-line backup):

1. Base backup (полный бекап)
2. On-line backup (архивирование WAL — Write-Ahead Log (Журнал опережающей записи или архивные журналы, по аналогии с СУБД Oracle))

Первый способ организует полное резервное копирования баз данных (системных файлов), второй способ сохраняет лишь журнал записей, используя которые при восстановлении в дальнейшем, можно откатиться на тот или иной срез данных во времени, в диапазоне, начиная от времени создания Base backup и заканчивая последним сохраненным журналом (архивный лог (WAL)). Чем больше журналов вы храните, тем, при желании, на более широкий диапазон данных вы сможете откатиться при восстановлении (PITR — Point-In-Time Recovery (восстановление к состоянию в определённый момент времени)).

Для резервного копирования данных так же можно использовать утилиту pg_dump или pg_dumpall, но ее применение не гарантирует целостность полученных данных, на больших базах данных для ее работы необходимо большое количество памяти, во время ее использования происходят блокировки таблиц и объектов, что не применимо в высоконагруженных системах, в которых активно используются транзакции и происходит постоянное изменение и добавление данных.

Если у вас больше одного сервера, наряду к вышеописанным способами, рекомендуется также использовать Master — (multi)Slave архитектуру, при которой все действия (insert / delete / update / create / drop) на Master сервере дублируются (реплицируются) на slave сервер(ы), которые в дальнейшем можно / нужно использовать под READ-ONLY и тяжелые запросы, чтобы лишний раз не нагружать Master сервер.
Master сервер должен быть один, Slave серверов может быть сколько угодно много.

Встроенная асинхронная Master — (multi)Slave репликация в PostgreSQL появилась, начиная с версии PostgreSQL 8.2, и с каждым выходом новой версии дорабатывается и совершенствуется.
В предыдущих версиях, для организации Master — (multi)Slave репликации применялось различное дополнительное программное обеспечение: slony, londiste (от компании Skype), pgpool и другое, а начиная с версии PostgreSQL 8.2 Master — (multi)Slave репликация добавлена в основной код и доступна из коробки.

Из последних значимых достижений в данном вопросе стоит отметить добавления режима синхронной репликации Master — (multi)Slave, который ожидается в новой версии PostgreSQL 9.1 и не требует установки дополнительных модулей и программного обеспечения. Реализация данного режима интересна тем, что есть возможность включения постоянного синхронного режима работы репликации, как на конкретный Slave сервер или группу Slave серверов, так и во время конкретного запроса или части запроса внутри DML блока, при включении которого, часть данных будет реплицирована в синхронном режиме, а при выключении данного режима в этом же DML блоке, в асинхронном режиме. (Данный режим с примерами применения и результатами тестированием я опишу в следующих статьях).

Переходим к практике.

Base backup (полный бекап).

1. Создание каталога для хранения WAL:
   mkdir -p /var/lib/pgsql/9.0/backups/archive
2. Установка прав доступа на каталог хранения base backup для пользователя postgres:
   chown –R postgres /var/lib/pgsql/9.0/backups/base
   
3. Создание контрольной точки в WAL:
   SELECT pg_start_backup('$PG_BACKUP_DIR/$DATE_TIME_PREFIX');
   
4. Архивирование системного каталога данных /var/lib/pgsql/9.0/data/:
   tar cvjf /var/lib/pgsql/9.0/backups/base/[BASE BACKUP NAME].tar.bz2 /var/lib/pgsql/9.0/data
   
5. Удаление контрольной точки в WAL:
   SELECT pg_stop_backup();
   

On-line backup (архивирование WAL).

1. Создание каталога для хранения WAL:
   mkdir -p /var/lib/pgsql/9.0/backups/archive
2. Установка прав доступа на каталог хранения WAL для пользователя postgres:
   chown –R postgres /var/lib/pgsql/9.0/backups/archive
3. В файле конфигурации /var/lib/pgsql/9.0/data/postgresql.conf добавить строчки:
   archive_command = 'test ! -f /var/lib/pgsql/9.0/backup_in_progress || cp -i %p /var/lib/pgsql/9.0/backups/archive/%f < /dev/null'
archive_mode = on
wal_level = archive

4. Перегрузить postgresql
   /etc/init.d/postgresql-9.0 restart
5. В каталоге /var/lib/pgsql/9.0/backups/archive должны появиться WAL.

Ниже привожу скрипт для автоматического создания base backup, который можно запускать или вручную или по cron:

#!/bin/sh

PG_DIR="/var/lib/pgsql/9.0"
PG_DATA_DIR="$PG_DIR/data"
PG_BACKUP_DIR="$PG_DIR/backups"
PG_BASE_BACKUP_DIR="$PG_BACKUP_DIR/base"
PG_ARCHIVE_BACKUP_DIR="$PG_BACKUP_DIR/archive"
DATE="/bin/date"
DATE_TIME_PREFIX=`$DATE +%Y%m%d%H%M%S`
PG_BASE_BACKUP_FILE="$DATE_TIME_PREFIX.tar"
PSQL="/usr/bin/psql"
TAR="/bin/tar"
CHMOD="/bin/chmod"
TOUCH="/bin/touch"
RM="/bin/rm"
BZIP2="/usr/bin/bzip2"
MKDIR="/bin/mkdir"
ECHO="/bin/echo"

if [[ -f $PG_DIR/backup_in_progress ]]; then
  $ECHO "Error: Backup in progress."
  exit 1
fi

if [[ ! -d $PG_BASE_BACKUP_DIR ]]; then
  $ECHO "Directory $PG_BASE_BACKUP_DIR doesn't exists. Trying create it."
  $MKDIR -p $PG_BASE_BACKUP_DIR
  $CHMOD 0700 $PG_BASE_BACKUP_DIR
fi

$TOUCH $PG_DIR/backup_in_progress
$PSQL -c "SELECT pg_start_backup('$PG_BASE_BACKUP_DIR/$DATE_TIME_PREFIX');"
$TAR cvf $PG_BASE_BACKUP_DIR/$PG_BASE_BACKUP_FILE $PG_DATA_DIR
$PSQL -c "SELECT pg_stop_backup();"
$TAR rf $PG_BASE_BACKUP_DIR/$PG_BASE_BACKUP_FILE $PG_ARCHIVE_BACKUP_DIR
$CHMOD 0600 $PG_BASE_BACKUP_DIR/$PG_BASE_BACKUP_FILE
$BZIP2 -9 $PG_BASE_BACKUP_DIR/$PG_BASE_BACKUP_FILE
$RM -f $PG_ARCHIVE_BACKUP_DIR/*
$RM -f $PG_DIR/backup_in_progress

exit 0

* This source code was highlighted with Source Code Highlighter.