I'm having issues replicating data between 2 Postgres servers using SymmetricDS (open source version). Here's the relevant informations to understand my problem:
I have deployed 3 servers with Vagrant:
- symmetricds.local [192.168.44.9]
- postgres01.local [192.168.44.10]
- postgres02.local [192.168.44.11]
In the symmetricds.local server I install SymmetricDS as a service.
Int the postgres##.local servers I install postgres 11 and load Sakila data (sample data).
I then create the following configuration files in the symmetricds.local server:
cat <<EOF >>/opt/symmetric-server-3.12.10/engines/postgres01.properties
engine.name=postgres01
db.driver=org.postgresql.Driver
db.url=jdbc:postgresql://192.168.44.10:5432/postgres?stringtype=unspecified
db.user=postgres
db.password=postgres
registration.url=
sync.url=http://192.168.44.9:31415/sync/postgres01
group.id=primary
external.id=postgres01
auto.registration=true
initial.load.create.first=true
EOF
cat <<EOF >>/opt/symmetric-server-3.12.10/engines/postgres02.properties
engine.name=postgres02
db.driver=org.postgresql.Driver
db.url=jdbc:postgresql://192.168.44.11:5432/postgres?stringtype=unspecified
db.user=postgres
db.password=postgres
registration.url=http://192.168.44.9:31415/sync/postgres01
sync.url=http://192.168.44.9:31415/sync/postgres02
group.id=primary
external.id=postgres02
EOF
After that I start the SymmetricDS service:
sudo /opt/symmetric-server-3.12.10/bin/sym_service start
At that point all sym_ tables are created in both Postgres servers.
I add some data in postgres01.local server for replication between the two Postgres servers to work:
INSERT INTO sym_node_group_link (source_node_group_id,target_node_group_id,data_event_action)
VALUES ('primary','primary','P');
INSERT INTO sym_trigger (trigger_id, source_schema_name, source_table_name, channel_id, sync_on_update, sync_on_insert, sync_on_delete, sync_on_update_condition, sync_on_insert_condition, sync_on_delete_condition, last_update_time, create_time)
VALUES ('public.all', 'public', '*', 'default', 1, 1, 1, '1=1', '1=1', '1=1', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
INSERT INTO sym_router (router_id,source_node_group_id,target_node_group_id,router_type,router_expression,sync_on_update,sync_on_insert,sync_on_delete,use_source_catalog_schema,create_time,last_update_by,last_update_time)
VALUES ('primary_2_primary', 'primary', 'primary', 'default', NULL, 1, 1, 1, 0, CURRENT_TIMESTAMP, 'console', CURRENT_TIMESTAMP);
INSERT INTO sym_trigger_router (trigger_id, router_id, enabled, initial_load_order, create_time, last_update_time)
VALUES ('public.all', 'primary_2_primary', 1, 10, CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
Finally, I run this in the symmetricds.local server to allow registration:
sudo /opt/symmetric-server-3.12.10/bin/symadmin -e postgres01 open-registration primary postgres02
After a few minutes, triggers are created and data is shared between sym_ tables. But user data (Sakila tables) are not replicated.
I try to force table reload with:
INSERT INTO sym_table_reload_request (source_node_id, target_node_id, trigger_id, router_id, create_time, last_update_time) VALUES ('postgres01', 'postgres02', 'ALL', 'ALL', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP);
But then I get this error in SymmetricDS logs:
2021-07-01 15:03:19,097 ERROR [postgres01] [InitialLoadService] [postgres01-job-11] Error while queuing initial loads StackTraceKey.init [SymmetricException:46596748] org.jumpmind.symmetric.SymmetricException: Unable to issue an update for sym_node_security. Check the sym_trigger_hist for sym_node_security.
at org.jumpmind.symmetric.service.impl.DataService.insertNodeSecurityUpdate(DataService.java:2230)
at org.jumpmind.symmetric.service.impl.DataService.insertSqlEventsPriorToReload(DataService.java:1227)
at org.jumpmind.symmetric.service.impl.DataService.insertReloadEvents(DataService.java:1006)
at org.jumpmind.symmetric.service.impl.InitialLoadService.processTableRequestLoads(InitialLoadService.java:282)
at org.jumpmind.symmetric.service.impl.InitialLoadService.queueLoads(InitialLoadService.java:98)
at org.jumpmind.symmetric.job.InitialLoadJob.doJob(InitialLoadJob.java:43)
at org.jumpmind.symmetric.job.AbstractJob.invoke(AbstractJob.java:227)
at org.jumpmind.symmetric.job.AbstractJob.run(AbstractJob.java:298)
at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54)
at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:515)
at java.base/java.util.concurrent.FutureTask.runAndReset(FutureTask.java:305)
at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305)
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128)
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628)
at java.base/java.lang.Thread.run(Thread.java:829)
2021-07-01 15:03:29,118 INFO [postgres01] [InitialLoadService] [postgres01-job-14] Found 1 table reload requests to process.
2021-07-01 15:03:29,131 INFO [postgres01] [InitialLoadService] [postgres01-job-14] Cancelling load 2 for node postgres02
2021-07-01 15:03:29,134 INFO [postgres01] [InitialLoadService] [postgres01-job-14] Marked 1 load requests as OK for node postgres02
2021-07-01 15:03:29,136 INFO [postgres01] [InitialLoadService] [postgres01-job-14] Marked 0 extract requests as OK for node postgres02
2021-07-01 15:03:29,137 INFO [postgres01] [InitialLoadService] [postgres01-job-14] Marked 0 batches as OK for node postgres02
2021-07-01 15:03:29,147 INFO [postgres01] [DataService] [postgres01-job-14] Queueing up an initial load to node postgres02
2021-07-01 15:03:29,175 ERROR [postgres01] [InitialLoadService] [postgres01-job-14] Error while queuing initial loads StackTraceKey [SymmetricException:46596748]
Still nothing gets replicated and the sym_trigger_hist table is empty on postgres01 but full with all tables on postgres02.
Cloud you help me understanding the problem with my configuration?
