An important group of extensions are the Foreign Data Wrappers . PostgreSQL itself does not have any constructs such as the database links from Oracle. External sources are made accessible using such Foreign Data Wrappers. In the simplest case, for example, there is a wrapper for connecting to other PostgreSQL databases, postgres_fdw . There are also Data Wrappers for connecting file-based sources.
Generally, it is warned that many foreign data wrappers are not officially supported or are in some cases not yet fully developed. Therefore, they should be tested intensively before you want to build productive scenarios on such extensions.
Due to the trend of austria telegram screening migrating Oracle databases to PostgreSQL, the oracle_fdw serves as a representative example [2] . It enables read and write access to Oracle databases from a PostgreSQL database. The extension must be installed additionally and at least one Oracle instant_client is required. The corresponding libraries for the oracle_fdw are linked from this Oracle client (see Listing 2).
Listing 2: Installation of oracle_fdw using CentOS Linux as an example
If the extension has been activated in the database, you can declare an Oracle database as a server using a connection string . You can then grant access to the roles and map the other side locally in the Oracle database using user mapping and the creation of a foreign table. As described in the listing, you can then access and change the data in the Oracle database from the PostgreSQL database using the usual SQL statements.
To simplify the laborious task of recreating the tables, the oracle_fdw extension also offers the option of transferring the complete schema definition of the Oracle user. This saves you having to specify the foreign table, which, depending on the scope of a schema, significantly reduces the effort involved.