CODEWITHSUNDEEP
databasepostgresqlsymmetricds
willy
willy

Reputation: 209

SymmetricDS replication referencing wrong schema for foreign key

I use symmetricds replication for postgres database (one server node, one client node). I have got two schemas in my db: 'public' and 'schemaB'. Table from schemaB (tabB) has foreign key referencing field from table in public schema (tabA).

Table definition:

CREATE TABLE schemaB.tabB (
    id character varying(255) NOT NULL,
    external_id character varying(255),
)

ALTER TABLE ONLY schemaB.tabB
    ADD CONSTRAINT fk_ma9cnn7779v065434iugg1321 FOREIGN KEY (external_id) REFERENCES public.tabA(id);

Symmetricds configuration for mentioned table:

insert into sym_trigger (trigger_id, source_schema_name, source_table_name, channel_id, sync_on_incoming_batch, last_update_time, create_time) values
  ('tid', 'schemaB', 'tabB', 'channel_B', 0, current_timestamp, current_timestamp);

During initial load for client node symmetricds tries to create table with definition:

ALTER TABLE "schemaB"."tabB"
    ADD CONSTRAINT "fk_ma9cnn7779v065434iugg1321" FOREIGN KEY ("external_id") REFERENCES schemaB."tabA" ("id");

what causes error:

ERROR: relation "schemaB.users" does not exist.  Failed to execute: ALTER TABLE "schemaB"."tabB"
    ADD CONSTRAINT "fk_ma9cnn7779v065434iugg1321" FOREIGN KEY ("external_id") REFERENCES schemaB."tabA" ("id")

Symmetricds clearly references wrong schema. Can I do anything about it?

Upvotes: 0

Views: 320

Answers (1)

Boris Pavlović
Boris Pavlović

Reputation: 64632

I would suggest having two symmetricDs engines for both schemas public and schemaB that would sync to corresponding schemas on the target node. To create another engine just duplicate the symmetric-ds.properties file, rename it to, let's say symmetric-ds-public.properties and edit to connect to the public schema.

Upvotes: 0

Related Questions