Репликация базы данных PostgreSQL на основе SymmetricDS

В этой статье я расскажу, как настроить репликацию баз данных для PostgreSQL. Для экспериментов будем использовать дистрибутив линукса CentOS 5.3, хотя это не принципиально. будем использовать версию PostgreSQL 8.4.7 и SymmetricDS-2.2.2.

Что такое репликация?

По сути, это механизм автоматической синхронизации содержимого баз данных, работающих на разных серверах. В результате репликации эти базы данных содержат абсолютно идентичные данные. Это нужно например для того, чтобы обеспечить отказоустойчивость системы (в случае падения первого сервера баз данных в работу вступает второй), или чтобы осуществить балансировку нагрузки — разных клиентов могут обслуживать разные сервера.

Для репликации нужно как минимум два сервера баз данных, поэтому готовим два одинаковых сервера с базой данных PostgreSQL на каждом. У первого будет IP адрес 10.0.2.20, у второго — 10.0.2.21, у обоих гейтвей 10.0.2.2.
Можно обойтись виртуальной машиной, например VirtualBox, создать в ней два виртуальных сервера и запустить их на своем собственном компе.

В приведенных командах первым символом будет стоять знак # либо $, эти знаки означают, что команда запускается из-под root или из-под обычного пользователя, соответственно.
Итак, какие действия нужно предпринять:

Начнем настройку

Включаем сервис postgresql, если он еще не включен:
# chkconfig --level 3 postgresql on
# service postgresql start


Теперь нам нужно создать базу данных. Создать ее «в лоб» не получится:
# createdb mytest
psql: FATAL: user "root" does not exist


тут нужно создать роль в постгресе для линуксовского юзера. Создадим суперюзера в постгресе с логином «sa» и линуксовского пользователя с таким же именем, пароль будет тот же, «sa».
# adduser -m sa

# su - postgres
$ createuser -d -s -P sa

теперь можно создать таблицу «test», инициировав команду из-под юзера «sa»:
# su - sa
$ createdb mytest

теперь можно пользоваться утилитой psql:
psql mytest

Выход из этой утилиты осуществляется набором двух символов \q

Установка SymmetricDS

Ну что, теперь пора приступить к самой интересной части работы. Перед тем, как продолжить, убедимся, что у нас одновременно работают два сервера с работающим сервером баз данных postgreSQL на каждом.
Сервера должны пинговать друг друга, потому что SymmetricDS использует HTTP протокол для синхронизации. Порты 8080 и 9090 на серверах не должны быть заблокированы фаерволлом.

ОК. Для работы SymmetricDS нужен интерпретатор java и сама JRE 1.6, поэтому устанавливаем их на сервера, если это еще не было сделано:
скачиваем с сайта java.com инсталлятор jre-6u24-linux, устанавливаем его (достаточно просто запустить этот файл),
настраиваем символическую ссылку (если она по какой-то причине не создалась):
# ln -s /usr/java/latest/bin/java /usr/bin/java

и прописываем в командной строке путь к классам — без них SymmetricDS упадет с ошибкой что класс не найден.
$ CLASSPATH=/usr/java/latest/lib; export CLASSPATH

Чтобы sym (главный исполнимый файл Symmetric DS) работал, также необходимо указать серверу БД, чтобы он слушал на внешнем сетевом интерфейсе (а не только на localhost) и чтобы он пускал юниксовских юзеров к себе. Это делается так, на каждом из наших двух серверов:
1) раскомментировать эту строку в /var/lib/pgsql/data/postgresql.conf:
listen_addresses = '*'

2) указать метод trust для всех соединений в /var/lib/pgsql/data/pg_hba.conf:
local all all trust
host all all 127.0.0.1/32 trust
host all all ::1/128 trust


3) Для поддержки переменных сессии в файл /var/lib/pgsql/data/postgresql.conf добавить строчку:
custom_variable_classes = 'symmetric'

4) перезапустить сервер БД:
# service postgresql restart

Проверить, что это работает, можно так:
$ psql mytest -U sa -h localhost

Такого сообщения об ошибке быть не должно: psql: FATAL: Ident authentication failed for user «sa».
Вместо этого должен открыться psql, как обычно.

