postgres_fdw

postgres_fdw is an open-source plug-in. Its code is released with the PostgreSQL source code. openGauss 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 openGauss using the installation package, you can directly use postgres_fdw without performing other operations.

NOTE: Currently, postgres_fdw supports only connection between openGauss 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 openGauss 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 openGauss, the table is not created on the remote openGauss database. You need to use the Oracle client to connect to the remote openGauss database to create a table.
The openGauss user used for executing CREATE USER MAPPING must have the permission to remotely connect to the openGauss 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 openGauss 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 openGauss database for execution. Instead, SELECT JOIN is divided into two SQL statements and transferred to the remote openGauss 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.