By using AWS re:Post, you agree to the Terms of Use
/Syncing up a specific schema between two RDS Tables (postgres)/

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



Currently we have two dbs in AWS RDS (Postgres). One for Demo (Internal Testing) and another for beta environment. Both dbs have a schema called "strapi" which contains common data to display to user. Our use case is that whenever the admin updates the strapi data in demo db, it should reflect in beta as well. So I was searching for AWS services that supports our use case.

Searched for similar questions and I found people recommending AWS DMS. So went ahead and created endpoints and initially ran the task with "Do Nothing" option for Target Table Preparation Mode.

Later ran the full load task with "Truncate" option for Target Table Preparation Mode .

The task failed with following error

"RetCode: SQL_ERROR SqlState: 0A000 NativeError: 1 Message: ERROR: cannot truncate a table referenced in a foreign key constraint; Error while executing the query 1022502. Failed to truncate table strapi.programs 1022502"

Tried setting session_replication_role parameter to replica on target database in RDS and still getting the same error. Any help would be appreciated.

Note: I still am not sure whether AWS DMS is a good service for our use case.

Thanks in Advance.

1 Answers
Accepted Answer

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

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

do $$ declare t record;
    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())

        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;
    -- 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 $$;
answered 19 days ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions