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.
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
- Create the dblink plug-in.
- 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
- 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 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
- 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.