search

parksunwoo / memo-archive

memo of dev issues
0 stars 0 forks source link

PostgreSQL to Aurora PostgreSQL Serverless migration #8

Open parksunwoo opened 3 years ago

parksunwoo commented 3 years ago

AWS DMS 서비스를 사용해서 Azure PostgreSQL 을 Aurora PostgreSQL Serverless 로 마이그레이션 하는 과정에서 아래 메시지로 에러발생,

2021-05-07T03:46:48 [TARGET_LOAD ]E: Failed (retcode -1) to execute statement [1022502] (ar_odbc_stmt.c:4808) 2021-05-07T03:46:48 [TARGET_LOAD ]E: RetCode: SQL_ERROR SqlState: 2BP01 NativeError: 1 Message: ERROR: cannot drop table forum_topic because other objects depend on it; Error while executing the query [1022502] (ar_odbc_stmt.c:4815) 2021-05-07T03:46:48 [TARGET_LOAD ]E: Failed to drop table public.forum_topic [1022502] (odbc_endpoint_imp.c:4064) 2021-05-07T03:46:48 [TARGET_LOAD ]E: Handling new table 'public'.'forum_topic' failed [1022502] (endpointshell.c:2688)

외래 키 제약 조건 위반 오류로 인해 실패한 AWS DMS 작업의 문제를 해결하려면 어떻게 해야 합니까? https://aws.amazon.com/ko/premiumsupport/knowledge-center/dms-foreign-key-constraint-error/

aws docs 를 참고했으나

​해결 방법 이 오류를 해결하려면 다음 중 하나를 수행하십시오.

2가지 방법을 적용해도 동일한 현상임

parksunwoo commented 3 years ago

By default, AWS DMS tasks load eight tables at a time during the full load phase. These tables are loaded alphabetically by default, unless you configure the loading order for the task.

테이블 적재 순서를 지정해서 작업시도했으나 동일하게 같은 테이블에서 에러발생

parksunwoo commented 3 years ago

극단적으로 에러발생하는 1개 테이블만 DMS 작업을 시도했으나 동일하게 에러 발생, 테이블의 갯수가 중요한게 아니라 외래키제약을 해제하는게 중요

parksunwoo commented 3 years ago

Amazon Aurora로 안전하게 migration 하기 https://www.slideshare.net/jesangyoon/amazon-aurora-migration

https://medium.com/hbsmith/aws-aurora-%EB%8F%84%EC%9E%85%EC%97%90-%EB%8C%80%ED%95%9C-%EB%AA%87%EA%B0%80%EC%A7%80-%EC%82%AC%EC%8B%A4-45eb602bad58

parksunwoo commented 3 years ago 
SELECT
  nspname AS schemaname,relname,reltuples
FROM pg_class C
LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
WHERE
  nspname NOT IN ('pg_catalog', 'information_schema') AND
  relkind='r'
ORDER BY reltuples DESC;

postgresql TABLE name , row count select query

parksunwoo commented 3 years ago

MICROSOFT SQL SERVER TO AMAZON AURORA (POSTGRESQL) https://dms-immersionday.workshop.aws/en/sqlserver-aurora-postgres.html

이 과정에선 target table 의 constraint 를 일괄로 삭제하고 마이그레이션 작업이후에 다시 constraint 를 더하는 작업 진행

parksunwoo commented 3 years ago

DropConstraintsSQLServer-to-PostgreSQL.sql

ALTER TABLE dms_sample_dbo.player DROP CONSTRAINT IF EXISTS sport_team_fk;

ALTER TABLE dms_sample_dbo.seat DROP CONSTRAINT IF EXISTS seat_type_fk;

ALTER TABLE dms_sample_dbo.sport_division DROP CONSTRAINT IF EXISTS sd_sport_type_fk;

ALTER TABLE dms_sample_dbo.sport_division DROP CONSTRAINT IF EXISTS sd_sport_league_fk;

ALTER TABLE dms_sample_dbo.sport_league DROP CONSTRAINT IF EXISTS sl_sport_type_fk;

ALTER TABLE dms_sample_dbo.sport_team DROP CONSTRAINT IF EXISTS st_sport_type_fk;

ALTER TABLE dms_sample_dbo.sport_team DROP CONSTRAINT IF EXISTS home_field_fk;

ALTER TABLE dms_sample_dbo.sporting_event DROP CONSTRAINT IF EXISTS se_sport_type_fk;

