background:
The two systems of the same project use the PG library and the Oracle library respectively. Oracle is a production library and the data is updated dynamically. Now, the updated data needs to be obtained in real time for statistics in the PG library. Based on this method, it can be implemented through ETL tools, but it needs to be maintained regularly, etc. So I thought about whether it can be implemented through a method similar to the Oracle database DBLINK. After searching for relevant information online, I found that it can be implemented through oracle_fdw.
Test environment:
The test environment is built locally, and the basic configuration is as follows:
Oracle Database Test Server (IP: 192.168.1.110): WIN10 operating system, Oracle Database version is 11.2.0.4, the instance name is orcl, and a 32-bit client is installed;
PG library test server (virtual machine, IP: 192.168.30.128, NAT mode): WIN10 operating system, PG database version is 11.11.1;
Implementation steps:
1. First, determine the network. Usually, the PG library server can access the Oracle library server.
2. Install the PG library (steps omitted). It should be noted here that the installed PG library does not enable remote access. If remote access is required, you need to modify the pg_hba.conf file first and add the following content.
host all all 0.0.0.0/0 md5
3. Download oracle_fdw, note that when downloading, you need to match the version of the PG library.
Download address:Releases · laurenz/oracle_fdw · GitHub
What I downloaded here is a matching PG11 and select the Windows 64 location operating system.
Note: The fdw version must correspond to the PG library version and the operating system version, otherwise there will be problems later.
3. Unzip oracle_fdw and copy the files in the [lib] and [share/extension] folders to the corresponding [lib] and [share/extension] folders under the PG library installation path.
After copying, oracle_fdw can be queried through the SQL statement, indicating that the file copy is successfully placed, but has not been installed yet (issalled_version is empty).
select * from pg_available_extensions;
4. Install Oracle Client (steps omitted)
Don't rush to install oracle_fdw (the installation will not be successful either), because Oracle client support is also required. If the Oracle client is not installed, the following error message will be displayed.
Oracle client recommends that the same version is used as the connected Oracle server (there is no effect on the difference in the test version, and the large version has not been tested). In addition, you can also use the lightweight oracle instant client to replace it with the lightweight oracle instant client. I have not tried it here, if you are interested, you can try it.
After the installation is completed, please pay attention to conducting connection tests first to ensure that the connection is normal.
Note: The version of the client must be the same as that of the PG library. For example, I installed a 64-bit PG library, so I must install a 64-bit oracle client. I used to installing a 32-bit client before, but it cannot be opened after creating an external table, which prompts the following error.
If there is still a problem, you can check whether the installation path has been written to the Path variable and move it to the top.
5. Create oracle_fdw installation
-- createoracle_fdw create extension oracle_fdw;
After the installation is successful, verify it through the previous statement below.
select * from pg_available_extensions;
You can see that the installed_version has already shown the installation version, and verification means the installation is successful.
Note: If the installation fails multiple times, it is recommended to restart the PG service or the server and try again.
6. Create test data in Oracle library
Database connection information is as follows: 192.168.1.110/orcl Username/Password: GIS/GIS
-- Create test table create table ORACLEDATA_TEST ( ID NUMBER(10) not null, XZQMC NVARCHAR2(50), XZQDM NVARCHAR2(30) )
-- insert test data insert into oracledata_test values(1,'Shinan District','370202'); insert into oracledata_test values(2,'Shibei District','370203');
After adding test data, pay attention to submitting operations.
7. Create Oracle connection by PG library
--createOracleExternal connection，inoradb_110is the connection name create server oradb_110 foreign data wrapper oracle_fdw options(dbserver '192.168.1.110/orcl');
After creation, Oracle database data can be obtained through connection.
8. User authorization of PG library
--Authorization grant usage on foreign server oradb_110 to postgres;
Authorization is carried out according to actual needs.
9. Create a map to Oracle
--Create tooracleMapping of create user mapping for postgres server oradb_110 options(user 'GIS',password 'GIS');
where oradb_110 is the database connection name created previously, and GIS is the user name and password for connecting to Oracle.
10. Create a corresponding table that needs to access Oracle
Note that when creating here, you should pay attention to the conversion of field types. There are still differences in field types between Oracle and PG libraries. where oradb_110 is the database connection name we created above, and GIS is the connection.
--Create what you need to accessoracleThe structure of the corresponding table in create foreign table ORACLEDATA_TEST_PG ( ID numeric(10) not null, XZQMC VARCHAR(50), XZQDM VARCHAR(30) ) server oradb_110 options(schema 'GIS',table 'ORACLEDATA_TEST');
Note: The table created here does not obtain the fields in the oracle specified table like a view, but uses sequential mapping. The test description will be performed later.
11. You can now view Oracle's data through external tables.
If you need to delete the created content, you can use the following statement:
DROP FOREIGN TABLE table_name; DROP USER MAPPING FOR user_name SERVER server_name; DROP SERVER server_name;
11. Data synchronization test.
Insert a record in real time in oracle database
-- insert test data insert into oracledata_test values(3,'Licang District','370203');
After inserting the data, please submit it and then query and confirm.
Confirm query in PG library:
It can be seen that the data can be synchronized in real time.
12. Table mapping test.
For example, there are three fields in the test table now. If I only use the first and third fields in the PG library, then my external table will be built like this:
--Create what you need to accessoracleThe structure of the corresponding table in create foreign table ORACLEDATA_TEST_PG_2 ( ID numeric(10) not null, XZQDM VARCHAR(30) ) server oradb_110 options(schema 'GIS',table 'ORACLEDATA_TEST');
Then query the data:
From the results, we can see that the xzqdm we selected does not get the value of xzqdm, but the value of xzqmc, which is mapped in order, not through field names.
13. Performance
I have tested it in advance and the performance of large data volume is relatively low. There is no strict test in this area. I can add it later if I have the chance.
References:
Detailed explanation of the successful installation of oracle_fdw method of PostgreSQL, and solve the specified procedure could not be found error_ljinxin's blog - CSDN blog
PostgreSQL oracle_fdw installation and use - Kevin_zheng - Blog Park ()
This is the article about the implementation steps of PostgreSQL accessing Oracle data through oracle_fdw. This is the end. For more related PostgreSQL accessing Oracle data, please search for my previous articles or continue browsing the related articles below. I hope everyone will support me in the future!