search

darold / ora2pg

Ora2Pg is a free tool used to migrate an Oracle database to a PostgreSQL compatible schema. It connects your Oracle database, scan it automatically and extracts its structure or data, it then generates SQL scripts that you can load into PostgreSQL.
http://www.ora2pg.com/
GNU General Public License v3.0
978 stars 341 forks source link

Get `server closed the connection unexpectedly` error during COPY of oracle BLOB to postgresql bytea #1767

Open taneraruk opened 2 months ago

taneraruk commented 2 months ago

I have 2 different database one is dev and the other one is test. While copying dev database to the same postgresql db there is no issue. but while copying test database to the same postgresql db during COPY process of oracle blob data which contains json strings to postgresql database bytea column I get the error below:

DEBUG: Formatting bulk of 125 data (real: 33 rows) for PostgreSQL. DEBUG: Creating output for 125 tuples DEBUG: Sending COPY bulk output directly to PostgreSQL backend Extracted records from table XXX: total_records = 1537658 (avg: 139 recs/sec) FATAL: server closed the connection unexpectedly This probably means the server terminated abnormally before or while processing the request.

when I check the database everything is copied successfully but at the end of the process it throws FATAL error.

config:

oracle connection

ORACLE_DSN dbi:Oracle:host=xxx ORACLE_USER xxx ORACLE_PWD xxx

data export to PG db

PG_DSN dbi:Pg:host=xxx PG_USER xxx PG_PWD xxx PG_SCHEMA xxx PG_VERSION 15

schema to export

SCHEMA xxx EXPORT_SCHEMA 1

limiting objects

ALLOW xxx ENABLE_BLOB_EXPORT 1

psql import

DATA_LIMIT 125 FILE_PER_CONSTRAINT 1 FILE_PER_INDEX 1 FILE_PER_FKEYS 1 FILE_PER_TABLE 1 FILE_PER_FUNCTION 1 TRUNCATE_TABLE 1 STOP_ON_ERROR 1

column type

PG_NUMERIC_TYPE 0 PG_INTEGER_TYPE 0 DEFAULT_NUMERIC bigint TO_NUMBER_CONVERSION numeric ENABLE_MICROSECOND 1 TO_CHAR_NOTIMEZONE 1

# LOB_CHUNK_SIZE 512000 KEEP_PKEY_NAMES 1 DISABLE_PARTITION 1 USE_RESERVED_WORDS 0 DISABLE_UNLOGGED 1 REWRITE_OUTER_JOIN 1

DEBUG 1

parallel

JOBS 1 ORACLE_COPIES 1

ora2pg version: 24.1

"PostgreSQL 15.5 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit"

I would appreciate if you help.

darold commented 2 months ago

If all data are fully imported this is a false positive, maybe a connection timeout. Have you set idle_session_timeout on PostgreSQL side?