Различия

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


Предыдущая версия
—rezervnoe_kopirovanie_i_vosstanovlenie_v_postgresql [2025/07/06 12:39] (текущий) – внешнее изменение 127.0.0.1
Строка 1: Строка 1:
 +====== Резервное копирование и восстановление ======
 +~~Title: Резервное копирование и восстановление PostgreSQL ~~
 +{{htmlmetatags>
 +metatag-keywords=(резервирование PostgreSQL)
 +metatag-description=(Логика бекапов базы данных. Пример полного логического (SQL) резервирования и восстановления базы PostgreSQL.)
 +}}
  
 +
 +  * **WAL** (Write Ahead Log) - лог с упреждением. Является механизмом протоколирования всех транзакций. Позволяет восстановить систему после возможных сбоев. WAL состоит из последовательности файлов, расположенных в каталоге pg_xlog, вашей базы данных.
 +  * [[Репликация PostgreSQL]]
 +
 +Резервное копирование и восстановление в [[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|anacron]] (если установлено) или [[cron|cron]], для этого создаем символическую ссылку в директорию /etc/cron.weekly
 +<file>
 +# ln -s /scripts/psql_backup_zabbix /etc/cron.weekly/
 +</file>
 +
 +===== Дополнительная информация по изучению PostgreSQL  =====
 +
 +  * [[https://postgresql.leopard.in.ua/html/#x1-21600010|Работа с PostgreSQL: настройка и масштабирование. Бэкап и восстановление PostgreSQL]]
 +  * [[https://habr.com/ru/articles/178567/|Резервное копирование и восстановление в PostgreSQL]] Предположим что у нас есть postgresql в режиме потоковой репликации. master-сервер и hot-standby готовый заменить погибшего товарища. При плохом развитии событий, нам остается только создать trigger-файл и переключить наши приложения на работу с новым мастером. Однако, возможны ситуации когда вполне законные изменения были сделаны криво написанной миграцией и попали как на мастер, так и на подчиненный сервер. Например, были удалены/изменены данные в части таблиц или же таблицы были вовсе удалены. С точки зрения базы данных все нормально, а с точки зрения бизнеса — катастрофа. В таком случае провозглашение горячего hot-standby в мастера, процедура явно бесполезная... Для предостережения такой ситуации есть, как минимум, два варианта… использовать периодическое резервное копирование средствами pg_dump;использовать резервное копирование на основе базовых копий и архивов WAL.
 +  * [[https://habr.com/ru/sandbox/28003/|Резервное копирование баз данных в СУБД PostgreSQL (On-line backup)]]
 +  * [[pg_basebackup]]

📌 Удобный подбор VPS по параметрам доступен на DIEGfinder.com - официальном инструменте проекта DIEG. Это часть единой экосистемы, созданной для того, чтобы помочь быстро найти подходящий VPS/VDS сервер для любых задач хостинга.

📌 Для тестирования скриптов, установщиков VPN и Python-ботов рекомендуем использовать надежные VPS на короткий срок. Подробнее о быстрой аренде VPS для экспериментов - читайте здесь.

💥 Подпишись в Телеграм 💥 и задай вопрос по сайтам и хостингам бесплатно!

7 Самых Популярных Статей

  1. Как запустить скрипты и веб-приложения на Python
  2. Что такое страны TIER 1,2,3
  3. 7 способов сравнения файлов по содержимому в Windows или Linux
  4. Установка и тестирование веб-панели HestiaCP
  5. Nginx простые примеры конфигурации
  6. top, htop, atop определение загрузки ОС (Load average, LA)
  7. Использование rsync в примерах

7 Самых Популярных Обзоров

  1. Хостинг для Python-скриптов и приложений
  2. ТОП 4 лучших антидетект браузеров (Бесплатные & Платные)
  3. Подборка купонов (промокоды) на хостинг, антидетект браузеры
  4. Обзор THE.Hosting (PQ Hosting): надежный хостинг с профессиональной поддержкой
  5. Хостинг в России
  6. Хостинг в Европе
  7. Обзор браузера Dolphin {anty} для мультиаккаунтинга