How to Enable dblink and postgres_fdw Permissions in ApsaraDB RDS for PostgreSQL 11

By digoal

Background

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:

Same VPC ECS (oracle, mysql)/rds mysql
Same VPC ECS (port forwarding) —> other VPC/network mysql | oracle

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.

Examples

dblink

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)

postgres_fdw

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)

Community

How to Enable dblink and postgres_fdw Permissions in ApsaraDB RDS for PostgreSQL 11

Background

Examples

dblink

postgres_fdw

References

Read previous post:

Read next post:

digoal

You may also like

Comments

digoal

Related Products

Database Overview

Oracle Database Migration Solution

PolarDB for PostgreSQL

PolarDB for MySQL