Инструменты пользователя

Инструменты сайта


rezervnoe_kopirovanie_i_vosstanovlenie_v_postgresql

Различия

Здесь показаны различия между двумя версиями данной страницы.

Ссылка на это сравнение

rezervnoe_kopirovanie_i_vosstanovlenie_v_postgresql [2018/07/06 08:27] (текущий)
Строка 1: Строка 1:
 +====== Резервное копирование и восстановление ======
 +~~Title: Резервное копирование и восстановление PostgreSQL ~~
 +{{htmlmetatags>​
 +metatag-keywords=(резервирование PostgreSQL)
 +metatag-description=(Логика бекапов базы данных. Пример полного логического (SQL) резервирования и восстановления базы PostgreSQL.)
 +}}
  
 +
 +  * **WAL** (Write Ahead Log) - лог с упреждением. Является механизмом протоколирования всех транзакций. Позволяет восстановить систему после возможных сбоев. WAL состоит из последовательности файлов,​ расположенных в каталоге pg_xlog, вашей базы данных.
 +  * [[Репликация PostgreSQL]]
 +
 +Резервное копирование и восстановление в [[PostgreSQL]]. Существуют три принципиально различных подхода к резервному копированию данных PostgreSQL:
 +  * Логическое резервирование:​ SQL бэкап (дамп SQL);
 +  * Физическое копирование файловой системы;​
 +  * Непрерывное резервное копирование.
 +
 +===== Восстановление после сбоя =====
 +Если работа сервера аварийно завершается,​ в логе сервера появляется сообщение с уровнем важности PANIC.
 +
 +
 +**Восстановление после сбоя**. Все изменения данных записываются на диск только после их гарантированного журналирования в WAL. Следует заметить,​ что изменения в базе данных не пишутся на диск в момент фиксации транзакции. Они записываются позже в фоновом процессе.
 +
 +В логе WAL есть контрольный точки (checkpoints).
 +
 +{{ ::​backup-drop-01.jpg?​nolink |}}
 +===== Логическое резервирование (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<​file bash>
 +pg_dump -U postgres dbname > outfile
 +</​file>​
 +  * Для восстановления такого бэкапа достаточно выполнить:<​file bash>
 +psql -U postgres dbname < infile
 +</​file>​ базу данных «dbname» потребуется создать перед восстановлением и пользователя (которому принадлежит восстанавливаемая база данных)<​file sql>
 +postgres=# create database dbname;
 +</​file>​
 +
 +<note important>​После восстановления бэкапа желательно запустить «ANALYZE»,​ чтобы оптимизатор запросов обновил статистику.</​note>​
 +
 +  * Бекап только одной таблицы codes из БД testbd777<​file>​
 +pg_dump -U postgres testbd777 -t codes > codes_backup`date +%d.%m.%Y-%H.%M`.sql
 +</​file>​
 +  * Ключ **-s** (--schema-only) позволяет создать только схему БД без данных,​ например<​file>​
 +$ sudo -u postgres pg_dump -s mbillcz5054 > mbillcz5054_schema.sql
 +</​file>​
 +  * Ключ **--inserts** позволяет сделать бекап в формате SQL<​file>​
 +# pg_dump -U postgres --inserts testbd777 -t codes > codes_backup`date +%d.%m.%Y-%H.%M`.sql
 +</​file>​
 +  * Восстановление всего бекапа с остановкой на первой ошибке<​file>​
 +psql -h localhost -U postgres --set ON_ERROR_STOP=on -f mydb.sql
 +</​file>​
 +==== Пример ====
 +**Пример**:​ Полное логическое (SQL) резервирование и восстановление БД mbillcz5054. Алгоритм:​
 +  - Копируем глобальный данные сервера,​ используя утилиту pg_dumpall (с ключем -g, --globals-only dump only global objects, no databases). Будут сохранены глобальные объекты roles и tablespaces,​ без баз данных:​ <​file>​
 +sudo -u postgres pg_dumpall --globals-only > globals-only_`date +%Y-%m-%d.%H.%M`.sql
 +</​file>​
 +  - Можно сохранить определение объектов базы данных:​ роли, табличные пространства,​ схемы, индексы,​ триггеры и т.д.<​file>​
 +sudo -u postgres pg_dumpall --schema-only > schema-only_`date +%d.%m.%Y-%H.%M`.sql
 +</​file>​
 +  - Копируем данные. Копируем каждую базу данных при помощи утилиты pg_dump<​file>​
 +pg_dump -U postgres mbillcz5054 | gzip  > mbillcz5054_backup_`date +%Y-%m-%d.%H.%M`.sql.gz
 +</​file>​Если вы хотите исключить какие-либо таблицы из дампа, не забудьте сделать схему этой таблицы,​ чтобы в будущем Вы ее могли восстановить на новом сервере,​ например исключим таблицу cdr из дампа и создадим ее схему:<​file>​
 +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
 +</​file>​
 +  - Перед восстановлением нужно создать базу данных mbillcz5054<​file>​
 +sudo -u postgres createdb mbillcz5054
 +</​file>​
 +  - Восстановление пользовательских ролей, групп<​file>​
 +psql -U postgres -f globals-only.sql
 +</​file>​
 +  - Восстановление данных БД mbillcz5054 из сжатого бекапа. Создание исключенной таблицы.<​file>​
 +gunzip -c mbillcz5054_backup.sql.gz | psql -U postgres mbillcz5054
 +
 +psql -U postgres mb11 -f cdr_create.sql
 +</​file>​
 +  - Желательно запустить ANALYZE для свеже восстановленной базы данных.<​file>​
 +mbillcz5054=#​ ANALYZE VERBOSE;
 +</​file>​
 +===== Скрипт для резервирования БД =====
 +
 +<file bash>
 +#!/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`;
 +</​file>​
 +Запускаем еженедельно при помощи [[anacron]] (если установлено) или [[cron]], для этого создаем символическую ссылку в директорию /​etc/​cron.weekly
 +<​file>​
 +# ln -s /​scripts/​psql_backup_zabbix /​etc/​cron.weekly/​
 +</​file>​
 +
 +===== Дополнительная информация =====
 +
 +  * [[http://​postgresql.leopard.in.ua/​html/#​x1-21600010|Работа с PostgreSQL: настройка и масштабирование. Бэкап и восстановление PostgreSQL]]
 +  * [[http://​funix.ru/​bazy-dannyx/​pg_dump.html|pg_dump]]
 +  * [[https://​habrahabr.ru/​post/​178567/​|Резервное копирование и восстановление в PostgreSQL]]Предположим что у нас есть postgresql в режиме потоковой репликации. master-сервер и hot-standby готовый заменить погибшего товарища. При плохом развитии событий,​ нам остается только создать trigger-файл и переключить наши приложения на работу с новым мастером. Однако,​ возможны ситуации когда вполне законные изменения были сделаны криво написанной миграцией и попали как на мастер,​ так и на подчиненный сервер. Например,​ были удалены/​изменены данные в части таблиц или же таблицы были вовсе удалены. С точки зрения базы данных все нормально,​ а с точки зрения бизнеса — катастрофа. В таком случае провозглашение горячего hot-standby в мастера,​ процедура явно бесполезная... Для предостережения такой ситуации есть, как минимум,​ два варианта… использовать периодическое резервное копирование средствами pg_dump;​использовать резервное копирование на основе базовых копий и архивов WAL.
 +  * [[https://​habrahabr.ru/​sandbox/​28003/​|Резервное копирование баз данных в СУБД PostgreSQL (On-line backup)]] ​
загрузка...
rezervnoe_kopirovanie_i_vosstanovlenie_v_postgresql.txt · Последние изменения: 2018/07/06 08:27 (внешнее изменение)