Репликация базы данных 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

Успехов.