Постановка задачи
От бизнеса поступила задача необходимо регулярно сохранять копии отдельных баз данных. Упрощенно говоря бекапить отдельные базы данных, на случай сверки и потери данных в исходных базах.Первое и самое очевидное решение pg_dump
Достоинства простота решения. Штатные методы. Все отработано, документации и материалов великое множество.
Но, достоинства есть продолжения недостатков.
Во первых-объемы дампов.
Во вторых-и это самое неприятное, были случаи несовпадения исходной и целевой БД при восстановлении из дампа.
В третьих-время, сначала на создание дампа, потом на восстановление БД из дампа.
Нужно искать другой путь копирования БД между серверами. Бизнес требовал, задача интересная.
Не факт, что решение получилось максимально эффективным и не будет изменено/улучшено. Но как этюд использования возможностей PostgreSQL, может показаться интересным.
В результате, для копирования БД между серверами было принято решение использовать механизм логической репликации PostgreSQL.
Термины и исходные данные
Исходный кластер -кластер PostgreSQL содержащий БД которую нужно скопировать.Исходная БД объект копирования, БД на исходном кластере
Клон БД копия исходной БД на исходном кластере
Кластер хранения копий БД отдельный кластер PostgreSQL.
Копия БД-целевая копия БД на кластере хранения копий БД
Решение было реализовано в виде bash-скрипта, запускаемого на кластера хранения копий БД, и упрощенно, процесс можно представить последовательностью следующих шагов.
Шаг 1.
Создается клон БД, используя
CREATE DATABASE ... TEMPLATE = Исходная БД
фрагмент скрипта
CLONE_DB=$source_db_name'_'$timestamp_labelpsql -h $source_host_name -U postgres -Aqt -c "UPDATE pg_database SET datallowconn = FALSE WHERE datname = '$source_db_name'" >>$LOG_FILE 2>&1psql -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>&1psql -h $source_host_name -U postgres -Aqt -c "CREATE DATABASE $CLONE_DB TEMPLATE=$source_db_name " >>$LOG_FILE 2>&1psql -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 ...
фрагмент скрипта
pg_dump -h $source_host_name -U postgres --schema-only --verbose --file=$DUMP_FILE $source_db_name 2>$SCHEMA_DUMP_LOG_FILE
Шаг 3.
Создается пустая БД копия БД, используя
createdb ...
фрагмент скрипта
createdb $CLONE_DB
Шаг 4.
Создается схема клона БД в копии БД, используя
psql ... < $DUMP_FILE
фрагмент скрипта
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
фрагмент скрипта
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 ...
фрагмент скрипта
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
БД считаются синхронизированными, если нет процесса синхронизации
между таблицами. Клон БД для работы приложения не
используется, что гарантирует идентичность баз.
фрагмент скрипта
flag=0while [[ $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 60done
Шаг 6.
Удалить логическую репликацию, используя
DROP SUBSCRIPTION...
фрагмент скрипта
psql -d $CLONE_DB -Aqt -c "DROP SUBSCRIPTION $SUBSCRIPTION_NAME" >> $LOG_FILE 2>&1
DROP PUBLICATION ...
фрагмент скрипта
в копии БД и клоне БД соответственно.
psql -h $source_host_name -d $CLONE_DB -Aqt -c "DROP PUBLICATION $PUBLICATION_NAME" >> $LOG_FILE 2>&1
Шаг 7.
Удалить клон БД, используя
DROP DATABASE ...
фрагмент скрипта
psql -h $source_host_name -U postgres -Aqt -c "UPDATE pg_database SET datallowconn = FALSE WHERE datname = '$CLONE_DB'" >>$LOG_FILE 2>&1psql -h $source_host_name -U postgres -Aqt -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE datname='$CLONE_DB'" >>$LOG_FILE 2>&1psql -h $source_host_name -U postgres -Aqt -c "DROP DATABASE $CLONE_DB " >>$LOG_FILE 2>&1psql -h $source_host_name -U postgres -Aqt -c "UPDATE pg_database SET datallowconn = TRUE WHERE datname = '$CLONE_DB'" >>$LOG_FILE 2>&1
Результат
Проведено копирование БД между серверами с минимальными издержками на передачу и хранение данных.
