postgres_fdw Extension for Cross-database Access

마지막 업데이트 시간:2025-06-09 10:26:49
postgres_fdw Extension for Cross-database Access
마지막 업데이트 시간: 2025-06-09 10:26:49
Cross-database access refers to the process of performing data reading, writing, and joint operations on data in other libraries within this instance or in other instances. The target objects of cross-database access are collectively referred to as external data sources.
TencentDB for PostgreSQL provides a plug-in for accessing external data sources, which is used to implement and enhance cross-database access capabilities. There are two kinds of external access plug-ins:
Type
Plugins
Isomorphic cross-database access extension
dblink,postgresql_fdw
Heterogeneous cross-database access extension
mysql_fdw,cos_fdw
Directions for cross-database access
1. Use the "CREATE EXTENSION plug-in name;" statement to install a plug-in.
2. Create an external server object for each remote database they want to connect to and create a link mapping.
3. Use the corresponding command to access external tables to retrieve data.
Additionally, without constraints, the powerful access capability of the cross-database access extension may bring certain potential security risks. Therefore, TencentDB for PostgreSQL has optimized permission control and manages classification according to the environment where the target instance is located. Additional auxiliary parameters have been added on the basis of the open-source version to verify user identity and adjust network policies. For details, reference Plugin Auxiliary Parameters.
Note:
The dblink plug-in is only supported in the kernel of TencentDB for PostgreSQL with a major version of 10 or higher. Please be informed.
Plugin Auxiliary Parameters
host
Required items for performing cross-instance access. The IP address of the target instance.
port
Required items for performing cross-instance access. The port number of the target instance.
instanceid
Instance ID
Cross-instance access between TencentDB for PostgreSQL is a required item. The format is similar to postgres-xxxxxx, pgro-xxxxxx, which can be viewed in the instance list on the console.
If the target instance is on Tencent Cloud CVM, fill in the instance ID of the CVM instance, with a format similar to ins-xxxxx.
dbname 
database name. Fill in the database name of the remote PostgreSQL service you need to access. If you do not perform cross-instance access and only perform cross-database access within the same instance, you only need to configure this parameter, and other parameters can be left blank.
access_type
Optional. The types that the target instance belongs to are as follows:
Value is 1: The target instance is a TencentDB instance, including TencentDB for PostgreSQL, TencentDB for MySQL, etc. If not specified, this item is selected by default.
Value is 2: The target instance is on a Tencent Cloud CVM machine. 
Value is 3: The target instance is self-built on the public network.
Value is 4: The target instance is a connected instance of Cloud VPN.
Value is 5: The target instance is accessed through a self-built VPN.
Value is 6: The target instance is a Direct Connect-enabled instance.
uin
Required. The account ID to which the instance belongs. User permissions are identified through this information. See Query uin.
own_uin
Not required. The root account ID to which the instance belongs also requires this information for user permission identification.
vpcid
Optional. VPC ID. If the target instance is in the VPC network of Tencent Cloud CVM, this parameter is required. It can be viewed in the VPC console.
subnetid
Optional. The subnet ID of the VPC. If the target instance is in the VPC network of Tencent Cloud CVM, this parameter is required. It can be viewed in the subnet of the VPC console .﻿
dcgid
Optional. Dedicated line ID. If the target instance needs to connect via a dedicated network, this parameter value is required.
vpngwid
Not required. VPN gateway ID. If the target instance needs to connect through a VPN, this parameter value is required.
region
Optional. The region where the target instance is located. For example, "ap-guangzhou" refers to Guangzhou. If cross-region data access is required, this parameter value is needed.
Using Postgres_fdw Example
The postgres_fdw plug-in can be used to access data from other databases in this instance or from other PostgreSQL instances.
Step 1: Prerequisite
1. Create a test database in this instance.
postgres=>create role user1 with LOGIN  CREATEDB tencentdb_superuser  PASSWORD 'password1';
CREATE ROLE
postgres=>create database testdb1;
CREATE DATABASE
Note:
If an error occurs while creating a plugin, submit a ticket to contact Tencent Cloud After-sales Service for assistance.
2. Create test data in the target instance.
postgres=>create role user2 with LOGIN  CREATEDB PASSWORD 'password2';
postgres=> create database testdb2;
CREATE DATABASE
postgres=# grant all on database testdb2 to user2;
GRANT
postgres=> \\c testdb2 user2
You are now connected to database "testdb2" as user "user2".
Testdb2=> create schema test_schema;
CREATE SCHEMA
testdb2=> create table test_schema.test_table2(id integer);
CREATE TABLE
testdb2=> insert into test_schema.test_table2 values (1);
INSERT 0 12
Step 2: Create the Postgres_fdw Plug-In
Note:
If a prompt indicates that the plug-in does not exist or there are insufficient permissions when creating a plug-in, submit a ticket for handling.
#Create
postgres=> \\c testdb1
You are now connected to database "testdb1" as user "user1".
testdb1=> create extension postgres_fdw;
CREATE EXTENSION
#View
testdb1=> \\dx
                            List of installed extensions
      Name     | Version |   Schema   |                    Description
