Резервное копирование и восстановление

  • WAL (Write Ahead Log) - лог с упреждением. Является механизмом протоколирования всех транзакций. Позволяет восстановить систему после возможных сбоев. WAL состоит из последовательности файлов, расположенных в каталоге pg_xlog, вашей базы данных.
  • Репликация PostgreSQL

Резервное копирование и восстановление в PostgreSQL. Существуют три принципиально различных подхода к резервному копированию данных PostgreSQL:

  • Логическое резервирование: SQL бэкап (дамп SQL);
  • Физическое копирование файловой системы;
  • Непрерывное резервное копирование.

Восстановление после сбоя

Если работа сервера аварийно завершается, в логе сервера появляется сообщение с уровнем важности PANIC.

Восстановление после сбоя. Все изменения данных записываются на диск только после их гарантированного журналирования в WAL. Следует заметить, что изменения в базе данных не пишутся на диск в момент фиксации транзакции. Они записываются позже в фоновом процессе.

В логе WAL есть контрольный точки (checkpoints).

Логическое резервирование (SQL)

  • pg_dumpall - выполняет резервную копию всех БД (включая системную). Представляет меньший контроль над процессом создания дампа, чем утилита pg_dump. Лучше использовать pg_dump, а утилитой pg_dumpall с ключем -g сохранить только глобальные данные сервера(пользователи, группы, табличные пространства).
  • pg_dump - выполняет резервирование одной базы данных

Ключи pg_dump:

  • -t, –table=TABLE позволяет помещать в дамп только таблицы (или представления или последовательности или внешние таблицы), соответствующие table. Несколько таблиц могут быть выбраны написанием их всех с ключем -t. Также параметр table интерпретируется как шаблон в соответствии с теми же правилами используемыми командой \d консольного клиента psql, поэтому несколько таблиц может быть выбрано написанием маски символов в шаблоне. Когда -t задана, то pg_dump не пытается сбросить в дамп любые другие объекты базы данных от которых может зависеть выбранная таблица(ы). Поэтому нет никакой гарантии, что результаты дампа конкретной таблицы могут быть успешно восстановлены сами по себе в чистую базу данных.
  • -T, –exclude-table=TABLE Не сбрасывать в дамп любые таблицы соответствующие шаблону table. Шаблон интерпретируется в соответствии с теме же правилами, что и для -t. -T может быть задано более одного раза, исключая схемы соответствующие любым из нескольких шаблонов. Когда заданы обе опции -t и -T в дамп сбрасываются только те таблицы, которые соответствуют хотя бы одному из заданных вхождений -n и ни одному -N. При наличии -T без заданных -t, таблицы соответствующие перечисленным в -T исключаются из дампа.
  • -v, –verbose Установить подробный режим. В этом случае pg_dump будет выводить детальные комментарии объекта и время пуска/завершения создания файла дампа, и сообщения хода выполнения направляются на стандартный поток ошибок.

Логическое резервирование заключается в создании текстового файла с командами SQL. Такой файл можно передать обратно на сервер и воссоздать базу данных в том же состоянии, в котором она была во время бэкапа. У PostgreSQL для этого есть специальная утилита - pg_dump. При выполнении pg_dump, таблицы блокируются минимально, только запрет на изменение структуры таблицы.

  • Создаем бэкап с помощью pg_dump
    pg_dump -U postgres dbname > outfile
  • Для восстановления такого бэкапа достаточно выполнить:
    psql -U postgres dbname < infile

    базу данных «dbname» потребуется создать перед восстановлением и пользователя (которому принадлежит восстанавливаемая база данных) 

    postgres=# CREATE DATABASE dbname;
