This topic describes how to use the dblink and postgre_fdw plug-ins provided with PostgreSQL to manage tables across databases.

Background information

ApsaraDB for RDS instances that run PostgreSQL based on standard or enhanced SSDs support the dblink and postgres_fdw plug-ins. You can use these plug-ins to manage tables across databases on instances that reside in the same VPC. These instances include user-created PostgreSQL instances. If you want to access an RDS for PostgreSQL instance that resides in a different VPC, you can use an ECS instance in your VPC to redirect access requests between the database instances.

To purchase an RDS instance that runs PostgreSQL 11 based on standard or enhanced SSDs, click here.

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.