--------------+---------+------------+----------------------------------------------------
  plpgsql      | 1.0     | pg_catalog | PL/pgSQL procedural language
  postgres_fdw | 1.1     | public     | foreign-data wrapper for remote PostgreSQL servers
(2 rows)
Step 3: Creating a SERVER
Note:
Only the Linux kernel versions v10.17_r1.2, v11.12_r1.2, v12.7_r1.2, v13.3_r1.2, v14.2_r1.0 and later support access across instances.
Cross-instance access.
# Access the data of the access target testdb2 from the testdb1 in this instance
testdb1=>create server srv_test1 foreign data wrapper postgres_fdw options (host 'xxx.xxx.xxx.xxx',dbname 'testdb2', port '5432', instanceid 'postgres-xxxxx');
CREATE SERVER
Access across databases without cross-instance access, just need to fill in the dbname parameter.
# Access the data of testdb2 in this instance from testdb1 in this instance
create server srv_test1 foreign data wrapper postgres_fdw options (dbname 'testdb2');
The target instance is on a Tencent Cloud CVM with a basic network type.
  testdb1=>create server srv_test foreign data wrapper postgres_fdw options (host 'xxx.xxx.xxx.xxx', dbname 'testdb2', port '5432', instanceid 'ins-xxxxx', access_type '2', region 'ap-guangzhou',uin 'xxxxxx',own_uin 'xxxxxx');
  CREATE SERVER
The target instance is on a Tencent Cloud CVM, and the network type is a private network.
  testdb1=>create server srv_test1 foreign data wrapper postgres_fdw options (host 'xxx.xxx.xxx.xxx',dbname 'testdb2', port '5432', instanceid 'ins-xxxxx', access_type '2', region 'ap-guangzhou', uin 'xxxxxx', own_uin 'xxxxxx', vpcid 'vpc-xxxxxx', subnetid 'subnet-xxxxx');
  CREATE SERVER
The target instance is self-built on the public network.
  testdb1=>create server srv_test1 foreign data wrapper postgres_fdw options (host 'xxx.xxx.xxx.xxx',dbname 'testdb2', port '5432', access_type '3', region 'ap-guangzhou', uin 'xxxxxx', own_uin 'xxxxxx');
  CREATE SERVER 
The target instance is a connected instance of Tencent Cloud VPN.
  testdb1=>create server srv_test1 foreign data wrapper postgres_fdw options (host 'xxx.xxx.xxx.xxx',dbname 'testdb2', port '5432', access_type '4', region 'ap-guangzhou', uin 'xxxxxx', own_uin 'xxxxxx', vpngwid 'xxxxxx');
The target instance is in a self-built VPN access instance.
  testdb1=>create server srv_test1 foreign data wrapper postgres_fdw options (host 'xxx.xxx.xxx.xxx',dbname 'testdb2', port '5432', access_type '5', region 'ap-guangzhou', uin 'xxxxxx', own_uin 'xxxxxx', vpngwid 'xxxxxx');   
The target instance is an instance connected to Tencent Cloud DC.
  testdb1=>create server srv_test1 foreign data wrapper postgres_fdw options (host 'xxx.xxx.xxx.xxx',dbname 'testdb2', port '5432', access_type '6', region 'ap-guangzhou', uin 'xxxxxx', own_uin 'xxxxxx', dcgid 'xxxxxx');    
  CREATE SERVER       
Step 4: Creating User Mapping
testdb1=> create user mapping for user1 server srv_test1 options (user 'user2',password 'password2');
CREATE USER MAPPING
Step 5: Creating an External Table
testdb1=> create foreign table foreign_table1(id integer) server srv_test1 options(schema_name ‘test_schema’,table_name'test_table2');
CREATE FOREIGN TABLE
Note:
For cross-database access within the same instance, just fill in the name of the target table (table_name). No need to specify the schema_name parameter.
Step 6: Accessing External Data
testdb1=> select * from foreign_table1;
  id
----
   1
(1 row)
Reference Link
﻿Official introduction to postgres_fdw﻿
﻿Creating 9.3 Version SERVER﻿
﻿Creating 9.5 Version SERVER﻿
﻿Creating 10 Version SERVER﻿
﻿Creating 11 Version SERVER﻿
﻿Creating 12 Version SERVER﻿
﻿Creating 13 Version SERVER﻿
﻿Creating 14 Version SERVER 
Using a Dblink Example
Step 1: Create a Dblink Plug-In
postgres=> create extension dblink;
postgres=> \\dx
                                         List of installed extensions
        Name        | Version |   Schema   |                            Description                            
