SqlSyncによるデータベースの同期化
Ubuntu、Fedora、openSUSEの標準リポジトリにはSqlSyncパッケージがない。そこで、本稿ではバージョン1.0.0-rc1を使い、64ビットのFedora 8マシンでソースからビルドすることにした。プロジェクトのfreshmeatページにもホームページにも書かれていないが、SqlSyncはデータベースへのアクセスにODBCを使う。したがって、unixODBC開発パッケージがインストールされていないと、ビルドは成功しない。
SqlSyncはautotoolsによる自動ビルドを行わず、Makefileを使うので、ビルドに失敗したときは、Makefile.logを見れば失敗理由がわかる。筆者は、-fPIC 位置独立コードオプションなしで共有ライブラリをmakeしようとして、ビルドに失敗したことがある。PICコードの特長はメモリ中のどこにでもロードできることであり、共有ライブラリのコードのコンパイルでこれを使えば、2つのライブラリが同じアドレスを使いたいと主張するとき、一方を別のアドレスに移動できて便利である。また、静的リンクバージョンのビルドでも、やはりリンクに関連して別の問題が発生した。これの解決には、Makefile.defを次のように変更して、makeを実行し、SqlSyncをビルドした。これで、静的にリンクされたsrc/sqlsync_staticツールが得られる。
$ vi Makefile.def - ODBC_STATIC_LIB=`odbc_config --static-libs` -L`odbc_config --lib-prefix` -liconv + ODBC_STATIC_LIB=`odbc_config --static-libs` -L`odbc_config --lib-prefix` -ldl $ make WITH_SHARED=NO WITH_STATIC=YES clean all ...
sqlsyncコマンドで指定する主なオプションには、ソースと宛先のODBCデータソース名（DSN）、比較したいテーブルと列の特定、同期化のためのクエリを表示したいのか実行したいのか、などがある。ODBC DSNとは、データベースや使用ドライバ、その他の設定パラメータを特定するための名前である。クエリ表示なのかクエリ実行（宛先データベースの更新）なのかを指定せずにsqlsyncを実行すると、加えられた変更の要約が表示される。
手始めに、2つのPostgreSQLデータベース、sourcedbとdestinationdbでテストしてみよう。データベースの初期セットアップは下に示すとおりである。sqlsyncを実行する前に、destinationdbにデータベーススキーマがなければならないことに注意。下記の最後のコマンドでは、sourcedbからdestinationdbにスキーマだけがダンプされる。つまり、destinationdbにはテーブルが存在するものの、タプルは存在しない。
$ psql
ben=# create database sourcedb;
CREATE DATABASE
ben=# create database destinationdb;
CREATE DATABASE
ben=# \c sourcedb
sourcedb=# create table customers ( cid serial primary key, name varchar(100) ) ;
sourcedb=# create table info ( id serial primary key, cid int references customers(cid), note text );
sourcedb=# insert into customers values ( default, 'fred' );
sourcedb=# insert into customers values ( default, 'john' );
sourcedb=# insert into customers values ( default, 'peter' );
sourcedb=# insert into info values ( default,
(select cid from customers where name='fred'), 'fred is home' );
sourcedb=# insert into info values ( default,
(select cid from customers where name='john'), 'john is with fred' );
sourcedb=# insert into info values ( default,
(select cid from customers where name='fred'), 'a second note for him' );
sourcedb=# \q
$ pg_dump -c -s sourcedb | psql destinationdb
わかりやすくするために、ODBC DSNをpostgresqlデータベース名と同じにしよう。ODBC DSNのセットアップには、ODBCConfigグラフィカルクライアントを使用できる。以下では、まず、2つのODBC DSNの間にある違いの要約を表示してから、destinationdbをsourcedbと同じ状態にするためのSQLクエリを表示する。クエリの表示には、sqlsync --show-queriesオプションを使う。
$ sqlsync_static --src-dsn sourcedb --dst-dsn destinationdb --show-summary
Connected to dsn_src server sourcedb
Connected to dsn_dst server destinationdb
All available tables will be synchronized
User didn't specify list of tables to synchronize
Checking availability of specified tables
Comparing tables customers
Comparing tables customers completed
[BEGIN] -----------------------------
[SUMMARY] ---------------------------
Table customers
Number of columns 2
Equal rows 0
Different rows 0
Missing rows 3
Additional rows 0
[COLUMNS] ---------------------------
cid| name|
[DIFF] ------------------------------
[MISSING] ---------------------------
1| fred|
2| john|
3| peter|
[ADDITIONAL] ------------------------
[END] -------------------------------
Comparing tables info
Comparing tables info completed
[BEGIN] -----------------------------
[SUMMARY] ---------------------------
Table info
Number of columns 3
Equal rows 0
Different rows 0
Missing rows 3
Additional rows 0
[COLUMNS] ---------------------------
id| cid| note|
[DIFF] ------------------------------
[MISSING] ---------------------------
1| 1| fred is home|
2| 2| john is with fred|
3| 1| a second note for him|
[ADDITIONAL] ------------------------
[END] -------------------------------
Comparing tables sql_parts
Error occured while running SELECT query on source table,
probably generated query is corrupted for sql_parts table
[unixODBC]Error while executing the query (non-fatal);
ERROR: relation "sql_parts" does not exist
Comparing tables sql_parts completed
[BEGIN] -----------------------------
[SUMMARY] ---------------------------
Table sql_parts
...
[END] -------------------------------
$ sqlsync_static --src-dsn sourcedb --dst-dsn destinationdb --show-queries
Connected to dsn_src server sourcedb
Connected to dsn_dst server destinationdb
...
Creating synchronization queries
INSERT INTO "customers" ("cid", "name") VALUES (1, 'fred');
INSERT INTO "customers" ("cid", "name") VALUES (2, 'john');
INSERT INTO "customers" ("cid", "name") VALUES (3, 'peter');
Creating synchronization queries completed
Comparing tables info
Comparing tables info completed
Creating synchronization queries
INSERT INTO "info" ("id", "cid", "note") VALUES (1, 1, 'fred is home');
INSERT INTO "info" ("id", "cid", "note") VALUES (2, 2, 'john is with fred');
INSERT INTO "info" ("id", "cid", "note") VALUES (3, 1, 'a second note for him');
Creating synchronization queries completed
...
--show-queriesオプションを--run-queriesに変えるだけで、いま表示したクエリ群を実行し、宛先データベースをソースデータベースと同じ状態に更新できる。この同期化の実行後に、ソースデータベースと宛先データベースに新しいタプルを追加してみよう。最後のコマンドでは、sqlsyncがソースデータベースの新タプルを宛先データベースに追加し、筆者が宛先データベースに追加しておいた新タプルを削除していることがわかる。
$ sqlsync_static --src-dsn sourcedb --dst-dsn destinationdb --run-queries
Connected to dsn_src server sourcedb
Connected to dsn_dst server destinationdb
...
Creating synchronization queries
Running UPDATE synchronization queries
Running DELETE synchronization queries
Running INSERT synchronization queries
Running synchronization query (0/3)
...
$ sqlsync_static --src-dsn sourcedb --dst-dsn destinationdb --show-queries
Connected to dsn_src server sourcedb
Connected to dsn_dst server destinationdb
...
Creating synchronization queries
Creating synchronization queries completed
Comparing tables info
Comparing tables info completed
Creating synchronization queries
Creating synchronization queries completed
...
$ psql sourcedb
sourcedb=# insert into info values ( default, (select cid from customers where name='john'), 'another' );
sourcedb=# \c destinationdb
destinationdb=# insert into info (cid, note) values ( (select cid from customers where name='peter'), 'this will be gone' );
destinationdb=# \q
$ sqlsync_static --src-dsn sourcedb --dst-dsn destinationdb --show-queries
...
Creating synchronization queries
DELETE FROM "info" WHERE "id"=20;
INSERT INTO "info" ("id", "cid", "note") VALUES (4, 2, 'another');
Creating synchronization queries completed
次に、customersテーブルへの外部キー参照を行う新タプルをinfoテーブルに追加し、それからsqlsyncによる同期化を行ってみよう。
$ psql sourcedb
sourcedb=# insert into customers values ( default, 'luke' );
sourcedb=# insert into info values ( default, (select cid from customers where name='luke'), 'lucky' );
sourcedb=# \q
$ sqlsync_static --src-dsn sourcedb --dst-dsn destinationdb --show-queries
...
INSERT INTO "customers" ("cid", "name") VALUES (4, 'luke');
...
DELETE FROM "info" WHERE "id"=20;
INSERT INTO "info" ("id", "cid", "note") VALUES (4, 2, 'another');
INSERT INTO "info" ("id", "cid", "note") VALUES (5, 4, 'lucky');
$ sqlsync_static --src-dsn sourcedb --dst-dsn destinationdb --run-queries
異種DBMSエンジン間の同期化
異種データベース間の同期化をテストするため、宛先となるMySQLデータベースを作り、これをdestinationdbと呼ぶことにする。ODBC DSNはmysql-destinationdbである。unixODBCからisqlコマンドを使えば、ODBC経由でデータベースに接続できる。この方法は、ODBCアクセス問題のトラブルシューティングにも便利に使える。MySQLデータベーススキーマには、serialデータ型でなく、単なる整数型を使うことにした。次に示すsqlsyncコマンドでは、userオプションとpasswordオプションを使い、MySQLデータベースDSNに接続している。
$ isql mysql-destinationdb user passwd SQL> create table customers ( cid int primary key, name varchar(100) ) ; SQL> create table info ( id int primary key, cid int references customers(cid), note text ); SQL> quit $ sqlsync_static --src-dsn sourcedb \ --dst-dsn mysql-destinationdb \ --dst-user user --dst-password passwd \ --show-queries ... Creating synchronization queries INSERT INTO `customers` (`cid`, `name`) VALUES (1, 'fred'); INSERT INTO `customers` (`cid`, `name`) VALUES (2, 'john'); INSERT INTO `customers` (`cid`, `name`) VALUES (3, 'peter'); INSERT INTO `customers` (`cid`, `name`) VALUES (5, 'luke'); ... INSERT INTO `info` (`id`, `cid`, `note`) VALUES (1, 1, 'fred is home'); INSERT INTO `info` (`id`, `cid`, `note`) VALUES (2, 2, 'john is with fred'); INSERT INTO `info` (`id`, `cid`, `note`) VALUES (3, 1, 'a second note for him'); INSERT INTO `info` (`id`, `cid`, `note`) VALUES (4, 2, 'another'); INSERT INTO `info` (`id`, `cid`, `note`) VALUES (6, 5, 'lucky');
同期化するテーブルと列は、sqlsyncの--tablesオプションで制限できる。たとえば、「--tables customers[name],info[note]」とすれば、nameフィールドとnoteフィールドだけを同期化できる。これをテストするため、infoテーブルにidフィールドとnoteフィールドしかない新しいデータベースを作り、このデータベースに対して下記の同期化を実行した。
特定の列だけを選択的に同期化するという機能は便利だが、現在のところ、テーブル名マッピング（たとえば、infoテーブルで宛先データベースのlimitedinfoテーブルを同期化すること）はできない。ただ、将来、そのような機能がSqlSyncに追加されない理由はない。
$ psql
ben# create database partdestdb
ben# \c partdestdb
partdestdb# create table info ( id serial primary key, note text );
partdestdb# \q
$ sqlsync_static --src-dsn sourcedb --dst-dsn partdestdb --tables info[id,note] --show-queries
Parsing tables and columns passed from command line
Parsing tables and columns completed
Connected to dsn_src server sourcedb
Connected to dsn_dst server partdestdb
Checking availability of specified tables
Comparing tables info
Comparing tables info completed
Creating synchronization queries
INSERT INTO "info" ("id", "note") VALUES (1, 'fred is home');
INSERT INTO "info" ("id", "note") VALUES (2, 'john is with fred');
INSERT INTO "info" ("id", "note") VALUES (3, 'a second note for him');
INSERT INTO "info" ("id", "note") VALUES (4, 'another');
INSERT INTO "info" ("id", "note") VALUES (6, 'lucky');
Creating synchronization queries completed
データベースのバックアップ方法として、SqlSyncは十分に考慮に値する。レプリケーションツールを用意しているデータベースは多いが、SqlSyncでは、別のデータベースサーバを使ってデータベースのバックアップができる。あるデータベースエンジンから別のデータベースエンジンへの移行で、テスト中はそれぞれのデータベースサーバ上にデータベースを置いておきたい場合などに、便利に使用できる。
SqlSyncは便利だが、完璧ではない。宛先データベースに自動的にスキーマをセットアップしてくれる機能などは、あって決して邪魔にならないと思う。
Ben Martinは、ファイルシステムに携わって10数年。現在、PH.D.も取得し、libferris、各種ファイルシステム、検索ソリューションを中心にコンサルティング事業を展開している。
