Configure cross-database queries using FDW - AnalyticDB for PostgreSQL

By default, databases within the same AnalyticDB for PostgreSQL instance cannot access each other's data. Cross-database query removes this barrier by using a foreign data wrapper (FDW) so you can query data across databases without moving or copying it.

greenplum_fdw is built on the massively parallel processing (MPP) architecture and distributes query execution across compute nodes to improve data access efficiency for cross-database queries.

Supported versions

Deployment mode	Minimum version
V7.0 in elastic storage mode	V7.0.1.x
V6.0 in elastic storage mode	V6.3.11.2
Serverless mode	V1.0.6.x

Limitations

Foreign tables support SELECT and INSERT only. UPDATE and DELETE are not supported.
Join pushdown and aggregate pushdown are available only in V7.0 elastic storage mode.
Only the Orca optimizer (V7.0 elastic storage mode) can generate execution plans for foreign tables. V6.0 elastic storage mode and Serverless mode use the native optimizer instead.

Set up cross-database query

The following steps configure cross-database access so that db01 can query tables in db02:

Connect to the instance with psql.
Create the two databases.
Install the required extensions in both databases.
Prepare test data in db02.
Create a server and user mapping in db01.
Expose db02 tables to db01 as foreign tables.
Run a cross-database query.

Prerequisites

Before you begin, ensure that you have:

An AnalyticDB for PostgreSQL instance running a supported version
psql installed with network access to the instance
A database account with permissions to create databases and extensions

Step 1: connect to the instance

Connect to the instance using psql. For connection details, see Client connection.

Step 2: create the databases

CREATE DATABASE db01;
CREATE DATABASE db02;

Step 3: install extensions

Install both greenplum_fdw and gp_parallel_retrieve_cursor in each database. For instructions, see Install, update, and uninstall extensions.

Step 4: prepare test data in db02

\c db02
CREATE SCHEMA s01;
CREATE TABLE s01.t1(a int, b int, c text);
INSERT INTO s01.t1 VALUES(generate_series(1,10), generate_series(11,20), 't1');

Step 5: create a server and user mapping in db01

Switch to db01, then create a server that points to db02.

\c db01
CREATE SERVER db02 FOREIGN DATA WRAPPER greenplum_fdw
    OPTIONS (host 'localhost', dbname 'db02');

Parameter	Description
`host`	The endpoint for cross-database access. Always set to `localhost`.
`dbname`	The name of the source database. In this example, `db02`.

Next, create a user mapping that provides credentials for connecting to db02. For more information, see CREATE USER MAPPING.

CREATE USER MAPPING FOR CURRENT_USER SERVER db02
    OPTIONS (user 'report', password '******');

Parameter	Description
`user`	The database account used to connect to `db02`. The account needs read permissions on `db02`. For INSERT operations, write permissions are also required. To create a database account, see Create a database account.
`password`	The password for the database account.

Step 6: expose db02 tables as foreign tables

Choose one of the following methods based on your situation:

Use Method 1 if you need to select specific columns or customize the foreign table structure.
Use Method 2 if you need to import many tables quickly and can use the source table structure as-is.

Method 1: create foreign tables individually

Define each foreign table explicitly using CREATE FOREIGN TABLE. This lets you control which columns to expose—for example, exposing only a and b from a table that also contains c.

CREATE SCHEMA s01;
CREATE FOREIGN TABLE s01.t1(a int, b int)
    SERVER db02 OPTIONS (schema_name 's01', table_name 't1');

	Detail
Advantage	Custom column selection; no requirement to match source table DDL exactly.
Disadvantage	Requires knowing the DDL structure of each table; time-consuming for multiple foreign tables.

Method 2: import all tables from a schema

Use IMPORT FOREIGN SCHEMA to import multiple tables at once without specifying DDL. For more information, see IMPORT FOREIGN SCHEMA.

CREATE SCHEMA s01;
IMPORT FOREIGN SCHEMA s01 LIMIT TO (t1, t2, t3)
    FROM SERVER db02 INTO s01;

	Detail
Advantage	Fast bulk import; no need to know each table's DDL structure.
Disadvantage	Foreign tables must have the same names and column definitions as the source tables.

Step 7: query db02 data from db01

SELECT * FROM s01.t1;

Expected output:

 a  | b  | c
----+----+----
  2 | 12 | t1
  3 | 13 | t1
  4 | 14 | t1
  7 | 17 | t1
  8 | 18 | t1
  1 | 11 | t1
  5 | 15 | t1
  6 | 16 | t1
  9 | 19 | t1
 10 | 20 | t1
(10 rows)

AnalyticDB:Cross-database query