Теперь качаем собственно SymmetricDS-2.2.2 и распаковываем на оба сервера:
$ unzip symmetric-ds-2.2.2-server.zip
$ cd symmetric-ds-2.2.2/samples


Настройка SymmetricDS

Редактируем файлы root.properties и client.properties, которые лежат в этом каталоге samples. В них нужно раскомментировать строчки для postgresql, закомментировав или удалив другие варианты, и указать логин и пароль к базе (у нас это sa).
root.properties нужен только на первой ноде, client.properties — только на второй.
Начнем по-порядку.

Настройка SymmetricDS на первом сервере

Формиреум такой файл root.properties:

root.properties
# The class name for the JDBC Driver
db.driver=org.postgresql.Driver

# The JDBC URL used to connect to the database
db.url=jdbc:postgresql://localhost/mytest

# The user to login as who can create and update tables
db.user=sa
# The password for the user to login as
db.password=sa
registration.url=http://10.0.2.20:8080/sync
sync.url=http://10.0.2.20:8080/sync
# Do not change these for running the demo
group.id=corp
external.id=00000
# Don't muddy the waters with purge logging
job.purge.period.time.ms=7200000


Создаем начальную базу данных на рутовой ноде:
$ psql postgres -c "CREATE database mytest;"

Для целей этого простого примера у нас в базе данных будет одна таблица t2, которая будет синхронизироваться (реплицироваться) в обе стороны. Создадим эту таблицу.
$ ../bin/sym -p root.properties --run-ddl create_sample.xml

здесь используется файл create_sample.xml
<?xml version="1.0"?>
<!DOCTYPE database SYSTEM "http://db.apache.org/torque/dtd/database.dtd">

<database name="mytest">
  <table name="t2">
     <column name="id" type="INTEGER" required="true" primaryKey="true" autoIncrement="true"/>
     <column name="mydata" type="VARCHAR" size="64" required="false" />
     <column name="intval" type="DECIMAL" size="10,2" required="false" />
  </table>
</database>


Также надо в базе данных создать обработчики plpgsql — запускаем этот скрипт:
$ ./create_func.sh

create_func.sh
#!/bin/sh
psql mytest -U sa -h localhost -c "CREATE FUNCTION plpgsql_call_handler() RETURNS language_handler AS '\$libdir/plpgsql' LANGUAGE C;"
psql mytest -U sa -h localhost -c "CREATE FUNCTION plpgsql_validator(oid) RETURNS void AS '\$libdir/plpgsql' LANGUAGE C;"
psql mytest -U sa -h localhost -c "CREATE TRUSTED PROCEDURAL LANGUAGE plpgsql HANDLER plpgsql_call_handler VALIDATOR plpgsql_validator;"


теперь можно инициализировать систему SymmetricDS на рутовой ноде:

$ ../bin/sym -p root.properties --auto-create

Вывод будет примерно такой:
[sa@CENTOS1 samples]$ ../bin/sym -p root.properties --auto-create
Log output will be written to ../logs/symmetric.log
SymmetricLauncher - Option: name=properties, value={root.properties}
SymmetricLauncher - Option: name=auto-create, value={}
PlatformFactory - The name/version pair returned for the database, PostgreSQL8,
was not mapped to a known database platform. Defaulting to using just the database type of PostgreSql
PostgreSqlDbDialect - The DbDialect being used is org.jumpmind.symmetric.db.postgresql.PostgreSqlDbDialect
ConfigurationService - Initializing SymmetricDS database.
PostgreSqlDbDialect - There are SymmetricDS tables missing. They will be auto created.
PostgreSqlDbDialect - Starting auto update of SymmetricDS tables.
PostgreSqlDbDialect - Just installed sym_triggers_disabled
PostgreSqlDbDialect - Just installed sym_node_disabled
PostgreSqlDbDialect - Just installed sym_fn_sym_largeobject
ConfigurationService - Auto-configuring config channel.
ConfigurationService - Auto-configuring reload channel.
ConfigurationService - Done initializing SymmetricDS database.


Всё получилось. Этот скрипт создал целых 26 таблиц для своей работы. Эти таблицы начинаются на sym_ и посмотреть их список можно, введя команду
$ psql mytest -c "select tablename from pg_tables where tablename like 'sym%';"

