This topic describes how to use the dblink and postgres_fdw plug-ins provided with
PostgreSQL to manage tables across databases.
Background information
ApsaraDB RDS for PostgreSQL instances that use standard SSDs or enhanced SSDs (ESSDs) support the dblink and postgres_fdw plug-ins. You can use the plug-ins to manage
tables across databases on instances that reside in the same virtual private cloud
(VPC). The instances include self-managed PostgreSQL instances.
If you want to purchase an ApsaraDB RDS for PostgreSQL instance that uses standard
SSDs or ESSDs, go to the ApsaraDB RDS buy page.
Precautions
Take notes of the following items before you perform cross-database operations in
ApsaraDB RDS for PostgreSQL instances that use standard SSDs or ESSDs:
- 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 different VPCs, you can use the oracle_fdw or mysql_fdw plug-in.
- If you want to manage tables across databases on the same RDS instance, you must set
the host parameter to localhost and the port parameter to the local port that is obtained
by executing the
show port
statement.
- You must add the CIDR block of the VPC where your RDS instance resides to an IP address
whitelist of the destination instance that you want to connect. The CIDR block of
the VPC follows the
172.xx.xx.xx/16
format.
Note You can view the CIDR block of the VPC where an RDS instance resides on the
Database Connection page of the ApsaraDB RDS console.

Use the dblink plug-in
- Create the dblink plug-in.
- Create a dblink connection to the destination RDS instance that resides in the same
VPC as your RDS instance.
postgres=> select dblink_connect('<The name of the connection>', 'host=<The internal endpoint used to connect to the destination RDS instance> port=<The internal port used to connect to the destination RDS instance> user=<The username used to log on to the database that you want to manage on the destination RDS instance> password=<The password used to log on to the database that you want to manage on the destination RDS instance> dbname=<The name of the database that you want to manage on the destination RDS instance>');
postgres=> SELECT * FROM dblink('<The name of the connection>', '<The SQL statement to execute>') 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 destination RDS instance.
For more information, see dblink.
Use the postgres_fdw plug-in
- 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
- Create the postgres_fdw plug-in.
db1=> create extension postgres_fdw;
- Create a remote database server that can connect to the destination RDS instance that
resides in the same VPC as your RDS instance.
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 destination RDS instance>,port '<The internal port used to connect to the destination RDS instance>', dbname '<The name of the database that you want to manage on the destination RDS instance>');
db1=> CREATE USER MAPPING FOR <The username used to log on to your RDS instance>
SERVER <The name of the created remote database server>
OPTIONS (user '<The username used to log on to the database that you want to manage on the destination RDS instance>', password '<The password used to log on to the database that you want to manage on the destination 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
- 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.