pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedOra2pg migration
7+ messages / 5 participants
[nested] [flat]
* Ora2pg migration
@ 2024-03-22 05:38 Narendran .j <jnarendran3@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Narendran .j @ 2024-03-22 05:38 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
Hello Team,
I have export one table which has blob data type by ora2pg tool.while
Importing insert file in postgres I'm getting this error .
psql:ERROR: out of memory
DETAIL: Cannot enlarge string buffer containing 1073741808 bytes by 8191
more bytes.
Please give me any suggestions and I adjust shared_buffer it didn't work.
Regards,
Narendran
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Ora2pg migration
@ 2024-03-22 10:03 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Narendran .j <jnarendran3@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: Laurenz Albe @ 2024-03-22 10:03 UTC (permalink / raw)
To: Narendran .j <jnarendran3@gmail.com>; pgsql-admin@lists.postgresql.org
On Fri, 2024-03-22 at 11:08 +0530, Narendran .j wrote:
> I have export one table which has blob data type by ora2pg tool.while Importing insert file in postgres I'm getting this error .
> psql:ERROR: out of memory
> DETAIL: Cannot enlarge string buffer containing 1073741808 bytes by 8191 more bytes.
>
> Please give me any suggestions and I adjust shared_buffer it didn't work.
1GB is a hard limit for memory allocations.
How big are those BLOBs? The theoretical limit in PostgreSQL is 1GB, but in
practice things start getting difficult around 500MB, unless you use binary
transfer.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Ora2pg migration
@ 2024-03-25 13:46 Narendran .j <jnarendran3@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 3 replies; 7+ messages in thread
From: Narendran .j @ 2024-03-25 13:46 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-admin@lists.postgresql.org
Hello
It's almost 8gb, could you explain me how can I transfer as
binary file?.I'm new on this.
On Fri, 22 Mar 2024, 3:33 pm Laurenz Albe, <laurenz.albe@cybertec.at> wrote:
> On Fri, 2024-03-22 at 11:08 +0530, Narendran .j wrote:
> > I have export one table which has blob data type by ora2pg tool.while
> Importing insert file in postgres I'm getting this error .
> > psql:ERROR: out of memory
> > DETAIL: Cannot enlarge string buffer containing 1073741808 bytes by 8191
> more bytes.
> >
> > Please give me any suggestions and I adjust shared_buffer it didn't work.
>
> 1GB is a hard limit for memory allocations.
>
> How big are those BLOBs? The theoretical limit in PostgreSQL is 1GB, but
> in
> practice things start getting difficult around 500MB, unless you use binary
> transfer.
>
> Yours,
> Laurenz Albe
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Ora2pg migration
@ 2024-03-25 14:27 Rajesh Kumar <rajeshkumar.dba09@gmail.com>
parent: Laurenz Albe <laurenz.albe@cybertec.at>
1 sibling, 0 replies; 7+ messages in thread
From: Rajesh Kumar @ 2024-03-25 14:27 UTC (permalink / raw)
To: Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: Narendran .j <jnarendran3@gmail.com>; Pgsql-admin <pgsql-admin@lists.postgresql.org>
Kindly provide the possible options to perform migration in this case.
Kindly elaborate more on binary transfer.
On Fri, 22 Mar 2024, 15:34 Laurenz Albe, <laurenz.albe@cybertec.at> wrote:
> On Fri, 2024-03-22 at 11:08 +0530, Narendran .j wrote:
> > I have export one table which has blob data type by ora2pg tool.while
> Importing insert file in postgres I'm getting this error .
> > psql:ERROR: out of memory
> > DETAIL: Cannot enlarge string buffer containing 1073741808 bytes by 8191
> more bytes.
> >
> > Please give me any suggestions and I adjust shared_buffer it didn't work.
>
> 1GB is a hard limit for memory allocations.
>
> How big are those BLOBs? The theoretical limit in PostgreSQL is 1GB, but
> in
> practice things start getting difficult around 500MB, unless you use binary
> transfer.
>
> Yours,
> Laurenz Albe
>
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Ora2pg migration
@ 2024-03-25 15:30 Laurenz Albe <laurenz.albe@cybertec.at>
parent: Narendran .j <jnarendran3@gmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: Laurenz Albe @ 2024-03-25 15:30 UTC (permalink / raw)
To: Narendran .j <jnarendran3@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org
> On Fri, 22 Mar 2024, 3:33 pm Laurenz Albe, <laurenz.albe@cybertec.at> wrote:
> > On Fri, 2024-03-22 at 11:08 +0530, Narendran .j wrote:
> > > I have export one table which has blob data type by ora2pg tool.while Importing insert file in postgres I'm getting this error .
> > > psql:ERROR: out of memory
> > > DETAIL: Cannot enlarge string buffer containing 1073741808 bytes by 8191 more bytes.
> > >
> > > Please give me any suggestions and I adjust shared_buffer it didn't work.
> >
> > 1GB is a hard limit for memory allocations.
> >
> > How big are those BLOBs? The theoretical limit in PostgreSQL is 1GB, but in
> > practice things start getting difficult around 500MB, unless you use binary
> > transfer.
>
> It's almost 8gb, could you explain me how can I transfer as binary file?.I'm new on this.
With 8GB, you cannot use "bytea" on PostgreSQL.
There is support for PostgreSQL Large Objects with ora2pg; you'd have to read the
documentation to find out more.
I cannot help you with the memory allocation error, mostly because you
didn't tell us what exactly you did to trigger it.
Yours,
Laurenz Albe
^ permalink raw reply [nested|flat] 7+ messages in thread
* RE: Ora2pg migration
@ 2024-03-25 19:06 Clay Jackson (cjackson) <Clay.Jackson@quest.com>
parent: Narendran .j <jnarendran3@gmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: Clay Jackson (cjackson) @ 2024-03-25 19:06 UTC (permalink / raw)
To: Narendran .j <jnarendran3@gmail.com>; Laurenz Albe <laurenz.albe@cybertec.at>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
You might find this section of the ora2pg documentation relevant. Note that the size of a bytea object is limited to < 1Gb,
Importing BLOB as large objects
By default Ora2Pg imports Oracle BLOB as bytea, the destination column is created using the bytea data type. If you want to use large object instead of bytea, just add the --blob_to_lo option to the ora2pg command. It will create the destination column as data type Oid and will save the BLOB as a large object using the lo_from_bytea() function. The Oid returned by the call to lo_from_bytea() is inserted in the destination column instead of a bytea. Because of the use of the function this option can only be used with actions SHOW_COLUMN, TABLE and INSERT. Action COPY is not allowed.
If you want to use COPY or have huge size BLOB ( > 1GB) than can not be imported using lo_from_bytea() you can add option --lo_import to the ora2pg command. This will allow to import data in two passes.
1) Export data using COPY or INSERT will set the Oid destination column for BLOB to value 0 and save the BLOB value into a dedicated file. It will also create a Shell script to import the BLOB files into the database using psql command \lo_import and to update the table Oid column to the returned large object Oid. The script is named lo_import-TABLENAME.sh
2) Execute all scripts lo_import-TABLENAME.sh after setting the environment variables PGDATABASE and optionally PGHOST, PGPORT, PGUSER, etc. if they do not correspond to the default values for libpq.
You might also execute manually a VACUUM FULL on the table to remove the bloat created by the table update.
Limitation: the table must have a primary key, it is used to set the WHERE clause to update the Oid column after the large object import. Importing BLOB using this second method (--lo_import) is very slow so it should be reserved to rows where the BLOB > 1GB for all other rows use the option --blob_to_lo. To filter the rows you can use the WHERE configuration directive in ora2pg.conf.
Also note:
LONGREADLEN
Use this directive to set the database handle's 'LongReadLen' attribute to a value that will be the larger than the expected size of the LOBs. The default is 1MB witch may not be enough to extract BLOBs or CLOBs. If the size of the LOB exceeds the 'LongReadLen' DBD::Oracle will return a 'ORA-24345: A Truncation' error. Default: 1023*1024 bytes.
Take a look at this page to learn more: http://search.cpan.org/~pythian/DBD-Oracle-1.22/Oracle.pm#Data_Interface_for_Persistent_LOBs
Important note: If you increase the value of this directive take care that DATA_LIMIT will probably needs to be reduced. Even if you only have a 1MB blob, trying to read 10000 of them (the default DATA_LIMIT) all at once will require 10GB of memory. You may extract data from those table separately and set a DATA_LIMIT to 500 or lower, otherwise you may experience some out of memory.
Good luck!
Clay Jackson
Database Solutions Sales Engineer
[cid:image001.jpg@01DA7EAB.873FBE60]<https://www.quest.com/solutions/database-performance-monitoring/;
clay.jackson@quest.com<mailto:clay.jackson@quest.com>
office 949-754-1203 mobile 425-802-9603
From: Narendran .j <jnarendran3@gmail.com>
Sent: Monday, March 25, 2024 6:47 AM
To: Laurenz Albe <laurenz.albe@cybertec.at>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: Ora2pg migration
CAUTION: This email originated from outside of the organization. Do not follow guidance, click links, or open attachments unless you recognize the sender and know the content is safe.
Hello
It's almost 8gb, could you explain me how can I transfer as binary file?.I'm new on this.
On Fri, 22 Mar 2024, 3:33 pm Laurenz Albe, <laurenz.albe@cybertec.at<mailto:laurenz.albe@cybertec.at>> wrote:
On Fri, 2024-03-22 at 11:08 +0530, Narendran .j wrote:
> I have export one table which has blob data type by ora2pg tool.while Importing insert file in postgres I'm getting this error .
> psql:ERROR: out of memory
> DETAIL: Cannot enlarge string buffer containing 1073741808 bytes by 8191 more bytes.
>
> Please give me any suggestions and I adjust shared_buffer it didn't work.
1GB is a hard limit for memory allocations.
How big are those BLOBs? The theoretical limit in PostgreSQL is 1GB, but in
practice things start getting difficult around 500MB, unless you use binary
transfer.
Yours,
Laurenz Albe
Attachments:
[image/jpeg] image001.jpg (38.5K, ../../CO1PR19MB4984C4B5022EEB36FD035CE59B362@CO1PR19MB4984.namprd19.prod.outlook.com/3-image001.jpg)
download | view image
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Ora2pg migration
@ 2024-03-25 19:12 Ron Johnson <ronljohnsonjr@gmail.com>
parent: Narendran .j <jnarendran3@gmail.com>
2 siblings, 0 replies; 7+ messages in thread
From: Ron Johnson @ 2024-03-25 19:12 UTC (permalink / raw)
To: Pgsql-admin <pgsql-admin@lists.postgresql.org>
I can't help asking: what in the heck are you storing that's *eight
gigabytes*???
On Mon, Mar 25, 2024 at 2:33 PM Narendran .j <jnarendran3@gmail.com> wrote:
> Hello
> It's almost 8gb, could you explain me how can I transfer as
> binary file?.I'm new on this.
>
> On Fri, 22 Mar 2024, 3:33 pm Laurenz Albe, <laurenz.albe@cybertec.at>
> wrote:
>
>> On Fri, 2024-03-22 at 11:08 +0530, Narendran .j wrote:
>> > I have export one table which has blob data type by ora2pg tool.while
>> Importing insert file in postgres I'm getting this error .
>> > psql:ERROR: out of memory
>> > DETAIL: Cannot enlarge string buffer containing 1073741808 bytes by
>> 8191 more bytes.
>> >
>> > Please give me any suggestions and I adjust shared_buffer it didn't
>> work.
>>
>> 1GB is a hard limit for memory allocations.
>>
>> How big are those BLOBs? The theoretical limit in PostgreSQL is 1GB, but
>> in
>> practice things start getting difficult around 500MB, unless you use
>> binary
>> transfer.
>>
>> Yours,
>> Laurenz Albe
>>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2024-03-25 19:12 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-03-22 05:38 Ora2pg migration Narendran .j <jnarendran3@gmail.com>
2024-03-22 10:03 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-03-25 13:46 ` Narendran .j <jnarendran3@gmail.com>
2024-03-25 15:30 ` Laurenz Albe <laurenz.albe@cybertec.at>
2024-03-25 19:06 ` Clay Jackson (cjackson) <Clay.Jackson@quest.com>
2024-03-25 19:12 ` Ron Johnson <ronljohnsonjr@gmail.com>
2024-03-25 14:27 ` Rajesh Kumar <rajeshkumar.dba09@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox