Subscribe to RSS feed  Follow @jackvamvas - Twitter

*Use the Comments section for questions Links



Troubleshoot SQL Server varbinary(max) to PostgreSQL bytea using AWS DMS

24 April,2023 by Rambler


I'm trying to migrate some SQL Server tables with varbinary(MAX)  data type defined columns using the AWS Database Migration Service (DMS).   The target is AWS RDS PostgreSQL

I've noticed the LOB sizes are different when migrated to the targtet PostgreSQL environment , can you direct me in the right direction?

The source SQL Server data type for the column is :  varbinary(max) 

The target PostgreSQL server data type is  : bytea



FIRST STEP -  as a basic initial investigation compare the  byte length of the LOB columns across SQL Server & PostgreSQL. Use these queries to compare the source & target tables.

On SQL Server :

select datalength(BLOBData) as bytes from myTable;

On PostgreSQL:

---You can use the length() function to get the length in bytes:
select length(blobdata) as bytes from myTable ;


bytea is a sensible choice as an equivalent for varbinary(max)

Using "large objects" (aka "oid") only make sense if you have values > 1GB or if you need the streaming capabilities that large objects offer.

These are the recommended source and target data types 


Data type - SQL Server Data type - details Data type - PostgreSQL
VARBINARY(n) Variable length byte string , 1 <= n <= 8000 BYTEA
VARBINARY(max) Variable length byte string , <= 2GB BYTEA


SECOND STEP- you need to review the DMS LOB configuration settings . If these configurations are not set correctly , you may experience unexpedted outcomes.

In the official AWS documentation for these are basic descriptions

. Limited LOB mode migrates all LOB values up to a user-specified size limit (default is 32 KB). LOB
values larger than the size limit must be manually migrated. Limited LOB mode, the default for all
migration tasks, typically provides the best performance. However, ensure that the Max LOB size
parameter setting is correct. Set this parameter to the largest LOB size for all your tables.


. Full LOB mode migrates all LOB data in your tables, regardless of size. Full LOB mode provides the
convenience of moving all LOB data in your tables, but the process can have a significant impact on

The Full LOB mode will not convert\migrate the source column onto the target  RDS PostgreSQL if the source Table does not have a primary key created. 



There are a number of other associated memory problems related to migrating LOBs using AWS DMS. 

  • AWS DMS processes LOBs in memory. This operation requires a fair amount of memory.

A  Common error when migrating LOBs through AWS DMS is :  

Last Error Replication task out of memory. Stop Reason FATAL_ERROR Error Level FATAL

Review your  DMS task setting - and read through Troubleshooting AWS DMS error "Last Error Replication task out of memory. Stop Reason FATAL_ERROR Error Level FATAL"



Author: Rambler (


Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.


Post a comment on Troubleshoot SQL Server varbinary(max) to PostgreSQL bytea using AWS DMS