Objective to introduce
A problem with project development now is the need to access the Oracle database from PostgreSQL
As a slag-slag ape, a face is crazy. So consulted the company's database on the Daniel Han. Tell me to use ORACLE_FDW can be achieved, but in the implementation of the installation process, still a variety of problems, and trouble Han help me to see for a long time. Sorry, now for this installation process is recorded, because the deployment of the time to use the
Install ORACLE_FDW1. Download ORACLE_FDW
Click GitHub to download. Note that you want to download and install packages for your PostgreSQL version of the project. I am the download is not the same cause here for a long time.
2. Move the file to the PG installation path
Download completes the ZIP package decompression, the "Lib" Folder Oracle_fdw.dll and "Share/extension" directory of the three files are copied to the PostgreSQL installation directory "Lib" folder and "Share/extension" Directory.
3. Access Oracle via ORACLE_FDW
--创建访问oracle的连接create server oracle foreign data wrapper oracle_fdw options(dbserver '127.0.0.1:1521/orcl');--授予zhazha用户访问权限grant usage on foreign server oracle to zhazha;--创建到oracle的映射create user mapping for zhazha server oracle options(user 'SCOTT',password 'tiger');--创建需要访问的oracle中对应表的结构create foreign table emptest(EMPNO NUMBER(4),ENAME VARCHAR(10),JOB VARCHAR(9),MGR NUMBER(4),HIREDATE DATE,SAL NUMBER(7,2),COMM NUMBER(7,2),DEPTNO NUMBER(2)) server oracle options(schema 'SCOTT',table 'EMP');--在pg中访问oracle的表select * from emptest;
Below paste my query out of
Reference:
[1]:77772587
[2]:81700898
Win under Install ORACLE_FDW: Access Oracle database from PostgreSQL