This topic describes how to use plug-ins provided by PostgreSQL such as dblink and postgre_fdw to manage tables across databases.

Background information

ApsaraDB for RDS instances that run PostgreSQL and use SSDs support dblink and postgres_fdw plug-ins. You can use these plug-ins to manage tables across such instances (including user-created instances) that reside in the same VPC. If you want to access an RDS PostgreSQL instance that resides in a different VPC from your on-premises instance, you can use an ECS instance in your VPC to redirect access requests between the database instances.

To purchase an RDS PostgreSQL 11 instance with SSDs, click here.

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.