As you might know PostgreSQL 9.5 is currently in alpha 2. One of the new features will be the possibility to import foreign schemas. Having this
it is no longer required to create all the foreign tables which saves a lot of work.

Setting up oracle_fdw was done in exactly the same way as in the previous post. Make sure you use the latest version of oracle_fdw as some internals changed in PostgreSQL 9.5 which oracle_fdw depends on. The latest zipped version can be downloaded here

For this little demo I created the SCOTT user in my Oracle database ( $ORACLE_HOME/rdbms/admin/utlsampl.sql ):

1
2
3
4
5
6
7
8
9
10
11
12
SQL> show user
USER is "SCOTT"
SQL> select table_name from user_tables;
 
TABLE_NAME
--------------------------------------------------------------------------------
BONUS
DEPT
EMP
SALGRADE
 
SQL>

On the PostgreSQL side lets create a schema and setup oracle_fdw:

1
2
3
4
5
6
7
8
9
10
postgres@oel7:/home/postgres/ [PG6] psql postgres
psql (9.5alpha2)
Type "help" for help.
 
postgres=# create schema scott;
CREATE SCHEMA
postgres=# create server oracle foreign data wrapper oracle_fdw options (dbserver '//192.168.22.242/PROD.local' );
CREATE SERVER
postgres=# create user mapping for postgres server oracle options (user 'SCOTT', password 'tiger');
CREATE USER MAPPING

Before PostgreSQL 9.5 now would be the time to create the foreign tables as they are defined in the Oracle database. But starting with PostgreSQL 9.5 we are able to do this:

1
2
3
postgres=# import foreign schema "SCOTT" from server oracle into scott;
IMPORT FOREIGN SCHEMA
postgres=#

This should have imported all the table definitions:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
postgres=# set search_path='scott';
SET
postgres=# d
              List of relations
 Schema |   Name   |     Type      |  Owner  
--------+----------+---------------+----------
 scott  | bonus    | foreign table | postgres
 scott  | dept     | foreign table | postgres
 scott  | emp      | foreign table | postgres
 scott  | salgrade | foreign table | postgres
(4 rows)
 
postgres=# d+ dept
                                    Foreign table "scott.dept"
 Column |         Type          | Modifiers | FDW Options  | Storage  | Stats target | Description
--------+-----------------------+-----------+--------------+----------+--------------+-------------
 deptno | smallint              | not null  | (key 'true') | plain    |              |
 dname  | character varying(14) |           |              | extended |              |
 loc    | character varying(13) |           |              | extended |              |
Server: oracle
FDW Options: (schema 'SCOTT', "table" 'DEPT')
 
postgres=# d+ emp
                                          Foreign table "scott.emp"
  Column  |              Type              | Modifiers | FDW Options  | Storage  | Stats target | Description
----------+--------------------------------+-----------+--------------+----------+--------------+-------------
 empno    | smallint                       | not null  | (key 'true') | plain    |              |
 ename    | character varying(10)          |           |              | extended |              |
 job      | character varying(9)           |           |              | extended |              |
 mgr      | smallint                       |           |              | plain    |              |
 hiredate | timestamp(0) without time zone |           |              | plain    |              |
 sal      | numeric(7,2)                   |           |              | main     |              |
 comm     | numeric(7,2)                   |           |              | main     |              |
 deptno   | smallint                       |           |              | plain    |              |
Server: oracle
FDW Options: (schema 'SCOTT', "table" 'EMP')

What happens if we try to insert a row that violates the primary key on emp?

1
2
3
4
5
6
postgres=# insert into emp values (7639,'aa','aa',7902,current_date,123,11,20);
INSERT 0 1
postgres=# insert into emp values (7639,'aa','aa',7902,current_date,123,11,20);
ERROR:  error executing query: OCIStmtExecute failed to execute remote query
DETAIL:  ORA-00001: unique constraint (SCOTT.PK_EMP) violated
postgres=#

Really, really cool. One command to get a whole schema definition out of an Oracle database. Makes life a lot easier.

Post Views: 1,620
Thumbnail [60x60]
by
Daniel Westermann