-
Notifications
You must be signed in to change notification settings - Fork 0
/
aws-dms-postgres-support-ticket-020817.txt
191 lines (157 loc) · 16.1 KB
/
aws-dms-postgres-support-ticket-020817.txt
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
I am looking for you, as a collective, to help me figure out what is the right methodology to migrate a Postgres database in RDS that is 9.4.9 and not in a VPC to a new Postgres RDS server in a VPC
I could use a restore of a snapshot yet that takes 45 minutes or so. I want to be able to let AWS DMS do what it wants for Postgres and tell it not to use text datatype for any JSON columns. Then I want to be able to pause CDC and add in counters, referential FK/unique constraints, Pk's, indexes, etc.
With MySQL I was able to allow the AWS DMS to load as it thought. Pause ongoing CDC. And then proceed to change it over to utf8mb4 charset and utf8mb4 unicode ci collation plus autoinc, PK's, and constraints. That task is running fine. All from a replica.
My test of letting AWS DMS for Postgres (from the main source db) make it's own decisions has run into the following issues (which I have worked around for now)
1. The db is not owned by the super user -- I created the table, trigger, etc as the db user and granted select on them to the super user.
2. The truncate option in AWS DMS for Postgres won't work with constraints so I have written a truncate table with CASCADE clause for the target to get around it.
3. On the source side I have granted select on all the app db tables to the super user so that the replication slot is created by the super user and then the load proceeds without failure
4. I can't use load then pause before CDC as the task will let me chose the option and it will fail and then the task is listed as having me toggling go on and don't pause before CDC (this hampers my idea of being able to make changes after AWS DMS automagically does and resume CDC with the hope there are no errors)
I did several tests... the one with AWS DMS doing the create objects on target and a test with AWS DMS using the existing truncated schema are worth delving into.
The first test had 6 table errors. The second test had over 20 errors yet the table statistics say the data is loaded in the tables with "Table error" and there are 0 records
With letting AWS DMS create the table on the target the truncation happened on load
Existing eligibility records with truncation error from 2/3 trial
Error message in log https://console.aws.amazon.com/cloudwatch/home?region=us-east-1#logEventViewer:group=dms-tasks-dms-wbid-migration;stream=dms-task-Y6MFNJYIH5DTNOGWKVWTI2OWPM
2017-02-03T15:08:08 [SOURCE_UNLOAD ]W: Value for column 'external_data' was truncated. data len: 756, bind len: 512 (ar_odbc_stmt.c:2789)
2017-02-03T15:08:08 [SOURCE_UNLOAD ]W: Value for column 'external_data' was truncated. data len: 756, bind len: 512 (ar_odbc_stmt.c:2789)
15:08:08
2017-02-03T15:08:08 [TARGET_LOAD ]I: Load finished for table 'public'.'eligibility_records' (Id = 9). 699079 rows received. 0 rows skipped. Volume transfered 886382672 (streamcomponent.c:2787)
15:08:08
2017-02-03T15:08:08 [SOURCE_UNLOAD ]I: Unload finished for table 'public'.'eligibility_records' (Id = 9). 699079 rows sent. (streamcomponent.c:2567)
15:08:09
2017-02-03T15:08:09 [TASK_MANAGER ]I: Table 'public'.'eligibility_records' (Id = 9) Loading finished by subtask 4. 699079 records transferred. (replicationtask.c:1639)
15:08:09
2017-02-03T15:08:09 [TASK_MANAGER ]I: All tables are loaded. Full load only task is stopped (replicationtask.c:2522)
This is what the table should look like (from source)
********* QUERY **********
SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '17992' ORDER BY c.oid::pg_catalog.regclass::pg_catalog.text;
**************************
Table "public.eligibility_records"
Column | Type | Modifiers
---------------+-----------------------------+------------------------------------------------------------------
id | integer | not null default nextval('eligibility_records_id_seq'::regclass)
type | character varying(255) | not null
user_id | integer | not null
sponsor_id | integer | not null
status | integer | not null
external_key | character varying(255) |
external_data | json | not null
eligible_at | timestamp without time zone |
revoked_at | timestamp without time zone |
superseded_at | timestamp without time zone |
created_at | timestamp without time zone |
updated_at | timestamp without time zone |
Indexes:
"eligibility_records_pkey" PRIMARY KEY, btree (id)
"index_eligibility_records_on_external_key" UNIQUE, btree (external_key) WHERE status = 1 AND type::text = 'EligibilityRecord::Hippo'::text
"index_eligibility_records_on_user_id_unique" UNIQUE, btree (user_id) WHERE status = 1
"index_eligibility_records_on_sponsor_id" btree (sponsor_id)
"index_eligibility_records_on_user_id" btree (user_id)
Foreign-key constraints:
"fk_rails_1cf58f1150" FOREIGN KEY (user_id) REFERENCES users(id)
"fk_rails_f50b3bcf00" FOREIGN KEY (sponsor_id) REFERENCES sponsors(id)
The current running task for CDC says it is replicating… yet the tables with errors are many and on the target those tables have 0 records in them
https://console.aws.amazon.com/dms/home?region=us-east-1#tasks:ids=wbid-migration-cdc
https://console.aws.amazon.com/cloudwatch/home?region=us-east-1#logEventViewer:group=dms-tasks-dms-wbid-migration;stream=dms-task-2B4D4C2I2PNE6VUCMMHRTFYLRY
Schema Table State Inserts Deletes Updates DDLs Full Load Rows Total Last updated
public attempted_registrations Table error 0 0 0 0 159,328 159,328 2/8/17, 11:35 AM
public auto_product_enrollments Table error 0 0 0 0 22,089 22,089 2/8/17, 11:35 AM
public background_enrollers Table error 0 0 0 0 0 0 2/8/17, 11:35 AM
public customer_contracts Table error 0 0 0 0 75 75 2/8/17, 11:35 AM
public customers Table error 0 0 0 0 62 62 2/8/17, 11:35 AM
public eligibility_records Table error 0 0 0 0 140,372 140,372 2/8/17, 11:35 AM
public email_events Table error 0 0 0 0 171,531 171,531 2/8/17, 11:35 AM
public external_authentication_records Table error 0 0 0 0 41,438 41,438 2/8/17, 11:35 AM
public external_authentications Table error 0 0 0 0 180,307 180,307 2/8/17, 11:35 AM
public healthways_accounts Table error 0 0 0 0 66,600 66,600 2/8/17, 11:35 AM
public hipaa_consents Table error 0 0 0 0 147,836 147,836 2/8/17, 11:35 AM
public hippo_eligibility_failures Table error 0 0 0 0 69,513 69,513 2/8/17, 11:35 AM
public hps_payloads Table error 0 0 0 0 54,867 54,867 2/8/17, 11:35 AM
public imported_users Table error 0 0 0 0 170,772 170,772 2/8/17, 11:35 AM
public oauth_access_grants Table error 0 0 0 0 291,631 291,631 2/8/17, 11:35 AM
public product_registrations Table error 0 0 0 0 540,679 540,679 2/8/17, 11:35 AM
public report_job_destinations Table error 0 0 0 0 0 0 2/8/17, 11:35 AM
public report_job_inclusions Table error 0 0 0 0 0 0 2/8/17, 11:35 AM
public report_job_runs Table error 0 0 0 0 0 0 2/8/17, 11:35 AM
public rewards_behavior_events Table error 0 0 0 0 525,683 525,683 2/8/17, 11:35 AM
public rewards_behaviors Table error 0 0 0 0 0 0 2/8/17, 11:35 AM
public rewards_incentive_orders Table error 0 0 0 0 49,155 49,155 2/8/17, 11:35 AM
public rewards_incentives Table error 0 0 0 0 0 0 2/8/17, 11:35 AM
public rewards_shipping_addresses Table error 0 0 0 0 0 0 2/8/17, 11:35 AM
public sponsor_consents Table error 0 0 0 0 376,743 376,743 2/8/17, 11:35 AM
public sponsor_organizations Table error 0 0 0 0 0 0 2/8/17, 11:35 AM
public sponsor_profiles Table error 0 0 0 0 416,816 416,816 2/8/17, 11:35 AM
public sponsors Table error 0 0 0 0 0 0 2/8/17, 11:35 AM
public terms_privacy_consents Table error 0 0 0 0 476,214 476,214 2/8/17, 11:35 AM
public user_events Table error 0 0 0 0 332,126 332,126 2/8/17, 11:35 AM
public user_profile_versions Table error 0 0 0 0 199,657 199,657 2/8/17, 11:35 AM
public user_profiles Table error 0 0 0 0 467,025 467,025 2/8/17, 11:35 AM
public user_sessions Table error 0 0 0 0 267,406 267,406 2/8/17, 11:35 AM
public user_versions Table error 0 0 0 0 132,682 132,682 2/8/17, 11:35 AM
public wbix_api_logs Table error 0 0 0 0 102,332 102,332 2/8/17, 11:35 AM
To give a bit of context on a few of the tables that have errors above. I can not trust that dashboard tab for the task. It's bogus.
This is their current DDL on target
Table "public.background_enrollers"
Column | Type | Modifiers
--------------------+-----------------------------+-------------------------------------------------------------------
id | integer | not null default nextval('background_enrollers_id_seq'::regclass)
status | integer | not null
user_id | integer | not null
contract_id | integer | not null
number_of_attempts | integer | not null
maximum_attempts | integer | not null
completed_at | timestamp without time zone |
deactivated_at | timestamp without time zone |
moot_at | timestamp without time zone |
created_at | timestamp without time zone |
updated_at | timestamp without time zone |
Indexes:
"background_enrollers_pkey" PRIMARY KEY, btree (id)
"index_background_enrollers_on_user_id" UNIQUE, btree (user_id)
Foreign-key constraints:
"fk_rails_28ac3b984d" FOREIGN KEY (user_id) REFERENCES users(id)
"fk_rails_d2f19644d1" FOREIGN KEY (contract_id) REFERENCES customer_contracts(id)
Table "public.auto_product_enrollments"
Column | Type | Modifiers
--------------------+-----------------------------+-----------------------------------------------------------------------
id | integer | not null default nextval('auto_product_enrollments_id_seq'::regclass)
sponsor_profile_id | integer | not null
status | integer | not null
product_id | character varying | not null
api_response | json |
created_at | timestamp without time zone | not null
updated_at | timestamp without time zone | not null
Indexes:
"auto_product_enrollments_pkey" PRIMARY KEY, btree (id)
"index_auto_product_enrollments_on_sponsor_profile_and_product" UNIQUE, btree (sponsor_profile_id, product_id)
"index_auto_product_enrollments_on_sponsor_profile_id" btree (sponsor_profile_id)
Foreign-key constraints:
"fk_rails_3af9bb512c" FOREIGN KEY (sponsor_profile_id) REFERENCES sponsor_profiles(id)
wellbeingid_production=> select count(*) from background_enrollers;
count
-------
0
(1 row)
wellbeingid_production=> \conninfo
You are connected to database "wellbeingid_production" as user "wellbeingid_production_app" on host "wbid-prd-dms-testing.cduvhvvpw6oo.us-east-1.rds.amazonaws.com" at port "5432".
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
wellbeingid_production=> select count(*) from auto_product_enrollments;
count
-------
0
(1 row)
wellbeingid_production=> \conninfo
You are connected to database "wellbeingid_production" as user "wellbeingid_production_app" on host "wbid-prd-dms-testing.cduvhvvpw6oo.us-east-1.rds.amazonaws.com" at port "5432".
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)
It appears that table completed status is accurate and updating (loading from replication slot on source)
wellbeingid_production=> select count(*) from auth_product_credentials;
count
--------
102993
(1 row)
wellbeingid_production=> \conninfo
You are connected to database "wellbeingid_production" as user "wellbeingid_production_app" on host "wbid-prd-dms-testing.cduvhvvpw6oo.us-east-1.rds.amazonaws.com" at port "5432".
SSL connection (protocol: TLSv1.2, cipher: ECDHE-RSA-AES256-GCM-SHA384, bits: 256, compression: off)