Use remote AnalyticDB data source access to perform cross-instance queries -

You can use the remote AnalyticDB data source access feature of AnalyticDB for PostgreSQL to access data of other AnalyticDB for PostgreSQL instances that belong to the same Alibaba Cloud account as your instance. This feature allows you to perform joint queries on external data sources, which ensures data timeliness and reduces data redundancy.

A cross-instance query involves at least two instances. For the sake of distinction, the instance that initiates a query is referred to as the local instance, and the instance that serves as an external data source is referred to as the remote instance.

Supported versions

AnalyticDB for PostgreSQL V6.0: V6.6.0.0 or later.
AnalyticDB for PostgreSQL V7.0: V7.0.3.0 or later.
AnalyticDB for PostgreSQL in Serverless mode: V2.1.1.5 or later.

Note

For information about how to view the minor version of an AnalyticDB for PostgreSQL instance, see View the minor version of an instance. If your AnalyticDB for PostgreSQL instance does not meet the preceding requirements, we recommend that you update the minor version of the instance. For more information, see UpgradeDBVersion.

Prerequisites

The local and remote instances must reside in the same virtual private cloud (VPC).

Preparations

In this example, a cross-instance query is performed on the local instance A and the remote instance B. You can access tables in the remote_db database of the remote instance B from the local_db database of the local instance A for joint queries.

Create initial accounts for the local instance A and the remote instance B.
Add the IP address or CIDR block of the client to the IP address whitelists of the local instance A and the remote instance B.
Prepare test data.
- On the local instance A, create a database and a schema for creating external tables and accessing data of remote instance B.
  1. Connect to the local instance A.
```
psql -h gp-bp166cyrtr4p*****-master.gpdb.rds.aliyuncs.com -p 5432 -d postgres -U gpdbaccount
```
    If your Elastic Compute Service (ECS) instance does not reside in the same VPC as the AnalyticDB for PostgreSQL instance, or if you connect to the AnalyticDB for PostgreSQL instance from an on-premises device, use the public endpoint of the instance.
  2. On the local instance A, create a database named local_db and switch to the local_db database.
```
CREATE DATABASE local_db;
\c local_db
```
  3. Create a schema in the local_db database of the local instance A.
```
CREATE SCHEMA s02;
```
- On the remote instance B, create a database and tables for querying table data of the remote instance B from the local instance A.
  1. Connect to the remote instance B in the same manner as you connect to the local instance A.
  2. On the remote instance B, create a database named remote_db and switch to the remote_db database.
```
CREATE DATABASE remote_db;
\c remote_db
```
  3. Prepare test data in the remote_db database of the remote 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');
```

Procedure

Step 1: Add a data source to the local instance

Log on to the AnalyticDB for PostgreSQL console. Find the local instance A and click the instance ID.
In the left-side navigation pane, click External Data Source Management.

Click the Remote AnalyticDB Data Source Access tab and click Add Data Source. In the panel that appears, configure the parameters that are described in the following table.

Parameter	Description
Local Instance ID	The ID of the local instance A. This parameter is automatically specified.
Local Database Name	The name of the database on the local instance A. In this example, `local_db` is used.
Local Initial Account	The initial account of the local instance A. If you have created an initial account, you do not need to specify this parameter.
Local Initial Password	The password of the initial account.
Remote Instance ID	The ID of the remote instance B.
Remote Database Name	The name of the database on the remote instance B. In this example, `remote_db` is used.
Remote Initial Account	The initial account of the remote instance B.
Remote Initial Password	The password of the initial account.
Data Source Name	The name of the data source. Example: example_name.

Click OK. After the connection status of the data source changes to Running, you can query data across instances.
Note
- After you add a data source, the connection status of the data source changes to Adding Remote AnalyticDB Data Source. This process requires approximately less than 1 minute and does not affect instance creation or data read/write operations.
- After a data source is added, you can perform the following operations:
  - Click Edit in the Actions column to change the database account and password of the remote instance.
  - Click Remove in the Actions column to remove the data source.

Step 2: Query data across instances

Important

If the remote AnalyticDB for PostgreSQL instance is in Serverless mode, data of the instance in the scaling state cannot be accessed.

Connect to the local instance A and switch to the s02 schema of the local_db database.

In the s02 schema of the local instance A, create foreign tables for the t1, t2, and t3 tables of the remote instance B.

Important

The schema in the local instance cannot contain tables that have the same names as the remote tables. Otherwise, the operation fails.

Import multiple tables at a time

Syntax:

IMPORT FOREIGN SCHEMA remote_schema -- The name of the schema in the remote instance.
    [LIMIT TO ( table_name [, ...] ) ] -- The names of tables in the remote instance.
    FROM SERVER server_name -- The name of the data source configured in Step 1.
    INTO local_schema -- The name of the schema in the local instance.

Example:

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

Note

Refer to IMPORT FOREIGN SCHEMA for more syntax details.

Import a single table

Syntax:

CREATE FOREIGN TABLE [ IF NOT EXISTS ] table_name ( [ -- The name of the table in the remote instance.
  { column_name data_type } ] -- The table structure of the remote instance.
    [, ... ] )
  SERVER server_name -- The name of the data source configured in Step 1.
[ OPTIONS ( option 'value' [, ... ] ) ] -- The names of the schema and the table in the local instance.

Example:

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

Note

Refer to CREATE FOREIGN TABLE for more syntax details.

Query data of the t1 table in the remote_db database of the remote instance B from the local_db database of the local instance A.
Note
AnalyticDB for PostgreSQL does not allow you to access data in the Database.Schema.Table format.
```
SELECT * FROM s02.t1;
```
Sample result:
```
 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)
```

References

Cross-database query: describes how to query data across databases in the same instance.