После восстановления бэкапа желательно запустить «ANALYZE», чтобы оптимизатор запросов обновил статистику.
  • Бекап только одной таблицы codes из БД testbd777
    pg_dump -U postgres testbd777 -t codes > codes_backup`date +%d.%m.%Y-%H.%M`.sql
  • Ключ -s (–schema-only) позволяет создать только схему БД без данных, например
    $ sudo -u postgres pg_dump -s mbillcz5054 > mbillcz5054_schema.sql
  • Ключ –inserts позволяет сделать бекап в формате SQL
    # pg_dump -U postgres --inserts testbd777 -t codes > codes_backup`date +%d.%m.%Y-%H.%M`.sql
  • Восстановление всего бекапа с остановкой на первой ошибке
    psql -h localhost -U postgres --set ON_ERROR_STOP=on -f mydb.sql

Пример

Пример: Полное логическое (SQL) резервирование и восстановление БД mbillcz5054. Алгоритм:

  1. Копируем глобальный данные сервера, используя утилиту pg_dumpall (с ключем -g, –globals-only dump only global objects, no databases). Будут сохранены глобальные объекты roles и tablespaces, без баз данных: 
    sudo -u postgres pg_dumpall --globals-only > globals-only_`date +%Y-%m-%d.%H.%M`.sql
  2. Можно сохранить определение объектов базы данных: роли, табличные пространства, схемы, индексы, триггеры и т.д.
    sudo -u postgres pg_dumpall --schema-only > schema-only_`date +%d.%m.%Y-%H.%M`.sql
  3. Копируем данные. Копируем каждую базу данных при помощи утилиты pg_dump
    pg_dump -U postgres mbillcz5054 | gzip  > mbillcz5054_backup_`date +%Y-%m-%d.%H.%M`.sql.gz

    Если вы хотите исключить какие-либо таблицы из дампа, не забудьте сделать схему этой таблицы, чтобы в будущем Вы ее могли восстановить на новом сервере, например исключим таблицу cdr из дампа и создадим ее схему: 

    pg_dump -U postgres mbillcz5054 -T cdr | gzip  > mbillcz5054_backup_`date +%d.%m.%Y-%H.%M`_NO_cdr.sql.gz
sudo -u postgres pg_dump -s mbillcz5054 -t cdr > schema-only.cdr.sql
  4. Перед восстановлением нужно создать базу данных mbillcz5054
    sudo -u postgres createdb mbillcz5054
  5. Восстановление пользовательских ролей, групп
    psql -U postgres -f globals-only.sql
  6. Восстановление данных БД mbillcz5054 из сжатого бекапа. Создание исключенной таблицы.
    gunzip -c mbillcz5054_backup.sql.gz | psql -U postgres mbillcz5054

psql -U postgres mb11 -f cdr_create.sql
  7. Желательно запустить ANALYZE для свеже восстановленной базы данных.
    mbillcz5054=# ANALYZE VERBOSE;

Скрипт для резервирования БД

#!/bin/bash
 
# Backup  PostgreSQL
 
DIR="/var/log/BACKUP/db_backup_mbill"
TIMENAME=`date +%Y-%m-%d.%H.%M`
PG_DUMP="/usr/bin/pg_dump"
SUDO="/usr/bin/sudo"
GZIP="/bin/gzip"
ExcludeTable="-T cdr"
DBNAME=mbillcz5054
BACKUP=$DIR/psql-$DBNAME-backup-$TIMENAME-db.sql.gz
 
echo "$SUDO -u postgres $PG_DUMP $DBNAME $ExcludeTable | $GZIP > $BACKUP";
 
$SUDO -u postgres $PG_DUMP $DBNAME $ExcludeTable | $GZIP > $BACKUP;
 
echo `/usr/bin/du -hsx $BACKUP`;

Запускаем еженедельно при помощи anacron (если установлено) или cron, для этого создаем символическую ссылку в директорию /etc/cron.weekly 

# ln -s /scripts/psql_backup_zabbix /etc/cron.weekly/

Дополнительная информация по изучению PostgreSQL

PQ VPS сервера в 28+ странах.