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

Precautions

Before you perform cross-database operations, consider the following items:

• If a user-created ECS-based PostgreSQL instance resides in the same VPC as your RDS for PostgreSQL instance, you can directly manage tables across these database instances.
• An ECS instance in your VPC can be used to redirect access requests between database instances. This applies if you want to manage tables across your RDS for PostgreSQL instance and a user-created ECS-based PostgreSQL instance that resides in a different VPC.
• You can use the oracle_fdw or mysql_fdw plug-in to connect a user-created PostgreSQL instance and an Oracle or MySQL instance that reside in different VPCs.
• If you manage tables across databases on the same RDS for PostgreSQL instance, you must set the host parameter to localhost and the port parameter to the local port that is obtained by running the show port command.

Use dblink

1. Create the dblink plug-in.

   create extension dblink;

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

   postgres=> select dblink_connect('<The name of the connection>', 'host=<The internal endpoint used to connect to the remote RDS instance> port=<The internal port used to connect to the remote RDS instance> user=<The username used to log on to the target database on the remote RDS instance> password=<The password used to log on to the target database on the remote RDS instance> dbname=<The name of the target database on the remote RDS instance>');
   
   postgres=> SELECT * FROM dblink('<The name of the 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 on the remote RDS instance.
   
                           

For more information, see dblink.

Use postgres_fdw

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 a remote RDS for PostgreSQL instance that resides in the same VPC as your source database.

   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 remote RDS instance>,port '<The internal port used to connect to the remote RDS instance>', dbname '<The name of the target database on the remote RDS instance>');  
   
   db1=> CREATE USER MAPPING FOR <The username used to log on to your source database>      
           SERVER <The name of the created remote database server> 
           OPTIONS (user '<The username used to log on to the target database on the remote RDS instance>', password '<The password used to log on to the target database on the remote 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.