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

Choose an extension

postgres_fdw is the recommended choice for ongoing cross-database integration. Use dblink for simple, one-time queries.

Prerequisites

Before you begin, ensure that you have:

• An ApsaraDB RDS for PostgreSQL instance that uses cloud disks. To purchase one, go to the ApsaraDB RDS buy page

• Both the source and destination instances in the same VPC

• The VPC CIDR block (for example, 172.XX.XX.XX/16) added to the IP address whitelist of the destination instance

You can find the VPC CIDR block on the Database Connection page in the ApsaraDB RDS console.

Connection scenarios and constraints

• Self-managed PostgreSQL on ECS: If the ECS instance and your RDS instance are in the same VPC, cross-database operations work directly without additional configuration.

• Connecting to Oracle or MySQL in a different VPC: Use the oracle_fdw or mysql_fdw extension instead.

• Cross-database operations on the same RDS instance:

  • Set the host parameter to 127.0.0.1, not localhost. Using localhost causes connection failures on IPv6-enabled instances.

  • Do not hardcode the port parameter. The port can change after Alibaba Cloud maintenance or specification changes. If you omit port, the database uses its current port automatically.

  • If you must specify a port, run SHOW PORT; first to get the current value.

Use postgres_fdw

postgres_fdw establishes a persistent connection to a remote database using three objects: a foreign server (the remote connection details), a user mapping (the credentials), and foreign tables (local representations of remote tables).

-- Create and switch to a new database
CREATE DATABASE db1;
\c db1

-- Enable postgres_fdw in this database
CREATE EXTENSION postgres_fdw;

CREATE SERVER <server_name>
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host '<internal_endpoint>', port '<port>', dbname '<remote_db>');

CREATE SERVER foreign_server1
  FOREIGN DATA WRAPPER postgres_fdw
  OPTIONS (host 'pgm-bpxxxxx.pg.rds.aliyuncs.com', port '3433', dbname 'postgres');

CREATE USER MAPPING FOR <local_user>
  SERVER <server_name>
  OPTIONS (user '<remote_user>', password '<remote_password>');

CREATE USER MAPPING FOR testuser
  SERVER foreign_server1
  OPTIONS (user 'testuser2', password 'passwd1234');

-- Import all tables from the remote public schema into schema 'ft'
IMPORT FOREIGN SCHEMA public FROM SERVER foreign_server1 INTO ft;

-- Import specific tables only
IMPORT FOREIGN SCHEMA public LIMIT TO (products, orders) FROM SERVER foreign_server1 INTO ft;

-- Query the imported foreign table
SELECT * FROM ft.products;

Use dblink

dblink lets you run a query against a remote database without creating persistent objects. Each call passes the connection details directly.

CREATE EXTENSION dblink;

-- Open a named connection to the remote database
SELECT dblink_connect('<connection_name>',
  'host=<internal_endpoint> port=<port> user=<remote_user> password=<remote_password> dbname=<remote_db>');

-- Query a table through the connection
SELECT * FROM dblink('<connection_name>', '<sql_statement>')
  AS <alias>(<column_name> <column_type>, ...);

SELECT dblink_connect('conn_a',
  'host=pgm-bpxxxxx.pg.rds.aliyuncs.com port=3433 user=testuser2 password=passwd1234 dbname=postgres');

SELECT * FROM dblink('conn_a', 'SELECT * FROM products')
  AS T(id int, name text, price numeric);

FAQ

How do I import a partitioned foreign table with postgres_fdw?

Import only the name of the parent partitioned table, not the individual partition names. postgres_fdw automatically routes queries to the correct partitions.

For example, given this partitioned table on the source instance:

-- Source instance: create a range-partitioned table
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');

On the destination instance, import and query using the parent table name only:

IMPORT FOREIGN SCHEMA public LIMIT TO (sales) FROM SERVER pg_fdw_server INTO public;
SELECT * FROM sales;