Use the dblink and postgres_fdw plug-ins for cross-database operations

Precautions

Take notes of the following items before you perform cross-database operations in ApsaraDB RDS for PostgreSQL instances that use standard SSDs or ESSDs:

• If a self-managed PostgreSQL instance resides on an Elastic Compute Service (ECS) instance, and the ECS instance and your RDS instance reside in the same VPC, you can directly perform cross-database operations.
• If you want to connect a self-managed PostgreSQL instance to an Oracle or MySQL instance that resides in different VPCs, you can use the oracle_fdw or mysql_fdw plug-in.
• If you want to manage tables across databases on the same RDS instance, you must set the host parameter to localhost and the port parameter to the local port that is obtained by executing the show port statement.
• You must add the CIDR block of the VPC where your RDS instance resides to an IP address whitelist of the destination instance that you want to connect. The CIDR block of the VPC follows the 172.xx.xx.xx/16 format.
  Note You can view the CIDR block of the VPC where an RDS instance resides on the Database Connection page of the ApsaraDB RDS console.

Use the dblink plug-in

1. Create the dblink plug-in.

   create extension dblink;

2. Create a dblink connection to the destination RDS instance that resides in the same VPC as your RDS instance.

   postgres=> select dblink_connect('<The name of the connection>', 'host=<The internal endpoint used to connect to the destination RDS instance> port=<The internal port used to connect to the destination RDS instance> user=<The username used to log on to the database that you want to manage on the destination RDS instance> password=<The password used to log on to the database that you want to manage on the destination RDS instance> dbname=<The name of the database that you want to manage on the destination RDS instance>');
   
   postgres=> SELECT * FROM dblink('<The name of the connection>', '<The SQL statement to execute>') as <The name of the table to manage>(<The name of the column to manage> <The type of the column to manage>); 

   Example:

   postgres=> select dblink_connect('a', 'host=pgm-bpxxxxx.pg.rds.aliyuncs.com port=3433 user=testuser2 password=passwd1234 dbname=postgres'); 
   
   
   postgres=> select * from dblink('a','select * from products') as T(id int,name text,price numeric);  //Query a table on the destination RDS instance.
   
                           

For more information, see dblink.

Use the postgres_fdw plug-in

1. Create a database.

   postgres=> create database <The name of the database>;  //Create a database.
   
   postgres=> \c <The name of the created database>  //Switch to the database that you created.

   Example:

   postgres=> create database db1;  
   CREATE DATABASE  
   
   postgres=> \c db1  

2. Create the postgres_fdw plug-in.

   db1=> create extension postgres_fdw;

3. Create a remote database server that can connect to the destination RDS instance that resides in the same VPC as your RDS instance.

   db1=> CREATE SERVER <The name of the remote database server>                                                              
           FOREIGN DATA WRAPPER postgres_fdw  
           OPTIONS (host '<The internal endpoint used to connect to the destination RDS instance>,port '<The internal port used to connect to the destination RDS instance>', dbname '<The name of the database that you want to manage on the destination RDS instance>');   
   
   db1=> CREATE USER MAPPING FOR <The username used to log on to your RDS instance>      
           SERVER <The name of the created remote database server> 
            OPTIONS (user '<The username used to log on to the database that you want to manage on the destination RDS instance>', password '<The password used to log on to the database that you want to manage on the destination RDS instance>');  

   Example:

   db1=> CREATE SERVER foreign_server1                                                              
           FOREIGN DATA WRAPPER postgres_fdw  
           OPTIONS (host 'pgm-bpxxxxx.pg.rds.aliyuncs.com', port '3433', dbname 'postgres');  
   CREATE SERVER  
   
   db1=> CREATE USER MAPPING FOR testuser      
           SERVER foreign_server1  
           OPTIONS (user 'testuser2', password 'passwd1234');  
   CREATE USER MAPPING  

4. Import an external table.

   db1=> import foreign schema public from server foreign_server1 into <The name of the schema used by the external table>;  //Import an external table.
   
   db1=> select * from <The name of the schema used by the external table>.<The name of the external table>      //Query a remote table.

   Example:

   db1=> import foreign schema public from server foreign_server1 into ft;  
   IMPORT FOREIGN SCHEMA  
   
   db1=> select * from ft.products;  

For more information, see postgres_fdw.