Далее. Конфигурим symmetricDS путем заполнения только что созданных таблиц
$ ../bin/sym -p root.properties --run-sql insert_sample.sql

Данные для заполнения такие:

insert_sample.sql
insert into t2(mydata, intval) values('sdsdsdsds', 102);

--
-- Nodes
--
insert into sym_node_group (node_group_id, description)
values ('corp', 'Central Office');
insert into sym_node_group (node_group_id, description)
values ('store', 'Store');

insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action)
values ('store', 'corp', 'P');
insert into sym_node_group_link (source_node_group_id, target_node_group_id, data_event_action)
values ('corp', 'store', 'W');

insert into sym_node (node_id, node_group_id, external_id, sync_enabled)
values ('00000', 'corp', '00000', 1);
insert into sym_node_identity values ('00000');

--
-- Channels
--
insert into sym_channel
(channel_id, processing_order, max_batch_size, enabled, description)
values('channel_t2', 1, 100000, 1, 't2 data from register and back office');

--
-- Triggers
--
insert into sym_trigger
(trigger_id,source_table_name,channel_id,last_update_time,create_time)
values('trigger_t2','t2','channel_t2',current_timestamp,current_timestamp);

-- Example of a "dead" trigger, which is used to only sync the table during initial load
insert into sym_trigger
(trigger_id,source_table_name,channel_id, sync_on_insert, sync_on_update, sync_on_delete, last_update_time,create_time)
values('t2_dead','t2','channel_t2',0,0,0,current_timestamp,current_timestamp);

--
-- Routers
--

-- In this example, both routers pass everything all the time.

insert into sym_router
(router_id,source_node_group_id,target_node_group_id,create_time,last_update_time)
values('corp_store_identity', 'corp', 'store', current_timestamp, current_timestamp);

insert into sym_router
(router_id,source_node_group_id,target_node_group_id,create_time,last_update_time)
values('store_corp_identity', 'store', 'corp', current_timestamp, current_timestamp);

--
-- Trigger Router Links
--

insert into sym_trigger_router
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('trigger_t2','corp_store_identity',100,current_timestamp,current_timestamp);

insert into sym_trigger_router
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('trigger_t2','store_corp_identity', 200, current_timestamp, current_timestamp);

-- Example of a "dead" trigger, which is used to only sync the table during initial load
insert into sym_trigger_router
(trigger_id,router_id,initial_load_order,last_update_time,create_time)
values('trouter_dead','corp_store_identity', 300, current_timestamp, current_timestamp);


Что означает вышеприведенный код?

По сути, мы создаем конфигурацию для SymmetricDS — триггеры на изменение значений в полях таблиц. При каждом изменении таблицы source_table_name (в нашем случае это таблица t2), перечисленной в конфигурации SymmetricDS в таблице sym_trigger, срабатывает код SymmetricDS. Он определяет, какие маршруты ассоциированы с данным конкретым триггером (см. sym_trigger_router). Если указан только один маршрут, например, от первого сервера ко второму, то изменившиеся данные на втором сервере не будут доставлены на первый. В нашем случае имеется два канала, в обе стороны: от первого сервера ко второму и от второго к первому (см. sym_router).

Запускаем на первом сервере собственно сервер репликации. Эта команда не отдаст управление, будет работать и валить логи на экран.
$ ../bin/sym -p root.properties --port 8080 --server

Разрешаем на первом сервере регистрацию (запускаем из другого окна терминала)
$ ../bin/sym -p root.properties --open-registration "store,1"

Всё. Настройка сервера репликации на рутовой ноде завершена.

Настройка SymmetricDS на втором сервере

Переходим на второй сервер и делаем настройки там. Создаем такой файл в каталоге symmetric-ds-2.2.2/samples:

client.properties
# The class name for the JDBC Driver
db.driver=org.postgresql.Driver

# The JDBC URL used to connect to the database
db.url=jdbc:postgresql://localhost/mytest

# The user to login as who can create and update tables
db.user=sa
# The password for the user to login as
db.password=sa
# The HTTP URL of the root node to contact for registration
registration.url=http://10.0.2.20:8080/sync
# Do not change these for running the demo
group.id=store
external.id=1
job.routing.period.time.ms=2000
# This is how often the push job will be run.
job.push.period.time.ms=5000
# This is how often the pull job will be run.
job.pull.period.time.ms=5000


