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.

The postgres_fdw plug-in is involved in compilation by default. After installing MogDB using the installation package, you can directly use postgres_fdw without performing other operations.

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

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.
• Create a foreign table using CREATE FOREIGN TABLE. The structure of the foreign table must be the same as that of the remote MogDB table.
• Perform normal operations on the foreign table, such as INSERT, UPDATE, DELETE, SELECT, EXPLAIN, ANALYZE and COPY.
• 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.