postgres_fdw

postgres_fdw is an open-source plug-in. Its code is released with the PostgreSQL source code. MogDB is developed and adapted based on the open-source postgres_fdw source code (https://ftp.postgresql.org/pub/source/v9.4.26/postgresql-9.4.26.tar.gz) in PostgreSQL 9.4.26. postgres_fdw is provided as an extension.

NOTE: Currently, postgres_fdw supports only connection between MogDB databases.

Install postgres_fdw

postgres_fdw is involved in the compilation by default, and can be used by creating the extension through the CREATE EXTENSION postgres_fdw; statement after logging into the database.

You can also install postgres_fdw separately by following these steps.

1. Access Download page of the MogDB official website, download the postgres_fdw extension for the version you need.

2. Unpack the package, for example:

   tar -xzvf postgres_fdw-1.0-5.0.0-01-Kylin-x86_64.tar.gz

3. Go to the directory where the extension is located and execute the make install command.

   cd postgres_fdw/
   make install

4. Connect to the database and execute the create extension postgres_fdw; to use it.

   MogDB=# create extension postgres_fdw;
   CREATE EXTENSION

Using postgres_fdw

• Load the postgres_fdw extension using CREATE EXTENSION postgres_fdw;

• Create a server object using CREATE SERVER

• Create a user mapping using CREATE USER MAPPING

• DML operations on external tables can be performed directly using the @ symbol plus server_name, i.e. <table_name>@<server_name>.

  Note: You can alse create a foreign table using CREATE FOREIGN TABLE, then perform normal operations on the foreign table, such as INSERT, UPDATE, DELETE, SELECT, EXPLAIN, ANALYZE and COPY. The structure of the foreign table must be the same as that of the remote MogDB table.

• Drop a foreign table using DROP FOREIGN TABLE

• Drop a user mapping using DROP USER MAPPING

• Drop a server object using DROP SERVER

• Drop an extension using DROP EXTENSION postgres_fdw;

Common Issues

• When a foreign table is created on the MogDB, the table is not created on the remote MogDB database. You need to use the Oracle client to connect to the remote MogDB database to create a table.
• The MogDB user used for executing CREATE USER MAPPING must have the permission to remotely connect to the MogDB database and perform operations on tables. Before using a foreign table, you can use the gsql client on the local machine and use the corresponding user name and password to check whether the remote MogDB database can be successfully connected and operations can be performed.

Precautions

• SELECT JOIN between two postgres_fdw foreign tables cannot be pushed down to the remote MogDB database for execution. Instead, SELECT JOIN is divided into two SQL statements and transferred to the remote MogDB database for execution. Then the processing result is summarized locally.
• The IMPORT FOREIGN SCHEMA syntax is not supported.
• CREATE TRIGGER cannot be executed for foreign tables.