Hello,
We are using aws dms to copy the rds postgres data to our datawarehouse redshift. The issue we are facing was , in one of the tables we have jsonb as datatype in our source postgres , when dms is trying to migrate the data, it is converting to varchar by default which has limitation of 64K characters, so it is truncating the data.
Here the the jsonb limit in postgres is 255MB, varchar limit is 64K characters, super datatype limit is 16 MB.
We tried to convert the jsonb to super through transformation rules, but none of them got succeeded, eventhough we convert it, it has the limitation of 16MB only. So how can we solve this problem , how can i efficiently migrate with out losing any data .
I appreciate the solutions through dms way or any other alternate way on how to handle this large data through redshift datatypes.
As described in the question, even though we have done like that . The max limit is 16 MB, but in the source it is 255 MB
@aws_explorer having a 250 mb json object in a single field in a Postgres database indicates you have incorrectly designed your Postgres database. Convert that column into a separate table, and store any embedded files (or text fields containing war and peace) as separate columns. As long as the entire json object is under 16mb it’ll import it and truncate any long strings inside it to 65k chars. If u have json over 16mb it’s gonna blow up, just like if you had that in s3.