Создаем на втором сервере такую же базу данных:
$ psql postgres -c "CREATE database mytest;"

и создаем в ней такие же триггеры и функции:
$ ../bin/sym -p client.properties --run-ddl create_sample.xml
$ ./create_func.sh


Запускаем на втором сервере процесс репликации.
$ ../bin/sym -p client.properties --port 9090 --server

На данном этапе у нас базы данных еще не реплицированы, репликация не началась. Оценим это. зайдем на первый сервер и наберем:
$ psql mytest -c "select * from t2;"

Эта команда выведет нам содержимое таблицы на корневой ноде, там будет одна запись, созданная скриптом insert_sample.sql, который мы запускали только на рутовой ноде.
Перейдем на второй сервер, наберем эту же команду там. Таблица будет пустая.

Теперь самое основное: забрасываем на клиент начальный набор данных, вводя эту команду с первого сервера, и таким образом начинаем репликацию!
$ ../bin/sym -p root.properties --reload-node 1

Через пару секунд у нас на втором сервере содержимое таблицы t2 будет такое же! Ура!

Если у вас что-то пошло не так, например, вы указали неправильный IP адрес сервера для регистрации, то проще всего убить нашу базу данных (вместе со всеми 26 таблицами SymmetricDS) и начать всё с начала. Для этого надо вызвать команду на обоих серверах:
$ psql postgres -c "drop databаse mytest;"

и начать заново, с этой строки (ищи ее выше:)
$ psql postgres -c "CREATE database mytest;"

Моделирование реальной нагрузки

Напишем скрипт на perl для закачки инфы в таблицу на корневом сервере.
Для моделирования реальной нагрузки он будет производить сто UPDATE в секунду, в цикле.

test.pl
#!/usr/bin/perl

use DBI;
my $dbh = DBI->connect("DBI:Pg:dbname=mytest", "sa", "sa");

for(my $idval = 5000; $idval < 9000; $idval++)
{
  $dbh->do("insert into t2 (id,mydata, intval) values ($idval,'some data', $idval)");
}

my $newval = 3434;
my $interval = 0;
for(my $j = 0; $j < 100000; $j++)
{
 for(my $idval = 5000; $idval < 9000; $idval++)
 {
    $newval++;
    $interval++;
    $dbh->do("UPDATE t2 set intval=$newval where id=$idval");
    printf ("UPD[%04d] id=%d val=%d\r", $j, $idval, $newval);
    if($interval == 100)
    {
         $interval = 0;
        `ping localhost -w 1 > /dev/null 2>&1`;
    }
 }
}
$dbh->disconnect;

print "\nOK\n";


Запустив этот скрипт на root node, таблица будет постоянно меняться. На втором сервере мы сможем видеть, как изменения присылаются туда, с некоторой задержкой, например, так:
$ psql mytest -c "select * from t2 where id=5000;"

Значение intval для данной строки таблицы будет отставать от того, что печатается скриптом, работающим на первом сервере.

Проблемы при высокой нагрузке


В данном примере мы, на самом деле, не учитывали проблемы высокой нагрузки на сервер баз данных. При осуществлении репликации SymmetricDS ведет учет всех данных, отправляемых на удаленную базу данных, в своей таблице sym_data. Эта таблица содержит отдельную запись для каждого insert/update, который выполняется на корневой базе данных. Таким образом, при высоком траффике (скажем, сотни инсертов в секунду) размер таблицы sym_data начинает расти. Рост таблицы приводит к увеличению накладных расходов на осуществление репликации.

Чтобы решить эту проблему, необходимо настроить Purge Service в Symmetric DS. Ключевые параметры этого сервиса, которые необходимо указать в файле root.properties, такие:

start.purge.job
job.purge.period.time.ms (как часто будет запускаться Purge Job)
purge.retention.minutes (как долго будет храниться история уже отправленных на вторую базу изменений)

Последние два параметра напрямую влияют на максимальное количество записей в таблице sym_data: оно будет равно purge.retention.minutes * число insert/update в минуту.

