ApsaraDB RDS:Use the dblink and postgres_fdw extensions for cross-database operations

Usage notes

When you perform cross-database operations in RDS instances that use cloud disks, take notes of the following items:

• 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 a different VPC, you can use the oracle_fdw or mysql_fdw extension.

• If you want to manage tables across databases on the same RDS instance, take note of the following items:

  • We recommend that you explicitly set the host parameter to 127.0.0.1 instead of localhost. This helps prevent connection failures that occur on IPv6-enabled RDS instances.

  • We recommend that you do not explicitly configure the port parameter because the port number may change due to operations such as Alibaba Cloud O&M operations or specification changes. This helps prevent connection failures. If you do not explicitly configure the port parameter, the value of the port parameter of the database is used by default when you connect to the database. This ensures the validity of the connection.

  • If you want to explicitly configure the port parameter, we recommend that you connect to the database, execute the SHOW PORT; statement to query the port, and then configure the port parameter.

• You must add the CIDR block of the VPC in which your RDS instance resides, such as 172.XX.XX.XX/16, to an IP address whitelist of the destination instance that you want to connect.

  Note

  You can view the CIDR block of the VPC in which an RDS instance resides on the Database Connection page of the ApsaraDB RDS console.

Use the dblink extension

1. Create the extension.

   create extension dblink;

2. Create a dblink connection.

   postgres=> select dblink_connect('<Connection name>', 'host=<Internal endpoint of the instance in which the destination database resides in the same VPC> port=<Internal listening port of the instance in which the destination database resides in the same VPC> user=<Username to log on to the destination database> password=<Password> dbname=<Name of the destination database>');
   
   postgres=> SELECT * FROM dblink('<Connection name>', '<SQL statement>') as <Table name>(<Column name> <Column type>); 

   Examples

   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 tables in the destination database.
   
                           

For more information, see dblink.

Use the postgres_fdw extension

1. Create a database.

   postgres=> create database <Database name>; //Create a database.
   
   postgres=> \c <Database name> //Switch to the database.

   Examples

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

2. Create the postgres_fdw extension.

   db1=> create extension postgres_fdw;

3. Create a server object that is used to connect to the destination database.

   db1=> CREATE SERVER <Name of the server object>                                                              
           FOREIGN DATA WRAPPER postgres_fdw  
           OPTIONS (host '<Internal endpoint of the instance in which the destination database resides in the same VPC>,port '<Internal listening port of the instance in which the destination database resides in the same VPC>', dbname '<Name of the destination database in the same VPC>');  
   
   db1=> CREATE USER MAPPING FOR <Username used to log on to the source database>      
           SERVER <Name of the server object> 
           OPTIONS (user '<Username used to log on to the destination database>', password '<Password used to log on to the destination database>'); 

   Examples

   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 a foreign table.

   db1=> import foreign schema public from server foreign_server1 into <Schema name>; //Import the foreign table.
   
   db1=> select * from <Schema name>.<Table name>      //Table in the destination database.

   Examples

   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.

FAQ

When I use the postgres_fdw extension to access an partitioned foreign table, how do I import the table?

You need to only import the name of the partitioned table to the destination instance.

The following code provides an example on how to import the partitioned table named Range Partition:

-- The source database on the source instance.
CREATE TABLE sales (id int, p_name text, amount int, sale_date date) PARTITION BY RANGE (sale_date);
CREATE TABLE sales_2022_Q1 PARTITION OF sales FOR VALUES FROM ('2022-01-01') TO ('2022-03-31');
CREATE TABLE sales_2022_Q2 PARTITION OF sales FOR VALUES FROM ('2022-04-01') TO ('2022-06-30');
CREATE TABLE sales_2022_Q3 PARTITION OF sales FOR VALUES FROM ('2022-07-01') TO ('2022-09-30');
CREATE TABLE sales_2022_Q4 PARTITION OF sales FOR VALUES FROM ('2022-10-01') TO ('2022-12-31');

INSERT INTO sales VALUES (1,'prod_A',100,'2022-02-02');
INSERT INTO sales VALUES (2,'prod_B', 5,'2022-05-02');
INSERT INTO sales VALUES (3,'prod_C', 5,'2022-08-02');
INSERT INTO sales VALUES (4,'prod_D', 5,'2022-11-02');

-- Import only the name of the partitioned table to the destination RDS instance. 
import FOREIGN SCHEMA public limit to (sales) from server pg_fdw_server into public;
select * from sales;

The following figure shows the sample output.