Syncing up a specific schema between two RDS Tables (postgres)

Found the solution. The solution is to manually drop foreign key constraints before full load and recreating them once migration task is over.

A simple script to drop foreign key constraints (Taken from https://dba.stackexchange.com/a/97047)

create table if not exists dropped_foreign_keys (
        seq bigserial primary key,
        sql text
);

do $$ declare t record;
begin
    for t in select conrelid::regclass::varchar table_name, conname constraint_name,
            pg_catalog.pg_get_constraintdef(r.oid, true) constraint_definition
            from pg_catalog.pg_constraint r
            where r.contype = 'f'
            -- uncomment the below line for current schema only:
            -- and r.connamespace = (select n.oid from pg_namespace n where n.nspname = current_schema())
        loop

        insert into dropped_foreign_keys (sql) values (
            format('alter table %s add constraint %s %s',t.table_name, t.constraint_name, t.constraint_definition));

        execute format('alter table %s drop constraint %s', t.table_name, t.constraint_name);

    end loop;

    
end $$;

And to recreate the dropped foreign key constraints

do $$ declare t record;
begin
    -- order by seq for easier troubleshooting when data does not satisfy FKs
    for t in select * from dropped_foreign_keys order by seq loop
        execute t.sql;
        delete from dropped_foreign_keys where seq = t.seq;
    end loop;
end $$;