ApsaraDB RDS for PostgreSQL 11 supports dblink and postgres_fdw for instances in the same virtual private cloud (VPC). You can access a user-defined PostgreSQL instance in the same VPC. However, you can still access the user-defined PostgreSQL instances in an external VPC using the port redirection of Elastic Compute Service (ECS) in the same VPC.
Entry to purchase ApsaraDB RDS for PostgreSQL 11: https://rds-buy.aliyun.com/rdsBuy?spm=5176.7920951.1393245.1.41c64ce1pOvLll&aly_as=JoVfjAtF#/create/rds?initData=%7B%22data%22:%7B%22rds_dbtype%22:%22PostgreSQL%22%7D%7D
ApsaraDB RDS for PostgreSQL 11 (postgres_fdw, dblink) can access the following resources:
1) Same VPC ECS/ApsaraDB RDS for PostgreSQL
2) Same VPC ECS (port forwarding) —> other VPV/network PostgreSQL
3) Same VPC ECS (self-built PostgreSQL and oracle_fdw, mysql_fdw) —> other VPC/network oracle | mysql
ApsaraDB RDS for PostgreSQL 11, which will support oracle_fdw and mysql_fdw, can access the following resources:
Using port forwarding, an ECS instance in the same VPC can access any target network that an ECS instance can access. As long as you can access ECS, you can access RDS for PostgreSQL through this port proxy.
For more information about port forwarding, see references.
postgres=> select dblink_connect('a', 'host=同一vpc下的另一rds的内网域名 port=同一vpc下的另一rds的内网监听端口 user=远程数据库用户名 password=密码 dbname=库名'); dblink_connect ---------------- OK (1 row) postgres=> select version(); version ----------------- PostgreSQL 11.5 (1 row) postgres=> SELECT * FROM dblink('a', 'SELECT version()') as t(ver text); ver ------------------ PostgreSQL 10.10 (1 row)
Create a database.
postgres=> create database db1; CREATE DATABASE postgres=> \c db1
Create the postgres_fdw plug-in.
db1=> create extension postgres_fdw; CREATE EXTENSION
New remote database server:
db1=> CREATE SERVER foreign_server FOREIGN DATA WRAPPER postgres_fdw OPTIONS (host '同一vpc下的另一rds的内网域名 port=同一vpc下的另一rds的内网监听端口', port '同一vpc下的另一rds的内网监听端口', dbname '库名'); CREATE SERVER db1=> CREATE USER MAPPING FOR digoal SERVER foreign_server OPTIONS (user '远程数据库用户', password '密码'); CREATE USER MAPPING
Import an external table.
db1=> import foreign schema public from server foreign_server into ft; IMPORT FOREIGN SCHEMA db1=> \det ft.* List of foreign tables Schema | Table | Server --------+--------------------+---------------- ft | customer1 | foreign_server ft | district1 | foreign_server ft | ha_health_check | foreign_server ft | history1 | foreign_server ft | item1 | foreign_server ft | new_orders1 | foreign_server ft | order_line1 | foreign_server ft | orders1 | foreign_server ft | pg_stat_statements | foreign_server ft | stock1 | foreign_server ft | warehouse1 | foreign_server (11 rows)
PostgreSQL: Multi-node (Quorum-based), Zero Data Loss, and HA (Failover, Switchover) Solution
Small-scale Real-time Data Warehouse: How ApsaraDB RDS for PostgreSQL Supports mysql_fdw
digoal - April 29, 2021
digoal - April 30, 2021
digoal - October 12, 2022
digoal - March 20, 2019
digoal - December 14, 2018
digoal - May 16, 2019
Fully managed and less trouble database servicesLearn More
Migrate your legacy Oracle databases to Alibaba Cloud to save on long-term costs and take advantage of improved scalability, reliability, robust security, high performance, and cloud-native features.Learn More
An online MPP warehousing service based on the Greenplum Database open source programLearn More
Connect your VPCs to services in other VPCs through secure, reliable, and private connections.Learn More
More Posts by digoal