AnalyticDB:Cross-instance queries

Supported versions

Step 1: Connect to both instances

Connect to Instance A and Instance B using psql. See Client connection for instructions.

Step 2: Create databases

Create a database on each instance and switch to it.

On Instance A:

CREATE DATABASE db01;
\c db01

On Instance B:

CREATE DATABASE db02;
\c db02

Step 3: Install required extensions

Install the greenplum_fdw and gp_parallel_retrieve_cursor extensions on both instances — in the db01 database on Instance A and in the db02 database on Instance B. Both instances must have the same extensions installed for cross-instance queries to work. See Install, update, and uninstall extensions for instructions.

Step 4: Add Instance A's IP address to Instance B's whitelist

Run the following statement on Instance A to get its internal IP address:

SELECT dbid, address FROM gp_segment_configuration;

Add the returned IP address to a whitelist on Instance B. See Configure an IP address whitelist for instructions.

Step 5: Prepare test data on Instance B

Run the following statements on the db02 database of Instance B:

CREATE SCHEMA s01;

CREATE TABLE s01.t1(a int, b int, c text);
CREATE TABLE s01.t2(a int, b int, c text);
CREATE TABLE s01.t3(a int, b int, c text);

INSERT INTO s01.t1 VALUES(generate_series(1,10),generate_series(11,20),'t1');
INSERT INTO s01.t2 VALUES(generate_series(11,20),generate_series(11,20),'t2');
INSERT INTO s01.t3 VALUES(generate_series(21,30),generate_series(11,20),'t3');

Step 6: Create a server and user mapping on Instance A

Run the following statements on the db01 database of Instance A.

Create a server — defines the connection to Instance B:

CREATE SERVER remote_adbpg FOREIGN DATA WRAPPER greenplum_fdw
    OPTIONS (host 'gp-xxxxxxxx-master.gpdb.zhangbei.rds.aliyuncs.com',
             port '5432',
             dbname 'db02');

Create a user mapping — sets the credentials used to access Instance B. See CREATE USER MAPPING for the full syntax.

CREATE USER MAPPING FOR PUBLIC SERVER remote_adbpg
    OPTIONS (user 'report', password '******');

Step 7: Import foreign tables

Choose one of the following methods to expose Instance B's tables in Instance A.

Option 1: CREATE FOREIGN TABLE — import individual tables with custom column selection

CREATE SCHEMA s01;
CREATE FOREIGN TABLE s01.t1(a int, b int)
    server remote_adbpg options(schema_name 's01', table_name 't1');

This lets you expose only a subset of columns. In the example above, t1 has three columns on Instance B (a, b, c), but only a and b are imported. Each table requires a separate DDL statement.

Option 2: IMPORT FOREIGN SCHEMA — import all tables from a schema at once

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

This imports multiple tables in one statement without writing DDL for each. Foreign table columns must match the source tables exactly. See IMPORT FOREIGN SCHEMA for the full syntax.

Step 8: Query remote data

Run a query on Instance A to read data from Instance B:

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)