データベース移行 - SymmetricDS
異なるテーブル名。カラムは同じ
org.customers → mig.customers # スキーマ名を変更 org.plans → mig.plans # スキーマ名を変更
customersとplansをcustomerとplanとしてコピーします。
truncate_table.sql
truncate table mig.customers truncate table mig.plans
insert_migration.sql
#symテーブルを毎回作り直していればdeleteは不要
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, source_node_group_id, target_node_group_id, router_type, last_update_time, create_time)
values
('postgres_2_sqlserver', 'mig', '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', 'postgres_2_sqlserver', 100, current_timestamp, current_timestamp),
('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');