[PostgreSQL, Администрирование баз данных] Этюд — копированию баз данных PostgreSQL без использования pg_dump
Автор
Сообщение
news_bot ®
Стаж: 6 лет 3 месяца
Сообщений: 27286
Постановка задачиОт бизнеса поступила задача — необходимо регулярно сохранять копии отдельных баз данных. Упрощенно говоря — бекапить отдельные базы данных, на случай сверки и потери данных в исходных базах.
Первое и самое очевидное решение — pg_dump
Достоинства — простота решения. Штатные методы. Все отработано, документации и материалов великое множество.
Но, достоинства есть продолжения недостатков.
Во первых-объемы дампов.
Во вторых-и это самое неприятное, были случаи несовпадения исходной и целевой БД при восстановлении из дампа.
В третьих-время, сначала на создание дампа, потом на восстановление БД из дампа.
Нужно искать другой путь копирования БД между серверами. Бизнес требовал, задача интересная.
Не факт, что решение получилось максимально эффективным и не будет изменено/улучшено. Но как этюд использования возможностей PostgreSQL, может показаться интересным.
В результате, для копирования БД между серверами было принято решение использовать механизм логической репликации PostgreSQL.
Термины и исходные данныеИсходный кластер -кластер PostgreSQL содержащий БД которую нужно скопировать.
Исходная БД — объект копирования, БД на исходном кластере
Клон БД — копия исходной БД на исходном кластере
Кластер хранения копий БД — отдельный кластер PostgreSQL.
Копия БД-целевая копия БД на кластере хранения копий БД
Решение было реализовано в виде bash-скрипта, запускаемого на кластера хранения копий БД, и упрощенно, процесс можно представить последовательностью следующих шагов.
Шаг 1.Создается клон БД, используя
CREATE DATABASE ... TEMPLATE = Исходная БД
фрагмент скрипта
SPL
CLONE_DB=$source_db_name'_'$timestamp_label
psql -h $source_host_name -U postgres -Aqt -c "UPDATE pg_database SET datallowconn = FALSE WHERE datname = '$source_db_name'" >>$LOG_FILE 2>&1
psql -h $source_host_name -U postgres -Aqt -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='$source_db_name'" >>$LOG_FILE 2>&1
psql -h $source_host_name -U postgres -Aqt -c "CREATE DATABASE $CLONE_DB TEMPLATE=$source_db_name " >>$LOG_FILE 2>&1
psql -h $source_host_name -U postgres -Aqt -c "UPDATE pg_database SET datallowconn = TRUE WHERE datname = '$source_db_name'" >>$LOG_FILE 2>&1
Шаг 2.Загружается схема клона БД, используя
pg_dump --shema_only --file=$DUMP_FILE ...
фрагмент скрипта
SPL
pg_dump -h $source_host_name -U postgres --schema-only --verbose --file=$DUMP_FILE $source_db_name 2>$SCHEMA_DUMP_LOG_FILE
Шаг 3.Создается пустая БД копия БД, используя
createdb ...
фрагмент скрипта
SPL
createdb $CLONE_DB
Шаг 4. Создается схема клона БД в копии БД, используя
psql ... < $DUMP_FILE
фрагмент скрипта
SPL
TARGET_SCHEMA_DUMP_LOG_FILE=$FILE_LABEL'target.log'
psql -U postgres -d $CLONE_DB < $DUMP_FILE > $TARGET_SCHEMA_DUMP_LOG_FILE 2>&1
Шаг 5.Создание логической репликации.
Создание публикации в клоне БД, используя
CREATE PUBLICATION ... FOR ALL TABLES
фрагмент скрипта
SPL
PUBLICATION_NAME=$CLONE_DB'_pub'
psql -h $source_host_name -U postgres -d $CLONE_DB -c "CREATE PUBLICATION $PUBLICATION_NAME FOR ALL TABLES " >>$LOG_FILE 2>&1
Создание подписки в копии БД, используя
CREATE SUBSCRIPTION ...
фрагмент скрипта
SPL
SUBSCRIPTION_NAME=$CLONE_DB'_sub'
CONNECTION_STR="CREATE SUBSCRIPTION $SUBSCRIPTION_NAME CONNECTION 'host=$source_host_name port=5432 user=postgres dbname=$CLONE_DB' PUBLICATION $PUBLICATION_NAME"
psql -U postgres -d $CLONE_DB -c "$CONNECTION_STR" >>$LOG_FILE 2>&1
Шаг 6.Синхронизация клона БД и копии БД, используя
SELECT count(*) FROM pg_stat_subscription WHERE subname =... AND relid IS NOT NULL
БД считаются синхронизированными, если нет процесса синхронизации между таблицами. Клон БД для работы приложения не используется, что гарантирует идентичность баз.
фрагмент скрипта
SPL
flag=0
while [[ $flag = '0' ]];
do
COUNT_STR="SELECT count(*) FROM pg_stat_subscription WHERE subname ='$SUBSCRIPTION_NAME' AND relid IS NOT NULL "
subscription_process_count=`psql -At -U postgres -d $CLONE_DB -c "$COUNT_STR"`
if [[ $subscription_process_count = '0' ]];
then
break
fi
sleep 60
done
Шаг 6.Удалить логическую репликацию, используя
DROP SUBSCRIPTION...
фрагмент скрипта
SPL
psql -d $CLONE_DB -Aqt -c "DROP SUBSCRIPTION $SUBSCRIPTION_NAME" >> $LOG_FILE 2>&1
DROP PUBLICATION ...
фрагмент скрипта
SPL
psql -h $source_host_name -d $CLONE_DB -Aqt -c "DROP PUBLICATION $PUBLICATION_NAME" >> $LOG_FILE 2>&1
в копии БД и клоне БД соответственно.
Шаг 7.Удалить клон БД, используя
DROP DATABASE ...
фрагмент скрипта
SPL
psql -h $source_host_name -U postgres -Aqt -c "UPDATE pg_database SET datallowconn = FALSE WHERE datname = '$CLONE_DB'" >>$LOG_FILE 2>&1
psql -h $source_host_name -U postgres -Aqt -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='$CLONE_DB'" >>$LOG_FILE 2>&1
psql -h $source_host_name -U postgres -Aqt -c "DROP DATABASE $CLONE_DB " >>$LOG_FILE 2>&1
psql -h $source_host_name -U postgres -Aqt -c "UPDATE pg_database SET datallowconn = TRUE WHERE datname = '$CLONE_DB'" >>$LOG_FILE 2>&1
Результат
Проведено копирование БД между серверами с минимальными издержками на передачу и хранение данных.
===========
Источник:
habr.com
===========
Похожие новости:
- [Тестирование IT-систем, Администрирование баз данных] Типы угроз для базы данных (перевод)
- Обновление PostgreSQL с устранением уязвимостей
- [Oracle, Администрирование баз данных, Хранение данных] Решение проблемы безопасности данных интегрированными средствами Oracle
- [MySQL, IT-инфраструктура, Серверное администрирование, Администрирование баз данных] Как новость про +4 выходных дня уронила нам базу данных
- [MySQL, IT-инфраструктура, *nix, Серверное администрирование, Администрирование баз данных] MySQL 8 Performance Benchmark
- [NoSQL, Администрирование баз данных, Tarantool] Новый релиз — Tarantool 2.7
- [Open source, Java, ERP-системы, CRM-системы, 1С] Как мы сделали программу лояльности для 300 магазинов «У Палыча» на open source iDempiere ERP/CRM
- [Криптография, PostgreSQL, OpenStreetMap, Геоинформационные сервисы] Использование данных OSM для анализа
- Опубликован Kubegres, инструментарий для развёртывания кластера PostgreSQL
- [Python, PostgreSQL, Django, SQL] SQL в DjangoORM
Теги для поиска: #_postgresql, #_administrirovanie_baz_dannyh (Администрирование баз данных), #_postgresql, #_logical_replication, #_postgresql, #_administrirovanie_baz_dannyh (
Администрирование баз данных
)
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Текущее время: 18-Май 07:19
Часовой пояс: UTC + 5
| Автор | Сообщение |
|---|---|
|
news_bot ®
Стаж: 6 лет 3 месяца |
|
|
Постановка задачиОт бизнеса поступила задача — необходимо регулярно сохранять копии отдельных баз данных. Упрощенно говоря — бекапить отдельные базы данных, на случай сверки и потери данных в исходных базах. Первое и самое очевидное решение — pg_dump Достоинства — простота решения. Штатные методы. Все отработано, документации и материалов великое множество. Но, достоинства есть продолжения недостатков. Во первых-объемы дампов. Во вторых-и это самое неприятное, были случаи несовпадения исходной и целевой БД при восстановлении из дампа. В третьих-время, сначала на создание дампа, потом на восстановление БД из дампа. Нужно искать другой путь копирования БД между серверами. Бизнес требовал, задача интересная. Не факт, что решение получилось максимально эффективным и не будет изменено/улучшено. Но как этюд использования возможностей PostgreSQL, может показаться интересным. В результате, для копирования БД между серверами было принято решение использовать механизм логической репликации PostgreSQL. Термины и исходные данныеИсходный кластер -кластер PostgreSQL содержащий БД которую нужно скопировать. Исходная БД — объект копирования, БД на исходном кластере Клон БД — копия исходной БД на исходном кластере Кластер хранения копий БД — отдельный кластер PostgreSQL. Копия БД-целевая копия БД на кластере хранения копий БД Решение было реализовано в виде bash-скрипта, запускаемого на кластера хранения копий БД, и упрощенно, процесс можно представить последовательностью следующих шагов. Шаг 1.Создается клон БД, используя CREATE DATABASE ... TEMPLATE = Исходная БД
фрагмент скриптаSPLCLONE_DB=$source_db_name'_'$timestamp_label
psql -h $source_host_name -U postgres -Aqt -c "UPDATE pg_database SET datallowconn = FALSE WHERE datname = '$source_db_name'" >>$LOG_FILE 2>&1 psql -h $source_host_name -U postgres -Aqt -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='$source_db_name'" >>$LOG_FILE 2>&1 psql -h $source_host_name -U postgres -Aqt -c "CREATE DATABASE $CLONE_DB TEMPLATE=$source_db_name " >>$LOG_FILE 2>&1 psql -h $source_host_name -U postgres -Aqt -c "UPDATE pg_database SET datallowconn = TRUE WHERE datname = '$source_db_name'" >>$LOG_FILE 2>&1 Шаг 2.Загружается схема клона БД, используя pg_dump --shema_only --file=$DUMP_FILE ...
фрагмент скриптаSPLpg_dump -h $source_host_name -U postgres --schema-only --verbose --file=$DUMP_FILE $source_db_name 2>$SCHEMA_DUMP_LOG_FILE
Шаг 3.Создается пустая БД копия БД, используя createdb ...
фрагмент скриптаSPLcreatedb $CLONE_DB
Шаг 4. Создается схема клона БД в копии БД, используя psql ... < $DUMP_FILE
фрагмент скриптаSPLTARGET_SCHEMA_DUMP_LOG_FILE=$FILE_LABEL'target.log'
psql -U postgres -d $CLONE_DB < $DUMP_FILE > $TARGET_SCHEMA_DUMP_LOG_FILE 2>&1 Шаг 5.Создание логической репликации. Создание публикации в клоне БД, используя CREATE PUBLICATION ... FOR ALL TABLES
фрагмент скриптаSPLPUBLICATION_NAME=$CLONE_DB'_pub'
psql -h $source_host_name -U postgres -d $CLONE_DB -c "CREATE PUBLICATION $PUBLICATION_NAME FOR ALL TABLES " >>$LOG_FILE 2>&1 Создание подписки в копии БД, используя CREATE SUBSCRIPTION ...
фрагмент скриптаSPLSUBSCRIPTION_NAME=$CLONE_DB'_sub'
CONNECTION_STR="CREATE SUBSCRIPTION $SUBSCRIPTION_NAME CONNECTION 'host=$source_host_name port=5432 user=postgres dbname=$CLONE_DB' PUBLICATION $PUBLICATION_NAME" psql -U postgres -d $CLONE_DB -c "$CONNECTION_STR" >>$LOG_FILE 2>&1 Шаг 6.Синхронизация клона БД и копии БД, используя SELECT count(*) FROM pg_stat_subscription WHERE subname =... AND relid IS NOT NULL
фрагмент скриптаSPLflag=0
while [[ $flag = '0' ]]; do COUNT_STR="SELECT count(*) FROM pg_stat_subscription WHERE subname ='$SUBSCRIPTION_NAME' AND relid IS NOT NULL " subscription_process_count=`psql -At -U postgres -d $CLONE_DB -c "$COUNT_STR"` if [[ $subscription_process_count = '0' ]]; then break fi sleep 60 done Шаг 6.Удалить логическую репликацию, используя DROP SUBSCRIPTION...
фрагмент скриптаSPLpsql -d $CLONE_DB -Aqt -c "DROP SUBSCRIPTION $SUBSCRIPTION_NAME" >> $LOG_FILE 2>&1
DROP PUBLICATION ...
фрагмент скриптаSPLpsql -h $source_host_name -d $CLONE_DB -Aqt -c "DROP PUBLICATION $PUBLICATION_NAME" >> $LOG_FILE 2>&1
Шаг 7.Удалить клон БД, используя DROP DATABASE ...
фрагмент скриптаSPLpsql -h $source_host_name -U postgres -Aqt -c "UPDATE pg_database SET datallowconn = FALSE WHERE datname = '$CLONE_DB'" >>$LOG_FILE 2>&1
psql -h $source_host_name -U postgres -Aqt -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='$CLONE_DB'" >>$LOG_FILE 2>&1 psql -h $source_host_name -U postgres -Aqt -c "DROP DATABASE $CLONE_DB " >>$LOG_FILE 2>&1 psql -h $source_host_name -U postgres -Aqt -c "UPDATE pg_database SET datallowconn = TRUE WHERE datname = '$CLONE_DB'" >>$LOG_FILE 2>&1 Результат Проведено копирование БД между серверами с минимальными издержками на передачу и хранение данных. =========== Источник: habr.com =========== Похожие новости:
Администрирование баз данных ) |
|
Вы не можете начинать темы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Вы не можете отвечать на сообщения
Вы не можете редактировать свои сообщения
Вы не можете удалять свои сообщения
Вы не можете голосовать в опросах
Вы не можете прикреплять файлы к сообщениям
Вы не можете скачивать файлы
Текущее время: 18-Май 07:19
Часовой пояс: UTC + 5