Use dblink and postgre_fdw for cross-database operations

Precautions

For cross-database operations, note the following items:

• If a user-created ECS-based PostgreSQL instance resides in the same VPC as your RDS PostgreSQL instance, you can directly manage tables across these database instances.
• If a user-created ECS-based PostgreSQL instance resides in a different VPC from your RDS PostgreSQL instance, an ECS instance in your VPC can redirect access requests between the database instances, allowing you to manage tables across them.
• You can use the oracle_fdw or mysql_fdw plug-in to establish connections between user-created PostgreSQL instances and Oracle or MySQL instances that reside in different VPCs.

Use dblink

1. Create a dblink plug-in.

   create extension dblink;

2. Create a dblink connection to a remote RDS PostgreSQL instance that resides in the same VPC as your on-premises database.

   postgres=> select dblink_connect('<The name to use for this connection>', 'host=<The internal endpoint of the remote RDS instance> port=<The internal port number of the remote RDS instance> user=<The username to log on to the target remote database> password=<The password to log on to the target remote database> dbname=<The name of the target remote database>');
   
   postgres=> SELECT * FROM dblink('<The name of the created connection>', '<The SQL command to run>') 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 in the RDS instance.
   
   						

For more information, see dblink.

Use postgres_fdw

1. Create a database.

   postgres=> create database <The name to use for this 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 a postgres_fdw plug-in.

   db1=> create extension postgres_fdw;

3. Create a remote database server to connect to a remote RDS PostgreSQL instance that resides in the same VPC as your on-premises database.

   db1=> CREATE SERVER <The name to use for this remote database server>                                                              
           FOREIGN DATA WRAPPER postgres_fdw  
           OPTIONS (host '<The internal endpoint of the remote RDS instance>,port '<The internal port number of the remote RDS instance>', dbname '<The name of the target remote database>');  
   
   db1=> CREATE USER MAPPING FOR <The username to log on to your on-premises database>      
           SERVER <The name of the created remote database server> 
           OPTIONS (user '<The username to log on to the target remote database>', password '<The password to log on to the target remote database>');  

   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 that defines the external table>;  //Import an external table.
   
   db1=> select * from <The name of the schema to use>. <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.