ALTER TABLE dms_sample_dbo.sporting_event DROP CONSTRAINT IF EXISTS se_away_team_id_fk;

ALTER TABLE dms_sample_dbo.sporting_event DROP CONSTRAINT IF EXISTS se_home_team_id_fk;

ALTER TABLE dms_sample_dbo.sporting_event_ticket DROP CONSTRAINT IF EXISTS set_person_id;

ALTER TABLE dms_sample_dbo.sporting_event_ticket DROP CONSTRAINT IF EXISTS set_sporting_event_fk;

ALTER TABLE dms_sample_dbo.sporting_event_ticket DROP CONSTRAINT IF EXISTS set_seat_fk;

ALTER TABLE dms_sample_dbo.ticket_purchase_hist DROP CONSTRAINT IF EXISTS tph_sport_event_tic_id;

ALTER TABLE dms_sample_dbo.ticket_purchase_hist DROP CONSTRAINT IF EXISTS tph_ticketholder_id;

ALTER TABLE dms_sample_dbo.ticket_purchase_hist DROP CONSTRAINT IF EXISTS tph_transfer_from_id;

set search_path to dms_sample_dbo;

alter table player drop constraint if exists sport_team_fk_2037582297 cascade ; alter table seat drop constraint if exists s_sport_location_fk_2053582354 cascade ; alter table seat drop constraint if exists seat_type_fk_2069582411 cascade ; alter table sport_division drop constraint if exists sd_sport_league_fk_2085582468 cascade ; alter table sport_division drop constraint if exists sd_sport_type_fk_2101582525 cascade ; alter table sport_league drop constraint if exists sl_sport_type_fk_2117582582 cascade ; alter table sport_team drop constraint if exists home_field_fk_2133582639 cascade ; alter table sport_team drop constraint if exists st_sport_type_fk_2099048 cascade ; alter table sporting_event drop constraint if exists chk_sold_out_1253579504 cascade ; alter table sporting_event drop constraint if exists se_away_team_id_fk_18099105 cascade ; alter table sporting_event drop constraint if exists se_home_team_id_fk_34099162 cascade ; alter table sporting_event drop constraint if exists se_location_id_fk_50099219 cascade ; alter table sporting_event drop constraint if exists se_sport_type_fk_66099276 cascade ; alter table sporting_event_ticket drop constraint if exists set_person_id_82099333 cascade ; alter table sporting_event_ticket drop constraint if exists set_seat_fk_1333579789 cascade ; alter table sporting_event_ticket drop constraint if exists set_sporting_event_fk_98099390 cascade ; alter table sysdiagrams drop constraint if exists uk_principal_name_1621580815 cascade ; alter table ticket_purchase_hist drop constraint if exists tph_sport_event_tic_id_114099447 cascade ; alter table ticket_purchase_hist drop constraint if exists tph_ticketholder_id_130099504 cascade ; alter table ticket_purchase_hist drop constraint if exists tph_transfer_from_id_146099561 cascade ; DROP TRIGGER tr_sporting_event_biu ON dms_sample_dbo.sporting_event; ---drop secondary indexes on sporting_event_ticket drop index if exists set_ev_id_tkholder_id_idx ; drop index if exists set_seat_idx; drop index if exists set_ticketholder_idx; drop index if exists set_sporting_event_idx;

parksunwoo commented 3 years ago

AddConstraintsSQLServer-to-PostgreSQL.sql

ALTER TABLE dms_sample_dbo.player ADD CONSTRAINT sport_team_fk FOREIGN KEY (sport_team_id) REFERENCES dms_sample_dbo.sport_team(id) ON DELETE CASCADE;

ALTER TABLE dms_sample_dbo.seat ADD CONSTRAINT seat_type_fk FOREIGN KEY (seat_type) REFERENCES dms_sample_dbo.seat_type(name) ON DELETE CASCADE;

/* Skipping because of long wait time for the query to complete

ALTER TABLE dms_sample_dbo.seat ALTER COLUMN sport_location_id TYPE numeric;

ALTER TABLE dms_sample_dbo.seat ADD CONSTRAINT s_sport_location_fk FOREIGN KEY (sport_location_id) REFERENCES dms_sample_dbo.sport_location(id) ON DELETE CASCADE; */

