Postgresql Foreign Data Wrapper

Faruk Erdem

6 years ago

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 extension postgres_fdw ;

1	create extension postgres_fdw ;

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.

CREATE SERVER c3 FOREIGN DATA WRAPPER postgres_fdw OPTIONS(host 'remote_postgresql_ip',port '5432');

1	CREATE SERVER c3 FOREIGN DATA WRAPPER postgres_fdw OPTIONS(host 'remote_postgresql_ip',port '5432');

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 USER MAPPING FOR postgres SERVER c3 OPTIONS (username 'postgres',password 'remote_postgres_user_password);

1	CREATE USER MAPPING FOR postgres SERVER c3 OPTIONS (username 'postgres',password 'remote_postgres_user_password);

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.

CREATE FOREIGN TABLE foreign_table (
        id integer NOT NULL,
        data text
)
        SERVER c3
        OPTIONS (schema_name 'your_remote_scheme_name',table_name 'your_remote_table_name');

CREATE FOREIGN TABLE foreign_table (

id integer NOT NULL,

data text

)

SERVER c3

OPTIONS (schema_name 'your_remote_scheme_name',table_name 'your_remote_table_name');

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

 \det+

\det+