Этюд — копирование баз данных PostgreSQL без использования pg_dump

Постановка задачи

От бизнеса поступила задача — необходимо регулярно сохранять копии отдельных баз данных. Упрощенно говоря — бекапить отдельные базы данных, на случай сверки и потери данных в исходных базах.

Первое и самое очевидное решение — 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>&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>&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

Результат

Проведено копирование БД между серверами с минимальными издержками на передачу и хранение данных.