Postgresql Foreign Data Wrapper

Postgresql is one of the RDMS databases presented in simple form. Therefore, we need to install the extension to use dblink, Foreign Data Wrapper, and so on.

Foreign Data Wrapper allows you to connect to different remote systems and query and write over remote systems. The Foreign Data Wrapper feature is available in version 9.1 of Postgresql and above.

With Fdw (Foreing Data Wrapper), different extensions must be installed to read and write data from different systems. Some of these are as follows.

• PostgreSQL – PostgreSQL => postgres_fdw
• PostgreSQL – MSSQL        => tds_fdw
• PostgreSQL – Redis            => redis_fdw
• PostgreSQL – MySQL        => Mysql_fdw
• PostgreSQL – MongoDB   => Mongo_fdw

In this article we will create Foreign Data Wrapper between PostgreSQL servers.

If you want to create Foreign Data Wrapper between (PostgreSQL and MSSQL) or (PostgreSQL and Oracle), you should read the below articles:

“Foreign Data Wrapper Between PostgreSQL and MSSQL“,

“Foreign Data Wrapper Between PostgreSQL and Oracle”

CREATE EXTENSION:

We are installing the extension package, which is the first step for Foreing Data Wrapper, as follows.

CREATE SERVER:

After the extension package is installed, we run the following command in the first server so that we can access remote postgresql with foreign data wrapper. This command must be run with a user with superuser privilege.

USER MAPPING:

With the command above, we created the server with remote postgresql’s connection information.

Now we specify which user to access.

I used the postgres user in local and remote PostgreSQL. You can set different users for local and remote PostgreSQL.

CREATE FOREING TABLE:

Then, we create a foreing table to see a table in the remote PostgreSQL from PostgreSQL on the first server with the help of the following script.

And we have created our foreign table. You can use the following command to check.