--------------------+---------+------------+-------------------------------------------------------------------
 dblink             | 1.2     | public     | connect to other PostgreSQL databases from within a database
 pg_stat_log        | 1.0     | public     | track runtime execution statistics of all SQL statements executed
 pg_stat_statements | 1.6     | public     | track execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(4 rows)
Step Two: Creating a Dblink Link
select dblink_connect('yunpg1','host=10.10.10.11 port=5432 instanceid=postgres-2123455r dbname=postgres access_type=1 user=dbadmin password=P302!');
 dblink_connect 
----------------
 OK
(1 row)
Step 3: Accessing External Data
postgres=> select * from dblink('yunpg1','select catalog_name,schema_name,schema_owner from information_schema.schemata') as t(a varchar(50),b varchar(50),c varchar(50));
    a     |         b          |    c    
----------+--------------------+---------
 postgres | pg_toast           | user_00
 postgres | pg_temp_1          | user_00
 postgres | pg_toast_temp_1    | user_00
 postgres | pg_catalog         | user_00
 postgres | public             | user_00
 postgres | information_schema | user_00
(6 rows)
Reference Link
﻿Official introduction to dblink﻿
Using Mysql_fdw Example
Step 1: Create a Mysql_fdw Plug-In
postgres=> create extension mysql_fdw;
CREATE EXTENSION
postgres=> \\dx;
                                            List of installed extensions
        Name        | Version |   Schema   |                              Description                               
--------------------+---------+------------+------------------------------------------------------------------------
 dblink             | 1.2     | public     | connect to other PostgreSQL databases from within a database
 mysql_fdw          | 1.1     | public     | Foreign data wrapper for querying a MySQL server
 pg_stat_log        | 1.0     | public     | track runtime execution statistics of all SQL statements executed
 pg_stat_statements | 1.9     | public     | track planning and execution statistics of all SQL statements executed
 plpgsql            | 1.0     | pg_catalog | PL/pgSQL procedural language
(5 rows)
Step Two: Creating a SERVER
postgres=> CREATE SERVER mysql_svr  FOREIGN DATA WRAPPER mysql_fdw OPTIONS (host '171.16.10.13',port '3306',instanceid 'cdb-l1d95grp',uin '100026380431');
CREATE SERVER
Step Three: Creating External User Map
postgres=> CREATE USER MAPPING FOR PUBLIC SERVER mysql_svr OPTIONS (username 'fdw_user',password 'Secret!123');
CREATE USER MAPPING
Step Four: Accessing External Data
Note:
The mysql database that this instance wants to connect to must have at least one table before the table structure can be imported through IMPORT FOREIGN SCHEMA.
postgres=> IMPORT FOREIGN SCHEMA hrdb FROM SERVER mysql_svr INTO public;
Reference Link
﻿Official introduction to mysql_fdw﻿
Using Cos_fdw Example
For usage examples of cos_fdw, refer to the documentation Supporting hierarchical storage capacity through the cos_fdw plugin.
Usage Notes
Target instance. Pay attention to the following points.
1. Require lifting PostgreSQL's hba restrictions and allowing created mapped users (such as user2) to access using the MD5 method. For hba modification, refer to PostgreSQL official documentation.
2. If the target instance is not a TencentDB instance and has a Hot Standby Mode set up, after a primary/replica switch, you need to manually update the server connection address or recreate the server.
문제 해결에 도움이 되었나요?
더 자세한 내용은 문의하기 또는 티켓 제출 을 통해 문의할 수 있습니다.
예
아니오
피드백
Type	Plugins
Isomorphic cross-database access extension	dblink,postgresql_fdw
Heterogeneous cross-database access extension	mysql_fdw,cos_fdw
tencent cloud

Plugin Auxiliary Parameters

Using Postgres_fdw Example

Step 1: Prerequisite

Step 2: Create the Postgres_fdw Plug-In

Step 3: Creating a SERVER

Step 4: Creating User Mapping

Step 5: Creating an External Table

Step 6: Accessing External Data

Reference Link

Using a Dblink Example

Step 1: Create a Dblink Plug-In

Step Two: Creating a Dblink Link

Step 3: Accessing External Data

Reference Link

Using Mysql_fdw Example

Step 1: Create a Mysql_fdw Plug-In

Step Two: Creating a SERVER

Step Three: Creating External User Map

Step Four: Accessing External Data

Reference Link

Using Cos_fdw Example

Usage Notes
