How to use postgres_fdw and the dblink plugin to implement cross-database query in apsaradb RDS for PostgreSQL -

Disclaimer: This article may contain information about third-party products. Such information is for reference only. Alibaba Cloud does not make any guarantee, express or implied, with respect to the performance and reliability of third-party products, as well as potential impacts of operations on the products.

Overview

This article describes how to use postgres_fdw and dblink plug-ins to implement cross-database queries for apsaradb RDS for PostgreSQL instances.

Background information

Note:

postgres_fdw and dblink do not support cross-database queries in versions earlier than RDS PostgreSQL 11.

The SQL statement for creating an extension requires different accounts in different versions. There are no restrictions on accounts for PostgreSQL 9, as long as it is a high-privilege account, or has the createdb and createrole permissions. In PostgreSQL 10 and later versions, you must use an account that is created in the PostgreSQL console.

When you perform operations that have risks, such as modifying instance configurations or data, check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.

Before you modify the configurations and data of instances including but not limited to ECS and RDS instances, we recommend that you create snapshots or enable RDS log backup.

If you have authorized or submitted security information such as the logon account and password in the Alibaba Cloud Management console, we recommend that you modify such information in a timely manner.

Use postgres_fdw to query data across databases

You can use postgres_fdw to query data across databases as follows:

Install postgres_fdw

Log on to the RDS for PostgreSQL instance and run the following SQL statement to install the postgres_fdw plug-in.
```
create extension postgres_fdw
```
Run the following SQL statement to query all extensions on the RDS for PostgreSQL instance and confirm that the postgres_fdw plug-in has been successfully installed:
```
select * from pg_available_extensions
```

Create remote Server Server

Run the following SQL statement to create a remote Server:
```
create server [$Server_Name] FOREIGN data wrapper postgres_fdw OPTIONS(host '[$Host_Name]', port '[$Port]', dbname '[$DB]');
```
Note:
- [$Server_Name] is the name of the remote Server.
- [$Host_Name] is the intranet domain name of the other instance.
- [$Port] is the internal listening Port of another instance.
- [$DB] is the name of another database that requires remote access.
Run the following SQL statement to confirm that the application is created:
```
SELECT * from pg_foreign_server
```

Create user matching information

Run the following SQL statement to create a user for the remote Server:

create user mapping for [$Local_User] server [$Server_Name] options(user '[$User]',password '[Password]');

Note:

[$Local_User] is the username of the current logon.

[$User] is the User name of the remote instance.

[Password] indicates the Password of the remote instance.

Create an external table

Run the following SQL statement to create an external table that has the same name and schema as the external table in the remote instance. The field names of the external table can be fewer than those of the remote instance, but the field names must be the same.

CREATE FOREIGN TABLE [$Table_Name](id int,remark text) server [$Server_Name] options (table_name '[$Table_Name]');

Note:[$Table_Name] is the name of the table for which you want to perform cross-database queries.

Cross-database query

Run the following SQL statement to perform a cross-database query:

select * from [$Table_Name];

Implement cross-Library queries by using the dblink plug-in

Tips: When the Postgres database is created in an ECS instance, the dblink plug-in does not support cross-database query between different instances.

Log on to the RDS for PostgreSQL instance and run the following SQL statement to create a dblink plug-in:
```
create extension dblink;
```
Run the following SQL statement to create a connection to the remote database:
```
select dblink_connect('[$Server_Name]','host=[$Host_Name] port=[$Port] dbname=[$DB] user=[$User] password=[$Password]');
```
Note: If you use DBlink to access different databases within the same instance, you do not need to add the host and port options.
Run the following SQL statement to perform a cross-database query:
```
SELECT *
from dblink
  ('[$Server_Name]','select *
   from [$Table_Name]') as [$Table_Name]([$Type]);
```
Note:[$Type] is the field value and Type format of the table, as shown below.
id int, name varchar(20)

Application scope

ApsaraDB RDS for PostgreSQL