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.
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 $$;
Enable RDS Encryption with minimal downtime after creationAccepted Answerasked a year ago
Random RDS SQL Server QuestionsAccepted Answerasked 4 years ago
FATAL: password authentication failed for user "postgresasked 4 years ago
Syncing up a specific schema between two RDS Tables (postgres)Accepted Answerasked 20 days ago
How to create SSL dblink connection from RDS to postgres on premise?asked 4 months ago
How to restrict database users for RDS Postgres using AWS Managed AD trusted with customer on-prem ADAccepted Answerasked 2 years ago
60K updates to RDS Postgres - performance challengeAccepted Answerasked 2 years ago
Aurora PostgreSQL - recommended maximum number of databases (for a multi-tenant data layer)Accepted Answerasked 3 years ago
RDS Postgres + MySQL low read iopsasked 15 days ago
Postgres RDS Schema Upgrade (migration) with no downtimeasked 2 months ago