ERROR: out of memory DETAIL: Cannot enlarge string buffer containing 1073741808 bytes by 8191 more bytes. CONTEXT: COPY TABL, line 95ERROR: out of memory DETAIL: Cannot enlarge string buffer containing 1073741808 bytes by 8191 more bytes. CONTEXT:

[satam2007]

Hello,

We are migrating the BLOB object from Oracle to PostgreSQL using Ora2PG and getting the below error. Also, the size of the objects is around 100 GB, and the raw length is more than 1 GB, which creates problems because of the Byeta data type restriction. Can anyone suggest an alert problem to resolve this issue?

ERROR: out of memory DETAIL: Cannot enlarge string buffer containing 1073741808 bytes by 8191 more bytes. CONTEXT: COPY TABL, line 95ERROR: out of memory DETAIL: Cannot enlarge string buffer containing 1073741808 bytes by 8191 more bytes. CONTEXT:

[darold]

In this case you have two choices:

1. store the blob as files outside the database
2. use large object columns instead of bytea

The first case must be handled by you. The second case can be entirely handled by Ora2Pg, see option --blob_to_lo of the ora2pg command.

[satam2007]

Yes we do the same to keep the blob as files and use OID instead of Bytea but getting below error during insert.

psql:.:207: ERROR: invalid input syntax for type oid: "\x255044462d312e360d25e2e3cfd30d0a3435

Also i try with --blob_to_lo option during ora2pg copy command but given below message in output log.

Option blob_to_lo does not take an argument.

[darold]

Humm, if I recall well in this case you have to use INSERT, the COPY mode is not possible because we have to call a function to get the oid.

[satam2007]

I tried the INSERT command instead of COPY, but during import, I faced below error.

ERROR: invalid memory alloc request size 1073741824

[satam2007]

Hello darold, can you please help to update on this issue

[darold]

Ok, sorry I didn't remember exactly how this feature is working. Actually this OOM is normal with INSERT mode if the BLOB is > 1GB after translation to bytea. You were rigth you have to use COPY because it imports the the large object from a file. What is your ora2pg command?

[satam2007]

We use below command currently for migration.

With COPY command :

ora2pg -J 2 -t COPY -o TABLE_data_LOB.sql -b ./data -c ./config/ora2pg_LOB.conf -l ${LOG_4}

With Insert command :

ora2pg -J 2 -t INSERT -o TABLE_data_LOB.sql -b ./data -c ./config/ora2pg_LOB.conf -l ${LOG_4} --blob_to_lo

For Import :

psql -h ${Hostname} -p 5432 -U databaseowner -d ${DBNAME} -f /home/data/data_LOB.sql -L ${LOG_5}

[darold]

Well it is better to rely on the documentation than on my memory, for COPY export:

   --lo_import        : use psql \lo_import command to import BLOB as large
                        object. Can be use to import data with COPY and import
                        large object manually in a second pass. It is required
                        for BLOB > 1GB. See documentation for more explanation.

this option is missing.

[satam2007]

i used this command but now its created multiple .lo files on location. Could you please suggest how to insert the data now in postgresql with psql command

[darold]

Use the data/data.sql file with psql