データベース移行 - SymmetricDS
テーブル名が同じ、さらに派生する新テーブルを作成
org.customers → org.customers # テーブル名が同じ
org.plans → org.plans # テーブル名が同じ
→ mig.contract # 該当条件で派生する新テーブル
customersとplansをコピーし、コピーしたplansからcontractを生成します。
truncate_table.sql
truncate table org.customers truncate table org.plans truncate table mig.contract
insert_migration.sql
#symテーブルを毎回作り直していればdeleteは不要
delete from sym_transform_column;
delete from sym_transform_table;
delete from sym_trigger_router;
delete from sym_trigger;
delete from syu_router;
delete from sym_channel where channel_id in ('channel');
delete from sym_node_group_link;
delete from sym_node_group;
delete from sym_node_host;
delete from sym_node_identity;
delete from sym_node_security;
delete from sym_node;
insert into sym_channel
(channel_id, processing_order, max_batch_size, enabled, description)
values
('to_sqlserver', 1, 100000, 1, 'to_sqlserver channel data');
insert into sym_node_group
(node_group_id)
values
('postgres'),
('sqlserver');
insert into sym_node_group_link
(source_node_group_id, target_node_group_id, data_event_action)
values
('postgres', 'sqlserver', 'W');
insert into sym_trigger
(trigger_id, source_schema_name, source_table_name, channel_id, last_update_time, create_time)
values
('customers', 'org', 'customers', 'to_sqlserver', current_timestamp, current_timestamp),
('plans', 'org', 'plans', 'to_sqlserver', current_timestamp, current_timestamp);
insert into sym_router
(router_id, target_schema_name, target_table_name, source_node_group_id, target_node_group_id, router_type, last_update_time, create_time)
values
('customers_postgres_2_sqlserver', 'org', 'customers', 'postgres', 'sqlserver', 'default', current_timestamp, current_timestamp),
('plans_postgres_2_sqlserver', 'org', 'plans', 'postgres', 'sqlserver', 'default', current_timestamp, current_timestamp);
insert into sym_trigger_router
(trigger_id, router_id, initial_load_order, create_time, last_update_time)
values
('customers', 'customers_postgres_2_sqlserver', 100, current_timestamp, current_timestamp),
('plans', 'plans_postgres_2_sqlserver', 100, current_timestamp, current_timestamp);
insert into sym_node
(node_id, node_group_id, external_id, sync_enabled, sync_url, schema_version, symmetric_version, database_type, database_version, hertbeat_time, timezone_offset, batch_to_send_count, batch_in_error_count, create_at_node_id)
values
('000', 'postgres', '000', null, null, null, null, null, current_timestamp, null, 0, 0, '000'),
('001', 'sqlserver', '001', null, null, null, null, null, current_timestamp, null, 0, 0, '000');
insert into sym_node_security
(node_id, node_password, registration_enabled, registration_time, initial_load_enabled, initial_load_time, created_at_node_id)
values
('000', '5d1c92bbache2edb9e1ca5dbb0e481', 0, current_timestamp, 0, current_timestamp, '000'),
('001', '5d1c92bbache2edb9e1ca5dbb0e481', 1, null, 1, null, '000');
insert into sym_node_identity
values
('000');
# 異なるテーブル名の構成からの変更あり
insert into sym_transform_table
(transform_id, source_node_group_id, target_node_group_id, transform_point,
source_schema_name, source_table_name,
target_schema_name, target_table_name,
delete_action, transform_order, column_policy, update_first,
last_update_by, last_update_time, create_time)
values
('plans_to_plans', 'postgres', 'sqlserver', 'LOAD',
'org', 'plans',
'org', 'plans',
'DEL_ROW', 1, 'IMPLIED', 1,
'sample' current_timestamp, current_timestamp),
('plans_to_contract','postgres', 'sqlserver', 'LOAD',
'org', 'plans',
'mig', 'constract',
'DEL_ROW', 2, 'SPECIFIED', 1,
'sample' current_timestamp, current_timestamp);
insert into sym_transform_column
(transform_id, include_on, source_column_name, target_column_name, pk,
transform_type, transform_expression, transform_order,
last_update_time, last_update_by, create_time)
values
('plans_to_contract', '*', '', 'id', 1,
'identity', null, 1,
current_timestamp, 'sample'
current_timestamp),
('plans_to_contract', '*', '', 'created', 0,
'const', 'init', 1,
current_timestamp, 'sample'
current_timestamp),
('plans_to_contract', '*', 'id', 'plan_id', 1,
'copy', null, 1,
current_timestamp, 'sample'
current_timestamp),
('plans_to_contract', '*', 'contract_status', 'status', 0,
'copy', null, 1,
current_timestamp, 'sample'
current_timestamp);