Сервис PurgeService должен автоматически стартовать наряду с другими сервисами, такими как Push и Poll (которые обеспечивают синхронизацию данных в базах). Но, если по какой-то причине Purge Service не стартует, это приводит к черезмерному росту служебных таблиц SymmetricDS, поэтому требуется запускать Purge Service вручную, желательно в периоды низкой нагрузки на сервер БД:

../bin/sym –p root.properties –X

Успехов.

Средняя зарплата в IT

113 000 ₽/мес.
Средняя зарплата по всем IT-специализациям на основании 5 503 анкет, за 2-ое пол. 2020 года Узнать свою зарплату
AdBlock похитил этот баннер, но баннеры не зубы — отрастут

Подробнее
Реклама

Комментарии 29

    0
    В принципе интересно, но как добавить репликацию к уже имеющейся и пусть не сильно большой, но уже прилично весящей базе? Экспортировать текущую, создавать новую реплицируемую и импортировать данные туда? Или можно добавить репликацию к имеющейся рабочей базе «на лету» (естественно с требующейся по ходе дела остановкой, если нужно).
      0
      Да, можно добавить репликацию к имеющейся базе. Для этого необходимо модифицировать код файла insert_sample.sql, создав в таблицах Symmetric DS (начинающизся на sym_) нужные записи.
      Особенность Symmetric DS такова, что он может реплицировать не всю базу подряд, а только некоторые ее таблицы. Например, у нас есть branch office и менеджер по продажам вносит в локальную базу нового клиента. Эта информация о клиенте синхронизируется с главной базой в headquarters и становится после этого доступна для остальных branch offices.
      +2
      И еще, рассматривали, как ведут себя базы в случае потери соединения? Скажем, у меня две БД в разных городах — интернет есть всегда, но вдруг чего, разное бывает — пропадает. В таких случаях, если у нас мультимастер репликация, надо будет решать конфликты по id'ам insert'ов и update'ам. Хотелось бы еще несколько слов об этом.
        0
        Из тех систем, с которыми я сталкивался проблема решается двумя способами — либо каждая база данных имеет свой непересекающийся диапазон идентификаторов, либо первичный идентификатор составной — ИД записи + ИД базы данных. Можно еще позаморачиваться с UUID в качестве идентификаторов, но это уже совсем для гурманов.
          0
          Да-да, у меня, к примеру, две ноды и больше не предвидится, id'ы идут чёт-нечёт.
            0
            Проблема не только в id.
            А если так? Что будет на выходе?
        +2
        Каким образом SymmetricDS разрешает конфликты?
          +1
          А в чем преимущество над, например, slony?
          Если я понимаю, разрешения конфликтов при доступе в обе нет — это master-slave репликация только?
            0
            В нашем проекте используется master-slave модель, когда данные пишутся только в master базу и реплицируются на находящуюся в hot standby вторичную базу.

            Отвечу сразу на вопрос Lolka — при временном обрыве соединения между базами изменения в master базе накапливаются в служебных таблицах SymmetricDS, и при возобновлении соединения они загоняются в secondary db.

            Вопрос об разрешении конфликтов при мультимастерной конфигурации, думаю, это тема отдельного поста. Пока же скажу, что симметрик умеет изменять запрос INSERT на UPDATE в случае, если в destination db такая запись уже есть.
              0
              Если вы используете только master-slave, то чем это тогда лучше встроенной в постгрес streaming replication?
            +1
            К сожалению, полноценной мастер-мастер репликации не существует в природе. См. CAP-теорему Брюэра — это прямо следует оттуда.

            Как поведет себя система в такой ситуации:

            1> BEGIN;
            2> BEGIN;
            1> UPDATE…
            2> UPDATE той же строки с другими данными
            1> COMMIT;
            2> COMMIT;
              0
              Да, но можно решать конфликты одним из способов. Например — кто последний, тот и прав. Такое работает, не для всех данных, конечно же.
                0
                Это очень важно как он будет себя вести.
                Например, rubyrep, так же позионирующийся как мастер-мастер репликатор, тупо падает в этой ситуации. А это, согласитесь, обозначает полную неприменимость.

                Давно смотрел на М-М решения, но пока не видел ничего достойного.
                  0
                  тоже мониторим М+М решения. пока ничего не нашли работающего.
                  =(
                    +1
                    У меня работает уже второй год Bucardo в режиме асинхронной М-М для двух серверов в разных городах. С падениями канала справляется. Конфликты решаются по методу latest. Всё работает без сбоев. «Трафик» по запросам правда совсем маленький — до 50k запросов (update, insert, delete) в сутки.
              +1
              БД более 300G. продакшен. работаем на master-slave реплике (hot_standby WAL) + pgpool для отказоустойчивости. полет нормальный. отставание практически незаметное. pgpool допилен немного скриптами для грамотного переключения и управления нодами.

              так же есть балансировка нагрузки. на slave летит 99% SELECT.
                +1
                Какая у вас версия PG?

                Как осуществляется переключение при выходе из строя мастера?
                  +1
                  схема такая.

                  pgpool. 2 сервера под HA.

                  pgcluster. 2 сервера master-slave. все запросы идут через pgpool. он же переключает master-slave.

                  есть скрипты ручной/автоматической синхронизации мастера со слейвами.

                  на тестовом стенде делали 1 мастер 2 слейва. все прекрасно синкается, переключается, обрабатывается. отставания нод практически нет.

                  работает все под серверами ubuntu + pgpoolIII ( 2 проца. 2Г памяти)+ pgbonucer+pg9.0.4 (8 проц. 40Г память)

                  нагрузка порядка 100-200 коннектов на PG. 1000-1500 на бонусерах и пуле в сек.

                  п.с. в принципе есть дока… могу куда-нить выложить… наверное… =)
                    +1
                    Интересно. Выложите :)

                    Как пгпул переключает слейвы? Не нужно как-то явно говорить, что такой-то слейв теперь стал мастером?
                    Во всяком случае в слоне так — и он просто запрещает запись на слейв сервер.
                      +1
                      pgpool мониторит сервера и при отказе запускает скрипт, который и возвращает ему мастера.

                        +1
                        Мы поостереглись делать автоматическое переключение мастера — т.к. в случае временных перебоев или еще каких сетевых проблем, начнется переконфигурация кластера.
                          0
                          с чего она начнется-то?

                          1. падает мастер.
                          2. pgpool делает slave мастером. в случае нескольких слейвов запускается синхронизация с новым мастером.

                          даже если старый мастер поднимется он не включиться в текущую схему. его можно только принудительно сделать слейвом и так же принудительно включить в кластер.

                          где-же тут конфликты?
                            0
                            Представим, что где-то возник таймаут — например, за счет перебоев в сети, скачка нагрузки, где-то своп подкачался и пр. — причин может быть масса.
                            После этого скрипт считает, что мастер пропал — тут же все переключается на слейв. Затем мастер появился, но работать уже не может — т.к. он рассинхронизирован. Таким образом из рабочей конфигурации вы потеряли 1 сервер БД. Конфигурация ослабла. Если будет скачок нагрузки, то есть риск вылета еще одного сервера таким же макаром, а дальше все будет «складываться» как домино.
                              0
                              надо понимать, что это все-таки псевдо-кластер. да. с большим отказом, но есть узкие места.
                              =)

                              при переходе на нового мастера, слейвы должны будут посинкаться с новым мастером. старый мастер стать слейвом и тоже посинкаться. в общем есть там слабые места. есть. не спорю, но это лучше чем вообще ничего и если надо обеспечить 24Хх7 то это хоть что-то при отказах, а не тупое «БД не доступна».
                                0
                                момент переключения очень слабое место в таком конфиге. да. но пока нет ничего лучше.
                          0
                          все в автоматическом режиме. сиди, кури…
                          =)
                            0
                            Если это все еще развалится в автоматическом режиме :)
                              0
                              это с чего бы?
                    0
                    хорошая статья спасибо
                    позвольте поумничать
                    «Сервера должны пинговать друг друга, потому что SymmetricDS использует HTTP протокол для синхронизации. „
                    пинг это ICMP протокол, а HTTP — это другой протокол.
                    можно сделать чтобы сервера не пингались, а HTTP работает =)
                    [/умничать]

                    Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                    Самое читаемое