Резервное копирование баз данных в СУБД 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
.
Tags:
postgresql

You can't comment this post because its author is not yet a full member of the community. You will be able to contact the author only after he or she has been invited by someone in the community. Until then, author's username will be hidden by an alias.