Query data across databases via a network channel-PolarDB(PolarDB)-阿里云帮助中心

PolarDB for PostgreSQL provides a network channel feature that allows you to access data across databases. You can use methods such as FDW external tables, dblink for flexible and convenient cross-database data access. This topic describes how to perform these queries using a network channel.

Prerequisites

You have created a source cluster and a destination cluster. For more information, see Create a database cluster.

Note

The network channel feature for PolarDB for PostgreSQL supports the following scenarios:

The source cluster is a PolarDB for PostgreSQL cluster, and the destination database is in a PolarDB for PostgreSQL or PolarDB for PostgreSQL (Compatible with Oracle) cluster.
The source cluster is a PolarDB for PostgreSQL cluster, and the destination database is a self-managed PostgreSQL database on an ECS instance.

You have created databases in the source cluster and destination cluster. For more information, see Database management.
You have created a table in the database of the destination cluster.

Limitations

You can query data in the destination cluster only from the source cluster.

Procedure

Create a network channel.

PolarDB for PostgreSQL provides two ways to create a network channel:
- Create a network channel from a PolarDB for PostgreSQL cluster to a PolarDB cluster.
- Create a network channel from a PolarDB for PostgreSQL cluster to a self-managed PostgreSQL database.
Connect to the source cluster. For more information, see Connect to a database cluster.
After you create the network channel, use a supported method in the source cluster to query data from the destination cluster.

You can view the network channel name and its corresponding destination cluster in the console.

Examples

Access data using an FDW external table

Important

These steps require the postgres_fdw extension. To create the extension, run the CREATE EXTENSION command.

1. In the source cluster, create a server named myserver that uses the postgres_fdw Foreign Data Wrapper. The channel_name option specifies the network channel that you created in the console, such as 'chltest'. The dbname option specifies the database to access in the destination cluster, such as 'foodb'.

CREATE SERVER myserver FOREIGN DATA WRAPPER postgres_fdw OPTIONS (channel_name 'chltest', dbname 'foodb');

2. In the source cluster, create a user mapping for the user 'bob' on the server myserver. The user option specifies the user 'bob' used to access the destination cluster.

CREATE USER Mッピング FOR bob SERVER myserver OPTIONS (user 'bob', password 'mypassword');

3. In the source cluster, create a foreign table named foreign_table that uses the server myserver.

CREATE FOREIGN TABLE foreign_table (
 id integer NOT NULL,
 data text
)SERVER myserver
OPTIONS (table_name 'test');

4. In the destination database, run the following commands to create a table named test and insert sample data:

CREATE TABLE test(id integer NOT NULL, data text);
INSERT INTO test VALUES(1,'test');

5. Connect to the source database and run the following query:

SELECT * FROM foreign_table
id | data 
----+------
 1 | test
(1 row)

For more information, see DBLink and external tables.