Прежде чем перейти к статье, хочу вам представить, экономическую онлайн игру Brave Knights, в которой вы можете играть и зарабатывать. Регистируйтесь, играйте и зарабатывайте!
Постановка задачи
От бизнеса поступила задача — необходимо регулярно сохранять копии отдельных баз данных. Упрощенно говоря — бекапить отдельные базы данных, на случай сверки и потери данных в исходных базах.Первое и самое очевидное решение — pg_dump
Достоинства — простота решения. Штатные методы. Все отработано, документации и материалов великое множество.
Но, достоинства есть продолжения недостатков.
Во первых-объемы дампов.
Во вторых-и это самое неприятное, были случаи несовпадения исходной и целевой БД при восстановлении из дампа.
В третьих-время, сначала на создание дампа, потом на восстановление БД из дампа.
Нужно искать другой путь копирования БД между серверами. Бизнес требовал, задача интересная.
Не факт, что решение получилось максимально эффективным и не будет изменено/улучшено. Но как этюд использования возможностей PostgreSQL, может показаться интересным.
В результате анализа и выбора вариантов замены pg_dump, для копирования БД между серверами, возникла идея — использовать механизм логической репликации PostgreSQL.
Термины и исходные данные
Исходный кластер -кластер PostgreSQL содержащий БД которую нужно скопировать.Исходная БД — объект копирования, БД на исходном кластере
Клон БД — копия исходной БД на исходном кластере
Кластер хранения копий БД — отдельный кластер PostgreSQL.
Копия БД-целевая копия БД на кластере хранения копий БД
Решение было реализовано в виде bash-скрипта, запускаемого на кластера хранения копий БД, и упрощенно, процесс можно представить последовательностью следующих шагов.
Шаг 1.
Создается клон БД, используяCREATE DATABASE ... TEMPLATE = Исходная БД
фрагмент скрипта
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 ...
фрагмент скрипта
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=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...
фрагмент скрипта
psql -d $CLONE_DB -Aqt -c "DROP SUBSCRIPTION $SUBSCRIPTION_NAME" >> $LOG_FILE 2>&1DROP 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>&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Результат
Проведено копирование БД между серверами с минимальными издержками на передачу и хранение данных.
