14.05.2021 13:08
rinace 17 2500 Источник

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


От бизнеса поступила задача — необходимо регулярно сохранять копии отдельных баз данных, расположенных в разных кластерах 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

UPDATE.
Как показала практика для мониторинга процесса синхронизации лучше использовать запрос к каталогу pg_subscription_rel 
SELECT count(*) FROM pg_subscription_rel ; 
SELECT count(*) FROM pg_subscription_rel WHERE srsubstate ='r' ;
Первый запрос выдает общее количество таблиц в подписке которые будут синхронизированы. Второй запрос выдает количество синхронизированных таблиц.

Шаг 7


Удалить логическую репликацию

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

Шаг 8


Удалить клон БД

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

Результат


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

За рамками данного этюда — задача удаления старых БД на сервере хранения копий по заданному параметру глубины хранения и логирование процесса.