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

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

В результате репликации эти базы данных содержат абсолютно идентичные данные. Это нужно например для того, чтобы обеспечить отказоустойчивость системы (в случае падения первого сервера баз данных в работу вступает второй), или чтобы осуществить балансировку нагрузки - разных клиентов могут обслуживать разные сервера.
Для экспериментов будем использовать дистрибутив линукса CentOS 5.3, хотя это не принципиально. Версия PostgreSQL, используемая в статье: 8.4.7. Она еще не умеет сама реплицироваться - это фича 9-й версии постгреса.

Для репликации нужно как минимум два сервера баз данных, поэтому готовим два одинаковых сервера с базой данных 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

Ну что, теперь пора приступить к самой интересной части работы. Перед тем, как продолжить, убедимся, что у нас одновременно работают два сервера с работающим сервером баз данных 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

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

------------------------------------------------------------------------------
-- Sample Data
------------------------------------------------------------------------------
insert into t2(mydata, intval) values('sdsdsdsds', 102);

------------------------------------------------------------------------------
-- Sample Symmetric Configuration
------------------------------------------------------------------------------
--
-- 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" 

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

Далее. Переходим на второй сервер и делаем настройки там. Создаем такой файл в каталоге 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 для закачки инфы в таблицу на корневом сервере.
Для моделирования реальной нагрузки он будет производить тысячи INSERT и UPDATE в секунду, в цикле.

#!/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 для данной строки таблицы будет отставать от того, что печатается скриптом, работающим на первом сервере.