Re: PostGreSql to Oracle
Laurenz Albe
>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::DBI 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
Coniglio Sgabbiato
> 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/static/plpgsql-trigger.html
http://www.postgresql.org/docs/8.3/static/contrib-dblink-exec.html
Mladen Gogala
> 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 |
| 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=>
Mladen Gogala
> 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.
Mladen Gogala
> 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!