Use a database link to query data across databases - PolarDB

PolarDB for PostgreSQL(Compatible with Oracle) provides the database link (DBLink) feature to query data across databases. This topic describes how to use a database link to query data across databases.

Prerequisites

A source cluster and a destination cluster are created. For more information, see Create a PolarDB for PostgreSQL(Compatible with Oracle) cluster.
Note The DBLink feature of PolarDB for PostgreSQL(Compatible with Oracle) supports the following connection methods:
- The source database is in a PolarDB for PostgreSQL(Compatible with Oracle) cluster and the destination database is in a PolarDB for PostgreSQL(Compatible with Oracle) cluster.
- The source database is in a PolarDB for PostgreSQL(Compatible with Oracle) cluster. The destination database is a self-managed PostgreSQL database hosted on an ECS instance.
Databases are created in the source and destination clusters. For more information, see Create a database.
Tables are created in the destination database.

Note

You can query data of the destination cluster only from the source cluster. You cannot query data of the source cluster from the destination cluster.

Parameter configuration

PolarDB for PostgreSQL(Compatible with Oracle) provides the polar_enable_pushable_unsafe_collate_remote and polar_enable_pushable_all_any_remote parameters to improve query performance.

polar_enable_pushable_unsafe_collate_remote: specifies whether to push down the functions that do not meet collation requirements. This parameter is a session-level parameter. The default value of this parameter is on.
If you set this parameter to off, the functions that do not meet collation requirements are not pushed down.
- To set this parameter to on, run the following command:
```
SET polar_enable_pushable_unsafe_collate_remote = on;
```
- To set this parameter to off, run the following command:
```
SET polar_enable_pushable_unsafe_collate_remote = off;
```
polar_enable_pushable_all_any_remote: specifies whether to push down ANY() and ALL() expressions. This parameter is a session-level parameter. The default value of this parameter is on.
If you set this parameter to off, the ALL/ANY expressions that do not meet requirements are not pushed down.
- To set this parameter to on, run the following command:
```
SET polar_enable_pushable_all_any_remote = on;
```
- To set this parameter to off, run the following command:
```
SET polar_enable_pushable_all_any_remote = off;
```

Procedure

Create a database link.
PolarDB for PostgreSQL(Compatible with Oracle) provides the following methods to create database links:
- Create a database link from PolarDB for PostgreSQL(Compatible with Oracle) to PolarDB for PostgreSQL(Compatible with Oracle).
- Create a database link from PolarDB for PostgreSQL(Compatible with Oracle) to PostgreSQL.
Connect to the source cluster. For more information, see Connect to a PolarDB cluster.
Query data across databases from the source cluster.
Execute the following query statement:
```
SELECT * FROM <dbname>@<dblinkname>;
```
- <dbname>: the table in the database of the destination cluster.
  Note The table must be stored in the destination database to which the database link is connected.
- <dblinkname>: the name of the database link.
You can view the name of the database link and the destination database to which the database link is connected in the console, as shown in the following figure.

Examples

To create a table named test in the destination database for testing and insert test data into the table, execute the following statements:

CREATE TABLE test(id int);
INSERT INTO test VALUES(1);

Connect to the source database and execute the following query statement:

SELECT * FROM test@dblinkname;

The following query result is returned:

 id
----
  1
(1 row)