ALTER TABLE dms_sample_dbo.sport_division ADD CONSTRAINT sd_sport_type_fk FOREIGN KEY (sport_type_name) REFERENCES dms_sample_dbo.sport_type (name) ON DELETE CASCADE;

ALTER TABLE dms_sample_dbo.sport_division ADD CONSTRAINT sd_sport_league_fk FOREIGN KEY (sport_league_short_name) REFERENCES dms_sample_dbo.sport_league (short_name) ON DELETE CASCADE;

ALTER TABLE dms_sample_dbo.sport_league ADD CONSTRAINT sl_sport_type_fk FOREIGN KEY (sport_type_name) REFERENCES dms_sample_dbo.sport_type (name);

ALTER TABLE dms_sample_dbo.sport_team ADD CONSTRAINT st_sport_type_fk FOREIGN KEY (sport_type_name) REFERENCES dms_sample_dbo.sport_type (name) ON DELETE CASCADE;

ALTER TABLE dms_sample_dbo.sport_team ADD CONSTRAINT home_field_fk FOREIGN KEY (home_field_id) REFERENCES dms_sample_dbo.sport_location (id) ON DELETE CASCADE;

ALTER TABLE dms_sample_dbo.sporting_event ADD CONSTRAINT se_sport_type_fk FOREIGN KEY (sport_type_name) REFERENCES dms_sample_dbo.sport_type (name);

ALTER TABLE dms_sample_dbo.sporting_event ADD CONSTRAINT se_away_team_id_fk FOREIGN KEY (away_team_id) REFERENCES dms_sample_dbo.sport_team (id) ON DELETE CASCADE;

ALTER TABLE dms_sample_dbo.sporting_event ADD CONSTRAINT se_home_team_id_fk FOREIGN KEY (home_team_id) REFERENCES dms_sample_dbo.sport_team (id);

ALTER TABLE dms_sample_dbo.sporting_event_ticket ADD CONSTRAINT set_person_id FOREIGN KEY(ticketholder_id) REFERENCES dms_sample_dbo.person (id) ON DELETE CASCADE;

ALTER TABLE dms_sample_dbo.sporting_event_ticket ADD CONSTRAINT set_sporting_event_fk FOREIGN KEY (sporting_event_id) REFERENCES dms_sample_dbo.sporting_event (id) ON DELETE CASCADE;

/* Skipping because of long wait time for the query to complete

ALTER TABLE dms_sample_dbo.sporting_event_ticket ALTER COLUMN sport_location_id TYPE numeric;

ALTER TABLE dms_sample_dbo.sporting_event_ticket ADD CONSTRAINT set_seat_fk FOREIGN KEY (sport_location_id, seat_level, seat_section, seat_row, seat) REFERENCES dms_sample_dbo.seat (sport_location_id, seat_level, seat_section, seat_row, seat); */

ALTER TABLE dms_sample_dbo.ticket_purchase_hist ADD CONSTRAINT tph_sport_event_tic_id FOREIGN KEY (sporting_event_ticket_id) REFERENCES dms_sample_dbo.sporting_event_ticket (id) ON DELETE CASCADE;

ALTER TABLE dms_sample_dbo.ticket_purchase_hist ADD CONSTRAINT tph_ticketholder_id FOREIGN KEY (purchased_by_id) REFERENCES dms_sample_dbo.person (id);

ALTER TABLE dms_sample_dbo.ticket_purchase_hist ADD CONSTRAINT tph_transfer_from_id FOREIGN KEY (transferred_from_id) REFERENCES dms_sample_dbo.person (id);

-----these are here for reference they take a while to rebuild so not required for the lab only if you want to

---CREATE INDEX set_ev_id_tkholder_id_idx ON dms_sample.sporting_event_ticket (sporting_event_id float8_ops,ticketholder_id float8_ops); ---CREATE INDEX set_seat_idx ON dms_sample.sporting_event_ticket USING btree (sport_location_id, seat_level, seat_section, seat_row, seat); ---CREATE INDEX set_sporting_event_idx ON dms_sample.sporting_event_ticket USING btree (sporting_event_id); ---CREATE INDEX set_ticketholder_idx ON dms_sample.sporting_event_ticket USING btree (ticketholder_id);

parksunwoo commented 3 years ago

AWS Database Migration Service (DMS) - Tips & Tricks https://www.sentiatechblog.com/aws-database-migration-service-dms-tips-and-tricks