{"id":5191,"date":"2015-08-19T06:17:01","date_gmt":"2015-08-19T04:17:01","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/connecting-your-postgresql-instance-to-an-oracle-database\/"},"modified":"2015-08-19T06:17:01","modified_gmt":"2015-08-19T04:17:01","slug":"connecting-your-postgresql-instance-to-an-oracle-database","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/connecting-your-postgresql-instance-to-an-oracle-database\/","title":{"rendered":"Connecting your PostgreSQL instance to an Oracle database"},"content":{"rendered":"

For integrating data from other systems PostgreSQL has the concept of foreign data wrappers<\/a>. Many of these exist for different types of systems. In this post I’ll look into how you may connect PostgreSQL to Oracle.<\/p>\n

The home of the foreign data wrapper for Oracle (as most of the PostgreSQL stuff) is on github<\/a>. If you do not want to clone the git repository you can download it from the PostgreSQL extension network<\/a>, too.<\/p>\n

For building oracle_fdw you’ll need to download the Basic and SDK package of the Oracle instance client<\/a>. For simplicity I’ll use the rpm versions.<\/p>\n

Make sure that pg_config is in your path and you have installed both the Oracle instant client basic and sdk packages:<\/p>\n

postgres@oel7:\/home\/postgres\/$ which pg_config\n\/u01\/app\/postgres\/product\/94\/db_1\/bin\/pg_config\npostgres@oel7:\/home\/postgres\/$ unzip oracle_fdw-1.2.0.zip\npostgres@oel7:\/home\/postgres\/$ sudo yum localinstall oracle-instantclient12.1-basic-12.1.0.2.0-1.x86_64.rpm oracle-instantclient12.1-devel-12.1.0.2.0-1.x86_64.rpm\npostgres@oel7:\/home\/postgres\/$ cd oracle_fdw-1.2.0\npostgres@oel7:\/home\/postgres\/oracle_fdw-1.2.0\/$ make\npostgres@oel7:\/home\/postgres\/oracle_fdw-1.2.0\/$ make install\n<\/pre>\n
Quite easy. Let’s see if it really works:<\/p>\n
postgres@oel7:\/home\/postgres\/oracle_fdw-1.2.0\/ [PG1] psql postgres\npsql (9.4.1dbi services)\nType \"help\" for help.\n\npostgres=# create extension oracle_fdw;\nERROR:  could not load library \"\/u01\/app\/postgres\/product\/94\/db_1\/lib\/oracle_fdw.so\": libclntsh.so.12.1: cannot open shared object file: No such file or directory\n<\/pre>\n
Ok, we need to set LD_LIBRARY_PATH to include the Oracle libraries and restart PostgreSQL:<\/p>\n
postgres@oel7:\/home\/postgres\/oracle_fdw-1.2.0\/$ export LD_LIBRARY_PATH=\/usr\/lib\/oracle\/12.1\/client64\/lib\/\npostgres@oel7:\/home\/postgres\/oracle_fdw-1.2.0\/$ pg_ctl -D \/u02\/pgdata\/PG1 restart -m fast\npostgres@oel7:\/home\/postgres\/oracle_fdw-1.2.0\/$ psql postgres\npsql (9.4.1dbi services)\nType \"help\" for help.\n\npostgres=# create extension oracle_fdw;\nCREATE EXTENSION\n<\/pre>\n
Better. Foreign data wrappers work by defining foreign servers and user mappings:<\/p>\n
postgres=# create server oracle foreign data wrapper oracle_fdw options (dbserver '\/\/192.168.22.242\/PROD.local' );\nCREATE SERVER\npostgres=# create user mapping for postgres server oracle options (user 'A', password 'a');\nCREATE USER MAPPING\n<\/pre>\n
My Oracle database has a user called “A” with “a” as the password. Now we need to create the foreign table that should look like the table in Oracle and once this is done we should be able to select:<\/p>\n
postgres=# create foreign table INTERVAL_SALES ( prod_id int not null, time_id date, amount_sold numeric) server oracle options (schema 'A', table 'INTERVAL_SALES');\nCREATE FOREIGN TABLE\npostgres=# select count(*) from INTERVAL_SALES;\n count \n-------\n     3\n(1 row)<\/pre>\n
Cool. We even can write to Oracle (Make sure to read the readme of oracle_fdw for understanding the “key” option which is mandatory for updates):<\/p>\n
 -- oracle\nSQL> select * from \"A\".\"INTERVAL_SALES\";\n\n   PROD_ID TIME_ID   AMOUNT_SOLD\n---------- --------- -----------\n\t 1 04-MAY-15\t       1\n\t 2 08-MAY-16\t       1\n\t 3 13-MAY-17\t       1\n-- postgres\npostgres=# insert into INTERVAL_SALES values ( 4, current_date, 5 );\nINSERT 0 1\n\n<\/pre>\n
Lets check the data on the Oracle side:<\/p>\n
--oracle\nSQL> select * from \"A\".\"INTERVAL_SALES\";\n\n   PROD_ID TIME_ID   AMOUNT_SOLD\n---------- --------- -----------\n\t 1 04-MAY-15\t       1\n\t 2 08-MAY-16\t       1\n\t 3 13-MAY-17\t       1\n\t 4 18-AUG-15\t       5\n<\/pre>\n
Looks good. You now have access to the data hosted in an Oracle database and can start working with it.<\/p>\n
Again: make sure you read the readme of oracle_fdw, especially the “problems” and “options” sections.<\/p>\n","protected":false},"excerpt":{"rendered":"
For integrating data from other systems PostgreSQL has the concept of foreign data wrappers. Many of these exist for different types of systems. In this post I’ll look into how you may connect PostgreSQL to Oracle. The home of the foreign data wrapper for Oracle (as most of the PostgreSQL stuff) is on github. If […]<\/p>\n","protected":false},"author":29,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[229],"tags":[77],"type_dbi":[],"class_list":["post-5191","post","type-post","status-publish","format-standard","hentry","category-database-administration-monitoring","tag-postgresql"],"acf":[],"yoast_head":"\nConnecting your PostgreSQL instance to an Oracle database - dbi Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/connecting-your-postgresql-instance-to-an-oracle-database\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Connecting your PostgreSQL instance to an Oracle database\" \/>\n<meta property=\"og:description\" content=\"For integrating data from other systems PostgreSQL has the concept of foreign data wrappers. Many of these exist for different types of systems. In this post I’ll look into how you may connect PostgreSQL to Oracle. The home of the foreign data wrapper for Oracle (as most of the PostgreSQL stuff) is on github. If […]\" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/connecting-your-postgresql-instance-to-an-oracle-database\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi Blog\" \/>\n<meta property=\"article:published_time\" content=\"2015-08-19T04:17:01+00:00\" \/>\n<meta name=\"author\" content=\"Daniel Westermann\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@westermanndanie\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Daniel Westermann\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/connecting-your-postgresql-instance-to-an-oracle-database\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/connecting-your-postgresql-instance-to-an-oracle-database\/\"},\"author\":{\"name\":\"Daniel Westermann\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"headline\":\"Connecting your PostgreSQL instance to an Oracle database\",\"datePublished\":\"2015-08-19T04:17:01+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/connecting-your-postgresql-instance-to-an-oracle-database\/\"},\"wordCount\":279,\"commentCount\":0,\"keywords\":[\"PostgreSQL\"],\"articleSection\":[\"Database Administration & Monitoring\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/connecting-your-postgresql-instance-to-an-oracle-database\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/connecting-your-postgresql-instance-to-an-oracle-database\/\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/connecting-your-postgresql-instance-to-an-oracle-database\/\",\"name\":\"Connecting your PostgreSQL instance to an Oracle database - dbi Blog\",\"isPartOf\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\"},\"datePublished\":\"2015-08-19T04:17:01+00:00\",\"author\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\"},\"breadcrumb\":{\"@id\":\"https:\/\/www.dbi-services.com\/blog\/connecting-your-postgresql-instance-to-an-oracle-database\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/www.dbi-services.com\/blog\/connecting-your-postgresql-instance-to-an-oracle-database\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/connecting-your-postgresql-instance-to-an-oracle-database\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\/\/www.dbi-services.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Connecting your PostgreSQL instance to an Oracle database\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#website\",\"url\":\"https:\/\/www.dbi-services.com\/blog\/\",\"name\":\"dbi Blog\",\"description\":\"\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66\",\"name\":\"Daniel Westermann\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g\",\"caption\":\"Daniel Westermann\"},\"description\":\"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.\",\"sameAs\":[\"https:\/\/x.com\/westermanndanie\"],\"url\":\"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Connecting your PostgreSQL instance to an Oracle database - dbi Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/connecting-your-postgresql-instance-to-an-oracle-database\/","og_locale":"en_US","og_type":"article","og_title":"Connecting your PostgreSQL instance to an Oracle database","og_description":"For integrating data from other systems PostgreSQL has the concept of foreign data wrappers. Many of these exist for different types of systems. In this post I’ll look into how you may connect PostgreSQL to Oracle. The home of the foreign data wrapper for Oracle (as most of the PostgreSQL stuff) is on github. If […]","og_url":"https:\/\/www.dbi-services.com\/blog\/connecting-your-postgresql-instance-to-an-oracle-database\/","og_site_name":"dbi Blog","article_published_time":"2015-08-19T04:17:01+00:00","author":"Daniel Westermann","twitter_card":"summary_large_image","twitter_creator":"@westermanndanie","twitter_misc":{"Written by":"Daniel Westermann","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/connecting-your-postgresql-instance-to-an-oracle-database\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/connecting-your-postgresql-instance-to-an-oracle-database\/"},"author":{"name":"Daniel Westermann","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"headline":"Connecting your PostgreSQL instance to an Oracle database","datePublished":"2015-08-19T04:17:01+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/connecting-your-postgresql-instance-to-an-oracle-database\/"},"wordCount":279,"commentCount":0,"keywords":["PostgreSQL"],"articleSection":["Database Administration & Monitoring"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/connecting-your-postgresql-instance-to-an-oracle-database\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/connecting-your-postgresql-instance-to-an-oracle-database\/","url":"https:\/\/www.dbi-services.com\/blog\/connecting-your-postgresql-instance-to-an-oracle-database\/","name":"Connecting your PostgreSQL instance to an Oracle database - dbi Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2015-08-19T04:17:01+00:00","author":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66"},"breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/connecting-your-postgresql-instance-to-an-oracle-database\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/connecting-your-postgresql-instance-to-an-oracle-database\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/connecting-your-postgresql-instance-to-an-oracle-database\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Connecting your PostgreSQL instance to an Oracle database"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi Blog","description":"","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/8d08e9bd996a89bd75c0286cbabf3c66","name":"Daniel Westermann","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/31350ceeecb1dd8986339a29bf040d4cd3cd087d410deccd8f55234466d6c317?s=96&d=mm&r=g","caption":"Daniel Westermann"},"description":"Daniel Westermann is Principal Consultant and Technology Leader Open Infrastructure at dbi services. He has more than 15 years of experience in management, engineering and optimization of databases and infrastructures, especially on Oracle and PostgreSQL. Since the beginning of his career, he has specialized in Oracle Technologies and is Oracle Certified Professional 12c and Oracle Certified Expert RAC\/GridInfra. Over time, Daniel has become increasingly interested in open source technologies, becoming \u201cTechnology Leader Open Infrastructure\u201d and PostgreSQL expert. \u00a0Based on community or EnterpriseDB tools, he develops and installs complex high available solutions with PostgreSQL. He is also a certified PostgreSQL Plus 9.0 Professional and a Postgres Advanced Server 9.4 Professional. He is a regular speaker at PostgreSQL conferences in Switzerland and Europe. Today Daniel is also supporting our customers on AWS services such as AWS RDS, database migrations into the cloud, EC2 and automated infrastructure management with AWS SSM (System Manager). He is a certified AWS Solutions Architect Professional. Prior to dbi services, Daniel was Management System Engineer at LC SYSTEMS-Engineering AG in Basel. Before that, he worked as Oracle Developper &\u00a0Project Manager at Delta Energy Solutions AG in Basel (today Powel AG). Daniel holds a diploma in Business Informatics (DHBW, Germany). His branch-related experience mainly covers the pharma industry, the financial sector, energy, lottery and telecommunications.","sameAs":["https:\/\/x.com\/westermanndanie"],"url":"https:\/\/www.dbi-services.com\/blog\/author\/daniel-westermann\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/5191","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/29"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=5191"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/5191\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=5191"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=5191"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=5191"},{"taxonomy":"type","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/type_dbi?post=5191"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}