Re: PostGreSql to Oracle

#1

Saaran Erap wrote:

Quote:

I am an Oracle and MS SQL DBA and application developer so I'm very
familiar with those two RDBMSs. But recently I've been tasked to look at
coming up with a means of moving data from a PostGreSQL database to an
Oracle database. This because the way the application that uses the
PostGreSql database works, there is a table that is essentially working as
a circular queue with the data being overwritten after a relatively short
amount of time. What I need to do is intercept the data as it is being
inserted and send it to an Oracle database for longer term storage.

Since I'm not even at the neophyte level with PostGreSQL, can someone tell
me if there is any good means of accomplishing this? Perhaps point me to
some web sites where a technique is described?

I'm pretty much ok with writing java, C, vb.NET, C# or even some other
language.

You can use DBI-Link ( http://dbi-link.projects.postgresql.org/ )
to connect to an Oracle database from within a PostgreSQL
database (via Perl:

BI and PostgreSQL functions in PL/Perl).

That way you could for example write a trigger that does something
in the Oracle database whenever a row is added.

This will probably be quite expensive and slow down every
session that writes to the queue table.

Another option is to write a trigger that "historizes" every
operation on the queue table into a secong history table.
Then you could regularly dump that table to a CSV file
with a COPY operation and load that fiel into Oracle with
SQL*Loader. Afterwards the history table could be truncated.

Maybe you could change the queue table so that instead of
being overwritten, it is regularly COPYed out and truncated.
That way you could do without a trigger.

Yours,
Laurenz Albe

#2

Saaran Erap ha scritto:

Quote:

I am an Oracle and MS SQL DBA and application developer so I'm very
familiar with those two RDBMSs. But recently I've been tasked to look at
coming up with a means of moving data from a PostGreSQL database to an
Oracle database. This because the way the application that uses the
PostGreSql database works, there is a table that is essentially working as
a circular queue with the data being overwritten after a relatively short
amount of time. What I need to do is intercept the data as it is being
inserted and send it to an Oracle database for longer term storage.

Since I'm not even at the neophyte level with PostGreSQL, can someone tell
me if there is any good means of accomplishing this? Perhaps point me to
some web sites where a technique is described?

I'm pretty much ok with writing java, C, vb.NET, C# or even some other
language.

Perhaps you can write a trigger that inserts data in Oracle via dblink,
see this:

http://www.postgresql.org/docs/8.3/s...l-trigger.html
http://www.postgresql.org/docs/8.3/s...link-exec.html

#3

On Fri, 16 Oct 2009 17:16:40 +0200, Laurenz Albe wrote:

Quote:

Another option is to write a trigger that "historizes" every operation
on the queue table into a secong history table. Then you could regularly
dump that table to a CSV file with a COPY operation and load that fiel
into Oracle with SQL*Loader. Afterwards the history table could be
truncated.

Maybe you could change the queue table so that instead of being
overwritten, it is regularly COPYed out and truncated. That way you
could do without a trigger.

Yours,
Laurenz Albe

Oracle can establish a database link to PostgreSQL, although this can be
tricky to do:

SQL> select * from "pg_database"@ho.world;

d datdba encoding d d d d datconnlimit datlastsysoid datfrozenxid
- ---------- ---------- - - - - ------------ ------------- ------------
dattablespace d d
------------- - -
10 6 -1 11563 648
1663

10 6 -1 11563 648
1663

10 6 -1 11563 648
1663

d datdba encoding d d d d datconnlimit datlastsysoid datfrozenxid
- ---------- ---------- - - - - ------------ ------------- ------------
dattablespace d d
------------- - -
10 6 -1 11563 648
1663

10 6 -1 11563 648
1663

10 6 -1 11563 648
1663

d datdba encoding d d d d datconnlimit datlastsysoid datfrozenxid
- ---------- ---------- - - - - ------------ ------------- ------------
dattablespace d d
------------- - -
16391 6 -1 11563 648
1663

7 rows selected.

SQL>

SQL> desc "pg_database"@ho.world
Name Null? Type
----------------------------------------- --------
----------------------------
datname VARCHAR2
datdba NUMBER(10)
encoding NUMBER(10)
datcollate VARCHAR2
datctype VARCHAR2
datistemplate CHAR
datallowconn CHAR
datconnlimit NUMBER(10)
datlastsysoid NUMBER(10)
datfrozenxid NUMBER(10)
dattablespace NUMBER(10)
datconfig VARCHAR2
datacl VARCHAR2

Essentially, one doesn't need to copy to and from, one can do a select
from oracle side and insert into an oracle table, like this:

SQL> create table emp as select * from "emp"@ho.world;

Table created.

And, of course, table emp is well known to all Postgres users:

[mgogala@lpo-postgres-01 ~]$ psql -U scott
psql (8.4.1)
Type "help" for help.

scott=> select * from emp;
empno | ename | job | mgr | hiredate | sal | comm |
deptno
-------+--------+-----------+------+---------------------+------+------
+--------
7369 | SMITH | CLERK | 7902 | 1980-12-17 00:00:00 | 800 |

Quote:

20
7499 | ALLEN | SALESMAN | 7698 | 1981-02-20 00:00:00 | 1600 | 300
30
7521 | WARD | SALESMAN | 7698 | 1981-02-22 00:00:00 | 1250 | 500
30
7566 | JONES | MANAGER | 7839 | 1981-04-02 00:00:00 | 2975 |
20
7654 | MARTIN | SALESMAN | 7698 | 1981-09-28 00:00:00 | 1250 | 1400
30
7698 | BLAKE | MANAGER | 7839 | 1981-05-01 00:00:00 | 2850 |
30
7782 | CLARK | MANAGER | 7839 | 1981-06-09 00:00:00 | 2450 |
10
7788 | SCOTT | ANALYST | 7566 | 1987-04-19 00:00:00 | 3000 |
20
7839 | KING | PRESIDENT | | 1981-11-17 00:00:00 | 5000 |
10
7844 | TURNER | SALESMAN | 7698 | 1981-09-08 00:00:00 | 1500 | 0
30
7876 | ADAMS | CLERK | 7788 | 1987-05-23 00:00:00 | 1100 |
20
7900 | JAMES | CLERK | 7698 | 1981-12-03 00:00:00 | 950 |
30
7902 | FORD | ANALYST | 7566 | 1981-12-03 00:00:00 | 3000 |
20
7934 | MILLER | CLERK | 7782 | 1982-01-23 00:00:00 | 1300 |
10
(14 rows)

scott=>

--
http://mgogala.freehostia.com

#4

On Sat, 24 Oct 2009 18:25:41 +0000, Saaran Erap wrote:

Quote:

But in that case what is the definition of ho.world? Is that an
alias/service name defined in tnsnames.ora or is that a database link?
If it is a database link how is it defined?

That's a database link, defined by using dg4odbc gateway.

--
http://mgogala.freehostia.com

#5

On Sat, 24 Oct 2009 22:58:20 +0000, Saaran Erap wrote:

Quote:

Now it is starting to make sense. I found some web sites that document
that utility and they should give me what I need. Thanks.

It takes some playing. You will have to suffer things like below:

SQL> select * from "emp"@pgsql;
select * from "emp"@pgsql
*
ERROR at line 1:
ORA-01017: invalid username/password; logon denied
[unixODBC]FATAL: no pg_hba.conf entry for host "192.168.1.100", user
"scott",
database "scott", SSL off {28000,NativeErr = 210}
ORA-02063: preceding 2 lines from PGSQL

Good luck!

--
http://mgogala.freehostia.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Re: PostGreSql to Oracle

comp.databases.postgresql comp.databases.postgresql

Re: PostGreSql to Oracle - 10-16-2009 , 10:16 AM

Re: PostGreSql to Oracle - 10-21-2009 , 03:12 AM

Re: PostGreSql to Oracle - 10-23-2009 , 08:33 AM

Re: PostGreSql to Oracle - 10-24-2009 , 01:38 PM

Re: PostGreSql to Oracle - 10-24-2009 , 07:36 PM