Re: [GENERAL] Oracle to PostgreSQL Migration. - Mailing list pgsql-general
| From | PAWAN SHARMA |
|---|---|
| Subject | Re: [GENERAL] Oracle to PostgreSQL Migration. |
| Date | |
| Msg-id | CAPgXFMQbS+rBQpLNa0LDf49xmpyqHZ5EDzJyXY8521bm-1v=Bg@mail.gmail.com Whole thread Raw |
| In response to | Re: [GENERAL] Oracle to PostgreSQL Migration. (Chris Mair <chris@1006.org>) |
| Responses |
Re: [GENERAL] Oracle to PostgreSQL Migration.
|
| List | pgsql-general |
On Mon, May 29, 2017 at 6:28 PM, Chris Mair <chris@1006.org> wrote:
C:\ora2pg>ora2pg -c ora2pg.conf
[========================>] 2/2 tables (100.0%) end of scanning.
[> ] 0/2 tables (0.0%) end of scanning.
[========================>] 2/2 tables (100.0%) end of table export.
Looks good so far.
This means you could connect to Oracle DB now.DBD::Pg::st execute failed: ERROR: relation "mytab" does not exist
This is coming from the Postgres side.
In ora2pg.conf go to the section
OUTPUT SECTION (Control output to file or PostgreSQL database)
I suggest you comment out (prefix with #) the part
#PG_DSN dbi:Pg:dbname=test_db;host=localhost;port=5432
#PG_USER test
#PG_PWD test
and just have ora2pg write its ouput to a file by setting OUTPUT like this:
OUTPUT output.sql
This way you have your oputput for Postgres in a file that you can check out
and try importing step by step. I guess you are running this on some test
data, so the file will be small enough to open it with an editor.
You cap paste piece by piece into a Postgres prompt (psql or pgadmin or whatever
you're using).
You can then see at what point you get an error (and hopefully understand
what's happening).
Bye,
Chris.
Hi Chris,
Thanks for suggestion.!!!
here in ora2pg.conf, I have used below type in ora2pg.conf and create the table manually on PostgreSQL server.
TYPE TABLE
output after this
-------------------------------------
C:\ora2pg>ora2pg -c ora2pg.conf
Ora2Pg version: 18.1
Trying to connect to database: dbi:Oracle:host=<servername>;sid=<mysid>;port=1521
Isolation level: SET TRANSACTION ISOLATION LEVEL READ COMMITTED
Retrieving table information...
[1] Scanning table MYTAB (1 rows)...
[2] Scanning table TEST (1 rows)...
Dumping table TEST...
Dumping table MYTAB...
-- Generated by Ora2Pg, the Oracle database Schema converter, version 18.1
-- Copyright 2000-2017 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=<servername>;sid=<mysid>;port=1521
SET client_encoding TO 'UTF8';
\set ON_ERROR_STOP ON
CREATE TABLE test (
id bigint,
name varchar(30)
) ;
CREATE TABLE mytab (
id bigint,
name varchar(30),
dt timestamp
) ;
but, when i am trying to insert data using
TYPE TABLE, INSERT
C:\ora2pg>ora2pg -c ora2pg.conf
[========================>] 2/2 tables (100.0%) end of scanning.
[> ] 0/2 tables (0.0%) end of scanning.
[========================>] 2/2 tables (100.0%) end of table export.
-- Generated by Ora2Pg, the Oracle database Schema converter, version 18.1
-- Copyright 2000-2017 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=<servername>;sid=<mysid>;port=1521
SET client_encoding TO 'UTF8';
\set ON_ERROR_STOP ON
CREATE TABLE mytab (
id bigint,
name varchar(30),
dt timestamp
) ;
CREATE TABLE test (
id bigint,
name varchar(30)
) ;
[========================>] 1/1 rows (100.0%) Table MYTAB (0 recs/sec)
[============> ] 1/2 total rows (50.0%) - (4 sec., avg: 0 recs/sec).
Out of memory! ] 1/2 rows (50.0%) on total estimated data (4 sec., avg: 0 recs/sec)
Issuing rollback() due to DESTROY without explicit disconnect() of DBD::Oracle::db handle (DESCRIPTION=(ADDRESS=(PO
RT=1521)(PROTOCOL=tcp)(HOST=<servername>))(CONNECT_DATA=(SID=<mysid>))) at C:/Strawberry/perl/vendor/
lib/DBD/Oracle.pm line 348.
So, It will show data enter in mytab 100% but in test it is 50%. but when I checked on PostgreSQL server their data only in one table.
test=# select * from mytab ;
id | name | dt
----+------+---------------------
1 | aa | 2017-05-29 06:05:46
(1 row)
test=# select * from test;
id | name
----+------
(0 rows)
So, I am stuck here..!!! Please suggest.
pgsql-general by date: