How can I perform repeated Full Loads when there are Foreign Keys in the target?

[Karun]

I am setting up replication between Oracle and PostgreSQL databases. I have the target schema defined identical to source schema, including table names, primary keys and foreign keys. I am performing full load, and in the Full Load section I've set the "Target table preparation" action to "Truncate before loading".

In this scenario, if I need to do repeated full loads (for testing purposes, for example), how do I accomplish it? Only the first full load is successful for all tables, but any subsequent full load fails for many tables with the error that it cannot truncate a table referenced in a foreign key constraint. 

The only way I see around is to manually truncate the tables in the target with a DELETE CASCADE and run the replication again. But is there a way to do this from within the tool? 

Thanks for your time!

[Michael_Litz · Accepted solution]

Hi Karun,

No I do not believe there is a way to do this from the replicate task level. I would ask if you really need the foreign key constraints on the target database, as since is it populated from the source these "constraints" have already been enforced. Replicate target tables only need a Primary Key or a unique index defined on them - which the task should know about.

Please if you do not need the foreign keys on the target table just remove them and the standard truncate table should work okay from within the tool.

Thanks,
Michael Litz

[Karun]

Thanks for your reply.

I need those constraints because I'm moving the application to a new database. So, the target (Postgres) will become the primary DB after a cut-off date. My plan is to do a full load followed by a CDC near the cut-off date to keep the DB in sync, and switch over to the new DB when the time comes.

[Michael_Litz]

Hi Karun,

Okay, then are you able to not create the FK during your testing and then only create them when you go live?

Otherwise I think you would need to continue with the manual steps that you are doing from outside of replicate.

Thanks,
Michael

[Karun]

Yes, I think that is how I should go about it, if the tool doesn't have an option to temporarily not enforce or disable foreign keys at the target DB. I should do a full load without FK constraints, then switch over to CDC, and then apply the FKs.

Thanks for your inputs!

[Michael_Litz]

Hi Karun,

I agree, that sounds like the best way to move forward.

Help users find answers! Don't forget to mark a solution that worked for you! If already marked, give it a thumbs up!

[SwathiPulagam]

Hi @Karun ,

What I understand is rather than Truncate you want to use Delete cascade. If this is correct let me know.

Thanks,

Swathi

[john_wang]

Hello @Karun , copy @Michael_Litz @SwathiPulagam 

It's easy to control the TRUNCATE TABLE statement in Replicate, you can custom it to any valid DML/DDL SQL as you wish. In your scenario, you can add an internal parameter in PostgreSQL target endpoint, the parameter name is "$info.query_syntax.truncate_table" (press enter) then set it's value to 

DELETE FROM ${QO}${TABLE_OWNER}${QC}.${QO}${TABLE_NAME}${QC} CASCADE

 Or other SQL, eg TRUNCATE TABLE CASCADE etc. The screen copy likes:

This is a common solution for other endpoints as well, eg DB2 LUW TRUNCATE .

Hope this helps.

Regards,

John.

[Heinvandenheuvel]

DELETE CASCADE may help, but very tricky to be used as global alternative for truncate through the redefined syntax option. The parent better be loaded before any child tables. The problem being that replicate out of the box loads tables alphabetically, so with a child table 'child' and a parent table 'parent' a (re)load for the whole task would fail as there is are parents on first load, and on reload the load of the parent table would delete the already loaded child table. Yes you can play with load priority to influence this to a degree (watch out for parallel streams) but I'm sure this will eventually lead to bad errors, perhaps when reloading a single table later forgetting about the cascade.

It is best to NOT create (or disable) the FK relations until going life with the target DB  as primary and trust the old primary to validate the FK constraints.

This is also needed for high performance batch apply CDC  (the default) as those fail with FK's enforced and the slower, more resource consuming, transactional apply would be needed instead.

Good luck,

Hein.

[nareshkumar]

I believe  if we can grant  "replica" role for our Postgres User , we can skip the FK constraints during full loads 

:   AfterConnectScript= SET session_replication_role = replica...